| Using Stored Outlines [message #65745] |
Mon, 20 December 2004 11:51  |
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  |
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 |
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 |
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
