Search

Top 60 Oracle Blogs

Recent comments

Hints

Manual Optimisation

Here’s an example of “creative SQL” that I wrote in response to a question on OTN about combining data from two indexes to optimise access to a table. It demonstrates the principle that you can treat an index as a special case of a table – allowing you to make a query go faster by referencing the same table more times.

Unfortunately you shouldn’t use this particular example in a production system because it relies on the data appearing in the right order without having an “order by” clause. This type of thing makes me really keen to have a hint that says something like: /*+ qb_name(my_driver) assume_ordered(@my_driver) */ so that you could tell the optimizer that it can assume that the rowset from a given query block will appear in the order of the final “order by” clause.

DBMS_PARALLEL_EXECUTE

Here is a recent thread from OTN forums. The question raised is WTF? whether a hint in the examples section of DBMS_PARALLEL_EXECUTE documentation have to be used or not and why it is there. What do you think about it? Have a look in the documentation and mark your choice in a poll: PS. In [...]

Ignoring Hints

I’ve previously published a couple of notes (here and here) about the driving_site() hint. The first note pointed out that the hint was deliberately ignored if you write a local CTAS or INSERT that did a remote query. I’ve just found another case where the hint is ignored – this time in a simple SELECT [...]

Cursor Sharing 3

Here’s a simple piece of code demonstrating an irritating problem. I’ve created a table, a function-based index, collected stats (without histograms), and then run a query that should use that index – but doesn’t. execute dbms_random.seed(0) create table t1 as with generator as ( select --+ materialize rownum id from dual connect by rownum <= [...]

Rule Rules

Everybody knows you shouldn’t be using the Rule-based optimizer (RBO) any more – everyone, that is, except some of the folks at Oracle Corp. I had a conversation a few weeks ago with someone who was having a problem with their standby database on 10.2 because a query against v$archive_gap was taking a very long [...]

Hinting Dynamically Generated SQL in Application Engine

One of the clever things you can do in Application Engine is dynamically generate parts of a SQL statement.  However, this can produce challenges should decide that you also need to add an optimiser hint to a SQL statement.  In this post I want to demonstrate how you can also use the same techniques to dynamically generate valid Optimizer hints.

The following statement was generated by a step in a delivered Application Engine that had not been previously been changed.  The PS_XXX tables are custom tables, but the references are in custom meta-data that is used to dynamically generate the SQL.  I have added the hints and comments. 

The specific hints and comments are not important here, the point is how I managed to generate them.

Note that:

Hinting Dynamically Generated SQL in Application Engine

One of the clever things you can do in Application Engine is dynamically generate parts of a SQL statement.  However, this can produce challenges should decide that you also need to add an optimiser hint to a SQL statement.  In this post I want to demonstrate how you can also use the same techniques to dynamically generate valid Optimizer hints.

The following statement was generated by a step in a delivered Application Engine that had not been previously been changed.  The PS_XXX tables are custom tables, but the references are in custom meta-data that is used to dynamically generate the SQL.  I have added the hints and comments. 

The specific hints and comments are not important here, the point is how I managed to generate them.

Note that:

Hinting Sub-Queries on Oracle

This posting is a purely Oracle RDBMS discussion about how to correctly apply hints to sub-queries. However, it is particularly relevant to PeopleSoft, which it makes extensive use of correlated sub-queries. The point of this story is not the particular hints that I applied, but where I placed the hints, and how I scoped them to the sub-queries.

The following SQL extract is from the delivered Global Payroll GPGB_EDI process (although I have already made some minor changes, and PS_GP_RSLT_PIN is partitioned). Notice that each sub-query joins two tables together. PS_GP_RSLT_PIN is second largest of Global Payroll result tables. PS_GP_PIN is a look-up table, and the criterion on PIN_CODE will only return a single row.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
UPDATE Table(GPGB_EDIE_TMP) X

Hinting Sub-Queries on Oracle

This posting is a purely Oracle RDBMS discussion about how to correctly apply hints to sub-queries. However, it is particularly relevant to PeopleSoft, which it makes extensive use of correlated sub-queries. The point of this story is not the particular hints that I applied, but where I placed the hints, and how I scoped them to the sub-queries.

The following SQL extract is from the delivered Global Payroll GPGB_EDI process (although I have already made some minor changes, and PS_GP_RSLT_PIN is partitioned). Notice that each sub-query joins two tables together. PS_GP_RSLT_PIN is second largest of Global Payroll result tables. PS_GP_PIN is a look-up table, and the criterion on PIN_CODE will only return a single row.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
UPDATE Table(GPGB_EDIE_TMP) X