Oracle outline explain execution plan

I have a stored outline in the DBA_OUTLINES. I have two nearly identical SQLs that both use the Outline, as can be seen in V$SQL OUTLINE_CATEGORY column. There is only one entry in the DBA_OUTLINES. But the explain plan as shown in v$sql_plan table are different. The Category is TEST01.

Alter system set use_stored_outlines = TEST01;

Why are the explain plans different if the outline is used ?

My goal is to have the SQL executed use the same plain. The slower SQL is part of a vendor application and can not be modified. How do I make that happen.

Before this test was run : the shared_pool was flushed


V_SQL.xls              is the select from V$SQL
d295jrha2mqtx.xls         is the select from v$sql_plan slower sql
bx2mmbtxgm46q.xls        is the select from v$sql_plan faster sql

DBA_OUTLINES.xls             select * from DBA_OUTLINES.xls
DBA_OUTLINE_HINTS.xls       select DBAH.*,to_char(DBAH.HINT)from DBA_outline_hints DBAH;

OL.xls                        Select * from outln.OL$      
OL_HINTS.xls                  select * from outln.OL$HINTS
OL_NODES.xls                  select * from outln.OL$NODES

Oracle Database 10g Enterprise Edition Release - 64bi
PL/SQL Release - Production
CORE      Production
TNS for HPUX: Version - Production
NLSRTL Version - Production
Who is Participating?
AkenathonConnect With a Mentor Commented:
After you have qualified every object, there is really no reason I can think of for reuse to depend on the parsing schema. Some weird things that can change depending on the user are for example, that you use VPD (virtual private database), or that you are calling some PL/SQL that depends on the user for its results, or that your view depends on the connected user (common as a poor man's VPD, filtering who can see what). However, I doubt that the CBO has access to any statistics on those... you cannot e.g. have a function index to provide it with the info (because the results would vary when the connected user varies, so it would not be a deterministic function).

Let's see the bright side: we've managed to narrow down the problem quite a bit. Ruling out outlines, qualifying every object and getting the same SQL_ID were all important steps.

One question before you pack the testcase for support: If you run the FAST version (meaning you are connected as ECKERNEL_USP), does it still get slow if you just set your current schema to PKEEGAN? Or is it only when you open another connection as PKEEGAN? I'm looking for a minimal, subtler difference than the connected user.

Remember you can always wrap the SQL as a pipelined PL/SQL function or something in ECKERNEL_USP and invoke the function from other connections. They will hopefully go fast, since they are executed in the context of the function owner. I know it's not the best solution, but it looks like the best approach for a quick fix.

What about creation scripts? Even the fast plan is not too efficient... you might be able to create a custom index and make it more optimal (for any parsing user!).
The outlines need an exact textual match. That is not happening, or else your sqlids would be the same. DBA_OUTLINES shows only one row, for only one of your almost-identical SQLs. To know which one it is, you can use DBMS_OUTLN_EDIT.GENERATE_SIGNATURE.

For a solution, you need to generate another outline for the other statement after adjusting whatever you need to make Oracle choose the execution plan you like. For instance, open an interactive session, change some optimizer parameters like optimizer_index_cost_adj etc., and when the query runs fine use CREATE OUTLINE to store it.

However, the best approach is to tune your schema: even your "fast SQL" shows an INDEX FAST FULL SCAN. You might be able to choose another indexing strategy there, one that renders your outlines unnecessary.
Aaron ShiloChief Database ArchitectCommented:
hi start here

-- Enable stored outlines.
ALTER SESSION SET query_rewrite_enabled=TRUE;

-- Issue both statements again.
SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
SELECT e.empno, e.ename, d.dname, e.job FROM emp e, dept d WHERE e.deptno = d.deptno AND d.dname = 'SALES';

-- Check if the outlines have been used.
SELECT name, category, used FROM user_outlines;

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

fpkeeganAuthor Commented:
The code does not need to be an exact match for explain plans to work.  Oracle has changed that requirememnt. They are using  space compression, to before  the compare. The explain plan is be selected as can be see by the  V$SQL OUTLINE_CATEGORY .
The question is why are the plans different ?

I will check the ALTER SESSION SET query_rewrite_enabled setting. But the ALTER SESSION SET use_stored_outlines is being set,  

The plan is being used the used plan is being set, please look at the DBA_outline spread sheet. please see the provided data.

fpkeeganAuthor Commented:
Why does query_rewrite_enabled need to be set to true if there are no materilized views ?
Agreed, textual match is no longer a requirement. However, checking the signature won't hurt anyway, even V$SQL says the outline has been applied -somewhere something is clearly not working.

There is a number of reasons why the CBO cannot or will not honour a hint, including different session environment, different schemas (e.g. one object maps to a different ID on each SQL, etc.), that's why I propose you re-create the outline using the SQL you want to hint (I mean the exact text, user executing, session parameters, etc.).

query_rewrite_enabled is just another explicitly documented requirement for outlines to work, as stated in I guess injecting the hints is considered rewriting part of your SQL (even when syntactically you're just adding comments).

Please read this artice, it's a step by step guide to doing exactly what I understand you are doing.
fpkeeganAuthor Commented:

Thanks for the second link. I am working on following the write up to see If I can get the outline
to work
fpkeeganAuthor Commented:

The write-up will not work for me. It assumes that you can login to the schema. The schema for the bad SQL is an application schema. No one can use this schema but the application. All the other schemas that have access to the REAL underlying schema use synonyms to access it.

I have tried many times to create a matching SQL that will generate the same SQL_ID. The SQL I create is the same text but has a different SQL_ID. I just can not get it to match the same SQL_ID in a different SCHEMA. So it will not be possible to create an outline for the BAD SQL.  How do you generate an outline for SQL that you can not access the Actual SCHEMA and can not generate a BAD outline for.

Is there a way to manually build the complete outline and force it to match ?
You cannot force it in the way you are imagining, but you can trick the thing into using it anyway. The key is cloning the DB or at least the schema to some other place where you can connect as the user who executes the thing, then export/import the generated outline.

Read through this very good discussion for the details:
fpkeeganAuthor Commented:
I have the BAD SQL using the stored outline for the good SQL. I can see this by  

1. ALTER system SET query_rewrite_enabled=TRUE;
2. alter system set use_stored_outlines = true;
3. set USED flag to UNUSED in the DBA_outline using the DBMS_OUTLN.CLEAR_USED command.
5. verify the used flag is UNUSED in DBA_outlines;
6. execute the application that has the BAD sql.
7. verify the the USED in DBA_outline has changed to used.
8. for the entry in V$SQL table the OUTLINE_CATEGORY is default.
9. Examine the Actual PLAN in the v$sql_plan.

It uses the correct hints but build a bad plan. WHY ?

Have you actual set up and used complicated explain plan outlines ?

Thanks for your help.
fpkeeganAuthor Commented:
More Info......

Both SQLs are Using the SAME stored plan . This can be seen in the dbms_xplan.display_cursor output.  At the  botton of the output both state    - outline  PLAN1" used for this statement

The only plan in the DBA_OUTLINE is PLAN1. The V$SQL indicates that the OUTLINE_CATEGORY is DEFAULT.

The input data for the bind variables to the SQL is the same.  This is verified by looking at the data in v$sql_bind_capture

The FAST SQL CPU time   10.7 sec,    Buffer_gets    329,215  elapse time 42.876  seconds
The SLOW SQL CPU time  73.65 sec,  Buffer_gets 3,097, 812 elapse time 96.142  seconds

For the test there was a ALTER SYSTEM FLUSH SHARED_POOL;  between each execute.

The explain plans are completely different.   Enclosed are excel files of the outputs of the
 select * from TABLE(dbms_xplan.display_cursor(:SQL_ID,0));

Why are the explain plans different when using The same hints ? How do I make them the SLOW plan use the fast plan ?
Hints are comments, they can be honoured but there are a number of reasons why they could be ignored. For instance (I don't think it will be your case), the SQLs could simply reference different objects, because the synonyms could be pointing to a table with the same name but in another schema or database.

You are showing that the hard parse did include the hints stored in the outline. The execution plan generated was probably influenced by at least some of the hints in the outline, but still that doesn't mean that the plan will result in what you want. To guarantee that when you cannot connect as the app user and fire the exact SQL, the only way I've made it work is generating the outline on a DB clone as the app user, importing and exporting the outline as the paper suggests.

If you want to know WHY, just like everything relating to the CBO, the only way is to generate a 10053 trace when the actual SQL is parsed by the app user, and then digest it to see why the hints didn't do what you thought they would do. It's not too human friendly, but it's the only way to know the WHY.

Else forget about the WHY, do what the paper suggests and it will work. Then compare the hints that the new outline generates and you will see what the difference was.

Another option for you to explore: The list of hints that would form an outline can be obtained for any curson in the cache using the undocumented formatting options 'ADVANCED' or 'OUTLINE' for DBMS_XPLAN.DISPLAY_CURSOR. If you find a way to reproduce the GOOD plan on your DB clone, you can look for some difference in the hints without even actually building the outline. Still, you need a clone if you don't want to momentarily change the app user password.
fpkeeganAuthor Commented:
Some progress on why the performace is so different on the same SQL from two differe users / Schemas.

I was able to run TKPROF on each of the statmenst. The initiacl explay plan on both looks the same. But as the trace continues , The fast one  access the Hist_Head$ table. the slow one does not.  From that point on the trace is completly different.

I am really not familiar with histograms. But the quest now is how to  make all schemas / users us the histograms.
Aaron ShiloChief Database ArchitectCommented:

histograms = data distribution per value in a column.
histograms are created when you collect statistics on a table and specify the METHOD_OPT option.

the optimizer uses it (histograms) and you have to do in order to let  user/schema  use it.

here is a basic example and explenation for hitograms.
example : a table with a colum called city.

tel aviv = 5000 rows
new york = 12000000
london= 32000

now when the optimizer needs to make a disition to choose between a full table scan or a index
it look at the value you use in your condition and checks the number of rows for that value in the histogram and if it is a value with a relative small number then it will use the  index otherwise it will choose full table scan.

Ofcourse there are more factors for the optimizers desicion but this example is fairly accurate for the histograms part of the equation.

You don't have to do anything for histograms to be used. The CBO can use them whenever they are available on a particular object. They are queried by recursive SQL, you cannot (and don't need) to grant any access on them.

Please post the tkprof extract showing each SQL with their respective execution steps. If you say tkprof shows the same execution steps, then I don't see why you claim that the outlines are not working, that the SQLs get different optimization strategies, etc.

Remember that one thing is a PLAN, what Oracle intends to do. The actual thing is what tkprof and dbms_xplan.display_cursor show -actual execution STEPS, NOT just a PLAN. Again: post the tkprof output to make discussion possible. If you just post your interpretation of the facts, there's not much we can do... but believe me: histograms are NOT your real problem here.
fpkeeganAuthor Commented:
One of the schemas is just a user, it has no tables (SLOW). The other schema has all the tables, data and indexes (FAST).

See the enclosed output of TKPROF. Each start the same on the same identical SQL, with the same input data, same database,  It is using the stored outline hints. Both executed from TOAD.   Yet they have two very different behaviors.

Look just below the line in the file "TABLE ACCESS BY USER ROWID VIEW$" . one is doing a select from HIST_HEAD$ and the other is not.
You need a better trace... for some reason, all your execution steps show 0 rows (for ALL your SQLs), although the stats clearly show several non-0 rows fetched. So something is very wrong there... by any chance, do you happen to have statistics_level = BASIC or some other parameter set to a non-default value that could be interfering with row capturing? I don't actually know any, I'm just trying to avoid telling you "report this 0-rows to Oracle support"

Now, you can get a better version of your trace anyway by asking that it contains wait events and bind variables. You have the option when you start the trace from TOAD's session monitor. I'm VERY curious as to why the SQL with 0 phisical reads took so much longer than the one with 0 physical reads. The waits will show you how it spent (wasted?) that huge amount of time, and the binds will rule out the chance that they are executed with different data.

I see you have an index full scan and an index fast full scan. You might need to create another index at some point. Get those "reloaded" traces and we'll see :-)
fpkeeganAuthor Commented:

I will work on getting  more detailed trace.  But the question is still why the differences ?

If they both had the same poor performance then I could understand what is going on. But just changing the Schema/user should not effect the execution of the SQL.

Thank you for great and detailed help. Thanks for sticking with me and the problem.
I agree with you, that's why we need more data to dig deeper. We already know that outlines are out of the question since the plan *looks* the same.

An easy possibility are bind datatypes or values. I know you believe them to be identical, but only the trace can show you what was actually processed by the DBMS, plus we'll see what it is waiting on so much.

Another possible difference is that some objects are named the same, but are not the same. You can check that AND get the estimated cardinality+actual rows in one shot by checking v$sql_plan_statistics_all after hinting each SQL with /*+gather_plan_statistics*/, or else alter session/system set statistics_level = ALL.

Also try using dbms_xplan.display_cursor on both SQLs, try it with the options 'ALLSTATS LAST ROWS' and see if you can get the rows to show up. Same hint or parameter change for the actual rows to be collected.

As a sanity check, you can fire these two:

select * from dba_synonyms where table_owner not in ('SYS', 'SYSTEM') and synonym_name != table_name;

select count(*), object_name, subobject_name from dba_objects
where object_type not in ('SYNONYM', 'PACKAGE', 'TRIGGER')
group by object_name, subobject_name having count(*) > 1 order by count(*);
fpkeeganAuthor Commented:
I am trying to get a DBA to give me access to the traces. Not very easy around here on a friday.

select * from dba_synonyms where table_owner not in ('SYS', 'SYSTEM') and synonym_name != table_name;
returns nothing.

The select count(*), object_name, subobject_name from dba_objects
where object_type not in ('SYNONYM', 'PACKAGE', 'TRIGGER')
group by object_name, subobject_name having count(*) > 1 order by count(*);

returned items owned by sys,system,outln most of the object_names had $ in them.  The SUBOBJECT_NAME is alwasy null.  A lot of type of type and type body name matches. also so do tables that  have the same name and different owners, like OL$SIGNATURE owned by OUTLN amd SYSTEM.
None of the objects listed were applaction objects.

The excel spread sheet is the output of the /*+gather_plan_statistics*/  queries for
select * from  v$sql_plan_statistics SPS order by SQL_ID, operation_id;
SQL_ID = 9w4p4y3tqah53  = slow
SQL_ID = gxvxbjkfj8gg8  = fast

also are excel spread sheets for
 select * from TABLE(dbms_xplan.display_cursor (??SID?? ,0,'ALLSTATS LAST ROWS'));

fpkeeganAuthor Commented:
Another set of dbms_xplan.display_cursor .. I ran multiple times to see If I could remove the disk I/O... The problem as I see it is in the CPU time and BUFFER I/O.

if the two SQls with /*+gather_plan_statistics*/  do not match do to white space.

I have a FLUSH SHARED_POOL done before most of the tests. , but the explain plans remain the same.  is there a way to flush / delete  DBA_HIST_SQL_PLAN  .   I would like to get a very clean rebuild of the plan.

You don't "flush" DBA_HIST_SQL_PLAN, it's just an accumulation of what you see in V$SQL_PLAN, fed by the AWR snapshots. You don't want it emptied.

The SQLs with the hint for gathering statistics provide you with the cummulative statistics PLUS the last-execution statistics, so you don't need flushing anyway.

To force hard parsing, it's OK that you flush the shared pool. You can flush the buffer cache if needed just the same:


Another tip: if you ADD the option 'OUTLINE' or 'ADVANCED' to DBMS_XPLAN.DISPLAY_CURSOR's last parameter, you'll get to see which hints would make up an outline for the chosen EP. You can compare those to the contents of OL$HINTS.

I'm suspecting your indexing scheme might not be too adequate, both plans need some full scanning (be it table or index). Can you post creation scripts for the views+tables+indexes implicated? I'm tempted to reproduce the problem, maybe one more index can make an efficiet EP obvious enough to the CBO...
fpkeeganAuthor Commented:
I do not think it is possible to recreate the problem because of the data and database size.   The SQL is inside a product, and the Indexes are provided with the product. Also the SQL uses a lot of functions. The explain plan and CBO does not seem to analyze the functions.

There are three tables, the WELL 1600 rows, the WELL_VERSION 1800 rows, the PWEL_DAY_STATUS 1,200,000 rows. The two well tables do not changed very often. The PWEL_DAY_STATUS has 1600 rows added to it a midnight every day.

A full set of statistics are run daily on the tables daily in the early morning.

The PWEL_DAY_STATUS table has 4 indexes, the well table has one index, and WELL_VERSION  the has 16 indexes.

The SQL has date ranges, and date less than or equal to in the where clauses. This normal creates an index scan.

The indexes used in the initial steps of the explain plans are
I_WELL_VERSION_1 – 64 unique entries
I_WELL_VERSION_2 – 102 unique entries
I_WELL_VERISON_11 – 1711 unique entries
I_PWEL_DAY_STATUS_1 – 899 unique entries, one index value per day

The question remains that the same SQL from two different Schemas’ on the same data, have two different explain plans.  

I am still working on getting a trace.

I do not think it is possible to post product code on the net.

I will collent the  DBMS_XPLAN.DISPLAY_CURSOR's with additional info..

This is just strange..  Thanks for your help..
fpkeeganAuthor Commented:
More info as requested.

Enclosed is a spread sheet with the :
Select * from outln.OL$ ;
select * from outln.OL$HINTS;
select * from outln.OL$NODES;
was done before the runs where the  DISPLAY_CURSOR was taken.

The data id the two  DISPLAY_CURSOR outputs are very different. even though the Last line indicates that both used the FAST1 outline. The outline section is very different..

The plot thickens! Your new evidence shows that:

- The SQL_IDs are the same
- The peeked binds to produce the plan are the same (also equal to the ones caught in v$sql.bind_data, the only difference is the time when they were sampled, but when you decode the values, they are the same as the peeked ones)
- The optimizer_env is the same

Looking at Oracle's SQL sharing criteria the only thing left is that the two schemas are resolving an identical name to different objects. Your dumps from v$sql_plan* say otherwise, but please try this in TOAD so that at least you have a testcase to report this to your DBA (and eventually escalate it to support):

1- Connect with PKEEGAN
2- Flush the shared pool
3- Check the EP for your SQL (Control+E)
5- Repeat 2 and 3 and check for differences

Now do it again WITHOUT flushing the shared pool. If everything is the same, I don't see any reason to even create a new child instead of reusing the already parsed SQL.

Now do it yet again BUT edit your SQL and precede each object with its schema name. If you STILL find parse differences, you can skip the DBA and go directly to support!

Each time you can also run the SQL and note the elapsed time, check the output of DISPLAY_CURSOR with 'ADVANCED ALLSTATS LAST ROWS', doublecheck that the plans are the same in v$sql_plan, etc.

Some notes:

- You can try disabling the outlines, they don't seem to play a role here. Disabling them can help you prove that at least one of the SQLs is influenced by them
- You have OPTIMIZER_FEATURES_ENABLE = 9.2.0 and you say you're on 10g. Any good reasons for that? 9i uses I/O costing and 10g uses CPU costing, so you're well behind the current standard. You can try removing the setting, or setting it to with ALTER SESSION and see whether it makes a difference. Any other parameters set to non-default values? Use select * from v$parameter where isdefault='FALSE' to check for them
- You can ask your DBA to set _trace_files_public = TRUE so that you don't depend on him for your trace files (it's a hidden parameter, but he can check in Metalink that you don't lose support for setting THIS ONE)
- You say you update the statistics daily. Do you use DBMS_STATS? Note that you are on 10g, sthis can be an overkill since you typically have a scheduled job to do it for the tables which "changed a lot"
- You WILL need to give support a testcase for them to reproduce. You don't need an export with data, but you need an export without the rows, or creation scripts for the views+synonyms+tables+their indexes, plus an export of your table stats. For details on how to get the latter, please see:

How to Use DBMS_STATS to Move Statistics to a Different Database [ID 117203.1]

Transferring Optimizer Statistics to Support [ID 242489.1]

Please keep the thread updated, this gets progressively more interesting...
fpkeeganAuthor Commented:
I agree this is very strange.

1) remove explain outline, so outline is nologer available, verified by looking at OL$
2) login as Pkeegan
3) flush cache shared_pool
4) generate toad explan plain from toad - slow plan.
5) execute SQL - slow
6) verify in V$SQL the parsing schema is pkeegan, verify no outline
7) execute SQL - slow

9) flush cache shared_pool
10) generate toad explan plain from toad - slow plan.
11) execute SQL - slow
12) verify in V$SQL the parsing schema is ECKERNEL_USP

13)  modify sql to add schema name in front of view names as part of from clause
14) flush cache shared_pool
15) generate toad explan plain from toad - slow plan.
16) execute SQL - slow

17) login as ECKERNEL_USP
18) flush cache shared_pool
19) generate toad explan plain from toad - fast plan.
20) execute SQL - fast
21) verify in V$SQL the parsing schema is ECKERNEL_USP

It get stranger....

1) login as ECKERNEL_USP
2) flush cache
3) with qualified names in the FROM   set   ECKERNEL_USP.
4) run select -- FAST
5) login at PKEEGAN with session set to ECKERNEL_USP
6) run same select -- slow
7) look at V$SQL there are two entries in with the same SQL_ID.

8) flush cache
9) from PKEEGAN - run the SQL again it is SLOW
10) from ECKERNEL_USP run SQL again it is SLOW -- SLOW -- SLOW --
11) look at V$SQL there is one  entry for  the SQl with 2 executions.

So if the slow one is run first , it is reused.. If the fast one is run first it is not reused.

fpkeeganConnect With a Mentor Author Commented:

I have a solution.. But I still can not explain it.   I tested different optimizer versions by using
alter session set optimizer_features_enable=''

When the optmizer version is set equal to or greater than  the problem goes away. See table below or the excel spread sheet.

To answer one of your questions. login as ECKERNEL_USP. set the optimizer to 9.2.0, set the current_schema to PKEEGAN.. It runs fast.

Optimizer      ECKERNEL_USP                PKEEGAN
                  CPU time    BUFFER_GET     CPU time      BUFFER_GET
9.2.0      9.2      297060      74.6      3099773      9.2      297060      75.1      3099773
10.1.0      9.2      297060      75.3      3099773      9.2      297060      74.0      3099773      9.1      297060      73.5      3099773      9.3      297060      73.9      3099773      9.4      298530      73.6      3099773      9.2      298530      73.9      3099773      9.1      298530      9.1      298530      9.2      298530      9.2      298530      9.1      293761      9.2      293860

I'm glad you put the OPTIMIZER_FEATURES_ENABLE observation into good use. I hope you convince your DBA to change its value for the whole instance! Thanks for your words of appreciation, please let me know if you still involve support and they respond anything useful :-)
fpkeeganAuthor Commented:
Hard working person.. Thanks for sticking with the problem.  

Even if it appears to be an oracle bug.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.