In Production after rebuild of partitioned indexes we have some bad execution-plans. Now i want to take the good working Executuin-plans from another instance.
Instance 'good' :
* give application-user create any outline -Sys-priv
* create the outlines from the SQL in V$sql of the 'bad' instance
export the outln-tables
* give application-user create any outline -Sys-privs
* alter the system : use_stored_outlines = TRUE
* import the outln-tables from good db
* flush the shared_pool
But on 'bad' instance :
select * from user_outlines show still 'UNUSED'
in v$sql the columns OUTLINE_CATEGORY is still NULL
What i have do wrong?
both instances are the same Version
the *enabled-init.ora is the same
12-10-2002, 10:26 AM
Hope you have set the parameter 'CREATE_STORED_OUTLINES' to true or the name of category in the 'bad' instance.
Also, your SQL must match text for text in the 2 instances in order to use stored outlines.
12-10-2002, 04:33 PM
* 'CREATE_STORED_OUTLINES' has nothing to do if your outline is used?
it's used for automatic creation of outlines but not for using them
* The SQL fits perfectly,i pasted the whole V$sga.sql_text in an excel-sheet and found exact the sql_text in the stored-outline -view
So i tried it with a 9iR2 -Instance on winNT
and ... it WORKED!