Home » RDBMS Server » Performance Tuning » Using Stored Outlines
Using Stored Outlines [message #65745] Mon, 20 December 2004 11:51 Go to next message
Shane Kaszyca
Messages: 16
Registered: May 2003
Junior Member
Hello,

     I have a layered application that uses Documentum technology.  Basically a Java API that ends up at the lowest level creating SQL statements to manipulate tables in an Oracle 9i database. 

     The database is not creating consistent query plans.  Two SQL statements which are identical except for the literal in their where clause are creating different query plans.  One, executes quickly, the other, slowly. 

     I am starting to work with the outline manager, attempting to create an outline so that the slow query executes the same as the fast query.  Problem is the literals.  I have been reading and documentation says to use bind variables in my queries instead.  I experimented with this at the sqlplus command line and it appears that my query plan is used when I use bind variables.

     My problem is that I cannot manipulate the SQL queries before they are executed, as they are passed to the Oracle db as a function of the technology.  How can I get my outline to be used for all queries that are structured in a certain manner, without regard to the literals involved in the query?
Re: Using Stored Outlines [message #65746 is a reply to message #65745] Mon, 20 December 2004 12:03 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Documentum code isn't optimized for Oracle, so the solution may be as simple as deleting statistics for those tables you are having problems with. You can also insert "false" statistics (I don't remebemer the dbms_ pkg offhand) to try to get a consistent behavior.

Do remember however that the CBO generally requires stats on all the tables involved in SQL statement else it reverts to RULE. So if you remove stats to fix this one query, other statements involving the same table may change their plans too.

See this link on CURSOR_SHARING

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5180609822543
Re: Using Stored Outlines [message #65747 is a reply to message #65745] Mon, 20 December 2004 12:48 Go to previous messageGo to next message
Bruce McCartney
Messages: 7
Registered: September 2004
Junior Member
hi, what is your cursor_sharing set to. we had a problem in oracle 9.2 the optimizer peeks at a bind variable the first time a statement executes and uses that for every subsequent execution. this can be a problem if you use cursor sharing and can be suppressed by setting _optim_peek_user_binds to false.
Re: Using Stored Outlines [message #65749 is a reply to message #65747] Tue, 21 December 2004 04:00 Go to previous messageGo to next message
Shane Kaszyca
Messages: 16
Registered: May 2003
Junior Member
Our cusor_sharing is set to SIMILAR. It was set to force prior to the upgrade, but I've been told that the SIMILAR is an enhancement and (in theory at least) oracle should know when to use FORCE and when to use EXACT. So you are saying we should keep it as SIMILAR, but add this optim_peek_user_binds to false?

Another issue, it appears that the problem could be that I can't turn on the parameter to USE the stored outlines. When I perform "alter session set use_stored_outlines=DEFAULT;" then do a query "select name, value from v$parameter where upper(name) like '%USE%';", all that is returned to me is

use_indirect_data_buffers FALSE
license_max_users 0
serial_reuse DISABLE
user_dump_dest /tmp
parallel_adaptive_multi_user FALSE

Any idea why I wouldn't be able to use that session parameter?
Re: Using Stored Outlines [message #65750 is a reply to message #65749] Tue, 21 December 2004 04:27 Go to previous messageGo to next message
Shane Kaszyca
Messages: 16
Registered: May 2003
Junior Member
Is this because use_stored_outlines is not an initialization parameter and the v$parameter talbe only holds initialization parameters? If so, how can I check the status of the parameter while in the session to ensure that it is set correctly?

Shane
Re: Using Stored Outlines [message #65752 is a reply to message #65749] Tue, 21 December 2004 06:39 Go to previous message
Bruce McCartney
Messages: 7
Registered: September 2004
Junior Member
The USE_STORED_OUTLINES parameter determines whether the optimizer will use stored public outlines to generate execution plans. ** USE_STORED_OUTLINES is not an initialization parameter. **

The only ways i know to see if an outline is used is to look at USER_OUTLINES and/or v$sql that will show the query uses the outline
Previous Topic: ANALYZE TABLE Frequently
Next Topic: Performance for purging
Goto Forum:
  


Current Time: Thu Oct 01 02:17:00 CDT 2020