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
ALTER SYSTEM FLUSH SHARED_POOL;
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)f
rom 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 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for HPUX: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production