RE: Plan stability
At 01:14 PM 12/4/2003, you wrote: Hi Justin Didn't know you were on the list I'm usually about a week behind, so I don't get to participate very often... A properly formed hint will cause the CBO to consider the hinted path to be less costly than it would otherwise consider it, but hints do not force a query to use that particular plan. If you want to force Oracle to use a particular plan, plan stability is orders of magnitude easier! Umm, but if you look at plan stability you will see that it is implemented as hints (and query rewrite) - typically loads of them. A hint *does* force you to do what it says, if it is being 'ignored' then likely you haven't excluded alternative access paths. Of course for any sufficiently complex query (in my case that means 3 or more joins) then manually specifying an access path with hints becomes a too difficult problem. I've never looked under the covers on plan stability, so now I'm get confused. My understanding was that plan stability forced a query to follow a particular execution plan. My understanding of hints, however, was that they were only suggestions that the CBO could ignore. Tom Kyte writes (second or third response down): http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:1951680913800 1) yes -- IF it accepts the hint, hints are just that -- hints. They are NOT directives, they are suggestions. It took the suggestion in this case. If plan stability is just Oracle applying a bunch of hints, and hints are only suggestions, does that imply that the CBO can ignore plan stability? Justin Cave
RE: Plan stability
Title: Message
I could notget Outlook to prefix your message properly -
grrr. Comments are at the top which may make reading them hard.I have
tried to setup a simple demo that hints are not 'suggestions' sorry if this
becomes long - this is all 9.2 but should apply to 8i and later versions as
well. The sql I issue comes firstSQL create user niall identified by
niall;User created.SQL alter user niall default tablespace
users 2 temporary tablespace temp 3 quota
unlimited on users;User altered.SQL grant create
session, 2 alter session, 3 create
table, 4 select_catalog_role to niall;Grant
succeeded. create an unimaginatively named
userSQL conn niall/niallConnected.SQL
create table t1 as select * from dba_segments;Table
created.SQL create table t2 as select * from
dba_tablespaces;Table created.SQL create index i1 on
t1(tablespace_name);Index created.SQL create index i2 on
t2(tablespace_name);Index created.SQL conn
systemConnected.SQL exec
dbms_stats.gather_schema_stats('NIALL');PL/SQL procedure successfully
completed. create some tables and gather some
statsSQL alter session set events '10053 trace name
context forever, level 1';
Session altered.
SQL select /*+ index(t1 i1) */
t1.segment_name,t2.tablespace_name,t2.contents 2 from
t1,t2 3 where t1.tablespace_name=t2.tablespace_name
4 and t2.tablespace_name='USERS';
* Issue my query and tell Oracle to use index i1 for
table t1
SEGMENT_NAME
TABLESPACE_NAME
CONTENTS
--
-
snip boring results
20 rows selected.
SQL alter session set events '10053 trace name context
off';
Session altered.
This generates a trace file with the following info in it.
interesting bits bold and red
*** 2003-12-05 21:02:09.000*** SESSION ID:(9.15) 2003-12-05
21:02:09.000QUERYselect /*+ index(t1 i1) */
t1.segment_name,t2.tablespace_name,t2.contentsfrom t1,t2where
t1.tablespace_name=t2.tablespace_nameand
t2.tablespace_name='USERS'***PARAMETERS
USED BY THE
OPTIMIZEROPTIMIZER_FEATURES_ENABLE =
9.2.0OPTIMIZER_MODE/GOAL = Choose_OPTIMIZER_PERCENT_PARALLEL =
101snip parameter list
***BASE STATISTICAL
INFORMATION***Table stats Table:
T2 Alias: T2 TOTAL :: CDN: 15 NBLKS:
1 AVG_ROW_LEN: 88-- Index stats INDEX NAME: I2
COL#: 1 TOTAL :: LVLS: 1 #LB: 25
#DK: 100 LB/K: 1 DB/K: 1 CLUF:
800***Table stats Table:
T1 Alias: T1 TOTAL :: CDN: 1789 NBLKS:
25 AVG_ROW_LEN: 95-- Index stats INDEX NAME: I1
COL#: 5 TOTAL :: LVLS: 1 #LB: 25
#DK: 100 LB/K: 1 DB/K: 1 CLUF:
800_OPTIMIZER_PERCENT_PARALLEL =
0***SINGLE TABLE ACCESS
PATHColumn: TABLESPACE Col#: 5 Table:
T1 Alias: T1 NDV:
9 NULLS:
0 DENS:
1.e-001 NO HISTOGRAM: #BKT: 1 #VAL: 2 TABLE: T1 ORIG CDN:
1789 ROUNDED CDN: 199 CMPTD CDN: 199 Access path: index
(equal) Index:
I1 TABLE: T1
RSC_CPU: 0 RSC_IO: 92 IX_SEL: 0.e+000
TB_SEL: 1.e-001 BEST_CST: 92.00 PATH: 4
Degree: 1***SINGLE TABLE
ACCESS PATHColumn: TABLESPACE Col#: 1
Table: T2 Alias: T2 NDV:
15 NULLS:
0 DENS:
6.6667e-002 NO HISTOGRAM: #BKT: 1 #VAL: 2 TABLE:
T2 ORIG CDN: 15 ROUNDED CDN: 1 CMPTD CDN:
1 Access path: tsc Resc: 2 Resp: 2
Access path: index (equal) Index:
I2 TABLE: T2
RSC_CPU: 0 RSC_IO: 56 IX_SEL: 0.e+000
TB_SEL: 6.6667e-002 BEST_CST: 2.00 PATH: 2
Degree: 1***OPTIMIZER
STATISTICS AND
COMPUTATIONS***GENERAL
PLANS***Join order[1]: T2 [T2] T1 [T1] Now
joining: T1 [T1] ***NL Join
Outer table: cost: 2 cdn: 1 rcz: 17 resp: 2
Access path: index (join stp) Index:
I1 TABLE: T1
RSC_CPU: 0 RSC_IO: 92 IX_SEL: 0.e+000
TB_SEL: 1.e-001 Join: resc: 94 resp:
94Join cardinality: 199 = outer (1) * inner (199) * sel
(1.e+000) [flag=0] Best NL cost: 94 resp: 94Join
result: cost: 94 cdn: 199 rcz: 42Best so far: TABLE#: 0
CST: 2
CDN: 1
BYTES: 17Best so far:
TABLE#: 1 CST: 94
CDN: 199
BYTES:
8358***Join order[2]: T1 [T1] T2 [T2] Now
joining: T2 [T2] ***NL Join Outer table: cost: 92 cdn:
199 rcz: 25 resp: 92 Inner table: T2 Access path:
tsc Resc: 2 Join: Resc: 490
Resp: 490 Access path: index (join
stp) Index: I2
TABLE: T2 RSC_CPU: 0 RSC_IO:
56 IX_SEL: 0.e+000 TB_SEL:
6.6667e-002 Join: resc: 11236 resp:
11236Join cardinality: 199 = outer (199) * inner (1) * sel
(1.e+000) [flag=0] Best NL cost: 490 resp:
490Final: CST: 94 CDN: 199 RSC: 94 RSP: 94
BYTES: 8358 IO-RSC: 94 IO-RSP: 94 CPU-RSC: 0
CPU-RSP: 0*** 2003-12-05 21:02:30.000QUERYalter session set events
'10053 trace name context off'
You will see that for table t1
Oracle only ever considers an index access path using index i1. This is what we
told it to. By
RE: Plan stability
Hi Justin Didn't know you were on the list A properly formed hint will cause the CBO to consider the hinted path to be less costly than it would otherwise consider it, but hints do not force a query to use that particular plan. For a moderately complicated query, you'd potentially need a fair number of hints to get things working the way you want. If the statistics of the table changed, though, your carefully hinted query might well decide to take another path. Even if things work, adding hints-- particularly adding multiple hints-- to a query significantly increases the maintenance costs as future developers have to unravel what all the hints are doing, why they're doing it, whether any hints need to be changed as a result of the modifications, whether future changes to the CBO or new Oracle functionality should cause the ideal plan to change, etc. If you want to force Oracle to use a particular plan, plan stability is orders of magnitude easier! Umm, but if you look at plan stability you will see that it is implemented as hints (and query rewrite) - typically loads of them. A hint *does* force you to do what it says, if it is being 'ignored' then likely you haven't excluded alternative access paths. Of course for any sufficiently complex query (in my case that means 3 or more joins) then manually specifying an access path with hints becomes a too difficult problem. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Plan stability
Btw, the one good use for plan stability stored outlines are poorly written 3rd party applications, where you can't even add a hint to code. I these cases you can compose a good execution plan yourself and set your statement to use it. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 10:14 PM Hi Justin Didn't know you were on the list A properly formed hint will cause the CBO to consider the hinted path to be less costly than it would otherwise consider it, but hints do not force a query to use that particular plan. For a moderately complicated query, you'd potentially need a fair number of hints to get things working the way you want. If the statistics of the table changed, though, your carefully hinted query might well decide to take another path. Even if things work, adding hints-- particularly adding multiple hints-- to a query significantly increases the maintenance costs as future developers have to unravel what all the hints are doing, why they're doing it, whether any hints need to be changed as a result of the modifications, whether future changes to the CBO or new Oracle functionality should cause the ideal plan to change, etc. If you want to force Oracle to use a particular plan, plan stability is orders of magnitude easier! Umm, but if you look at plan stability you will see that it is implemented as hints (and query rewrite) - typically loads of them. A hint *does* force you to do what it says, if it is being 'ignored' then likely you haven't excluded alternative access paths. Of course for any sufficiently complex query (in my case that means 3 or more joins) then manually specifying an access path with hints becomes a too difficult problem. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Plan stability
Thanks for hint, I didn't know there's something like this bundled. Unfortunately, when I tried to create outline I got error The outline could not be viewed. ORA-01405: fetched column value is NULL I have create/alter/drop any outline privilege, I even tried it as dba, but no luck. So, there must be another problem. Any guesses? rw If you want to force Oracle to use a particular plan, plan stability is orders of magnitude easier! Only if you happen to have the licence for the 9.2 performance tuning pack, and can use the dinky little GUI for drawing and manipulating outlines. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Plan stability
What is complicated about stored outlines? If you don't like those you can always go back to the RBO. Gudmundur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wartiak Rastislav Sent: 2. desember 2003 08:44 To: Multiple recipients of list ORACLE-L Subject: Plan stability Hi, my question is about the same, but more general. How can i force Oracle to use my prefered way of explain plan and not use CBO's. I mean, apart from stored outlines, it somehow seems to complicated. I would like to say what order and join types it should use. But, try as I might, I many times cannot force Oracle to use my way, even though I know it is possible, for I saw this kind of explain plan for that specific query.. I tried to use hints like ordered and use_hj etc. Can someone give some examples of full set of hints for some simple queries? Thx, rw Hi, list friends: We are using partition to archive history data in our production OLTP database. We get great performance gains(Far less disk io), but we also hit performance trouble sometimes. So I am here ask for your experience. We used local index on all partitioned tables.We add/drop partition monthly to archive the history data. But the trouble is, when add/drop partition is being done on the partitioned table, CBO sometimes changed SQL execution path.We implemented partition 2 monthes ago, and in the first time, add/drop partition went on quite smoothly, but in the second time we add/drop partition, two SQL (just TWO SQL) get bad execution path and server load rushed to 10 times(from 2 to 20 in uptime), all waiting for latch free event. It severely affected our application. We are an online system and we do not have scheduled time every month so we have to add/drop partition while db is still running. So, with system still up and running, how do you add/drop partition without changing the SQL execution path? We do not have the time to reanalyze/dbms_stats the tables ,analyze takes hours and if SQL execution path changed, during these time, system is nearly unusable. I tried to import old statistics(dbms_stats.import_table_stats), but did not fix the problem. So, can you share your experience on managing partitioned table? Regards Zhu Chao www.cnoug.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Bjarni Josepsson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Plan stability
At 01:44 AM 12/2/2003, Wartiak Rastislav wrote: my question is about the same, but more general. How can i force Oracle to use my prefered way of explain plan and not use CBO's. I mean, apart from stored outlines, it somehow seems to complicated. I would like to say what order and join types it should use. But, try as I might, I many times cannot force Oracle to use my way, even though I know it is possible, for I saw this kind of explain plan for that specific query.. What's the objection to using stored outlines? That's the Oracle-provided mechanism for forcing queries to use a particular plan. I tried to use hints like ordered and use_hj etc. Can someone give some examples of full set of hints for some simple queries? A properly formed hint will cause the CBO to consider the hinted path to be less costly than it would otherwise consider it, but hints do not force a query to use that particular plan. For a moderately complicated query, you'd potentially need a fair number of hints to get things working the way you want. If the statistics of the table changed, though, your carefully hinted query might well decide to take another path. Even if things work, adding hints-- particularly adding multiple hints-- to a query significantly increases the maintenance costs as future developers have to unravel what all the hints are doing, why they're doing it, whether any hints need to be changed as a result of the modifications, whether future changes to the CBO or new Oracle functionality should cause the ideal plan to change, etc. If you want to force Oracle to use a particular plan, plan stability is orders of magnitude easier! Justin Cave Hi, list friends: We are using partition to archive history data in our production OLTP database. We get great performance gains(Far less disk io), but we also hit performance trouble sometimes. So I am here ask for your experience. We used local index on all partitioned tables.We add/drop partition monthly to archive the history data. But the trouble is, when add/drop partition is being done on the partitioned table, CBO sometimes changed SQL execution path.We implemented partition 2 monthes ago, and in the first time, add/drop partition went on quite smoothly, but in the second time we add/drop partition, two SQL (just TWO SQL) get bad execution path and server load rushed to 10 times(from 2 to 20 in uptime), all waiting for latch free event. It severely affected our application. We are an online system and we do not have scheduled time every month so we have to add/drop partition while db is still running. So, with system still up and running, how do you add/drop partition without changing the SQL execution path? We do not have the time to reanalyze/dbms_stats the tables ,analyze takes hours and if SQL execution path changed, during these time, system is nearly unusable. I tried to import old statistics(dbms_stats.import_table_stats), but did not fix the problem. So, can you share your experience on managing partitioned table? Regards Zhu Chao www.cnoug.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Justin Cave Distributed Database Consulting -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Justin Cave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Plan stability
What's the objection to using stored outlines? That's the Oracle-provided mechanism for forcing queries to use a particular plan. The problem is that I have to first analyze tables with real data for CBO to create plan I find useful (like using specific indexes etc.) and then to store it. When writing query I usually know what kind of data it will be there even before it is loaded there (and CBO cannot). I tried to use hints like ordered and use_hj etc. Can someone give some examples of full set of hints for some simple queries? A properly formed hint will cause the CBO to consider the hinted path to be less costly than it would otherwise consider it, but hints do not force a query to use that particular plan. For a moderately complicated query, you'd potentially need a fair number of hints to get things working the way you want. If the statistics of the table changed, though, your carefully hinted query might well decide to take another path. Even if things work, adding hints-- particularly adding multiple hints-- to a query significantly increases the maintenance costs as future developers have to unravel what all the hints are doing, why they're doing it, whether any hints need to be changed as a result of the modifications, whether future changes to the CBO or new Oracle functionality should cause the ideal plan to change, etc. That's why I am asking. I was thinking of some simple way of telling oracle - scan this index first, do full scan of other table, use hash join then etc. If you have explain plain with 5 steps, why cannot I write these 5 steps together with query so Oracle takes it for granted that this is the best way to do it? If you want to force Oracle to use a particular plan, plan stability is orders of magnitude easier! Justin Cave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Plan stability
AFAIK RBO cannot be used for partitioned tables, not talking about the fact that RBO might not be supported in future versions. What is complicated about stored outlines? If you don't like those you can always go back to the RBO. Gudmundur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wartiak Rastislav Sent: 2. desember 2003 08:44 To: Multiple recipients of list ORACLE-L Subject: Plan stability Hi, my question is about the same, but more general. How can i force Oracle to use my prefered way of explain plan and not use CBO's. I mean, apart from stored outlines, it somehow seems to complicated. I would like to say what order and join types it should use. But, try as I might, I many times cannot force Oracle to use my way, even though I know it is possible, for I saw this kind of explain plan for that specific query.. I tried to use hints like ordered and use_hj etc. Can someone give some examples of full set of hints for some simple queries? Thx, rw Hi, list friends: We are using partition to archive history data in our production OLTP database. We get great performance gains(Far less disk io), but we also hit performance trouble sometimes. So I am here ask for your experience. We used local index on all partitioned tables.We add/drop partition monthly to archive the history data. But the trouble is, when add/drop partition is being done on the partitioned table, CBO sometimes changed SQL execution path.We implemented partition 2 monthes ago, and in the first time, add/drop partition went on quite smoothly, but in the second time we add/drop partition, two SQL (just TWO SQL) get bad execution path and server load rushed to 10 times(from 2 to 20 in uptime), all waiting for latch free event. It severely affected our application. We are an online system and we do not have scheduled time every month so we have to add/drop partition while db is still running. So, with system still up and running, how do you add/drop partition without changing the SQL execution path? We do not have the time to reanalyze/dbms_stats the tables ,analyze takes hours and if SQL execution path changed, during these time, system is nearly unusable. I tried to import old statistics(dbms_stats.import_table_stats), but did not fix the problem. So, can you share your experience on managing partitioned table? Regards Zhu Chao www.cnoug.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Plan stability
Correct. The point is that stored outlines can be viewed as a tool for those who like the predictability of the RBO. When the RBO is no longer available, the best way I know of to force the CBO to use your plan is stored outlines. Gudmundur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wartiak Rastislav Sent: 2. desember 2003 09:39 To: Multiple recipients of list ORACLE-L Subject: RE: Plan stability AFAIK RBO cannot be used for partitioned tables, not talking about the fact that RBO might not be supported in future versions. What is complicated about stored outlines? If you don't like those you can always go back to the RBO. Gudmundur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wartiak Rastislav Sent: 2. desember 2003 08:44 To: Multiple recipients of list ORACLE-L Subject: Plan stability Hi, my question is about the same, but more general. How can i force Oracle to use my prefered way of explain plan and not use CBO's. I mean, apart from stored outlines, it somehow seems to complicated. I would like to say what order and join types it should use. But, try as I might, I many times cannot force Oracle to use my way, even though I know it is possible, for I saw this kind of explain plan for that specific query.. I tried to use hints like ordered and use_hj etc. Can someone give some examples of full set of hints for some simple queries? Thx, rw Hi, list friends: We are using partition to archive history data in our production OLTP database. We get great performance gains(Far less disk io), but we also hit performance trouble sometimes. So I am here ask for your experience. We used local index on all partitioned tables.We add/drop partition monthly to archive the history data. But the trouble is, when add/drop partition is being done on the partitioned table, CBO sometimes changed SQL execution path.We implemented partition 2 monthes ago, and in the first time, add/drop partition went on quite smoothly, but in the second time we add/drop partition, two SQL (just TWO SQL) get bad execution path and server load rushed to 10 times(from 2 to 20 in uptime), all waiting for latch free event. It severely affected our application. We are an online system and we do not have scheduled time every month so we have to add/drop partition while db is still running. So, with system still up and running, how do you add/drop partition without changing the SQL execution path? We do not have the time to reanalyze/dbms_stats the tables ,analyze takes hours and if SQL execution path changed, during these time, system is nearly unusable. I tried to import old statistics(dbms_stats.import_table_stats), but did not fix the problem. So, can you share your experience on managing partitioned table? Regards Zhu Chao www.cnoug.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Bjarni Josepsson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail
RE: Plan stability
I agree, but still you have load data, analyze tables, check explain plan that this is what you actually wanted and store outline. rw Correct. The point is that stored outlines can be viewed as a tool for those who like the predictability of the RBO. When the RBO is no longer available, the best way I know of to force the CBO to use your plan is stored outlines. Gudmundur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wartiak Rastislav Sent: 2. desember 2003 09:39 To: Multiple recipients of list ORACLE-L Subject: RE: Plan stability AFAIK RBO cannot be used for partitioned tables, not talking about the fact that RBO might not be supported in future versions. What is complicated about stored outlines? If you don't like those you can always go back to the RBO. Gudmundur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wartiak Rastislav Sent: 2. desember 2003 08:44 To: Multiple recipients of list ORACLE-L Subject: Plan stability Hi, my question is about the same, but more general. How can i force Oracle to use my prefered way of explain plan and not use CBO's. I mean, apart from stored outlines, it somehow seems to complicated. I would like to say what order and join types it should use. But, try as I might, I many times cannot force Oracle to use my way, even though I know it is possible, for I saw this kind of explain plan for that specific query.. I tried to use hints like ordered and use_hj etc. Can someone give some examples of full set of hints for some simple queries? Thx, rw Hi, list friends: We are using partition to archive history data in our production OLTP database. We get great performance gains(Far less disk io), but we also hit performance trouble sometimes. So I am here ask for your experience. We used local index on all partitioned tables.We add/drop partition monthly to archive the history data. But the trouble is, when add/drop partition is being done on the partitioned table, CBO sometimes changed SQL execution path.We implemented partition 2 monthes ago, and in the first time, add/drop partition went on quite smoothly, but in the second time we add/drop partition, two SQL (just TWO SQL) get bad execution path and server load rushed to 10 times(from 2 to 20 in uptime), all waiting for latch free event. It severely affected our application. We are an online system and we do not have scheduled time every month so we have to add/drop partition while db is still running. So, with system still up and running, how do you add/drop partition without changing the SQL execution path? We do not have the time to reanalyze/dbms_stats the tables ,analyze takes hours and if SQL execution path changed, during these time, system is nearly unusable. I tried to import old statistics(dbms_stats.import_table_stats), but did not fix the problem. So, can you share your experience on managing partitioned table? Regards Zhu Chao www.cnoug.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Bjarni Josepsson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE
RE: Plan stability
No, you don't. You could use dbms_stats to create (without analyzing) your statistics (if you know, what kind of data you'll be getting), and then store outline. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Wartiak Rastislav Sent: Tuesday, December 02, 2003 7:15 AM To: Multiple recipients of list ORACLE-L I agree, but still you have load data, analyze tables, check explain plan that this is what you actually wanted and store outline. rw Correct. The point is that stored outlines can be viewed as a tool for those who like the predictability of the RBO. When the RBO is no longer available, the best way I know of to force the CBO to use your plan is stored outlines. Gudmundur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wartiak Rastislav Sent: 2. desember 2003 09:39 To: Multiple recipients of list ORACLE-L Subject: RE: Plan stability AFAIK RBO cannot be used for partitioned tables, not talking about the fact that RBO might not be supported in future versions. What is complicated about stored outlines? If you don't like those you can always go back to the RBO. Gudmundur -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wartiak Rastislav Sent: 2. desember 2003 08:44 To: Multiple recipients of list ORACLE-L Subject: Plan stability Hi, my question is about the same, but more general. How can i force Oracle to use my prefered way of explain plan and not use CBO's. I mean, apart from stored outlines, it somehow seems to complicated. I would like to say what order and join types it should use. But, try as I might, I many times cannot force Oracle to use my way, even though I know it is possible, for I saw this kind of explain plan for that specific query.. I tried to use hints like ordered and use_hj etc. Can someone give some examples of full set of hints for some simple queries? Thx, rw Hi, list friends: We are using partition to archive history data in our production OLTP database. We get great performance gains(Far less disk io), but we also hit performance trouble sometimes. So I am here ask for your experience. We used local index on all partitioned tables.We add/drop partition monthly to archive the history data. But the trouble is, when add/drop partition is being done on the partitioned table, CBO sometimes changed SQL execution path.We implemented partition 2 monthes ago, and in the first time, add/drop partition went on quite smoothly, but in the second time we add/drop partition, two SQL (just TWO SQL) get bad execution path and server load rushed to 10 times(from 2 to 20 in uptime), all waiting for latch free event. It severely affected our application. We are an online system and we do not have scheduled time every month so we have to add/drop partition while db is still running. So, with system still up and running, how do you add/drop partition without changing the SQL execution path? We do not have the time to reanalyze/dbms_stats the tables ,analyze takes hours and if SQL execution path changed, during these time, system is nearly unusable. I tried to import old statistics(dbms_stats.import_table_stats), but did not fix the problem. So, can you share your experience on managing partitioned table? Regards Zhu Chao www.cnoug.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gudmundur Bjarni Josepsson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please
Re: Plan stability
Notes in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 9:04 AM A properly formed hint will cause the CBO to consider the hinted path to be less costly than it would otherwise consider it, but hints do not force a query to use that particular plan. Hints do not change the cost of a query - they simply dictate that Oracle should take the stated path and not consider alternatives which would otherwise be possible at that point in the plan. For a moderately complicated query, you'd potentially need a fair number of hints to get things working the way you want. If the statistics of the table changed, though, your carefully hinted query might well decide to take another path. Even if things work, adding hints-- particularly adding multiple hints-- to a query significantly increases the maintenance costs as future developers have to unravel what all the hints are doing, why they're doing it, whether any hints need to be changed as a result of the modifications, whether future changes to the CBO or new Oracle functionality should cause the ideal plan to change, etc. (a) One would hope that a hinted query would also have some documentation describing the expected execution plan, and the reaons why it was considered desirable - so the maintenance issues should be moot. (b) Plan stability exists to stop execution plans from changing - so any SQL with a plan should, by your comment above, require it's stored outline to be put under review in case any new functionality should be applied and the outline changed. So, again, your point is not entirely sound. If you want to force Oracle to use a particular plan, plan stability is orders of magnitude easier! Only if you happen to have the licence for the 9.2 performance tuning pack, and can use the dinky little GUI for drawing and manipulating outlines. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Plan stability
The biggest problem with hints is that you cannot specify a full set - in particular there is no effective way to handling unnesting of subqueries. For simple cases, you can put the tables in the main query in the 'correct' order and use the ORDERED hint, then name the indexes and join mechanisms you want. But if you have any subqueries things break if Oracle unnests, so you need to hint the subqueries with NO_UNNEST. On the other hand, unnesting may be desirable, in which case you have to rewrite the query in an unnested form, otherwise the unnested tables go to the top of the FROM list, and the ORDERED hint applies incorrectly. There is no easy option until you get to v10 - where a couple of hint enhancements make it much simpler to specify your requirements. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 8:44 AM Hi, my question is about the same, but more general. How can i force Oracle to use my prefered way of explain plan and not use CBO's. I mean, apart from stored outlines, it somehow seems to complicated. I would like to say what order and join types it should use. But, try as I might, I many times cannot force Oracle to use my way, even though I know it is possible, for I saw this kind of explain plan for that specific query.. I tried to use hints like ordered and use_hj etc. Can someone give some examples of full set of hints for some simple queries? Thx, rw -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
