This series of articles has focused on Cost-Based Optimizer (CBO) hints or directives to the cost-based optimizer (CBO) on manual directions for the explain plan (be sure to read part 1, part 2 and part 3). In this final article, let’s take a look at the hints that I do use. QB_NAME Hint I use the QB_NAME hint quite […]
Read More
I have had to use far fewer hints starting with Oracle10. This optimizer takes a much closer look at index processing than all prior Oracle databases. I have found I simply don’t need index hints of any kind anymore. Try this during your testing. This setting will cause the CBO to ignore any hint […]
Read More
This article series will focus using Oracle cost-based optimizer (CBO) hints, and this second article on producing a series of hints that will replicate an exact explain plan. In Part 1, we discussed the OUTLINE produced by DBMS_XPLAN.Display(FORMAT=>’OUTLINE’), I find the IGNORE_OPTIM_EMBEDDED_HINTS to be an interesting hint to appear here. This hint says to ignore all hints. […]
Read More
This article series will focus using Oracle cost-based optimizer (CBO) hints, and this first article on producing a series of hints that will replicate an exact explain plan. This technique might be useful for some problem SQL but this technique is also a useful learning tool for hints. Remember, the DBMS_XPAN.Display procedure can be run […]
Read More
In the 1990s and early 2000s, it was considered common knowledge that Oracle databases were not very good at pure number crunching. As a result, many people used C-based and other external methods to perform extensive calculations. Eventually, Oracle introduced a group of datatypes that could finally provide the required level of performance: BINARY_INTEGER, BINARY_FLOAT, […]
Read More
One of the most typical problems when processing textual information is filtering out good characters from bad characters. The approach of splitting strings into single-symbol arrays and validating them against a given list was outdated many years ago. But even when using the built-in functionality provided by Oracle, you need to understand the strengths and […]
Read More
This is the fourth of an article series on advanced Oracle explain plan techniques (read Part 3 here). In this article, we will finish the exploration of DBMS_XPLAN format options with a focus on the ‘ALLSTATS LAST’ formatting option. In the example above, notice that the SQL text has a hint: /+* GATHER_PLAN_STATISTICS */ . I have shown this in […]
Read More
This is the third of an article series on advanced Oracle explain plan techniques (read Part 2 here), continuing where we left off: a discussion of the Oracle DBMS_XPLAN format options. This article will illustrate the ‘ALL STATS LAST’ as well as the ‘ADVANCED’ formatting options. Notice above that the SQL text has a hint: /+* GATHER_PLAN_STATISTICS */ . I […]
Read More
This is the first in an article series that will illustrate some advanced SQL explain plan techniques that were introduced with Oracle 10g. Oracle 10g was a big change for the Oracle optimizer, the code that tries to determine the best path to retrieve the requested data. Changes include how Oracle uses indexes and just a […]
Read More
Scalability, flexibility and agility are the cornerstones of successful cloud deployments. Customers across the globe have relied on Oracle Database Real Application Clusters (RAC) for their database cloud systems. Oracle 12c comes with significant enhancements, including scalability and high availability with such features as Flex Clusters, Flex ASM, Application Continuity and Transaction Guard. We brought together […]
Read More
Provisioning a new server has become increasingly easy with virtualization. Provisioning data, however, can be more challenging, especially in Agile shops where the volume of environments required to support dev and testing can easily go beyond the abilities of your IT operations to keep up. Is there any way to make it easier? We brought […]
Read More
Be it SQL Server or the improved version of SQL Server on the cloud called as SQL Azure, when it comes to monitoring, there is nothing as powerful as DMVs (Dynamic Management Views). For a primer, Dynamic Management Views (DMVs) are a set of virtual views which expose the internal states of SQL Server deployment. […]
Read More