RE: stored outline for a 3rd party application

Jeffrey,
I have used cut/paste from v$sql and had that work, you can also have Oracle automatically generate outlines for all eligible SQL statements executed during a particular session or all eligible SQL statements executed system wide. To enable the automatic generation of outlines, use the ALTER SESSION or ALTER SYSTEM commands to set dynamic parameter called 'CREATE_STORED_OUTLINES'. This parameter is not an init.ora initialization parameter.

To enable automatic generation of outlines for a particular session you issue the command:

ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE; If you set 'CREATE_STORED_OUTLINES = TRUE' then outlines with be created in the DEFAULT category. You can substitute 'TRUE' for the name of a category to have the outlines created in a specific category. To disable the automatic generation of outlines for a session, set this parameter to 'FALSE'. The value 'TRUE' can be substituted for a category name to create outlines in specific categories. When automatically creating store outlines you can not control outline names. Oracle will automatically generate outline names for you.

Once in the OUTL tables you can then use outln_pkg to work with them, or substitute your 'hinted' statement for a particular vendor statement that needs tuning, remember to alter session set use_stored_outlines = true to have the outline actually used...

Bruce McCartney
DBIS
bruce.mccartney_at_dbinfosystems.com  

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeffrey Beckstrom
> Sent: February 10, 2005 6:51 AM
> To: oracle-l_at_freelists.org; oracle-db-l_at_Groups.ITtoolbox.com;
> ORACLE-L_at_IC.SUNYSB.EDU; oracledba_at_LazyDBA.com;
> oracle-rdbms_at_yahoogroups.com
> Subject: stored outline for a 3rd party application
>
> We have a SQL statement that is running poorly from a 3rd
> application and I was hoping to use stored outlines so that I
> could add a database hint. Looking into this I find that the
> stored outline SQL must match the actual SQL exactly (which
> uses bind variables). I can grab the sql from v$sql but how
> can I get it into the outline so it will have the same hash value?
>
>
>
> Jeffrey Beckstrom
> Database Administrator
> Greater Cleveland Regional Transit Authority 1240 W. 6th
> Street Cleveland, Ohio 44113
>
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l