Shivaswamy, December 12, 2001 - 2:38 pm UTC
Tom, thanks for your inputs.
I am sorry, I did not mean ORA-4030 is bug. My sort area size was 1 M. I tried with reduced sort area and retained sizes to.. without any better results. I had 3 Gig of physical memory. My process was going beyond about 500M on PGA/UGA(Dedicated server). Only handful of idle sessions were there.
** You should let the optimizer goal default to CHOOSE and this won't happen. ** !!!
I let it(Optimizer) at CHOOSE I got ORA-4030. I used RULE, SAME query did not return ORA-4030 under SAME conditions and answered the query. That was my riddle.
December 12, 2001 - 3:36 pm UTC
check to make sure someone hasn't accidently analyzed the SYS tables then! If you force it to rule and get different plans then when using choose -- its using the CBO which would only happen if there were stats.
analyze of sys tables
A reader, December 13, 2001 - 10:38 am UTC
Tom,
can you explain, what is wrong to analyze sys tables.
I know that it is wrong, but when I see it by the customers,
I can not explain it them.
December 13, 2001 - 2:39 pm UTC
it is wrong because we say so. It just is. (cannot we just say "don't do that, because its the rules"). You cannot analyze the full data dictionary (leads to deadlocks -- sort of a chicken and egg problem there) which means not all tables will have stats -- not having stats = bad queries = don't use cbo = don't analyze any of the sys tables.
Should all DBA Scripts use RBO?
Shivaswamy, December 13, 2001 - 5:24 pm UTC
Default is CBO:
16:14:44 SQL> SHOW PARAMETER OPTIMIZER_MODE
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
optimizer_mode string CHOOSE
SYS not analyzed, as seen below:
16:16:03 SQL> SELECT COUNT(*) FROM DBA_TABLES WHERE LAST_ANALYZED IS NOT NULL AND OWNER LIKE 'SYS%';
COUNT(*)
----------
0
1 row selected.
How can I you justify this:
select /*+ RULE */ dtp.partition_name, df.creation_time
from dba_tab_partitions dtp, v$datafile df, dba_data_files ddf
where dtp.tablespace_name = ddf.tablespace_name
and dtp.last_analyzed is null
and ddf.file_id = df.file#
/
=== I HAVE CUT THE OUT PUT OF 248 PARTITIONS HERE ========
248 rows selected.
Elapsed: 00:00:01.00
select dtp.partition_name, df.creation_time
from dba_tab_partitions dtp, v$datafile df, dba_data_files ddf
where dtp.tablespace_name = ddf.tablespace_name
and dtp.last_analyzed is null
and ddf.file_id = df.file#
/
=== I HAVE CUT THE OUT PUT OF 248 PARTITIONS HERE ========
248 rows selected.
Elapsed: 00:11:698.00
While I am aware that, SYS uses RBO, does it mean that, our (DBA)scripts also should use RBO..? I think not. Can you pl. clarify why is there so much of marked difference in response? If it is ONLY optmizer, what about the so many other DBA scripts we use, which return results fairly well? For SYS related queries, is it our responsibility to go for RBO or CBO changes to RBO for such queries intelligently?
TIA.
December 13, 2001 - 9:32 pm UTC
can you do this with:
set autotrace traceonly explain
on? I want to see if we get a COST/CARD with the first query (indicates CBO) and not the second (indicates RBO).
I cannot observe any differences in execution plans with or without the hint and on my system -- BOTH run with RBO.
Why Different Plans
An ardent fan, December 13, 2001 - 5:29 pm UTC
Tom,
With reference to your earlier followup reply, I have a similar situation. I use the init.ora optimizer_goal = CHOOSE but none of the tables have been analyzed. In a particular query, it shows full table scans. Howeever if I alter the session set optimizer_goal = rule, the plan becomes very different. As per you advice I deleted the stats on sys and sttem tables even though there was none. I ran dbms_stats.delete_table_stats as well as analyze table delete statistics. But the query plan remains the same. Can you explain why this happens?
Thanks a lot in advance.
Query:
select TO_CHAR(COUNT(distinct
case_claim_line.ic_claim_version_id),'999,999,999'),acronym,
TO_CHAR(COUNT(case_claim_line.ic_id),'999,999,999'),
TO_CHAR(SUM(case_claim_line.saved_amount),'999,999,999,999,999.99'),
cases.reporting_category from rules_flat, case_claim_line,
case_claim, cases where rules_flat.rule_id=cases.rule_id+0 and
case_claim_line.claim_line_action = 'DELETE' and
case_claim_line.ic_claim_version_id = case_claim.ic_claim_version_id+0 and
case_claim_line.ic_case_id = case_claim.ic_case_id+0
and case_claim.ic_case_id = cases.ic_case_id+0
and cases.rule_id = 1397 and cases.ic_case_id in (select
distinct cp.ic_case_id from case_processing cp, case_claim_line ccl,
claim_batch clb where cp.state||'' = 'ACCEPTED'
and cp.workflow||'' = 'WF1' and
cp.ic_case_id = ccl.ic_case_id+0 and ccl.claim_line_action='DELETE' and
ccl.ic_claim_version_id = clb.ic_claim_version_id+0 and clb.ic_batch_id in
(140)) group by cases.reporting_category, rules_flat.acronym
AFTER
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26058940563 Card=518
837573118 Bytes=81976336552644)
1 0 SORT (GROUP BY) (Cost=26058940563 Card=518837573118 Bytes=
81976336552644)
2 1 HASH JOIN (Cost=509180 Card=518837573118 Bytes=819763365
52644)
3 2 VIEW OF 'VW_NSO_1' (Cost=101722 Card=2032557 Bytes=264
23241)
4 3 SORT (UNIQUE) (Cost=101722 Card=2032557 Bytes=670743
810)
5 4 HASH JOIN (Cost=1786 Card=2032557 Bytes=670743810)
6 5 TABLE ACCESS (FULL) OF 'CASE_PROCESSING' (Cost=9
48 Card=51 Bytes=13821)
7 5 HASH JOIN (Cost=832 Card=3985405 Bytes=235138895
)
8 7 TABLE ACCESS (FULL) OF 'CASE_CLAIM_LINE' (Cost
=826 Card=7114 Bytes=234762)
9 7 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM_BATCH'
(Cost=2 Card=56022 Bytes=1456572)
10 9 INDEX (RANGE SCAN) OF 'CLAIM_BATCH_BATCH_ID_
IDX' (NON-UNIQUE) (Cost=1 Card=56022)
11 2 HASH JOIN (Cost=1499 Card=25526348 Bytes=3701320460)
12 11 TABLE ACCESS (FULL) OF 'RULES_FLAT' (Cost=2 Card=817
Bytes=32680)
13 11 HASH JOIN (Cost=1477 Card=3124400 Bytes=328062000)
14 13 TABLE ACCESS (FULL) OF 'CASE_CLAIM_LINE' (Cost=132
2 Card=7114 Bytes=327244)
15 13 HASH JOIN (Cost=9 Card=4391903 Bytes=259122277)
16 15 TABLE ACCESS (BY INDEX ROWID) OF 'CASES' (Cost=2
Card=3827 Bytes=126291)
17 16 INDEX (RANGE SCAN) OF 'FK_CASE_RULE_ID_IDX' (N
ON-UNIQUE) (Cost=1 Card=3827)
18 15 INDEX (FAST FULL SCAN) OF 'PK_CASE_CLAIM' (UNIQU
E) (Cost=4 Card=114761 Bytes=2983786)
SQL/prd1> alter session set optimizer_goal = rule;
Session altered.
1 0 SORT (GROUP BY) (Cost=111 Card=20 Bytes=3160)
2 1 NESTED LOOPS (Cost=89 Card=20 Bytes=3160)
3 2 NESTED LOOPS (Cost=88 Card=1 Bytes=118)
4 3 HASH JOIN (Cost=80 Card=4 Bytes=288)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'CASES' (Cost=2 C
ard=20 Bytes=660)
6 5 INDEX (RANGE SCAN) OF 'FK_CASE_RULE_ID_IDX' (NON
-UNIQUE) (Cost=1 Card=20)
7 4 MERGE JOIN (CARTESIAN) (Cost=77 Card=2000 Bytes=78
000)
8 7 VIEW OF 'VW_NSO_1' (Cost=73 Card=1 Bytes=13)
9 8 SORT (UNIQUE) (Cost=73 Card=1 Bytes=330)
10 9 NESTED LOOPS (Cost=50 Card=1 Bytes=330)
11 10 NESTED LOOPS (Cost=42 Card=4 Bytes=236)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'CLAIM_
BATCH' (Cost=2 Card=20 Bytes=520)
13 12 INDEX (RANGE SCAN) OF 'CLAIM_BATCH_BAT
CH_ID_IDX' (NON-UNIQUE) (Cost=1 Card=20)
14 11 TABLE ACCESS (BY INDEX ROWID) OF 'CASE_C
LAIM_LINE' (Cost=2 Card=20 Bytes=660)
15 14 INDEX (RANGE SCAN) OF 'CCLINE_IC_CLM_V
SN_ID_IDX' (NON-UNIQUE) (Cost=1 Card=20)
16 10 TABLE ACCESS (BY INDEX ROWID) OF 'CASE_PRO
CESSING' (Cost=2 Card=1 Bytes=271)
17 16 INDEX (RANGE SCAN) OF 'CASE_PROC_CASE_ID
_IDX' (NON-UNIQUE) (Cost=1 Card=1)
18 7 SORT (JOIN) (Cost=77 Card=2000 Bytes=52000)
19 18 INDEX (FAST FULL SCAN) OF 'PK_CASE_CLAIM' (UNI
QUE) (Cost=4 Card=2000 Bytes=52000)
20 3 TABLE ACCESS (BY INDEX ROWID) OF 'CASE_CLAIM_LINE' (
Cost=2 Card=20 Bytes=920)
21 20 INDEX (RANGE SCAN) OF 'FK_CCLINE_CASE_CLAIM_ID_IDX
' (NON-UNIQUE) (Cost=1 Card=20)
22 2 TABLE ACCESS (BY INDEX ROWID) OF 'RULES_FLAT' (Cost=1
Card=2000 Bytes=80000)
23 22 INDEX (UNIQUE SCAN) OF 'PK_RULES_FLAT' (UNIQUE)
December 13, 2001 - 9:37 pm UTC
Hate to tell you but you were using CBO in both cases!
You see, the cost/card would not show up with RBO:
ops$tkyte@ORA8I.WORLD> analyze table emp compute statistics;
Table analyzed.
ops$tkyte@ORA8I.WORLD> set autotrace traceonly explain
ops$tkyte@ORA8I.WORLD> select * from emp;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=560)
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=560)
ops$tkyte@ORA8I.WORLD> alter session set optimizer_goal = rule;
Session altered.
ops$tkyte@ORA8I.WORLD> select * from emp;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (FULL) OF 'EMP'
Is one of your tables partitioned? has a non-defaulit degree of paralellism set? uses something that forces the CBO?
I believe you have a case where the CBO was used 100% on the first go around and the RBO was used partially (subqueries and such can be optimized one way, the rest of the query another)....
In any case -- you used CBO both times.
Helena Markova, December 14, 2001 - 3:00 am UTC
CBO not using RULE for SYS Schema query
Shivaswamy, December 14, 2001 - 11:03 am UTC
Thanks, TOM. Here is the result.
09:58:06 SQL> set autotrace traceonly explain
09:58:22 SQL> select /*+ RULE */ dtp.partition_name, df.creation_time
09:58:35 2 from dba_tab_partitions dtp, v$datafile df, dba_data_files ddf
09:58:35 3 where dtp.tablespace_name = ddf.tablespace_name
09:58:35 4 and dtp.last_analyzed is null
09:58:35 5 and ddf.file_id = df.file#
09:58:35 6 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 MERGE JOIN
4 3 MERGE JOIN
5 4 SORT (JOIN)
6 5 MERGE JOIN
7 6 SORT (JOIN)
8 7 VIEW OF 'DBA_DATA_FILES'
9 8 UNION-ALL
10 9 NESTED LOOPS
11 10 NESTED LOOPS
12 11 FIXED TABLE (FULL) OF 'X$KCCFN'
13 11 TABLE ACCESS (BY INDEX ROWID) OF 'FI
LE$'
14 13 INDEX (UNIQUE SCAN) OF 'I_FILE1' (
UNIQUE)
15 10 TABLE ACCESS (CLUSTER) OF 'TS$'
16 15 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-
UNIQUE)
17 9 MERGE JOIN
18 17 SORT (JOIN)
19 18 NESTED LOOPS
20 19 NESTED LOOPS
21 20 FIXED TABLE (FULL) OF 'X$KTFBHC'
22 20 TABLE ACCESS (BY INDEX ROWID) OF
'FILE$'
23 22 INDEX (UNIQUE SCAN) OF 'I_FILE
1' (UNIQUE)
24 19 TABLE ACCESS (CLUSTER) OF 'TS$'
25 24 INDEX (UNIQUE SCAN) OF 'I_TS#' (
NON-UNIQUE)
26 17 SORT (JOIN)
27 26 FIXED TABLE (FULL) OF 'X$KCCFN'
28 6 SORT (JOIN)
29 28 FIXED TABLE (FULL) OF 'X$KCCFE'
30 4 SORT (JOIN)
31 30 FIXED TABLE (FULL) OF 'X$KCCFN'
32 3 SORT (JOIN)
33 32 FIXED TABLE (FULL) OF 'X$KCVFH'
34 1 SORT (JOIN)
35 34 VIEW OF 'DBA_TAB_PARTITIONS'
36 35 UNION-ALL
37 36 NESTED LOOPS
38 37 NESTED LOOPS
39 38 NESTED LOOPS
40 39 NESTED LOOPS
41 40 TABLE ACCESS (FULL) OF 'TABPART$'
42 40 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
43 42 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
44 39 TABLE ACCESS (CLUSTER) OF 'USER$'
45 44 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQ
UE)
46 38 TABLE ACCESS (CLUSTER) OF 'SEG$'
47 46 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON
-UNIQUE)
48 37 TABLE ACCESS (CLUSTER) OF 'TS$'
49 48 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
50 36 NESTED LOOPS
51 50 NESTED LOOPS
52 51 TABLE ACCESS (FULL) OF 'TABPART$'
53 51 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
54 53 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
55 50 TABLE ACCESS (CLUSTER) OF 'USER$'
56 55 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
57 36 NESTED LOOPS
58 57 NESTED LOOPS
59 58 NESTED LOOPS
60 59 TABLE ACCESS (FULL) OF 'USER$'
61 59 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
62 61 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
63 58 TABLE ACCESS (BY INDEX ROWID) OF 'TABCOMPART$'
64 63 INDEX (UNIQUE SCAN) OF 'I_TABCOMPART$' (UNIQ
UE)
65 57 TABLE ACCESS (CLUSTER) OF 'TS$'
66 65 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
09:58:35 SQL> select dtp.partition_name, df.creation_time
09:58:44 2 from dba_tab_partitions dtp, v$datafile df, dba_data_files ddf
09:58:44 3 where dtp.tablespace_name = ddf.tablespace_name
09:58:44 4 and dtp.last_analyzed is null
09:58:44 5 and ddf.file_id = df.file#
09:58:44 6 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=108 Card=481769 Byte
s=215350743)
1 0 NESTED LOOPS (Cost=108 Card=481769 Bytes=215350743)
2 1 NESTED LOOPS (Cost=94 Card=1 Bytes=404)
3 2 HASH JOIN (Cost=87 Card=1 Bytes=391)
4 3 NESTED LOOPS (Cost=79 Card=26 Bytes=2444)
5 4 FIXED TABLE (FULL) OF 'X$KCCFE' (Cost=7 Card=1 Byt
es=64)
6 4 VIEW OF 'DBA_DATA_FILES' (Cost=72 Card=2618 Bytes=
78540)
7 6 UNION-ALL
8 7 NESTED LOOPS (Cost=8 Card=1309 Bytes=479094)
9 8 NESTED LOOPS (Cost=8 Card=8 Bytes=2688)
10 9 FIXED TABLE (FULL) OF 'X$KCCFN' (Cost=7 Ca
rd=1 Bytes=297)
11 9 TABLE ACCESS (BY INDEX ROWID) OF 'FILE$' (
Cost=1 Card=818 Bytes=31902)
12 11 INDEX (UNIQUE SCAN) OF 'I_FILE1' (UNIQUE
)
13 8 TABLE ACCESS (CLUSTER) OF 'TS$'
14 13 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE
)
15 7 NESTED LOOPS (Cost=64 Card=1309 Bytes=496111)
16 15 NESTED LOOPS (Cost=64 Card=8 Bytes=2792)
17 16 NESTED LOOPS (Cost=8 Card=8 Bytes=2584)
18 17 FIXED TABLE (FULL) OF 'X$KCCFN' (Cost=7
Card=1 Bytes=297)
19 17 TABLE ACCESS (BY INDEX ROWID) OF 'FILE$'
(Cost=1 Card=818 Bytes=21268)
20 19 INDEX (UNIQUE SCAN) OF 'I_FILE1' (UNIQ
UE)
21 16 FIXED TABLE (FIXED INDEX #1) OF 'X$KTFBHC'
(Cost=7 Card=100 Bytes=2600)
22 15 TABLE ACCESS (CLUSTER) OF 'TS$'
23 22 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE
)
24 3 FIXED TABLE (FULL) OF 'X$KCCFN' (Cost=7 Card=1 Bytes
=297)
25 2 FIXED TABLE (FIXED INDEX #1) OF 'X$KCVFH' (Cost=7 Card
=100 Bytes=1300)
26 1 VIEW OF 'DBA_TAB_PARTITIONS' (Cost=14 Card=48176927 Byte
s=2071607861)
27 26 UNION-ALL
28 27 NESTED LOOPS (Cost=3 Card=8752109 Bytes=1741669691)
29 28 NESTED LOOPS (Cost=3 Card=53497 Bytes=9950442)
30 29 NESTED LOOPS (Cost=3 Card=327 Bytes=51012)
31 30 NESTED LOOPS (Cost=1 Card=2 Bytes=226)
32 31 TABLE ACCESS (FULL) OF 'TABPART$' (Cost=1 Ca
rd=99 Bytes=7326)
33 31 TABLE ACCESS (CLUSTER) OF 'SEG$'
34 33 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (N
ON-UNIQUE)
35 30 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (Cost=
1 Card=16360 Bytes=703480)
36 35 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
37 29 TABLE ACCESS (CLUSTER) OF 'TS$'
38 37 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
39 28 TABLE ACCESS (CLUSTER) OF 'USER$'
40 39 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
41 27 NESTED LOOPS (Cost=2 Card=26830 Bytes=2790320)
42 41 NESTED LOOPS (Cost=2 Card=164 Bytes=14924)
43 42 TABLE ACCESS (FULL) OF 'TABPART$' (Cost=1 Card=1
Bytes=48)
44 42 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (Cost=1
Card=16360 Bytes=703480)
45 44 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
46 41 TABLE ACCESS (CLUSTER) OF 'USER$'
47 46 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
48 27 NESTED LOOPS (Cost=9 Card=39397988 Bytes=5279330392)
49 48 NESTED LOOPS (Cost=9 Card=240819 Bytes=29139099)
50 49 HASH JOIN (Cost=9 Card=1472 Bytes=133952)
51 50 TABLE ACCESS (FULL) OF 'TABCOMPART$' (Cost=1 C
ard=9 Bytes=432)
52 50 TABLE ACCESS (FULL) OF 'OBJ$' (Cost=7 Card=163
60 Bytes=703480)
53 49 TABLE ACCESS (CLUSTER) OF 'TS$'
54 53 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
55 48 TABLE ACCESS (CLUSTER) OF 'USER$'
56 55 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
09:58:44 SQL> spool off
Also, about 4 weeks back I was floored. CBO did not use an index against a big table for a query satisfying ALL the conditions to use an index. I must have spent over 2 hours with all possible combinations - pl. trust me - to force it to use index. And it was ultimately RULE hint, which used index and at the end, unknowingly, I had exclaimed 'Useless Oracle..!' in front of a gleeful developer, to her satisfaction! (I do not have those details, however on this database now.)
TIA,
Shivaswamy
December 14, 2001 - 6:43 pm UTC
Shivaswamy,
as i expected -- you have something analyzed somewhere -- you need to investigate a little deeper. Look at the query plans -- as I described -- the one without RULE, its using the CBO.
run this for me and post the results (run as SYS or "as sysdba" or internal). also -- whats your version/platform.
set autotrace traceonly explain
select count(*) from FILE$;
select count(*) from OBJ$;
select count(*) from SEG$;
select count(*) from TABCOMPART$;
select count(*) from TABPART$;
select count(*) from TS$;
select count(*) from USER$;
select count(*) from X$KCCFE;
select count(*) from X$KCCFN;
select count(*) from X$KCVFH;
select count(*) from X$KTFBHC;
As for the last part -- you are the starter of myths here. In a court of law this would be thrown out as hersay. I believe you didn't have current stats on the table OR the index should not have been used (the index would have been the wrong plan) OR something else. Without a test case, without an example -- I have to reject comments like that out of hand. There is a logical scientific explanation for everything (its software after all). Without info -- I can mount no argument. For everything I say you'll just say "no i checked that" -- well, we'll never know would we.d..
Happens to me too...
Jan van Mourik, December 14, 2001 - 12:19 pm UTC
Tried Shivaswamy's query on two of my databases, and the explain plans show it too:
SELECT STATEMENT Optimizer=CHOOSE (Cost=157 Card=32758 Bytes
=15363502)
None of the sys tables have statistics, parallel degree = 1...
December 14, 2001 - 6:44 pm UTC
show me:
set autotrace traceonly explain
select count(*) from FILE$;
select count(*) from OBJ$;
select count(*) from SEG$;
select count(*) from TABCOMPART$;
select count(*) from TABPART$;
select count(*) from TS$;
select count(*) from USER$;
select count(*) from X$KCCFE;
select count(*) from X$KCCFN;
select count(*) from X$KCVFH;
select count(*) from X$KTFBHC;
when connected as sys.
No Surprises Here!
Mark A. Williams, December 14, 2001 - 9:06 pm UTC
Well, since I haven't posted anything to AskTom for a week or so, I thought I would just go ahead and jump right in here!
I ran the same query as Shivaswamy and Jan van Mourik against a 8.1.7 and a 9.0.1 database. (I am certain I do NOT have stats on ANY sys objects in these databases.)
Here are the first few lines of the "show plan":
8.1.7:
ID Operation
--- ----------------------------------
0 SELECT STATEMENT COST = N/A
1 MERGE JOIN
2 SORT
3 MERGE JOIN
9.0.1:
ID Operation
--- ----------------------------------
0 SELECT STATEMENT COST = N/A
1 CONCATENATION
2 MERGE JOIN
3 SORT
This is what I would expect to see with NO stats on my sys objects...
Now, just for fun, I ran dbms_stats.gather_database_stats against the same 8.1.7 database - I happen to know that there is a bug in the 8i gather_database_stats that causes stats to be created for sys objects. Here are the results of that:
8.1.7 (WITH sys stats):
ID Operation
--- ----------------------------------
0 SELECT STATEMENT COST = 112
1 NESTED LOOPS
2 NESTED LOOPS
3 NESTED LOOPS
Surprise, surprise, surprise. Notice anything different between the NO stats on sys, and the WITH stats on sys?
Try running:
begin
dbms_stats.delete_schema_stats('SYS');
end;
/
and then run Tom's suggestions again - bet you'll be surprised.
Output traceonly
jan van mourik, December 15, 2001 - 1:00 am UTC
Tom,
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'FILE$'
<snip>
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FIXED TABLE (FULL) OF 'X$KTFBHC'
The execution plan was exactly the same for each and every table you asked.
After that I ran the "dbms_stats.delete_schema_stats('SYS');". But still the same, still showing cost, no difference!
Wonder how it will go for Shivaswamy...
December 15, 2001 - 9:56 am UTC
Mystery solved -- here is the answer
In 815 and 816, the definition of dba_data_files is:
select v.name, f.file#, ts.name,
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks - 1), f.blocks - 1
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v
where v.file# = f.file#
and f.spare1 is NULL
and f.ts# = ts.ts#
union all
select /*+ ordered use_nl(f) use_nl(hc) */
v.name, hc.ktfbhcafno, ts.name,
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#,
decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL)
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts
where v.file# = f.file#
and f.spare1 is NOT NULL
and f.file# = hc.ktfbhcafno
and hc.ktfbhctsn = ts.ts#
/
See that -- they stick a HINT in that view, causing any query that used it to use the CBO unless that query used the rule hint/optimizer_goal!
This hint made dba_data_files run ok (they added this for locally managed tablespaces) in isolation but when you started joining that to other objects -- it could get nasty.
Here is more info:
BugTag: Support notes on <Bug:1614732> - DDR info <BugDesc:1614732>
Affects: RDBMS (81-820)
NB: FIXED
Abstract: Data dictionary selects using DBA_DATA_FILES may be slow in 8i
Fixed-Releases: 8172 8200
Tags: PERF REGRESSION
Details:
This problem is introduced in 8.1.5.
The presence of hints in the dba_data_files definition can cause
performance degradation over earlier releases.
In my 8172 database -- this is patched (hence why so many of us cannot reproduce this - the hints are removed)....
So, there we go -- the scientific explaination I was looking for. For every effect there is an identifiable cause. In this case, the presence of a hint in a view caused the CBO to be used.
It's that easy...
jan van mourik, December 15, 2001 - 5:02 pm UTC
Thanks Tom!
Makes me think of the "good old days" when people always thought they had found a bug in the compiler. Always turned out to be something in the source code though...
Learn something every day from your site!
Interesting MetaLink Info
Mark A. Williams, December 15, 2001 - 5:22 pm UTC
Tom:
I did some research on MetaLink after reading your findings in regard to the hint in the dba_data_files view. Seems the same hint is in other views as well (dba_free_space for example). At least it is good to know why I couldn't reproduce ("correctly") the issue with my 8.1.7.2.5 and 9.0.1.2.0 databases.
Whilst reading info on this topic, I came across this comment (apparently from an Oracle developer):
"We do not guarantee the performance against our dba views with joins. These views are designed to have the optimal performance when queried directly not for joins. In most cases we tell customers that if they don't like our views that are provided wiht [sic] the database, then they can go ahead and create their own."
I frequently do joins that involve dba_* views, and don't really want to have to re-create the views... I rarely use OEM, but it seems like it generated statement with similar joins. I guess that comment just caught me a bit by surprise.
Thanks,
Mark
Remote Guru
Shivaswamy, December 15, 2001 - 8:48 pm UTC
Tom,
*Thanks So much * for all the trouble you have taken to arrive at the explanations and the conclusion. I have not posted the results for what you have asked, as you have given satisfying answers, without them.
Your approach to the problems and the way you come out with the explanations based on the basic principles are simply marvellous. You are (remote)"GURU" in the Truest sense of the word.
Thanks, again.
Shivaswamy
Error after processing of certain records
hrishab, November 12, 2002 - 8:15 am UTC
Hi Tom,
I was executing one procedure which has query as
SELECT account_id
FROM monthly_table tds1
MINUS
SELECT account_id
FROM other_table tds2
WHERE BUSINESS_DATE = TO_DATE('31-OCT-2002','DD-MON-YYYY')
This query get around 750000 records in the cursor.
Then for each account_id gets the latest record and inserts into a table.
After processing roughly around 169000 records, procedure is thrown out with ORA-04030 error. I've gone through the explain plan and did not find any signs of table being analyzed. Can you please explain or guide me to a link where I can find a solution.
November 12, 2002 - 10:31 am UTC
Well, you must have some procedural code wrapped around this since there is not any "insert into" (sounds like this should be a SINGLE SQL STATEMENT TO ME!!!!!! I doubt you need to have the procedural code)
I would believe the culprit is your procedural code -- not this query.
Join Problem
A reader, November 12, 2002 - 12:55 pm UTC
Hi, Tom,
Thanks a lot for your precious comments.
We are now facing a big problem:
basically, we have two identical database(everything including data, index, and other strunctures are the same).
we have a very simple join query:
select table1.*, table2.* from table1, table2
where table1.pk=table.pk
this query runs instantly on database1, and it
uses index scan for one table in the execution plan,
while the same query runs forever in another database2,
when we look at the execution plan, it doesn't use
the index. Bothe databases are default to COST not RULE
based. We test the same query by;
select table1.pk, table2.pk from table1, table2
where table1.pk=table.pk
and it runs very fast in both the databases and have the
same execution plan.
Now, would you please tell me what could be the problem and
which parts should we look for?(the two databases are setup
the same and located in the same machine).
Thanks a million,
November 13, 2002 - 12:34 pm UTC
lets see the output of an "autotrace traceonly explain" run for both queries (you know, an example)
Please answer the question below
Balaji CN, November 12, 2002 - 11:17 pm UTC
Hi Tom
I am a new reader of ur site.Sivaswamy had asked "
While I am aware that, SYS uses RBO, does it mean that, our (DBA)scripts also
should use RBO..? I think not. Can you pl. clarify why is there so much of
marked difference in response? If it is ONLY optmizer, what about the so many
other DBA scripts we use, which return results fairly well? For SYS related
queries, is it our responsibility to go for RBO or CBO changes to RBO for such
queries intelligently?
TIA.
"
Should I use a RULE hint explicitly or should i let optimizer CHOOSE the plan for DBA Scripts.
November 13, 2002 - 1:00 pm UTC
just run the queries. no hints.
Here is the execution plan
A reader, November 13, 2002 - 3:17 pm UTC
Hi, TOm,
Here are the execution plan that you requested--they are the same query on different DBs in one machine, both DBs
setup same and data from the table is the same too. Both
DBs' optimizer_mode are default to "CHOOSE".
a) -- very fast, why still RULE based even DB default CHOOSE?
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TBL1'
2 1 INDEX (FULL SCAN) OF 'TBL1_PK3976135997099' (UNIQUE)
b) -- COST based but exetremely slow.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10824 Card=212156 By
tes=15063076)
1 0 SORT (ORDER BY) (Cost=10824 Card=212156 Bytes=15063076)
2 1 TABLE ACCESS (FULL) OF 'TBL1' (Cost=419 Car
d=212156 Bytes=15063076)
The query is:
SELECT * FROM tbl1 order by col1_pk, col2_pk;
Where col1_pk and col2_pk is the composite primary key
of the table.
And the table in both DBs have the same number of records,
same structure, index etc.
In the slower query one(b), when I add the hint--/*+RULE */
it becomes the same speed with the query(a), I also
change the parameter: optimizer_index_cost_adj = 35,
but got the same answer.
Do we really need the hint under the COST based system
if the query response time is really slow? Why RULE
based query has more faster response time?
Could you elaborate these issue please?
Thanks a million
November 13, 2002 - 5:59 pm UTC
you wrote:
...
We are now facing a big problem:
basically, we have two identical database(everything including data, index, and
other strunctures are the same).
we have a very simple join query:
select table1.*, table2.* from table1, table2
where table1.pk=table.pk
....
Now -- that is not the case -- one database has statistics, the other does not. The query isn't even close to what you said it was (and why didn't you just cut and paste the query into the question with the autotrace -- makes me TOTALLY suspicious that the query is not what you say but anyway....)
The first query will return the first row very fast but -- have you timed how long it takes to get the LAST row??? the second query will get the last row faster I would believe then the first query would.
So, lets use TKPROF and show me the END result -- the time to get the last row you fetch. I think you might be comparing the time to get the first row vs the time for the entire query to run.
CBO and SYS Schema
denni50, September 20, 2004 - 3:31 pm UTC
Tom
I recently converted the test db from rbo to cbo(CHOOSE)
SQL> show parameter optimizer;
NAME TYPE VALUE
------------------------------------ ----------- -----------------
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 90
optimizer_index_cost_adj integer 25
optimizer_max_permutations integer 2000
optimizer_mode string CHOOSE
I noticed some of the sys packages are now running slower.
I ran the dbms_stats on the application schema only
not SYS or SYSTEM.
Shouldn't the optimizer be choosing RULE when running sys packages.
It runs instantaneously when I use the RULE Hint.
Do I need to delete statistics for SYS even though I never gathered statistics for the SYS schema?...this is confusing.
SQL> set autotrace traceonly explain
SQL> select j.job, r.sid, j.log_user, j.failures, j.broken,
2 to_char(j.next_date,'MM/DD/YYYY HH24:MI:SS') rtime, j.interval,
3 j.what
4 from dba_jobs_running r, dba_jobs j
5 where r.job(+) = j.job
6 order by j.next_date;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8746 Card=8168 Bytes
=17928760)
1 0 SORT (ORDER BY) (Cost=8746 Card=8168 Bytes=17928760)
2 1 MERGE JOIN (OUTER) (Cost=258 Card=8168 Bytes=17928760)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'JOB$' (Cost=207 Card
=8168 Bytes=17716392)
4 3 INDEX (FULL SCAN) OF 'I_JOB_JOB' (UNIQUE) (Cost=26 C
ard=8168)
5 2 SORT (JOIN) (Cost=51 Card=1 Bytes=26)
6 5 VIEW OF 'DBA_JOBS_RUNNING' (Cost=50 Card=1 Bytes=26)
7 6 NESTED LOOPS (OUTER) (Cost=50 Card=1 Bytes=84)
8 7 NESTED LOOPS (Cost=48 Card=1 Bytes=71)
9 8 MERGE JOIN (CARTESIAN) (Cost=16 Card=1 Bytes=5
0)
10 9 FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=8 Card
=1 Bytes=30)
11 9 BUFFER (SORT) (Cost=8 Card=1 Bytes=20)
12 11 FIXED TABLE (FULL) OF 'X$KSQRS' (Cost=8 Ca
rd=1 Bytes=20)
13 8 VIEW OF 'GV$_LOCK' (Cost=32 Card=1 Bytes=21)
14 13 UNION-ALL
15 14 VIEW OF 'GV$_LOCK1' (Cost=16 Card=2 Bytes=
162)
16 15 UNION-ALL
17 16 FIXED TABLE (FULL) OF 'X$KDNSSF' (Cost
=8 Card=1 Bytes=94)
18 16 FIXED TABLE (FULL) OF 'X$KSQEQ' (Cost=
8 Card=1 Bytes=94)
19 14 FIXED TABLE (FULL) OF 'X$KTADM' (Cost=8 Ca
rd=1 Bytes=94)
20 14 FIXED TABLE (FULL) OF 'X$KTCXB' (Cost=8 Ca
rd=1 Bytes=94)
21 7 INDEX (UNIQUE SCAN) OF 'I_JOB_JOB' (UNIQUE)
*****************RULE HINT**********************
SQL> select/*+RULE*/j.job, r.sid, j.log_user, j.failures, j.broken,
2 to_char(j.next_date,'MM/DD/YYYY HH24:MI:SS') rtime, j.interval,
3 j.what
4 from dba_jobs_running r, dba_jobs j
5 where r.job(+) = j.job
6 order by j.next_date;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (ORDER BY)
2 1 MERGE JOIN (OUTER)
3 2 SORT (JOIN)
4 3 TABLE ACCESS (FULL) OF 'JOB$'
5 2 SORT (JOIN)
6 5 VIEW OF 'DBA_JOBS_RUNNING'
7 6 MERGE JOIN
8 7 SORT (JOIN)
9 8 MERGE JOIN
10 9 SORT (JOIN)
11 10 NESTED LOOPS (OUTER)
12 11 FIXED TABLE (FULL) OF 'X$KSQRS'
13 11 INDEX (UNIQUE SCAN) OF 'I_JOB_JOB' (UNIQ
UE)
14 9 SORT (JOIN)
15 14 VIEW OF 'GV$_LOCK'
16 15 UNION-ALL
17 16 VIEW OF 'GV$_LOCK1'
18 17 UNION-ALL
19 18 FIXED TABLE (FULL) OF 'X$KDNSSF'
20 18 FIXED TABLE (FULL) OF 'X$KSQEQ'
21 16 FIXED TABLE (FULL) OF 'X$KTADM'
22 16 FIXED TABLE (FULL) OF 'X$KTCXB'
23 7 SORT (JOIN)
24 23 FIXED TABLE (FULL) OF 'X$KSUSE'
September 20, 2004 - 4:28 pm UTC
that is definitely using the CBO, see the cost=, card=
someone analyzed something - look at last analyzed in the dictionary
Tom...I ran the below counts that you asked another reader to do
denni50, September 20, 2004 - 3:40 pm UTC
they are all CHOOSE??
SQL> set autotrace traceonly explain
SQL> select count(*) from FILE$;
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'FILE$'
SQL> select count(*) from OBJ$;
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'OBJ$'
SQL> select count(*) from SEG$;
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SEG$'
SQL> select count(*) from TABCOMPART$;
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TABCOMPART$'
SQL> select count(*) from TABPART$;
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TABPART$'
SQL> select count(*) from TS$;
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TS$'
SQL> select count(*) from USER$;
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'USER$'
SQL> select count(*) from X$KCCFE;
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FIXED TABLE (FULL) OF 'X$KCCFE'
SQL> select count(*) from X$KCCFN;
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FIXED TABLE (FULL) OF 'X$KCCFN'
SQL> select count(*) from X$KCVFH;
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FIXED TABLE (FULL) OF 'X$KCVFH'
SQL> select count(*) from X$KTFBHC;
Execution Plan
-----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FIXED TABLE (FULL) OF 'X$KTFBHC'
SQL>
September 20, 2004 - 4:49 pm UTC
you missed some, but look for last analyzed (tad easier then looking at this)
and check out the view text, look for a "hint" in it.
More on CBO and SYS
denni50, September 21, 2004 - 8:32 am UTC
Tom
I ran the 2 statements below on the SYS schema to see what objects had gotten analyzed that is causing SYS objects to use CBO instead of RBO.
The second statement produced 308 results showing nothing...I'm just cutting and pasting the first 3.
Is there somewhere else I should be looking.
Do you know anything about the DRSYS schema and what it is used for?
thanks for any help/tips.
SQL*Plus: Release 9.2.0.5.0 - Production on Tue Sep 21 07:57:59 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect xxx/xxxxxx@xxxxxxxxxxx as sysdba
Connected.
SQL> select table_name, inserts, updates,deletes from all_tab_modifications
2 where table_owner='SYS';
no rows selected
SQL> SQL> select num_rows,to_char(last_analyzed,'DD-MON-RRRR HH24:MI:SS') from
2 all_tables
3 where owner = 'SYS';
NUM_ROWS TO_CHAR(LAST_ANALYZE
---------- --------------------
NUM_ROWS TO_CHAR(LAST_ANALYZE
---------- --------------------
NUM_ROWS TO_CHAR(LAST_ANALYZE
---------- --------------------
September 21, 2004 - 8:40 am UTC
v$lock, any query against v$lock is using the cbo.
try this instead:
select j.job,
(select sid from dba_jobs_running where job = j.job) sid,
j.log_user, j.failures, j.broken,
to_char(j.next_date,'MM/DD/YYYY HH24:MI:SS') rtime,
j.interval, j.what
from dba_jobs j
order by j.next_date;
Analyze
aru, March 29, 2005 - 1:06 am UTC
Hi Tom,
In my new company in one of the 8.1.7 Oracle database's:-
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ---------------
optimizer_mode string RULE
The database is being analyzed every week, and most of the queries ( 80% ) do not use hints. When I inquired why the
schemas are being analyzed the senior DBA said if they did not, performance problems arise. I still think that's not possible. (and he did not sound too confident about his assumption either). Do you think they are being analyzed for the sake of the queries that use hints or is the whole exercise futile.
Please clarify.
Thanks in advance,
ARU.
March 29, 2005 - 8:12 am UTC
Well, if you are using index organized tables, tables with non-default degrees of parallelism, partitioned tables, tables with text indexes............ (and maybe some other things I'm just neglecting to list here)
You are using the CBO.
Else you are using the RBO and the stats would not be used.
select optimizer_mode, optimizer_cost, count(*) from v$sql group by optimizer_mode, optimizer_cost;
Analyze
ARU, March 29, 2005 - 3:55 pm UTC
Hi Tom,
Thanks for the query, which I ran and the result is:-
OPTIMIZER_ OPTIMIZER_COST COUNT(*)
---------- -------------- ----------
CHOOSE 6 2
CHOOSE 964 4
FIRST_ROWS 1 1
FIRST_ROWS 10 2
NONE 7172
RULE 0 1744
RULE 1 66
RULE 2 6170
RULE 3 402
RULE 4 11
RULE 5 178
RULE 6 13
RULE 7 9
RULE 8 11
RULE 9 3
RULE 11 1
RULE 12 5
RULE 1157 1
RULE 26072 1
RULE 125629 4
RULE 793639 5
RULE 190
The cost column is populated in all cases. How and what should I decipher from this.
Thanks again,
ARU.
March 29, 2005 - 5:03 pm UTC
ops$tkyte@ORA9IR2> create table t1 ( x int primary key ) organization index;
Table created.
ops$tkyte@ORA9IR2> create table t2 ( x int primary key );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select sql_text, optimizer_mode, optimizer_cost
2 from v$sql where sql_text like '%LOOK_FOR_ME%';
SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------
OPTIMIZER_ OPTIMIZER_COST
---------- --------------
select sql_text, optimizer_mode, optimizer_cost from v$sql where sql_text like '%LOOK_FOR_ME%'
RULE 0
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter session set optimizer_mode = rule;
Session altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t1 LOOK_FOR_ME;
no rows selected
ops$tkyte@ORA9IR2> select * from t2 LOOK_FOR_ME;
no rows selected
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select sql_text, optimizer_mode, optimizer_cost
2 from v$sql where sql_text like '%LOOK_FOR_ME%';
SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------
OPTIMIZER_ OPTIMIZER_COST
---------- --------------
select sql_text, optimizer_mode, optimizer_cost from v$sql where sql_text like '%LOOK_FOR_ME%'
RULE 0
select * from t2 LOOK_FOR_ME
RULE 0
select * from t1 LOOK_FOR_ME
RULE 5
If RULE and cost = 0, it was really rule.
If RULE and cost <> 0, it was really the CBO.
someone is setting the optimizer mode to choose and first rows (they want stats)
lots of your queries apparently have to use the CBO and they want stats.
Analyze
aru, March 29, 2005 - 5:30 pm UTC
Hi Tom,
Nice ,very nice explaination. I understand.
Another doubt(s):-
1)If we give a hint to use the CBO and the
tables refrenced in the query have not been analyzed then what happens? my guess- no stats, so, though we will use CBO, plan will be RBO by nature.
2) Suppose we use rule based optimzation and we create a index on a table referenced by the query, will the RBO force
the index use? The CBO would not I understand.
Thanks for everything, think I'll nail this now thanks to you.
ARU.
March 29, 2005 - 8:11 pm UTC
1) plan will be a bad CBO plan as it will use defaults (unless dynamic sampling kicks in and mini stats are gathered at parse time)
2) the rbo will use an index typically, if it is possible to use the index. the CBO is more selective.
Things to look at
Tanmoy Choudhury, June 07, 2005 - 8:31 am UTC
Hi Tom,
We have Oracle HRMS 11 installed for one of our clients. There are lot of interfaces are running (both inbound and outbound) frequently. One of the interfaces is running long for the past couple of months which needs to be tuned. This interface is an outbound interface which will select from the HRMS tables and spool it to some output file.I must admit it did runs couple of complex sql statements and lots of formatting ..like LPAD/RPAD etc. The optimizer mode is RULE. No hints has been used in the interface. It was running long at 4.5 hr 2 months back now its taking 9+ hrs to complete. All the tables are analyzed. It nearly processed 6000+ rows all the time.
Below are the details
DB version - 8.1.7.4.0
Below are the parameters -
NAME VALUE
_trace_files_public TRUE
processes 250
sessions 280
timed_statistics TRUE
timed_os_statistics 0
resource_limit FALSE
license_max_sessions 0
license_sessions_warning 0
cpu_count 24
instance_groups
event 10929 trace name context forever, 10932 trace name context level 2
shared_pool_size 200000000
shared_pool_reserved_size 10000000
large_pool_size 0
java_pool_size 20000K
java_soft_sessionspace_limit 0
java_max_sessionspace_size 0
pre_page_sga FALSE
shared_memory_address 0
hi_shared_memory_address 0
use_indirect_data_buffers FALSE
lock_sga FALSE
lock_name_space
enqueue_resources 5000
nls_language AMERICAN
nls_territory AMERICA
nls_sort BINARY
nls_date_language
nls_date_format DD-MON-RR
nls_currency
nls_numeric_characters .,
nls_iso_currency
nls_calendar
nls_time_format
nls_timestamp_format
nls_time_tz_format
nls_timestamp_tz_format
nls_dual_currency
nls_comp
disk_asynch_io TRUE
tape_asynch_io TRUE
dbwr_io_slaves 0
backup_tape_io_slaves FALSE
ops_interconnects
db_file_direct_io_count 64
resource_manager_plan
hpux_sched_noage
lm_ress 6000
lm_locks 12000
active_instance_count
control_files
db_file_name_convert
log_file_name_convert
db_block_buffers 50000
db_block_checksum FALSE
db_block_size 8192
db_block_lru_latches 24
db_writer_processes 1
db_block_max_dirty_target 50000
buffer_pool_keep
buffer_pool_recycle
max_commit_propagation_delay 700
compatible 8.1.7
log_archive_start TRUE
log_archive_dest
log_archive_duplex_dest
log_archive_dest_1
log_archive_dest_2
log_archive_dest_3
log_archive_dest_4
log_archive_dest_5
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
log_archive_dest_state_3 enable
log_archive_dest_state_4 enable
log_archive_dest_state_5 enable
log_archive_max_processes 1
log_archive_min_succeed_dest 1
standby_archive_dest ?/dbs/arch
log_archive_trace 0
log_archive_format %t_%s.log
log_buffer 1310720
log_checkpoint_interval 1000000
log_checkpoint_timeout 3600
db_files 1500
db_file_multiblock_read_count 64
read_only_open_delayed FALSE
parallel_server FALSE
parallel_server_instances 1
gc_releasable_locks 0
gc_rollback_locks 0-1024=32!8REACH
gc_files_to_locks
gc_defer_time 10
thread 0
fast_start_io_target 50000
log_checkpoints_to_alert TRUE
recovery_parallelism 0
control_file_record_keep_time 7
dml_locks 1000
row_locking ALWAYS
serializable FALSE
replication_dependency_tracking TRUE
instance_number 0
transactions 308
transactions_per_rollback_segment 5
max_rollback_segments 35
transaction_auditing TRUE
fast_start_parallel_rollback LOW
db_block_checking FALSE
os_roles FALSE
rdbms_server_dn
max_enabled_roles 30
remote_os_authent TRUE
remote_os_roles FALSE
O7_DICTIONARY_ACCESSIBILITY TRUE
remote_login_passwordfile NONE
dblink_encrypt_login FALSE
license_max_users 0
db_domain
global_names FALSE
distributed_transactions 1000
commit_point_strength 1
mts_dispatchers
mts_servers 0
mts_max_servers 20
mts_max_dispatchers 5
mts_sessions 0
mts_circuits 0
local_listener
mts_service HECHRW
mts_listener_address
mts_multiple_listeners FALSE
open_links 200
open_links_per_instance 4
optimizer_features_enable 8.1.7
fixed_date
audit_trail NONE
sort_area_size 524288
sort_area_retained_size 0
sort_multiblock_read_count 2
db_name HECHRW
open_cursors 1000
ifile
sql_trace FALSE
_optimizer_undo_changes TRUE
os_authent_prefix
optimizer_mode RULE
sql92_security FALSE
blank_trimming FALSE
always_anti_join HASH
partition_view_enabled FALSE
star_transformation_enabled FALSE
parallel_broadcast_enabled FALSE
parallel_adaptive_multi_user FALSE
parallel_threads_per_cpu 2
parallel_automatic_tuning FALSE
always_semi_join standard
optimizer_max_permutations 80000
optimizer_index_cost_adj 100
optimizer_index_caching 0
query_rewrite_enabled FALSE
query_rewrite_integrity enforced
sql_version NATIVE
serial_reuse DISABLE
cursor_space_for_time FALSE
session_cached_cursors 0
text_enable FALSE
remote_dependencies_mode TIMESTAMP
utl_file_dir
plsql_v2_compatibility FALSE
job_queue_processes 10
job_queue_interval 20
optimizer_percent_parallel 0
parallel_min_percent 0
create_bitmap_area_size 8388608
bitmap_merge_area_size 1048576
cursor_sharing EXACT
parallel_min_servers 0
parallel_max_servers 5
parallel_instance_group
parallel_execution_message_size 2148
hash_join_enabled TRUE
hash_area_size 1048576
hash_multiblock_io_count 0
shadow_core_dump partial
background_core_dump partial
oracle_trace_enable FALSE
oracle_trace_facility_path ?/otrace/admin/fdf
oracle_trace_collection_path ?/otrace/admin/cdf
oracle_trace_facility_name oracled
oracle_trace_collection_name
oracle_trace_collection_size 5242880
object_cache_optimal_size 102400
object_cache_max_size_percent 10
session_max_open_files 10
aq_tm_processes 0
hs_autoregister TRUE
I will be glad if you can share how would you approach such situation. What things to look at? If you need more information about this do let me know.
Thanks
Tanmoy
June 07, 2005 - 8:52 am UTC
why rule.
I too surprised !!
Tanmoy Choudhury, June 07, 2005 - 9:31 am UTC
You are right Tom , I too surprised ..Can't comment on this as it depends upon the management. But it was running ok earlier but for the past 2 -3 months its running long. As its RULE, it will always go through the same PLAN, am i right ?? then why suddenly its taking time? Do you suggest me to change the session setting to FIRST_ROWS and run the same once again.
Do you need any other information?
Thanks
Tanmoy
June 07, 2005 - 9:47 am UTC
it just sounds like "you are using rule, the data has grown larger, rule uses indexes like crazy, indexes should not be used anymore but rule will never understand that"
you'll need to find the "slow query" and perhaps hint it to death and revisit it over time if you are using rule.
Tkprof - If you want to comment
Tanmoy Choudhury, June 08, 2005 - 8:46 am UTC
Followup to the above thread if you want to comment. Here is the tkprof after running the interface for 50 peoples.It tooks around 24 min to complete. For 6000+ it tooks 9+ hrs.Trimmed due to program too large error.
TKPROF: Release 8.0.6.3.0 - Production on Tue Jun 7 20:40:26 2005
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Trace file: ././hechrw_ora_25326.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace = TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.01 0.02 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.01 0.02 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
********************************************************************************
UPDATE COM_INT_PROCESSING_LOG SET STATUS='P',PARAMETER_1=:b1,PARAMETER_2=:b2,
PARAMETER_3=:b3,PARAMETER_4=:b4,PARAMETER_5=:b5,PARAMETER_6=:b6,PARAMETER_7=
:b7,PARAMETER_8=:b8,PARAMETER_9=:b9,PARAMETER_10=:b10,START_DATE_PARAMETER=
TO_DATE(:b5,'DD-MON-YYYY'),END_DATE_PARAMETER=TO_DATE(:b6,'DD-MON-YYYY'),
CONFIG_OPTION_1=:b13,CONFIG_OPTION_2=:b14,CONFIG_OPTION_3=:b15,
CONFIG_OPTION_4=:b16,CONFIG_OPTION_5=:b17,FILENAME=NVL(:b18,FILENAME)
WHERE
PROC_LOG_ID = :b19
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.02 0 2 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.02 0 2 3 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE COM_INT_PROCESSING_LOG
1 INDEX UNIQUE SCAN (object id 101892)
********************************************************************************
SELECT PAYROLL_ID
FROM
PAY_ALL_PAYROLLS_F WHERE PAYROLL_NAME = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 1 4 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL PAY_ALL_PAYROLLS_F
********************************************************************************
SELECT START_DATE,END_DATE
FROM
PER_TIME_PERIODS WHERE REGULAR_PAYMENT_DATE = :b1 AND PAYROLL_ID = :b2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 1 0.01 0.01 0 182 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.02 0 182 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID PER_TIME_PERIODS
1045 INDEX RANGE SCAN (object id 88875)
********************************************************************************
SELECT TIME_PERIOD_ID
FROM
PER_TIME_PERIODS WHERE PAYROLL_ID = :b1 AND TRUNC(END_DATE) = TRUNC(:b2 +
7 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.02 0.01 0 171 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.01 0 171 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID PER_TIME_PERIODS
598 INDEX RANGE SCAN (object id 88875)
********************************************************************************
SELECT MAX(END_DATE)
FROM
PER_TIME_PERIODS WHERE PAYROLL_ID = :b1 AND TRUNC(END_DATE) < TRUNC(:b2)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.02 0.02 0 182 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.02 0 182 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
597 TABLE ACCESS BY INDEX ROWID PER_TIME_PERIODS
1045 INDEX RANGE SCAN (object id 88875)
********************************************************************************
SELECT TIME_PERIOD_ID
FROM
PER_TIME_PERIODS WHERE PAYROLL_ID = :b1 AND TRUNC(END_DATE) = TRUNC(:b2)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.01 0 171 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.02 0 171 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID PER_TIME_PERIODS
597 INDEX RANGE SCAN (object id 88875)
********************************************************************************
SELECT MIN(DATE_PROC_STARTED)
FROM
COM_INT_PROCESSING_LOG WHERE UPPER(INTERFACE_NAME) = UPPER(:b1) AND
UPPER(CONFIG_OPTION_1) = UPPER(:b2) AND TRUNC(TO_DATE(PARAMETER_6,
'DD-MON-RRRR')) = TRUNC(TO_DATE((:b3 + 7 ),'DD-MON-RRRR')) AND
UPPER(STATUS) IN ( 'C','P' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.71 1.09 451 664 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.72 1.09 451 664 4 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
0 TABLE ACCESS FULL COM_INT_PROCESSING_LOG
********************************************************************************
SELECT MIN(DATE_PROC_STARTED)
FROM
COM_INT_PROCESSING_LOG WHERE UPPER(INTERFACE_NAME) = UPPER(:b1) AND
UPPER(CONFIG_OPTION_1) = UPPER(:b2) AND TRUNC(TO_DATE(PARAMETER_6,
'DD-MON-RRRR')) = TRUNC(TO_DATE((:b3 + 7 ),'DD-MON-RRRR')) AND
UPPER(STATUS) = 'C'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.53 0.53 0 664 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.53 0.53 0 664 4 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
0 TABLE ACCESS FULL COM_INT_PROCESSING_LOG
********************************************************************************
SELECT COUNT(*),EE.ASSIGNMENT_ID ASSIGN_ID
FROM
PAY_ELEMENT_ENTRIES_F EE,(SELECT PEL.ELEMENT_TYPE_ID,PEL.ELEMENT_LINK_ID
FROM PAY_ELEMENT_LINKS_F PEL,PAY_ACCRUAL_PLANS AP WHERE
AP.ACCRUAL_CATEGORY = 'V' AND AP.ACCRUAL_PLAN_ELEMENT_TYPE_ID =
PEL.ELEMENT_TYPE_ID ) X WHERE TRUNC(SYSDATE) BETWEEN
EE.EFFECTIVE_START_DATE AND EE.EFFECTIVE_END_DATE AND X.ELEMENT_LINK_ID =
EE.ELEMENT_LINK_ID GROUP BY EE.ASSIGNMENT_ID HAVING COUNT(*) > 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1.82 22.68 4181 12897 4 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.83 22.69 4181 12897 4 0
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 FILTER
13243 SORT GROUP BY
13242 NESTED LOOPS
3 NESTED LOOPS
3 TABLE ACCESS FULL PAY_ACCRUAL_PLANS
4 TABLE ACCESS BY INDEX ROWID PAY_ELEMENT_LINKS_F
4 INDEX RANGE SCAN (object id 88719)
13242 TABLE ACCESS BY INDEX ROWID PAY_ELEMENT_ENTRIES_F
13546 INDEX RANGE SCAN (object id 88592)
********************************************************************************
INSERT INTO HEC554_INT_STAGE
VALUES
( :b1,:b2 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.01 0 0 0 0
Execute 51 0.04 0.11 10 14 169 51
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 54 0.04 0.12 10 14 169 51
Misses in library cache during parse: 1
Misses in library cache during execute: 2
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
********************************************************************************
SELECT PAP.PERSON_ID,PPB.PAY_ANNUALIZATION_FACTOR RATE_FACTOR,HSC.SEGMENT1
TAX_UNIT,PAF.ASSIGNMENT_ID ASSIGN_ID,PAP.EMPLOYEE_NUMBER,PAP.FIRST_NAME,
PAP.MIDDLE_NAMES MIDDLE_INITIAL,PAP.LAST_NAME || ' ' || PAP.SUFFIX
LAST_NAME,PAD.ADDRESS_LINE1 ADDR1,PAD.TOWN_OR_CITY CITY,PAD.REGION_2 STATE,
PAD.POSTAL_CODE ZIP,PAD.TELEPHONE_NUMBER_1 PHONE_1,
DECODE(PAP.PER_INFORMATION4,'VISA_TYPE','N','Y') CITIZEN_CODE,
PAP.NATIONAL_IDENTIFIER SSN,PAP.SEX,PAF.ASS_ATTRIBUTE2 CAR_ALLOW,
PAP.PER_INFORMATION1 MINORITY_DESCRIPTION,DECODE(PAS.USER_STATUS,'Terminate
Assignment','I','A') EMP_EMPLOY_STATUS,PAP.DATE_OF_BIRTH BIRTH_DATE,
PAP.ATTRIBUTE4 ORIGINAL_HIRE_DATE,PAF.ASS_ATTRIBUTE4 LABOR_CHARGE_CODE,
PAP.ATTRIBUTE3 HIRE_CODE,PAP.START_DATE,DECODE(PAP.ATTRIBUTE1, NULL ,
PAP.ATTRIBUTE4,PAP.ATTRIBUTE1) BENEFIT_DATE,HOU1.ATTRIBUTE1 COMPANY_CODE,
SUBSTR(HOU.ATTRIBUTE1,1,6) SOURCE_CODE1_Q,PJD.SEGMENT1 JOB_CODE,
PJD.SEGMENT3 JOB_TITLE,PAF.ASS_ATTRIBUTE6 SHIFT_CODE,PAF.NORMAL_HOURS
WORK_HOURS,PAF.ASS_ATTRIBUTE12 WORKING_LEADER_FLAG,PPB.RATE_BASIS,
PAF.BUSINESS_GROUP_ID,HSC.SEGMENT4 WORK_USER_COLUMN_ID,PAP.ATTRIBUTE2
GEOGRAPHIC_LOC,PAP.EFFECTIVE_START_DATE EFF_START_DATE,
PTS.EFFECTIVE_START_DATE STAT_EFF_START_DATE,PTF.EFFECTIVE_START_DATE
FED_EFF_START_DATE,DECODE(PAS.USER_STATUS,'Terminate Assignment',
PPS.LEAVING_REASON) TERM_SEPARATION_CODE,DECODE(PAS.USER_STATUS,'Terminate
Assignment',PPS.NOTIFIED_TERMINATION_DATE) NOTIFIED_TERM_DATE,
DECODE(PAS.USER_STATUS,'Terminate Assignment',PPS.ATTRIBUTE1)
LAST_DAY_WORKED,PAP.ATTRIBUTE3 HIRE_CODE1,DECODE(HOU1.ATTRIBUTE1,'HDT',
'999','00') BASE_ID,PGD.SEGMENT2 GRADE,PJ.ATTRIBUTE3 JOB_TYPE,
PJ.ATTRIBUTE10 GCC_CODE,PAF.EMPLOYMENT_CATEGORY EMP_CATEGORY,PJ.ATTRIBUTE5
SUPERVISOR_CODE,PJD.SEGMENT4 BARGAINING_UNIT,DECODE(PPB.PAY_BASIS,'HOURLY',
'H',PJ.JOB_INFORMATION3) SALARY_CLASS,PJD.SEGMENT1 JOB_CODE1,
HOU.ATTRIBUTE10 DIST_CODE,HOU1.ATTRIBUTE1 RESPONSIBLE_SECTOR,
SUBSTR(HOU.ATTRIBUTE1,1,6) SOURCE_CODE2,PAP.REGISTERED_DISABLED_FLAG
DISABILITY_CODE,DECODE(PAS.USER_STATUS,'Active Assignment','W00000000','LOA
Unpaid With Accruals','L' || TO_CHAR(PAF.EFFECTIVE_START_DATE,'YYYYMMDD') ,
'LOA Unpaid Without Accruals','L' || TO_CHAR(PAF.EFFECTIVE_START_DATE,
'YYYYMMDD') ,' 00000000') TIME_OFF_YR_CODE,DECODE(PAD.STYLE,'US',
PAD.REGION_2,' ') HOME_STATE,HRL.REGION_2 WORK_STATE,HRL.COUNTRY
WORK_COUNTRY,HOU.ATTRIBUTE1 SOURCE_CODE_3,PPS.ACTUAL_TERMINATION_DATE,
PAF.EFFECTIVE_START_DATE ASS_EFF_START_DATE
FROM
PER_PAY_BASES PPB,PER_PERSON_TYPES PPT,PAY_US_STATES STATES,
PER_JOB_DEFINITIONS PJD,HR_SOFT_CODING_KEYFLEX HSC,FND_COMMON_LOOKUPS
LOOKUP,PER_ALL_ASSIGNMENTS_F PAF,HR_LOCATIONS HRL,
PER_ASSIGNMENT_STATUS_TYPES PAS,PER_PERIODS_OF_SERVICE PPS,
HR_ALL_ORGANIZATION_UNITS HOU,PER_GRADES PG,PER_GRADE_DEFINITIONS PGD,
PER_JOBS PJ,PAY_US_EMP_FED_TAX_RULES_F PTF,PAY_US_EMP_STATE_TAX_RULES_F PTS,
PAY_PEOPLE_GROUPS PPG,PER_ADDRESSES PAD,PER_ALL_PEOPLE_F PAP,
HR_ALL_ORGANIZATION_UNITS HOU1 WHERE :b1 BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE AND PAF.PRIMARY_FLAG = 'Y' AND
PAS.ASSIGNMENT_STATUS_TYPE_ID = PAF.ASSIGNMENT_STATUS_TYPE_ID AND
((PAS.USER_STATUS = 'Terminate Assignment' AND PPS.ACTUAL_TERMINATION_DATE
>= :b2 AND PPS.ACTUAL_TERMINATION_DATE <= :b3 ) OR (PAS.USER_STATUS =
'Terminate Assignment' AND PAF.LAST_UPDATE_DATE BETWEEN :b2 AND :b3 AND
PPS.ACTUAL_TERMINATION_DATE < :b2 ) OR PAS.USER_STATUS IN ( 'Active
Assignment','LOA Paid With Accruals','LOA Paid Without Accruals','LOA
Unpaid With Accruals','LOA Unpaid Without Accruals' )) AND PAF.PERSON_ID =
PAP.PERSON_ID AND :b1 BETWEEN PAP.EFFECTIVE_START_DATE AND
PAP.EFFECTIVE_END_DATE AND PAP.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID AND
PPT.SYSTEM_PERSON_TYPE IN ( 'EMP','EX_EMP' ) AND
PAF.SOFT_CODING_KEYFLEX_ID = HSC.SOFT_CODING_KEYFLEX_ID AND
HOU1.ORGANIZATION_ID (+) = TO_NUMBER(HSC.SEGMENT1) AND HOU1.ATTRIBUTE1 =
LOOKUP.LOOKUP_CODE AND LOOKUP.LOOKUP_TYPE = 'HEC_INT_DTV_COMPANY_CODES'
AND PAF.ORGANIZATION_ID = HOU.ORGANIZATION_ID AND PAD.PERSON_ID =
PAP.PERSON_ID AND PAD.PRIMARY_FLAG = 'Y' AND :b1 BETWEEN PAD.DATE_FROM
AND NVL(PAD.DATE_TO,'31-DEC-4712') AND PPS.PERSON_ID = PAF.PERSON_ID AND
PPS.PERIOD_OF_SERVICE_ID = PAF.PERIOD_OF_SERVICE_ID AND PAF.GRADE_ID =
PG.GRADE_ID AND :b1 BETWEEN PG.DATE_FROM AND NVL(PG.DATE_TO,'31-DEC-4712')
AND PG.GRADE_DEFINITION_ID = PGD.GRADE_DEFINITION_ID AND
PTF.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID AND :b1 BETWEEN
PTF.EFFECTIVE_START_DATE AND PTF.EFFECTIVE_END_DATE AND PTS.ASSIGNMENT_ID =
PAF.ASSIGNMENT_ID AND :b1 BETWEEN PTS.EFFECTIVE_START_DATE AND
PTS.EFFECTIVE_END_DATE AND STATES.STATE_CODE = PTS.STATE_CODE AND
PJ.JOB_ID = PAF.JOB_ID AND PJD.JOB_DEFINITION_ID = PJ.JOB_DEFINITION_ID
AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID AND PAF.PEOPLE_GROUP_ID =
PPG.PEOPLE_GROUP_ID (+) AND HRL.LOCATION_ID = PAF.LOCATION_ID AND
STATES.STATE_ABBREV = HRL.REGION_2 AND ROWNUM < 50 ORDER BY HOU.ATTRIBUTE1,
PAP.EMPLOYEE_NUMBER,PAP.NATIONAL_IDENTIFIER,PAF.EFFECTIVE_START_DATE DESC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.50 0.49 0 0 0 0
Fetch 50 27.75 32.65 9574 816237 20 49
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 52 28.27 33.16 9574 816237 20 49
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
49 SORT ORDER BY
49 COUNT STOPKEY
49 MERGE JOIN
49 SORT JOIN
6237 NESTED LOOPS
6238 NESTED LOOPS
6238 NESTED LOOPS
6274 NESTED LOOPS
6274 NESTED LOOPS
6274 NESTED LOOPS OUTER
6274 NESTED LOOPS
6274 NESTED LOOPS
6274 NESTED LOOPS
30446 NESTED LOOPS
30446 NESTED LOOPS
30446 NESTED LOOPS
30446 NESTED LOOPS
30446 NESTED LOOPS
30448 NESTED LOOPS OUTER
30448 NESTED LOOPS
32667 NESTED LOOPS
32677 NESTED LOOPS
61844 TABLE ACCESS FULL PER_ALL_PEOPLE_F
94519 TABLE ACCESS BY INDEX ROWID PER_PERSON_TYPES
123686 INDEX UNIQUE SCAN (object id 423510)
65342 TABLE ACCESS BY INDEX ROWID PER_ADDRESSES
84710 INDEX RANGE SCAN (object id 36673)
63113 TABLE ACCESS BY INDEX ROWID PER_ALL_ASSIGNMENTS_F
291815 INDEX RANGE SCAN (object id 32600)
30210 INDEX UNIQUE SCAN (object id 249202)
60892 TABLE ACCESS BY INDEX ROWID PER_JOBS
60892 INDEX UNIQUE SCAN (object id 248526)
60890 TABLE ACCESS BY INDEX ROWID PER_GRADES
60890 INDEX UNIQUE SCAN (object id 248501)
60890 TABLE ACCESS BY INDEX ROWID PER_GRADE_DEFINITIONS
60890 INDEX UNIQUE SCAN (object id 423371)
60890 TABLE ACCESS BY INDEX ROWID HR_ALL_ORGANIZATION_UNITS
60890 INDEX UNIQUE SCAN (object id 130975)
60890 TABLE ACCESS BY INDEX ROWID PER_PERIODS_OF_SERVICE
60890 INDEX UNIQUE SCAN (object id 248464)
36718 TABLE ACCESS BY INDEX ROWID PER_ASSIGNMENT_STATUS_TYPES
60890 INDEX UNIQUE SCAN (object id 94274)
12546 TABLE ACCESS BY INDEX ROWID HR_LOCATIONS
12546 INDEX UNIQUE SCAN (object id 248511)
12546 TABLE ACCESS BY INDEX ROWID HR_SOFT_CODING_KEYFLEX
12546 INDEX UNIQUE SCAN (object id 248487)
6273 TABLE ACCESS BY INDEX ROWID HR_ALL_ORGANIZATION_UNITS
12546 INDEX UNIQUE SCAN (object id 130975)
12546 TABLE ACCESS BY INDEX ROWID PER_JOB_DEFINITIONS
12546 INDEX UNIQUE SCAN (object id 423403)
12546 TABLE ACCESS BY INDEX ROWID PAY_US_STATES
12546 INDEX UNIQUE SCAN (object id 249106)
12510 TABLE ACCESS BY INDEX ROWID PER_PAY_BASES
12510 INDEX UNIQUE SCAN (object id 248677)
12474 TABLE ACCESS BY INDEX ROWID PAY_US_EMP_FED_TAX_RULES_F
28931 INDEX RANGE SCAN (object id 88767)
6237 TABLE ACCESS BY INDEX ROWID PAY_US_EMP_STATE_TAX_RULES_F
24688 AND-EQUAL
37238 INDEX RANGE SCAN (object id 88772)
18889 INDEX RANGE SCAN (object id 88770)
49 SORT JOIN
11 TABLE ACCESS FULL FND_COMMON_LOOKUPS
********************************************************************************
SELECT DISTINCT DECODE(PAT.NAME,'Fellowship Unpaid','1',' ') FELLOWSHIP
FROM
PER_ABSENCE_ATTENDANCES PAA,PER_ABSENCE_ATTENDANCE_TYPES PAT WHERE
PAT.ABSENCE_ATTENDANCE_TYPE_ID (+) = PAA.ABSENCE_ATTENDANCE_TYPE_ID AND
:b1 BETWEEN NVL(PAA.DATE_START,'01-JAN-1900') AND NVL(PAA.DATE_END,
'31-DEC-4712') AND PAA.PERSON_ID = :b2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 49 0.04 0.03 0 0 0 0
Fetch 49 0.03 0.16 15 106 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 99 0.07 0.19 15 106 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 80 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT UNIQUE
0 NESTED LOOPS OUTER
49 TABLE ACCESS BY INDEX ROWID PER_ABSENCE_ATTENDANCES
56 INDEX RANGE SCAN (object id 36654)
0 TABLE ACCESS BY INDEX ROWID PER_ABSENCE_ATTENDANCE_TYPES
0 INDEX UNIQUE SCAN (object id 423338)
********************************************************************************
SELECT MAX(DECODE(CIA.TYPE_OF_CHANGE,'INT_COST_ALL_CHG','R','ORG_CHANGE','R',
'SHIFT_CODE','R','JOB_CHANGE','R','WEEKLY_HRS_CHANGE','R','COMPANY','R',
'ASSIGN_STATUS','R','HIRE_CODE','R',' '))
FROM
COM_INT_AUDIT CIA WHERE (CIA.ASSIGNMENT_ID = :b1 OR CIA.PERSON_ID = :b2 )
AND TRUNC(CIA.CURRENT_DATE) BETWEEN :b3 AND :b4 AND TRUNC(CIA.START_DATE)
<= :b3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 49 0.01 0.01 0 0 0 0
Fetch 49 0.08 1.27 135 408 0 49
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 99 0.09 1.28 135 408 0 49
Misses in library cache during parse: 1
Misses in
June 08, 2005 - 9:16 am UTC
well, what YOU do is find the low hanging fruit and tune that sql, make that sql go faster
Does gather stats effect RULE based optimizer
Tanmoy Choudhury, July 07, 2005 - 11:29 am UTC
Hi Tom,
We have one PROD database having RULE based optimizer. I am thinking of changing database parameters for one sesion (for one single program). Now as i am changing Optimizer_mode as CHOOSE for that particular program i have to gather stats for the table. As i am using CHOOSE for only one program does it effects for the entire system because i have to gather stats for all the table though all other program will be using RULE based optimizer for which stats is not necessary. Does gather stats for a table effect RULE based optimizer??
Application - ORA 11.0 (Oracle HRMS)
Database version - 8.1.7
Your comments will be helpful.
Thanks
Tanmoy
July 07, 2005 - 1:47 pm UTC
if the optimizer mode is set to RULE, it'll use the RBO even in the presence of statistics.
Will it hamper Performance
A reader, July 07, 2005 - 3:09 pm UTC
Hi Tom,
Follow up to the above, does any way it hampers execution plans if we have gather stats for tables and use RBO. Any impact of the existing system ?
Thanks
Tanmoy
July 07, 2005 - 3:56 pm UTC
RBO doesn't even know what stats are, if you have "forced" the use of the RBO it'll not look at stats at all.
The same query - different execution plans on prod and its mirror
danield, July 08, 2005 - 1:18 am UTC
Tom,
I have been asked to find out why the same query has different execution plan on production and its mirror (Oracle8i ver. 8.1.7.4). I have explain plan from both – production and mirror, on production, the query uses full table scan, on mirror – index. The query is fast on mirror, very slow on production.
So far, IÂ’ve found that both, mirror and production do have the same valid indexes on the table, however, the prod has statistic computed on dictionary tables ( probably caused by:
begin
dbms_stats.gather_database_stats;
end;
/
). Furthermore, the OPTIMIZER_MODE on mirror is set to FIRST_ROWS and CHOOSE on production. My plan is to set mirror to be as much like prod as possible and then check if the query will use full table scan. If yes, I will revert the changes back and check again. If the queryÂ’s performance will improve, I will do the same changes on prod. In additon, I am planing on removing statistics from tables owned by sys and system on prod.
Can you please advice or provide hints as what else should I check for when looking for differences between mirror and prod that may cause different execution plans?
Thank you very much for your time.
DanielD
July 08, 2005 - 7:28 am UTC
choose = all_rows when using the cbo, so that would be a material and very large difference between the two.
Different Result when running in RBO and CBO
Gaoyuan, December 28, 2005 - 1:09 am UTC
HI, Tom,
I hit this problem in Oracle9i that a query returns result correctly in CBO, but fails in RBO. From the explain output, I believe the difference is caused by the filtering sequence chosen by the optimizer.
The interesting part is that CBO seems to be able to determine a more proper/optimized filtering sequence, while RBO seems to filter from the last condition to the first in the where clause. Any explanation about this?
Thanks
SQL> create table my_tab (hash_value number, cpu_time_diff number, executions_diff number);
Table created.
SQL> insert into my_tab values (1, 900, 3);
1 row created.
SQL> insert into my_tab values (1, 560, 2);
1 row created.
SQL> insert into my_tab values (2, 10, 0);
1 row created.
SQL> insert into my_tab values (2, 230, 1);
1 row created.
SQL> commit;
Commit complete.
SQL> create table my_tab2 as select * from my_tab;
Table created.
SQL> analyze table my_tab2 compute statistics;
Table analyzed.
SQL> explain plan into my_plan_table for
2 select hash_value, cpu_time_diff/executions_diff from my_tab2 where hash_value=1
3 and cpu_time_diff/executions_diff>=270;
Explained.
SQL> explain plan for
2 select hash_value, cpu_time_diff/executions_diff from my_tab where hash_value=1
3 and cpu_time_diff/executions_diff>=270;
Explained.
SQL> select * from table (dbms_xplan.display('my_plan_table'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 |
|* 1 | TABLE ACCESS FULL | MY_TAB2 | 1 | 7 | 2 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("MY_TAB2"."HASH_VALUE"=1 AND
"MY_TAB2"."CPU_TIME_DIFF"/"MY_TAB2"."EXECUTIONS_DIFF">=270)
Note: cpu costing is off
15 rows selected.
SQL> select * from table (dbms_xplan.display('plan_table'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS FULL | MY_TAB | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("MY_TAB"."CPU_TIME_DIFF"/"MY_TAB"."EXECUTIONS_DIFF">=2
70 AND "MY_TAB"."HASH_VALUE"=1)
Note: rule based optimization
15 rows selected.
SQL> select hash_value, cpu_time_diff/executions_diff from my_tab2 where hash_value=1
2 and cpu_time_diff/executions_diff>=270;
HASH_VALUE CPU_TIME_DIFF/EXECUTIONS_DIFF
---------- -----------------------------
1 300
1 280
SQL> select hash_value, cpu_time_diff/executions_diff from my_tab where hash_value=1
2 and cpu_time_diff/executions_diff>=270;
ERROR:
ORA-01476: divisor is equal to zero
no rows selected
SQL> explain plan for
2 select hash_value, cpu_time_diff/executions_diff from my_tab where cpu_time_diff/executions_diff>=270 and hash_value=1;
Explained.
SQL> select * from table (dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS FULL | MY_TAB | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("MY_TAB"."HASH_VALUE"=1 AND
"MY_TAB"."CPU_TIME_DIFF"/"MY_TAB"."EXECUTIONS_DIFF">=270)
Note: rule based optimization
15 rows selected.
SQL> select hash_value, cpu_time_diff/executions_diff from my_tab where cpu_time_diff/executions_diff>=270 and hash_value=1;
HASH_VALUE CPU_TIME_DIFF/EXECUTIONS_DIFF
---------- -----------------------------
1 300
1 280
SQL> create index idx_my_tab_01 on my_tab (hash_value);
Index created.
SQL> explain plan for
2 select hash_value, cpu_time_diff/executions_diff from my_tab where hash_value=1
3 and cpu_time_diff/executions_diff>=270;
Explained.
SQL> select * from table (dbms_xplan.display('plan_table'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | TABLE ACCESS BY INDEX ROWID| MY_TAB | | | |
|* 2 | INDEX RANGE SCAN | IDX_MY_TAB_01 | | | |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("MY_TAB"."CPU_TIME_DIFF"/"MY_TAB"."EXECUTIONS_DIFF">=270)
2 - access("MY_TAB"."HASH_VALUE"=1)
Note: rule based optimization
16 rows selected.
SQL> select hash_value, cpu_time_diff/executions_diff from my_tab where hash_value=1
2 and cpu_time_diff/executions_diff>=270;
HASH_VALUE CPU_TIME_DIFF/EXECUTIONS_DIFF
---------- -----------------------------
1 300
1 280
December 28, 2005 - 9:32 am UTC
You are getting the right answer from both - exactly the right answer.
The problem is, you are assuming some predicable order of operation - and SQL never ever will make that promise. The only safe thing (I can get the RBO to return an answer without zero divide and the CBO gets the zero divide and vice-versa) is to use decode or case
where decode(executions_diff,0,to_number(null),cpu_time_diff/executions_diff) >= 270 ...
where case when executions_diff <> 0 then cpu_time/executions_diff end >= 270 ...
whenever you might have a zero divide (and never ever assume any sort of order of operation and remember - () do not assure the order of operation either and things like predicate pushing:
select *
from (select * from t where x = 5 )
where y/x > 0.2
is to Oracle the same as "select * from t where x = 5 and y/x > 0.2" hence you still need to consider zero divides even there)
Filtering in RBO
Gaoyuan, December 28, 2005 - 10:55 pm UTC
Thanks Tom for the reply.
Agree that we should always put in decode/case to prevent any divide by zero problem in real implementation. I accidentally hit this problem because I knew my expected result (filtered by "hash_value=1") would not have any executions_diff=0 data and I ommited the testing of executions_diff.
There are three test cases in my original post:
1) CBO: Select * from my_tab2 where hash_value=1 and cpu_time_diff/executions_diff>270
2) RBO: Select * from my_tab where hash_value=1 and cpu_time_diff/executions_diff>270
3) RBO with index on hash_value: Select * from my_tab where hash_value=1 and cpu_time_diff/executions_diff>270
Test 1 and 3 return the result correctly while test 2 gives the error of divisor equal to zero.
I did another test and test 4 works as well:
4) RBO: Select * from my_tab where cpu_time_diff/executions_diff>270 and hash_value=1
It seems () ensure some degree of operation order. Please see the two test cases below. They are supposed to be the same if the predicate is pushed, but one works and one fails.
SQL> select * from (select * from my_tab where hash_value=1)
2 where cpu_time_diff/executions_diff>270;
HASH_VALUE CPU_TIME_DIFF EXECUTIONS_DIFF
---------- ------------- ---------------
1 900 3
1 560 2
SQL> select * from my_tab where hash_value=1 and cpu_time_diff/executions_diff>270;
ERROR:
ORA-01476: divisor is equal to zero
no rows selected
<Quote>I can get the RBO to return an answer without zero divide and the CBO gets the zero divide and vice-versa</Quote>
Can you please show me some examples? Just in case I may hit these odd cases.
December 29, 2005 - 10:16 am UTC
No, I'll say it again (more strongly perhaps)
All three are correct in their observed behavior, all three are subject to returning an error at any point in time. None of the three is correctly coded. Two out of the three "got lucky". The two lucky ones could become unlucky at any point in the future. Do not look at them and call them "better or worse", they are all the same and all equally subject to raising an error.
You use CASE or DECODE, as stated. That is the only safe way to do it, there are no other safe ways. An optimizer path change (as YOU observed by adding an index!!!), a patch upgrade (causing the optimizer to change its path), ANYTHING - will cause the error to manifest itself.
CASE and DECODE are the only answer to your question, period.
What index facts influence CBO
A reader, January 12, 2006 - 11:36 pm UTC
Tom,
Recently I have been tuning a query which joins four tables in 9iR2. I found that the plan CBO generated is not optimal because of the assumption/available fact about an index of one of the table. I analyzed the indexes of all the table used yet the plan remains same.
With the CBO generated plan "Total Consistent get" approximates to 9000 gets when I tried with hints the consistent get approximates about 550.
Table Details
--------------
1. ACC_LVL_PERM #488653 Rows
2. ALERTS_DELIVERY_GROUP_INFO #21
3. ALERTS_SYSTEM_CONFIG #28
4. WIRE_OUTGOING #105468
The query is
SELECT t2.alerts_delivery_group_id,t3.user_id,t3.perm_id
FROM
ACC_LVL_PERM t3
, WIRE_OUTGOING t4
, ALERTS_DELIVERY_GROUP_INFO t2
, ALERTS_SYSTEM_CONFIG t1
WHERE t3.user_id = 1063781
AND t3.perm_id = t2.perm_id
AND t1.alert_type_id IN (22,24)
AND t1.alerts_delivery_group_id = t2.alerts_delivery_group_id
AND t4.client_id = 5004535
AND t3.account_identifier = t4.account_identifier
AND (((t4.template_id IS NULL) AND (t1.alert_type_id = 22)) OR
((t4.template_id IS NOT NULL) AND (t1.alert_type_id = 24)))
The plan generated by CBO
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=24)
1 0 CONCATENATION
2 1 NESTED LOOPS (Cost=12 Card=1 Bytes=44)
3 2 NESTED LOOPS (Cost=3 Card=3 Bytes=84)
4 3 MERGE JOIN (CARTESIAN) (Cost=3 Card=26 )
5 4 TABLE ACCESS (BY INDEX ROWID) OF
'ALERTS_SYSTEM_CONFIG' (Cost=1 Card=1 Bytes=6)
6 5 INDEX (UNIQUE SCAN) OF 'PK_ALERTS_SYS_CFG'
(UNIQUE)
7 4 INDEX (RANGE SCAN) OF 'PK_ACC_LVL_PERM'
(UNIQUE) (Cost=2 Card=26 Bytes=390)
8 3 INDEX (UNIQUE SCAN) OF 'PK_ALERTS_DLV_GRP_INFO'
(UNIQUE)
9 2 TABLE ACCESS (BY INDEX ROWID) OF 'WIRE_OUTGOING'
(Cost=12 Card=1 Bytes=16)
10 9 AND-EQUAL
11 10 INDEX (RANGE SCAN) OF 'FK1_WIRE_OUTGOING'
(NON-UNIQUE) (Cost=1 Card=57)
12 10 INDEX (RANGE SCAN) OF 'FK7_WIRE_OUTGOING'
(NON-UNIQUE) (Cost=1 Card=41)
13 1 NESTED LOOPS (Cost=12 Card=1 Bytes=44)
14 13 NESTED LOOPS (Cost=3 Card=3 Bytes=84)
15 14 MERGE JOIN (CARTESIAN) (Cost=3 Card=26
Bytes=546)
16 15 TABLE ACCESS (BY INDEX ROWID) OF
'ALERTS_SYSTEM_CONFIG' (Cost=1 Card=1 Bytes=6)
17 16 INDEX (UNIQUE SCAN) OF 'PK_ALERTS_SYS_CFG'
(UNIQUE)
18 15 INDEX (RANGE SCAN) OF 'PK_ACC_LVL_PERM'
(UNIQUE) (Cost=2 Card=26 Bytes=390)
19 14 INDEX (UNIQUE SCAN) OF
'PK_ALERTS_DLV_GRP_INFO' (UNIQUE)
20 13 TABLE ACCESS (BY INDEX ROWID) OF 'WIRE_OUTGOING'
(Cost=12 Card=1 Bytes=16)
21 20 AND-EQUAL
22 21 INDEX (RANGE SCAN) OF 'FK1_WIRE_OUTGOING'
(NON-UNIQUE) (Cost=1 Card=57)
23 21 INDEX (RANGE SCAN) OF 'FK7_WIRE_OUTGOING'
(NON-UNIQUE) (Cost=1 Card=41)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8696 consistent gets
0 physical reads
0 redo size
51811 bytes sent via SQL*Net to client
2259 bytes received via SQL*Net from client
162 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2412 rows processed
The plan generated by using ordered hint.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=46 Card=1
Bytes=44)
1 0 HASH JOIN (Cost=46 Card=1 Bytes=44)
2 1 HASH JOIN (Cost=43 Card=27 Bytes=1026)
3 2 HASH JOIN (Cost=41 Card=26 Bytes=806)
4 3 INDEX (RANGE SCAN) OF 'PK_ACC_LVL_PERM'
(UNIQUE) (Cost=3 Card=26 Bytes=390)
5 3 TABLE ACCESS (BY INDEX ROWID) OF
'WIRE_OUTGOING' (Cost=37 Card=57 Bytes=912)
6 5 INDEX (RANGE SCAN) OF 'FK1_WIRE_OUTGOING'
(NON-UNIQUE) (Cost=1 Card=57)
7 2 INDEX (FULL SCAN) OF 'PK_ALERTS_DLV_GRP_INFO'
(UNIQUE)(Cost=1 Card=21 Bytes=147)
8 1 INLIST ITERATOR
9 8 TABLE ACCESS (BY INDEX ROWID) OF
'ALERTS_SYSTEM_CONFIG' (Cost=2 Card=2 Bytes=12)
10 9 INDEX (RANGE SCAN) OF 'PK_ALERTS_SYS_CFG' (UNIQUE) (Cost=1 Card=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
558 consistent gets
0 physical reads
0 redo size
51811 bytes sent via SQL*Net to client
2259 bytes received via SQL*Net from client
162 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2412 rows processed
The plan generated scans the table "wire_outgoing" twice, splits the IN clause into two separate queries, uses INDEX JOIN on FK1 and FK7. For me everything looks scary about this plan.
I hope you will help me to fix this without using the hint and also where can I learn more about facts like how CBO decides or as a user what things we need to do to make sure CBO generates optimal plan (something like checklist).
Thanks!
"Rule" under 10.1.0.4.0
Shivaswamy, January 17, 2006 - 10:03 am UTC
*** string too long, truncated *** (30146), ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Tom,
I have found that, many of my DBA scripts run considerably slower on 10GR1 (as against 9.1.4) under CBO. Here is one such example which stands apart. Do you have any comments?
********************************************************************************
SELECT /*+ rule */ dba_data_files.tablespace_name "TABLESPACE NAME",
dba_data_files.file_name "FILE NAME",
dba_data_files.bytes/1024/1024 "CUR SIZE MB",
SUM(NVL(dba_free_space.bytes, 0))/1024 "FREE SIZE KB",
(SUM(NVL(dba_free_space.blocks, 0)) / MAX(NVL(dba_data_files.blocks, 1)))
* 100 "% FREE"
FROM dba_data_files,
dba_free_space
WHERE dba_data_files.file_id = dba_free_space.file_id (+)
GROUP BY dba_data_files.tablespace_name,
dba_data_files.file_name,
dba_data_files.bytes
ORDER BY dba_data_files.tablespace_name,
dba_data_files.file_name
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 72 21.36 41.36 18940 414789 4296 1062
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 74 21.38 41.38 18940 414792 4296 1062
Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: 604
Rows Row Source Operation
------- ---------------------------------------------------
1062 SORT GROUP BY (cr=414789 pr=18940 pw=0 time=41358211 us)
6218 MERGE JOIN OUTER (cr=414789 pr=18940 pw=0 time=41324138 us)
1062 SORT JOIN (cr=4260 pr=1395 pw=0 time=10285870 us)
1062 VIEW (cr=4260 pr=1395 pw=0 time=10289205 us)
1062 UNION-ALL (cr=4260 pr=1395 pw=0 time=10288136 us)
0 NESTED LOOPS (cr=1064 pr=2 pw=0 time=38143 us)
0 NESTED LOOPS (cr=1064 pr=2 pw=0 time=38139 us)
1062 FIXED TABLE FULL X$KCCFN (cr=0 pr=0 pw=0 time=5519 us)
0 TABLE ACCESS BY INDEX ROWID FILE$ (cr=1064 pr=2 pw=0 time=29737 us)
1062 INDEX UNIQUE SCAN I_FILE1 (cr=2 pr=1 pw=0 time=20566 us)(object id 41)
0 TABLE ACCESS CLUSTER TS$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_TS# (cr=0 pr=0 pw=0 time=0 us)(object id 7)
1062 NESTED LOOPS (cr=3196 pr=1393 pw=0 time=10246758 us)
1062 MERGE JOIN (cr=2132 pr=1393 pw=0 time=10230759 us)
1062 SORT JOIN (cr=2132 pr=1393 pw=0 time=10213761 us)
1062 NESTED LOOPS (cr=2132 pr=1393 pw=0 time=9084706 us)
1062 FIXED TABLE FULL X$KTFBHC (cr=1068 pr=1042 pw=0 time=8771620 us)
1062 TABLE ACCESS CLUSTER TS$ (cr=1064 pr=351 pw=0 time=276702 us)
1062 INDEX UNIQUE SCAN I_TS# (cr=2 pr=0 pw=0 time=11588 us)(object id 7)
1062 SORT JOIN (cr=0 pr=0 pw=0 time=13794 us)
1062 FIXED TABLE FULL X$KCCFN (cr=0 pr=0 pw=0 time=4884 us)
1062 TABLE ACCESS BY INDEX ROWID FILE$ (cr=1064 pr=0 pw=0 time=12551 us)
1062 INDEX UNIQUE SCAN I_FILE1 (cr=2 pr=0 pw=0 time=4886 us)(object id 41)
5871 SORT JOIN (cr=410529 pr=17545 pw=0 time=31036517 us)
5871 VIEW (cr=410529 pr=17545 pw=0 time=4210956 us)
5871 UNION-ALL (cr=410529 pr=17545 pw=0 time=4205078 us)
0 NESTED LOOPS (cr=1074 pr=0 pw=0 time=15922 us)
0 NESTED LOOPS (cr=1074 pr=0 pw=0 time=15916 us)
1062 TABLE ACCESS FULL FILE$ (cr=10 pr=0 pw=0 time=1196 us)
0 TABLE ACCESS CLUSTER FET$ (cr=1064 pr=0 pw=0 time=13492 us)
1062 INDEX UNIQUE SCAN I_TS# (cr=2 pr=0 pw=0 time=4776 us)(object id 7)
0 TABLE ACCESS CLUSTER TS$ (cr=0 pr=0 pw=0 time=0 us)
4292 NESTED LOOPS (cr=16330 pr=1110 pw=0 time=2262462 us)
4292 NESTED LOOPS (cr=12036 pr=1110 pw=0 time=2189462 us)
4292 FIXED TABLE FULL X$KTFBFE (cr=3450 pr=1110 pw=0 time=2107867 us)
4292 TABLE ACCESS BY INDEX ROWID FILE$ (cr=8586 pr=0 pw=0 time=71026 us)
4292 INDEX UNIQUE SCAN I_FILE2 (cr=4294 pr=0 pw=0 time=35321 us)(object id 42)
4292 TABLE ACCESS CLUSTER TS$ (cr=4294 pr=0 pw=0 time=62305 us)
4292 INDEX UNIQUE SCAN I_TS# (cr=2 pr=0 pw=0 time=19850 us)(object id 7)
1579 NESTED LOOPS (cr=391223 pr=16432 pw=0 time=61339983 us)
1579 NESTED LOOPS (cr=389642 pr=16432 pw=0 time=61305215 us)
1579 NESTED LOOPS (cr=386482 pr=16432 pw=0 time=61265682 us)
210189 FIXED TABLE FULL X$KTFBUE (cr=72823 pr=16418 pw=0 time=13039312 us)
1579 TABLE ACCESS BY INDEX ROWID RECYCLEBIN$ (cr=313659 pr=14 pw=0 time=13754403 us)
6195459 INDEX RANGE SCAN RECYCLEBIN$_TS (cr=227498 pr=5 pw=0 time=8010907 us)(object id 405072)
1579 TABLE ACCESS BY INDEX ROWID FILE$ (cr=3160 pr=0 pw=0 time=32444 us)
1579 INDEX UNIQUE SCAN I_FILE2 (cr=1581 pr=0 pw=0 time=15998 us)(object id 42)
1579 TABLE ACCESS CLUSTER TS$ (cr=1581 pr=0 pw=0 time=29022 us)
1579 INDEX UNIQUE SCAN I_TS# (cr=2 pr=0 pw=0 time=8393 us)(object id 7)
0 NESTED LOOPS (cr=1902 pr=3 pw=0 time=32591 us)
0 NESTED LOOPS (cr=1902 pr=3 pw=0 time=32583 us)
0 NESTED LOOPS (cr=1902 pr=3 pw=0 time=32577 us)
943 TABLE ACCESS FULL RECYCLEBIN$ (cr=14 pr=3 pw=0 time=1954 us)
0 TABLE ACCESS CLUSTER UET$ (cr=1888 pr=0 pw=0 time=14791 us)
943 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=945 pr=0 pw=0 time=7637 us)(object id 9)
0 TABLE ACCESS BY INDEX ROWID FILE$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_FILE2 (cr=0 pr=0 pw=0 time=0 us)(object id 42)
0 TABLE ACCESS CLUSTER TS$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_TS# (cr=0 pr=0 pw=0 time=0 us)(object id 7)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 72 0.00 0.00
control file sequential read 78 0.00 0.00
db file sequential read 18718 0.02 21.79
SQL*Net message from client 72 0.05 0.26
********************************************************************************
SELECT dba_data_files.tablespace_name "TABLESPACE NAME",
dba_data_files.file_name "FILE NAME",
dba_data_files.bytes/1024/1024 "CUR SIZE MB",
SUM(NVL(dba_free_space.bytes, 0))/1024 "FREE SIZE KB",
(SUM(NVL(dba_free_space.blocks, 0)) / MAX(NVL(dba_data_files.blocks, 1)))
* 100 "% FREE"
FROM dba_data_files,
dba_free_space
WHERE dba_data_files.file_id = dba_free_space.file_id (+)
GROUP BY dba_data_files.tablespace_name,
dba_data_files.file_name,
dba_data_files.bytes
ORDER BY dba_data_files.tablespace_name,
dba_data_files.file_name
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 8 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 72 1803.53 2373.75 1001581 14575096 3384594 1062
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 74 1803.57 2373.79 1001581 14575104 3384594 1062
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 604
Rows Row Source Operation
------- ---------------------------------------------------
1062 SORT GROUP BY (cr=14575096 pr=1001581 pw=0 time=1323331415 us)
6218 NESTED LOOPS OUTER (cr=14575096 pr=1001581 pw=0 time=3422825538 us)
1062 VIEW (cr=4254 pr=0 pw=0 time=246501 us)
1062 UNION-ALL (cr=4254 pr=0 pw=0 time=241188 us)
0 NESTED LOOPS (cr=1064 pr=0 pw=0 time=19853 us)
0 NESTED LOOPS (cr=1064 pr=0 pw=0 time=19847 us)
1062 FIXED TABLE FULL X$KCCFN (cr=0 pr=0 pw=0 time=4942 us)
0 TABLE ACCESS BY INDEX ROWID FILE$ (cr=1064 pr=0 pw=0 time=11884 us)
1062 INDEX UNIQUE SCAN I_FILE1 (cr=2 pr=0 pw=0 time=4975 us)(object id 41)
0 TABLE ACCESS CLUSTER TS$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_TS# (cr=0 pr=0 pw=0 time=0 us)(object id 7)
1062 NESTED LOOPS (cr=3190 pr=0 pw=0 time=190507 us)
1062 NESTED LOOPS (cr=1064 pr=0 pw=0 time=136337 us)
1062 NESTED LOOPS (cr=1064 pr=0 pw=0 time=66204 us)
1062 FIXED TABLE FULL X$KCCFN (cr=0 pr=0 pw=0 time=35406 us)
1062 TABLE ACCESS BY INDEX ROWID FILE$ (cr=1064 pr=0 pw=0 time=25198 us)
1062 INDEX UNIQUE SCAN I_FILE1 (cr=2 pr=0 pw=0 time=11664 us)(object id 41)
1062 FIXED TABLE FIXED INDEX X$KTFBHC (ind:1) (cr=0 pr=0 pw=0 time=66932 us)
1062 TABLE ACCESS CLUSTER TS$ (cr=2126 pr=0 pw=0 time=39377 us)
1062 INDEX UNIQUE SCAN I_TS# (cr=2 pr=0 pw=0 time=14007 us)(object id 7)
5871 VIEW (cr=14570842 pr=1001581 pw=0 time=2123169538 us)
5871 UNION-ALL PARTITION (cr=14570842 pr=1001581 pw=0 time=2123154753 us)
0 NESTED LOOPS (cr=4248 pr=0 pw=0 time=65132 us)
0 NESTED LOOPS (cr=4248 pr=0 pw=0 time=60728 us)
1062 TABLE ACCESS BY INDEX ROWID FILE$ (cr=2124 pr=0 pw=0 time=28159 us)
1062 INDEX UNIQUE SCAN I_FILE1 (cr=1062 pr=0 pw=0 time=14778 us)(object id 41)
0 TABLE ACCESS CLUSTER FET$ (cr=2124 pr=0 pw=0 time=27090 us)
1062 INDEX UNIQUE SCAN I_TS# (cr=1062 pr=0 pw=0 time=15594 us)(object id 7)
0 TAB...
January 17, 2006 - 10:19 am UTC
my comment - wow, that was really big and long....
are your system statistics up to date.
Shivaswamy, January 17, 2006 - 12:24 pm UTC
Tom,
Because I decided to give both trace out put and explain plan out put, it grew in size. Sorry about that.
We migrated recently to 10G and have not started collecting the system stats as yet. But, however, I collected it and I have values in sys.aux_stats$ now. But this has not helped in speeding up the query.
Any other idea?
Thanks,
Shivaswamy
January 17, 2006 - 4:15 pm UTC
that is just system statistics, speed of single/multi block IO, cpuspeed and such. It doesn't tell the optimizer how big anything is.
In 10g, the CBO is in use.
DO your system tables have statistics, have you gathered statstics for things owned by sys
A reader, January 17, 2006 - 4:31 pm UTC
Tom,
Yes, the Sys/System objects do have stats in place. Ee have gathered them. Also, GATHER_STATS_JOB is active.
Shivaswamy
January 17, 2006 - 4:47 pm UTC
I'd guess that fixed table stats haven't been - can you ask them about that?
Shivaswamy, January 17, 2006 - 6:36 pm UTC
Tom,
As you suggested, I collected stats for SYS & SYSTEM schema with
exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
I re-ran the script. No improvement. What more can I try, please?
Thanks.
January 18, 2006 - 7:27 am UTC
Shivaswamy, January 18, 2006 - 9:39 am UTC
My sincere thanks to you. That worked. Just as /*+ RULE*/ now CBO gives me the output in 11 sec.!
A reader, March 21, 2006 - 2:04 pm UTC
Is OPTIMIZER_MAX_PERMUTATIONS obsolete in Oracle10gR2?
March 22, 2006 - 2:43 pm UTC
i would consider it "obsolete" (as in, don't touch it) in all releases myself.
yes, in 10g it is "hidden", not to be changed.
How to check if Oracle 10g Collected Fixed Table Statistics
Jay, March 27, 2006 - 2:12 pm UTC
For ordinary tables we can check "last_analyzed" to find out whether or when last analyze was done on them. However, in 10g how can we check if statistics are already collected for fixed tables as they are not in dba_tables?
Thanks
March 27, 2006 - 3:32 pm UTC
desc dba_tab_statistics;
Re: check Oracle 10g Fixed Table Statistics
Jay, March 28, 2006 - 9:58 am UTC
Great, Thanks!
A reader, April 21, 2006 - 2:10 pm UTC
April 21, 2006 - 3:45 pm UTC
Suggest if you want to truly learn how the CBO works, you get the book by Jonathan Lewis
</code>
http://www.amazon.com/gp/product/1590596366/ <code>
The CBO is a mathematical model written by human beings. I think that says it all.
Why why why
Reader, April 22, 2006 - 7:43 am UTC
Why can't CBO recognize such a simple thing?
SQL> create table test (object_id primary key, object_name) as
2 select object_id, object_name
3 from all_objects;
Table created.
select min(object_id) from test -> INDEX FULL SCAN (MIN/MAX)
select max(object_id) from test -> INDEX FULL SCAN (MIN/MAX)
select min(object_id), max(object_id) from test -> INDEX FAST FULL SCAN!!!
Why why why CBO can't recognize it can do two min/max scans to get the answer?
Oracle 10g CBO behavior (Wrong cardinality estimation)
Tao, May 19, 2006 - 4:25 pm UTC
I came across this Oracle 10g CBO behavior and did not know how to fix it.
Version 10.2.0.1
OS: Redhat Linux
TESTTABLE has 35 million rows and has been analyzed. Calc_date column has 120 distinct values and has a histogram collected using frequency method.
select seq, max(calc_date) max_calc_date
from TESTTABLE
where calc_date <= to_date('3/31/2006', 'mm/dd/yyyy')
group by seq
This SQL is a inline view within two queries combined by UNION ALL.
The Oracle 10G evaluate this query and decided that it is going to return 30,000 rows. In reality, it is supposed to return 600,000 rows. Because of this, Oracle 10g optimizer picked up NestLoop instead of hash join, which messed up the execution plan.
After doing some research on the raw trace file, I found that Oracle 10g optimizer tries to rewirte the SQL by using a CHECK constraint on calc_date column, which is defined as calc_date >= to_date('01/01/1970', 'mm/dd/yyyy'). For some reason, Oracle 10g changed the SQL to be:
select seq, max(calc_date) max_calc_date
from TESTTABLE
where calc_date <= to_date('3/31/2006', 'mm/dd/yyyy')
group by seq
having calc_date >= to_date('01/01/1970', 'mm/dd/yyyy')
and then apply a selectivity of 0.05 on the having clause (600,000 * 0.05 = 30,000). That is why it estimate the cardinality to be 30,000 rows.
Where is the 0.05 selectivity coming from?
Also, this only happened when there is a UNION ALL in the SQL. If I run that SQL alone, it can estimate the right cardinality. I can provide the 10053 trace file if needed.
Any thoughts? Thanks for your help!
May 20, 2006 - 4:40 pm UTC
show me how you got that from a raw trace file? You don't see the rewritten query.
Something else is doing that for you, not going to see "query rewrite" in the trace file like that.
Tao, May 22, 2006 - 8:19 am UTC
Thanks for your reply.
Here is the SQLPLUS autotrace:
select A.mis_seq, A.calc_date, A.curr_earned_prem_amt, rownum
from mi_sub_earned_prem_hists A,
(
select mis_seq, max(calc_date) max_calc_date
from mi_sub_earned_prem_hists
where calc_date <= to_date('3/31/2006', 'mm/dd/yyyy')
group by mis_seq
) B
where A.mis_seq = B.mis_seq
and A.calc_date = B.max_calc_date
union
select A.mis_seq, A.calc_date, A.curr_earned_prem_amt, rownum
from mi_sub_earned_prem_hists A,
(
select mis_seq, max(calc_date) max_calc_date
from mi_sub_earned_prem_hists miseph2
where trunc(miseph2.calc_date,'year') < trunc(to_date('3/31/2006', 'mm/dd/yyyy'),'year')
group by mis_seq
) B
where A.mis_seq = B.mis_seq
and A.calc_date = B.max_calc_date 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 4197908426
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57530 | 2247K| | 157K (36)| 00:36:51 |
| 1 | SORT UNIQUE | | 57530 | 2247K| 5920K| 157K (36)| 00:36:51 |
| 2 | UNION-ALL | | | | | | |
| 3 | COUNT | | | | | | |
| 4 | NESTED LOOPS | | 29310 | 1144K| | 102K (1)| 00:23:49 |
| 5 | VIEW | | 29402 | 631K| | 72534 (2)| 00:16:56 |
|* 6 | FILTER | | | | | | |
| 7 | HASH GROUP BY | | 29402 | 373K| 866M| 72534 (2)| 00:16:56 |
|* 8 | INDEX FULL SCAN | MISEPH_I_3 | 37M| 467M| | 72534 (2)| 00:16:56 |
|* 9 | INDEX RANGE SCAN | MISEPH_I_3 | 1 | 18 | | 2 (0)| 00:00:01 |
| 10 | COUNT | | | | | | |
|* 11 | HASH JOIN | | 28220 | 1102K| | 55507 (7)| 00:12:58 |
| 12 | VIEW | | 28308 | 608K| | 27920 (10)| 00:06:31 |
|* 13 | FILTER | | | | | | |
| 14 | HASH GROUP BY | | 28308 | 359K| 43M| 27920 (10)| 00:06:31 |
|* 15 | INDEX FAST FULL SCAN| MISEPH_PK | 1886K| 23M| | 25061 (11)| 00:05:51 |
| 16 | INDEX FAST FULL SCAN | MISEPH_I_3 | 37M| 647M| | 27306 (2)| 00:06:23 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(MAX("CALC_DATE")>=TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
8 - access("CALC_DATE"<=TO_DATE('2006-03-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
filter("CALC_DATE"<=TO_DATE('2006-03-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
9 - access("A"."MIS_SEQ"="B"."MIS_SEQ" AND "A"."CALC_DATE"="B"."MAX_CALC_DATE")
11 - access("A"."MIS_SEQ"="B"."MIS_SEQ" AND "A"."CALC_DATE"="B"."MAX_CALC_DATE")
13 - filter(MAX("CALC_DATE")>=TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
15 - filter(TRUNC(INTERNAL_FUNCTION("MISEPH2"."CALC_DATE"),'fmyear')<TO_DATE('2006-01-01
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
SYS>set autotrace off
The 6 step shows there is a filter "MAX("CALC_DATE")>=TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')"
Here is the two parts of the 10053 trace file:
Part One:
constraint: TO_DATE('01/01/1970','MM/DD/YYYY')<="A"."CALC_DATE"
predicates with check contraints: "A"."MIS_SEQ"="B"."MIS_SEQ" AND "A"."CALC_DATE"="B"."MAX_CALC_DATE" AND "A"."CALC_DATE">=TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
after transitive predicate generation:"A"."MIS_SEQ"="B"."MIS_SEQ" AND "A"."CALC_DATE"="B"."MAX_CALC_DATE" AND "A"."CALC_DATE">=TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "B"."MAX_CALC_DATE">=TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')finally: "A"."MIS_SEQ"="B"."MIS_SEQ" AND "A"."CALC_DATE"="B"."MAX_CALC_DATE" AND "B"."MAX_CALC_DATE">=TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
FPD: Following transitive predicates are generated in SEL$1 (#3) :
"B"."MAX_CALC_DATE">=TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
FPD: Following are pushed to having clause of SEL$2 (#4) :
FPD: Considering simple filter push in SEL$2 (#4)
FPD: Current where clause predicates in SEL$2 (#4) :
"MI_SUB_EARNED_PREM_HISTS"."CALC_DATE"<=TO_DATE('2006-03-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
kkogcp: try to generate transitive predicate from check constraints for SEL$2 (#4)
constraint: TO_DATE('01/01/1970','MM/DD/YYYY')<="MI_SUB_EARNED_PREM_HISTS"."CALC_DATE"predicates with check contraints: "MI_SUB_EARNED_PREM_HISTS"."CALC_DATE"<=TO_DATE('2006-03-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "MI_SUB_EARNED_PREM_HISTS"."CALC_DATE">=TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
after transitive predicate generation:"MI_SUB_EARNED_PREM_HISTS"."CALC_DATE"<=TO_DATE('2006-03-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "MI_SUB_EARNED_PREM_HISTS"."CALC_DATE">=TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<=TO_DATE('2006-03-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')finally: "MI_SUB_EARNED_PREM_HISTS"."CALC_DATE"<=TO_DATE('2006-03-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<=TO_DATE('2006-03-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
Part Two: (having selectivity : 0.05)
*********************************
Number of join permutations tried: 1
*********************************
GROUP BY cardinality: 566157.00, TABLE cardinality: 1886558.00
HAVING selectivity: 0.05 -> GROUPS: 28308
SORT resource Sort statistics
Sort width: 36 Area size: 262144 Max Area size: 13107200
Degree: 1
Blocks to Sort: 2883 Row size: 25 Total Rows: 1886558
Initial runs: 4 Merge passes: 1 IO Cost / pass: 3369
Total IO sort cost: 3126 Total CPU sort cost: 0
Total Temp space used: 46105000
Final - All Rows Plan: Best join order: 1
Cost: 12206.0000 Degree: 1 Card: 1886558.0000 Bytes: 24525254
Resc: 12206.0000 Resc_io: 12206.0000 Resc_cpu: 0
Resp: 12206.0000 Resp_io: 12206.0000 Resc_cpu: 0
Let me know if more information is needed.
Thanks again.
May 22, 2006 - 1:20 pm UTC
I naturally assumed you meant a 10046 trace (sql_trace=true) trace file.
5% is the default selectivity for many things - that is where that is coming from.
Tao, May 22, 2006 - 1:59 pm UTC
I got the trace file using:
alter session set events '10053 trace name context forever, level 1'
Is there a way that I can stop the 10g optimzer pushing down the CHECK contraints or help it get the correct estimation of cardinality?
Thanks,
Tao
Constraints and transitive closure
Jonathan Lewis, May 22, 2006 - 4:00 pm UTC
I think you will probably find that changing the join predicate (possibly in both places) to
a.calc_date = b.calc_date + 0
should be sufficient to stop the constraint from becoming an extra filter predicate - which may have an impact on the 5% factor; but as Tom says, the optimize often uses 5% for an "I don't know what else to do" range comparison.
Predicate push down
Jonathan Lewis, May 22, 2006 - 4:54 pm UTC
Just as a further thought.
You might find that you can stop the push-down (and not do the +0 thing) if you add no_merge hints to the two inline aggregate views. For a more modern version of the same thing, a couple of factored subqueries, with the materialize hint might do just as well.
There's also a NO_PUSH_PRED hint that might have a suitable effect in the two queries that you UNION.
I'm a little worried that you might be losing data in your query, though. Remember that the UNION eliminates duplicates, and it's not obvious that the two halves of your main query are independently supposed to return no duplicates.
Tao, May 23, 2006 - 8:56 am UTC
Thanks a lot Jonathan!
I tried put a '+0'. It is working great! The pushing down is gone and the executino plan is perfect. Why is that doing the trick?
I tried to use 'no merge' and 'no_push_pred' hint like this:
select /*+ NO_MERGE(B) NO_PUSH_PRED(B) */ A.mis_seq, A.calc_date, A.curr_earned_prem_amt
from mi_sub_earned_prem_hists A,
(
select mis_seq, max(calc_date) max_calc_date
from mi_sub_earned_prem_hists miseph2
where trunc(miseph2.calc_date) <= trunc(to_date('3/31/2006', 'mm/dd/yyyy'))
group by mis_seq
) B
where A.mis_seq = B.mis_seq
and A.calc_date = B.max_calc_date
union all
select /*+ NO_MERGE(B) NO_PUSH_PRED(B) */ A.mis_seq, A.calc_date, A.curr_earned_prem_amt
from mi_sub_earned_prem_hists A,
(
select mis_seq, max(calc_date) max_calc_date
from mi_sub_earned_prem_hists miseph2
where trunc(miseph2.calc_date) < trunc(to_date('3/31/2006', 'mm/dd/yyyy'),'year')
group by mis_seq
) B
where A.mis_seq = B.mis_seq
and A.calc_date = B.max_calc_date
It is still doing the filter having max() > '1/1/1970' using the CHECK constraint. Am I doing something wrong with the hints here?
Tao, May 23, 2006 - 9:12 am UTC
why oracle 10g CBO push down the CHECK constraints and still apply the 0.05 selectivity when it should know that all the rows instead of 1/20 of them must satisfy the Check constraints?
By the way, will the histogram collected on column that is in range comparison help the CBO to stop using the default selectivity (0.05)?
Thanks.
Cost/Rule based Optimizer
Michael, July 16, 2006 - 10:30 am UTC
We have installed windows based software sccounting package which when looking at the sql statements being run, they are using /*+ RULE */ based Hints. On Oracle 10G Release 2, does this have an impact on the performance? What would Oracle 10GR2 do in such a case, ignore the Rule and use COST based optimization instead? I have included a session parameter of cursor_sharing = FORCE because of the non-existience of Bind Variables using a logon trigger. Is there anything else to improve performance that I might be missing?
Thanks for your insight.
July 16, 2006 - 11:54 am UTC
sounds like the only real way to increase performance might be to discover another vendor, since this one has decided to do all of the wrong things.
/*+ RULE */ still "works" in 10g.
You could evaluate what would happen IF they used the CBO in YOUR TEST DATABASE (in case you didn't catch that - in YOUR TEST DATABASE) by gathering statistics and telling the parser to ignore optimizer hints all together:
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select /*+ RULE */ * from dual d1;
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| DUAL |
----------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
ops$tkyte%ORA10GR2> alter session set "_optimizer_ignore_hints" = true;
Session altered.
ops$tkyte%ORA10GR2> select /*+ RULE */ * from dual d2;
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
ops$tkyte%ORA10GR2> set autotrace off
<b>in your TEST INSTANCE</b>, giving you some data points to take to your vendor - to get them to start removing these things..
full table scan,
A reader, August 09, 2006 - 12:35 pm UTC
I have a table (partitioned table) with multi million rows in it and is analyzed. The NUM_ROWS column in dba_tab_partitions has pretty big number.
Now I truncate the table.
The num_rows column in dba_tab_partitions has not changed since I did not analyze the table now.
If I issue a query to do a full table scan, will the optimizer try to scan the table considering the numbers in data dictionary OR will it scan upto the high water mark which in this case is at the bottom since the table is empty?
Also, is there a need to truncate each partition (after truncating the table) to make sure the storage in each partition is being released back?
thanks,
August 09, 2006 - 4:13 pm UTC
the optimizer will use the statistics to determine a plan.
and if the plan says "full scan", the full scan will scan up to the high water mark.
which the truncation reset.
the optimizer doesn't "scan"
the optimizer generates a plan - the plan might say 'full scan table' and we can only full scan that which exists.
the truncate of the table hits all of the partitions.
OK
Raju, August 16, 2006 - 7:01 am UTC
Hi Tom,
I was asked this question by an interviewer.
"I have two SQL statements with different costs(assume
100 and 200).which one will execute faster? "
My answer was "Statement with lower cost will execute
faster than the other one with higher cost".
Is this correct?
August 16, 2006 - 8:43 am UTC
http://asktom.oracle.com/Misc/i-believe-strongly-there-are-only-two.html
answer #2 is the only correct answer.
tell me, which of the following two queries will execute faster. One of them has a cost of 76, the other a cost of 1:
ops$tkyte%ORA10GR2> create table t
2 as
3 select * from all_objects;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(object_id);
Index created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> alter session set optimizer_index_cost_adj = 100;
Session altered.
ops$tkyte%ORA10GR2> select * from t where object_id between 0 and 5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2963 | 269K| 76 (0
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2963 | 269K| 76 (0
|* 2 | INDEX RANGE SCAN | T_IDX | 2963 | | 8 (0
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=0 AND "OBJECT_ID"<=5000)
ops$tkyte%ORA10GR2> alter session set optimizer_index_cost_adj = 1;
Session altered.
ops$tkyte%ORA10GR2> select * from t where object_id between 0 and 5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2963 | 269K| 1 (0
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2963 | 269K| 1 (0
|* 2 | INDEX RANGE SCAN | T_IDX | 2963 | | 1 (0
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=0 AND "OBJECT_ID"<=5000)
ops$tkyte%ORA10GR2> set autotrace off
Yet again
Shivaswamy, May 15, 2007 - 12:21 pm UTC
Tom,
After six years, I got one more issue on DBA_DATA_FILES. The query runs over 30 moniutes. Extract from Level 12 trace:
********************************************************************************
SELECT NVL(SUM(MAXBYTES-BYTES),0)
FROM
DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_DATA_FILES WHERE
TABLESPACE_NAME=:B1 AND MAXBYTES <> 0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 394 0.16 0.13 0 4 0 0
Fetch 394 525.73 1725.44 0 1592569 317564 394
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 789 525.89 1725.58 0 1592573 317564 394
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 2 0.00 0.00
control file sequential read 3984128 0.01 1377.06
row cache lock 403 0.00 0.04
gc current block 3-way 1 0.00 0.00
gc current block 2-way 3 0.00 0.00
********************************************************************************
With the rule hint.
********************************************************************************
SELECT /*+ RULE */ NVL(SUM(MAXBYTES-BYTES),0)
FROM
DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_DATA_FILES WHERE
TABLESPACE_NAME=:B1 AND MAXBYTES <> 0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 394 0.10 0.10 0 0 0 0
Fetch 394 46.38 57.37 0 962938 317564 394
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 789 46.48 57.47 0 962938 317564 394
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 65 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 35460 0.00 13.86
********************************************************************************
My query has been doing just good in Non-RAC 10.1.0.5 DB. This RAC DB is on 10.2.0.2.0 where I have issue.
I found that, we are going to x$kccfe in 10.2.0.2 for dba_data_files to get ONLINSE_STATUS. Here are the plans:
> explain plan for
2 SELECT /*+ RULE */ NVL(SUM(MAXBYTES-BYTES),0)
3 FROM
4 DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_DATA_FILES WHERE
5 TABLESPACE_NAME=:B1 AND MAXBYTES <> 0);
Explained.
> @utlxls
Plan hash value: 767823743
-------------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | MERGE JOIN | |
| 3 | SORT JOIN | |
| 4 | VIEW | DBA_DATA_FILES |
| 5 | UNION-ALL | |
| 6 | MERGE JOIN | |
| 7 | SORT JOIN | |
| 8 | NESTED LOOPS | |
| 9 | NESTED LOOPS | |
| 10 | FIXED TABLE FULL | X$KCCFE |
|* 11 | TABLE ACCESS BY INDEX ROWID | FILE$ |
|* 12 | INDEX UNIQUE SCAN | I_FILE1 |
| 13 | TABLE ACCESS CLUSTER | TS$ |
|* 14 | INDEX UNIQUE SCAN | I_TS# |
|* 15 | SORT JOIN | |
|* 16 | FIXED TABLE FULL | X$KCCFN |
| 17 | NESTED LOOPS | |
| 18 | MERGE JOIN | |
| 19 | MERGE JOIN | |
| 20 | SORT JOIN | |
| 21 | NESTED LOOPS | |
| 22 | FIXED TABLE FULL | X$KCCFE |
|* 23 | TABLE ACCESS BY INDEX ROWID | FILE$ |
|* 24 | INDEX UNIQUE SCAN | I_FILE1 |
|* 25 | SORT JOIN | |
|* 26 | FIXED TABLE FULL | X$KCCFN |
|* 27 | SORT JOIN | |
| 28 | FIXED TABLE FULL | X$KTFBHC |
| 29 | TABLE ACCESS CLUSTER | TS$ |
|* 30 | INDEX UNIQUE SCAN | I_TS# |
|* 31 | SORT JOIN | |
| 32 | VIEW | VW_NSO_1 |
| 33 | SORT UNIQUE | |
| 34 | VIEW | DBA_DATA_FILES |
| 35 | UNION-ALL | |
| 36 | MERGE JOIN | |
| 37 | SORT JOIN | |
| 38 | NESTED LOOPS | |
| 39 | NESTED LOOPS | |
| 40 | TABLE ACCESS BY INDEX ROWID | TS$ |
|* 41 | INDEX UNIQUE SCAN | I_TS1 |
| 42 | FIXED TABLE FULL | X$KCCFE |
|* 43 | TABLE ACCESS BY INDEX ROWID | FILE$ |
|* 44 | INDEX UNIQUE SCAN | I_FILE1 |
|* 45 | SORT JOIN | |
|* 46 | FIXED TABLE FULL | X$KCCFN |
| 47 | MERGE JOIN | |
| 48 | SORT JOIN | |
| 49 | NESTED LOOPS | |
| 50 | MERGE JOIN | |
| 51 | SORT JOIN | |
| 52 | MERGE JOIN | |
| 53 | TABLE ACCESS BY INDEX ROWID| TS$ |
|* 54 | INDEX UNIQUE SCAN | I_TS1 |
|* 55 | FILTER | |
|* 56 | FILTER | |
| 57 | FIXED TABLE FULL | X$KTFBHC |
|* 58 | SORT JOIN | |
|* 59 | FIXED TABLE FULL | X$KCCFN |
|* 60 | TABLE ACCESS BY INDEX ROWID | FILE$ |
|* 61 | INDEX UNIQUE SCAN | I_FILE1 |
|* 62 | SORT JOIN | |
| 63 | FIXED TABLE FULL | X$KCCFE |
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - filter("F"."SPARE1" IS NULL)
12 - access("FE"."FENUM"="F"."FILE#")
14 - access("F"."TS#"="TS"."TS#")
15 - access("FNFNO"="F"."FILE#")
filter("FNFNO"="F"."FILE#")
16 - filter("FNTYP"=4 AND BITAND("FNFLG",4)<>4 AND "FNNAM" IS NOT
NULL AND "INST_ID"=USERENV('INSTANCE'))
23 - filter("F"."SPARE1" IS NOT NULL)
24 - access("FE"."FENUM"="F"."FILE#")
25 - access("FNFNO"="F"."FILE#")
filter("FNFNO"="F"."FILE#")
26 - filter("FNTYP"=4 AND BITAND("FNFLG",4)<>4 AND "FNNAM" IS NOT
NULL AND "INST_ID"=USERENV('INSTANCE'))
27 - access("FNFNO"="HC"."KTFBHCAFNO")
filter("FNFNO"="HC"."KTFBHCAFNO")
30 - access("HC"."KTFBHCTSN"="TS"."TS#")
31 - access("FILE_ID"="$nso_col_1")
filter("FILE_ID"="$nso_col_1")
41 - access("TS"."NAME"=:B1)
43 - filter("TS"."BLOCKSIZE"*"F"."MAXEXTEND"<>0 AND
"F"."TS#"="TS"."TS#" AND "F"."SPARE1" IS NULL)
44 - access("FE"."FENUM"="F"."FILE#")
45 - access("FNFNO"="F"."FILE#")
filter("FNFNO"="F"."FILE#")
46 - filter("FNTYP"=4 AND BITAND("FNFLG",4)<>4 AND "FNNAM" IS NOT
NULL AND "INST_ID"=USERENV('INSTANCE'))
54 - access("TS"."NAME"=:B1)
55 - filter(DECODE("HC"."KTFBHCCVAL",0,"TS"."BLOCKSIZE"*"HC"."KTFBHCMA
XSZ",NULL)<>0)
56 - filter("HC"."KTFBHCTSN"="TS"."TS#")
58 - access("FNFNO"="HC"."KTFBHCAFNO")
filter("FNFNO"="HC"."KTFBHCAFNO")
59 - filter("FNTYP"=4 AND BITAND("FNFLG",4)<>4 AND "FNNAM" IS NOT
NULL AND "INST_ID"=USERENV('INSTANCE'))
60 - filter("F"."SPARE1" IS NOT NULL)
61 - access("FNFNO"="F"."FILE#")
62 - access("FE"."FENUM"="F"."FILE#")
filter("FE"."FENUM"="F"."FILE#")
Note
-----
- rule based optimizer used (consider using cbo)
116 rows selected.
> explain plan for
2 SELECT NVL(SUM(MAXBYTES-BYTES),0)
3 FROM
4 DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_DATA_FILES WHERE
5 TABLESPACE_NAME=:B1 AND MAXBYTES <> 0);
Explained.
> @utlxls
Plan hash value: 3334791156
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 107 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
|* 2 | FILTER | | | | | |
| 3 | VIEW | DBA_DATA_FILES | 97 | 3783 | 102 (1)| 00:00:02 |
| 4 | UNION-ALL | | | | | |
| 5 | NESTED LOOPS | | 1 | 109 | 4 (25)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 100 | 3 (34)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 96 | 3 (34)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | FILE$ | 1 | 26 | 2 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL | X$KCCFN | 96 | 6720 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 4 | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS CLUSTER | TS$ | 1 | 9 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 96 | 11136 | 99 (2)| 00:00:02 |
| 14 | NESTED LOOPS | | 96 | 10272 | 3 (34)| 00:00:01 |
| 15 | NESTED LOOPS | | 96 | 9888 | 3 (34)| 00:00:01 |
|* 16 | HASH JOIN | | 96 | 7776 | 3 (34)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KCCFN | 96 | 6720 | 0 (0)| 00:00:01 |
|* 18 | TABLE ACCESS FULL | FILE$ | 395 | 4345 | 2 (0)| 00:00:01 |
|* 19 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 22 | 0 (0)| 00:00:01 |
|* 20 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 4 | 0 (0)| 00:00:01 |
| 21 | TABLE ACCESS CLUSTER | TS$ | 1 | 9 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 23 | VIEW | DBA_DATA_FILES | 2 | 86 | 4 (0)| 00:00:01 |
| 24 | UNION-ALL | | | | | |
| 25 | MERGE JOIN CARTESIAN | | 1 | 120 | 2 (0)| 00:00:01 |
| 26 | NESTED LOOPS | | 1 | 50 | 2 (0)| 00:00:01 |
| 27 | NESTED LOOPS | | 1 | 46 | 2 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | TS$ | 1 | 25 | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | I_TS1 | 1 | | 0 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 21 | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | 0 (0)| 00:00:01 |
|* 32 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 4 | 0 (0)| 00:00:01 |
| 33 | BUFFER SORT | | 1 | 70 | 2 (0)| 00:00:01 |
|* 34 | FIXED TABLE FULL | X$KCCFN | 1 | 70 | 0 (0)| 00:00:01 |
| 35 | NESTED LOOPS | | 1 | 126 | 2 (0)| 00:00:01 |
| 36 | MERGE JOIN CARTESIAN | | 1 | 110 | 2 (0)| 00:00:01 |
| 37 | NESTED LOOPS | | 1 | 40 | 2 (0)| 00:00:01 |
| 38 | NESTED LOOPS | | 1 | 36 | 2 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID| TS$ | 1 | 25 | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | I_TS1 | 1 | | 0 (0)| 00:00:01 |
|* 41 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 11 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | 0 (0)| 00:00:01 |
|* 43 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 4 | 0 (0)| 00:00:01 |
| 44 | BUFFER SORT | | 1 | 70 | 2 (0)| 00:00:01 |
|* 45 | FIXED TABLE FULL | X$KCCFN | 1 | 70 | 0 (0)| 00:00:01 |
|* 46 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 16 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM ( (SELECT "FNNAM" "FILE_NAME","F"."FILE#"
"FILE_ID","TS"."NAME" "TABLESPACE_NAME","TS"."BLOCKSIZE"*"F"."BLOCKS" "BYTES","F"."BLOCKS"
"BLOCKS",DECODE("F"."STATUS$",1,'INVALID',2,'AVAILABLE','UNDEFINED') "STATUS","F"."RELFILE#"
"RELATIVE_FNO",DECODE("F"."INC",0,'NO','YES') "AUTOEXTENSIBLE","TS"."BLOCKSIZE"*"F"."MAXEXTEND"
"MAXBYTES","F"."MAXEXTEND" "MAXBLOCKS","F"."INC"
"INCREMENT_BY","TS"."BLOCKSIZE"*("F"."BLOCKS"-1) "USER_BYTES","F"."BLOCKS"-1
"USER_BLOCKS",DECODE("FE"."FETSN",0,DECODE(BITAND("FE"."FESTA",2),0,'SYSOFF','SYSTEM'),DECODE(BI
TAND("FE"."FESTA",18),0,'OFFLINE',2,'ONLINE','RECOVER')) "ONLINE_STATUS" FROM SYS."X$KCCFE"
"FE",SYS."X$KCCFN" "X$KCCFN","SYS"."TS$" "TS","SYS"."FILE$" "F" WHERE "F"."FILE#"=:B1 AND
"F"."SPARE1" IS NULL AND "F"."TS#"="TS"."TS#" AND "TS"."BLOCKSIZE"*"F"."MAXEXTEND"<>0 AND
"TS"."NAME"=:B1 AND "FNFNO"=:B2 AND "FNTYP"=4 AND BITAND("FNFLG",4)<>4 AND "FNNAM" IS NOT NULL
AND "INST_ID"=USERENV('INSTANCE') AND "FE"."FENUM"=:B3) UNION ALL (SELECT "FNNAM"
"FILE_NAME","F"."FILE#" "FILE_ID","TS"."NAME" "TABLESPACE_NAME",DECODE("HC"."KTFBHCCVAL",0,"TS".
"BLOCKSIZE"*"HC"."KTFBHCSZ",NULL) "BYTES",DECODE("HC"."KTFBHCCVAL",0,"HC"."KTFBHCSZ",NULL)
"BLOCKS",DECODE("F"."STATUS$",1,'INVALID',2,'AVAILABLE','UNDEFINED') "STATUS","F"."RELFILE#"
"RELATIVE_FNO",DECODE("HC"."KTFBHCCVAL",0,DECODE("HC"."KTFBHCINC",0,'NO','YES'),NULL)
"AUTOEXTENSIBLE",DECODE("HC"."KTFBHCCVAL",0,"TS"."BLOCKSIZE"*"HC"."KTFBHCMAXSZ",NULL)
"MAXBYTES",DECODE("HC"."KTFBHCCVAL",0,"HC"."KTFBHCMAXSZ",NULL)
"MAXBLOCKS",DECODE("HC"."KTFBHCCVAL",0,"HC"."KTFBHCINC",NULL)
"INCREMENT_BY",DECODE("HC"."KTFBHCCVAL",0,"HC"."KTFBHCUSZ"*"TS"."BLOCKSIZE",NULL)
"USER_BYTES",DECODE("HC"."KTFBHCCVAL",0,"HC"."KTFBHCUSZ",NULL)
"USER_BLOCKS",DECODE("FE"."FETSN",0,DECODE(BITAND("FE"."FESTA",2),0,'SYSOFF','SYSTEM'),DECODE(BI
TAND("FE"."FESTA",18),0,'OFFLINE',2,'ONLINE','RECOVER')) "ONLINE_STATUS" FROM SYS."X$KCCFE"
"FE","SYS"."TS$" "TS","SYS"."X$KTFBHC" "HC","SYS"."FILE$" "F",SYS."X$KCCFN" "X$KCCFN" WHERE
"FNFNO"=:B4 AND "FNTYP"=4 AND BITAND("FNFLG",4)<>4 AND "FNNAM" IS NOT NULL AND
"INST_ID"=USERENV('INSTANCE') AND "F"."FILE#"=:B5 AND "F"."SPARE1" IS NOT NULL AND
"HC"."KTFBHCAFNO"=:B6 AND "FNFNO"="HC"."KTFBHCAFNO" AND "HC"."KTFBHCTSN"="TS"."TS#" AND
DECODE("HC"."KTFBHCCVAL",0,"TS"."BLOCKSIZE"*"HC"."KTFBHCMAXSZ",NULL)<>0 AND "TS"."NAME"=:B1 AND
"FE"."FENUM"=:B7)) "DBA_DATA_FILES"))
7 - access("FNFNO"="F"."FILE#")
8 - filter("F"."SPARE1" IS NULL)
9 - filter("FNTYP"=4 AND BITAND("FNFLG",4)<>4 AND "FNNAM" IS NOT NULL AND
"INST_ID"=USERENV('INSTANCE'))
10 - filter("FE"."FENUM"="F"."FILE#")
12 - access("F"."TS#"="TS"."TS#")
16 - access("FNFNO"="F"."FILE#")
17 - filter("FNTYP"=4 AND BITAND("FNFLG",4)<>4 AND "FNNAM" IS NOT NULL AND
"INST_ID"=USERENV('INSTANCE'))
18 - filter("F"."SPARE1" IS NOT NULL)
19 - filter("FNFNO"="HC"."KTFBHCAFNO")
20 - filter("FE"."FENUM"="F"."FILE#")
22 - access("HC"."KTFBHCTSN"="TS"."TS#")
29 - access("TS"."NAME"=:B1)
30 - filter("F"."SPARE1" IS NULL AND "F"."TS#"="TS"."TS#" AND
"TS"."BLOCKSIZE"*"F"."MAXEXTEND"<>0)
31 - access("F"."FILE#"=:B1)
32 - filter("FE"."FENUM"=:B1)
34 - filter("FNFNO"=:B1 AND "FNTYP"=4 AND BITAND("FNFLG",4)<>4 AND "FNNAM" IS NOT NULL AND
"INST_ID"=USERENV('INSTANCE'))
40 - access("TS"."NAME"=:B1)
41 - filter("F"."SPARE1" IS NOT NULL)
42 - access("F"."FILE#"=:B1)
43 - filter("FE"."FENUM"=:B1)
45 - filter("FNFNO"=:B1 AND "FNTYP"=4 AND BITAND("FNFLG",4)<>4 AND "FNNAM" IS NOT NULL AND
"INST_ID"=USERENV('INSTANCE'))
46 - filter("HC"."KTFBHCAFNO"=:B1 AND "FNFNO"="HC"."KTFBHCAFNO" AND
"HC"."KTFBHCTSN"="TS"."TS#" AND DECODE("HC"."KTFBHCCVAL",0,"TS"."BLOCKSIZE"*"HC"."KTFBHCMAXSZ",N
ULL)<>0)
115 rows selected.
Here again
Shivaswamy, May 15, 2007 - 1:43 pm UTC
Tom,
After six years, I got one more issue on DBA_DATA_FILES. The query runs over 30 moniutes. Extract from Level 12 trace:
********************************************************************************
SELECT NVL(SUM(MAXBYTES-BYTES),0)
FROM
DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_DATA_FILES WHERE
TABLESPACE_NAME=:B1 AND MAXBYTES <> 0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 394 0.16 0.13 0 4 0 0
Fetch 394 525.73 1725.44 0 1592569 317564 394
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 789 525.89 1725.58 0 1592573 317564 394
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 2 0.00 0.00
control file sequential read 3984128 0.01 1377.06
row cache lock 403 0.00 0.04
gc current block 3-way 1 0.00 0.00
gc current block 2-way 3 0.00 0.00
********************************************************************************
With the rule hint.
********************************************************************************
SELECT /*+ RULE */ NVL(SUM(MAXBYTES-BYTES),0)
FROM
DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_DATA_FILES WHERE
TABLESPACE_NAME=:B1 AND MAXBYTES <> 0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 394 0.10 0.10 0 0 0 0
Fetch 394 46.38 57.37 0 962938 317564 394
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 789 46.48 57.47 0 962938 317564 394
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 65 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 35460 0.00 13.86
********************************************************************************
My query has been doing just good in Non-RAC 10.1.0.5 DB. This RAC DB is on 10.2.0.2.0 where I have issue.
I found that, we are going to x$kccfe in 10.2.0.2 for dba_data_files to get ONLINSE_STATUS. Here are the plans:
-- to be continued.....
Again
Shivaswamy, May 15, 2007 - 1:46 pm UTC
Tom,
After six years, I got one more issue on DBA_DATA_FILES. The query runs over 30 moniutes. Extract from Level 12 trace:
********************************************************************************
SELECT NVL(SUM(MAXBYTES-BYTES),0)
FROM
DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_DATA_FILES WHERE
TABLESPACE_NAME=:B1 AND MAXBYTES <> 0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 394 0.16 0.13 0 4 0 0
Fetch 394 525.73 1725.44 0 1592569 317564 394
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 789 525.89 1725.58 0 1592573 317564 394
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 2 0.00 0.00
control file sequential read 3984128 0.01 1377.06
row cache lock 403 0.00 0.04
gc current block 3-way 1 0.00 0.00
gc current block 2-way 3 0.00 0.00
********************************************************************************
With the rule hint.
********************************************************************************
SELECT /*+ RULE */ NVL(SUM(MAXBYTES-BYTES),0)
FROM
DBA_DATA_FILES WHERE FILE_ID IN (SELECT FILE_ID FROM DBA_DATA_FILES WHERE
TABLESPACE_NAME=:B1 AND MAXBYTES <> 0)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 394 0.10 0.10 0 0 0 0
Fetch 394 46.38 57.37 0 962938 317564 394
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 789 46.48 57.47 0 962938 317564 394
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 65 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 35460 0.00 13.86
********************************************************************************
My query has been doing just good in Non-RAC 10.1.0.5 DB. This RAC DB is on 10.2.0.2.0 where I have issue.
I found that, we are going to x$kccfe in 10.2.0.2 for dba_data_files to get ONLINSE_STATUS. Here are the plans:
-- to be continued.....
A small question regarding buffer cache size and plan stability
Tim Akhmadeev, September 28, 2007 - 9:35 am UTC
Hello, Tom!
Could you please give a comment on a phrase about CBO in your book Expert One-on-One Oracle?
(Following is my interpreting of a Russian translated version, so probably it would sound like a nonsense for you. sorry :))
Chapter is called Plan stability; Capabilities Overview:
"Execution plan can be significantly affected by:
...
* changes of parameters in init.ora file, which affects optimizer behavior, for example db_block_buffers
..."
Could you please comment the example? What are the reasons for CBO to take into account
buffer cache size? How CBO implements this feature (if any)? When it can be useful?
Thanks in advance,
Tim Akhmadeev.
PS. It's my first post at AskTom, and I want to thank you for your work, it's really useful!
September 28, 2007 - 5:57 pm UTC
well, db_block_buffers (db_cache settings now in 9i and up) won't directly affect the optimizers final plan inasmuch as it will affect the runtime performance of it. I was sort of trying to say "there are many things that affect the query and it's performance and the optimization of it"
but, db block buffers does not directly influence the optimizer - sorry about that confusion.
funny, it has been so long since I've seen "db_block_buffers" - I had to think about it for a while!
compute or estimate stats
David, September 29, 2007 - 9:01 pm UTC
What is Oracle's recommendation with regard to stats? Is compute stats the best for optimizer as it gives all the information to optimizer? or estimate stats (10% for example)is recommended only because of time taken by compute stats may not be practical to find a window to run stats in production database considering the size of the database we have these days? Thanks
October 03, 2007 - 2:08 pm UTC
if we had a highly recommended way to do it - we would not have implemented the opportunities to do it many different ways.
it depends is the only answer
many times, 1% is better than good enough.
most times, 10% is way better than good enough.
sometimes, 100% is the way to go.
sometimes - 0% is the way to go and you should just set stats.
cbo plans change in 8i and 10gR2,
A reader, March 31, 2008 - 6:49 pm UTC
We are in the process of upgrading our databases from 8i to 10gR2. On one of the database server, we first had 8i binaries and imported oracle dictionary and table structure of our key tables into this server (probably from production...I didn't do this)
We extracted all the SQL from v$sqlarea and generated explain plan and stored them in our PLAN TABLE (plan_8i) .
The same server was later upgraded to 10gR2. The dictionary and everything else remained same.
We now performed the same process of generating explain plans for the same set of SQLs and stored them in a different PLAN TABLE (plan_10g).
Later we did diff on sql plans and found out few SQL statements are having different plans in 8i and 10g (even though the values in data dictionary for those underlying tables were exactly same).
What should we look into for the reason behind the change in explain plan apart from data dictionary statistics? The few init parameters and its values are:
optimizer_dynamic_sampling integer 0
optimizer_features_enable string 10.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string FIRST_ROWS_1
optimizer_secure_view_merging boolean TRUE
compatible string 10.2.0.3
db_file_multiblock_read_count integer 16
The database server where our table structure reside does not have any data and it is 10gr2.
Appreciate your help.
Thanks,
-S
April 01, 2008 - 7:21 am UTC
you should take into consideration that between 8i and 10gR2 there was
8iR3
9iR1
9iR2
10gR1
10gR2
you are talking five releases of changes
If the plans were all the same, I would be truly surprised.
Lose those init.ora parameters - all of them (well, maybe JUST maybe keep the optimizer mode, and you can keep compatible).
Lose all of them - every one. Get rid of any and all parameter you don't have to set - but especially lose every parameter you have set in that list with the exception of compatible and MAYBE the optimizer mode.
explain plan cont...
A reader, April 01, 2008 - 12:11 pm UTC
Thanks for the reply. Well, the team here assumes the 8i plans are the best and they are trying to make sure the plans on 10g to be same too (but they are happy if 10g plans are better than 8i). However, some of the SQL statements, it seems 8i is better.
For example:
SELECT .....
FROM BS_APP_SHIPMENT_UNIT , BS_APP_SHIPMENT
WHERE BS_APP_SHIPMENT_UNIT.app_shipment_id = BS_APP_SHIPMENT.id AND
BS_APP_SHIPMENT.account_number = :account_number AND
BS_APP_SHIPMENT.time_created >= :time_created AND
BS_APP_SHIPMENT.part_key IN ( 2, 1, 0, 5 ) AND
BS_APP_SHIPMENT_UNIT.part_key IN ( 2, 1, 0, 5 ) AND
BS_APP_SHIPMENT.status IN ( 'I', 'P' )
8i Plan 10g Plan
----------------------------------------------------------------------- | --------------------------------------------------------
| Id | Operation | Name | | Id | Operation | Name |
----------------------------------------------------------------------- | --------------------------------------------------------
| 0 | SELECT STATEMENT | | | 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | | | | 1 | HASH JOIN | |
| 2 | PARTITION RANGE INLIST | | | | 2 | PARTITION RANGE INLIST| |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| BS_APP_SHIPMENT | | | 3 | TABLE ACCESS FULL | BS_APP_SHIPMENT |
| 4 | INDEX RANGE SCAN | BS_APP_SHIPMENT_N1 | | | 4 | PARTITION RANGE INLIST| |
| 5 | PARTITION RANGE INLIST | | | | 5 | TABLE ACCESS FULL | BS_APP_SHIPMENT_UNIT |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| BS_APP_SHIPMENT_UNIT | | --------------------------------------------------------
| 7 | INDEX RANGE SCAN | BS_APP_SHIPMENT_UNIT_N1 | <
----------------------------------------------------------------------- <
It looks 8i is doing the right job by using the index.
I don't have data on this database to test but by looking at the query, I feel index range scan is perhaps good choice.
In this case, how to make 10g plan look similar(or same) to 8i plan without using hints?
Thanks,
-S
April 01, 2008 - 12:14 pm UTC
test.
do not hint
do not read a plan and say "this looks better"
test it.
cont...
A reader, April 01, 2008 - 12:13 pm UTC
The plans I copy and pasted are not in the readable format.
Pasting it again
8i Plan
-----------------------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | PARTITION RANGE INLIST | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| BS_APP_SHIPMENT |
| 4 | INDEX RANGE SCAN | BS_APP_SHIPMENT_N1 |
| 5 | PARTITION RANGE INLIST | |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| BS_APP_SHIPMENT_UNIT |
| 7 | INDEX RANGE SCAN | BS_APP_SHIPMENT_UNIT_N1 |
-----------------------------------------------------------------------
10g Plan
| --------------------------------------------------------
| Id | Operation | Name |
| --------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 1 | HASH JOIN | |
| | 2 | PARTITION RANGE INLIST| |
| | 3 | TABLE ACCESS FULL | BS_APP_SHIPMENT |
| | 4 | PARTITION RANGE INLIST| |
| | 5 | TABLE ACCESS FULL | BS_APP_SHIPMENT_UNIT |
| --------------------------------------------------------
<
<
April 01, 2008 - 12:15 pm UTC
there are no estimate card= values, why not.
you have removed all of those parameters right (don't really want to work with you otherwise, I don't like that at all)
8i plan vs 10g plan cont...
A reader, April 02, 2008 - 2:17 pm UTC
In continuing the effort of finding out the reason for the difference in explain plan between 8i and 10g, I have supplied an example. This example consists of the query, info from dba_tab_partitions, dba_ind_partitions, dba_tab_columns and dba_ind_columns.
The data dictionary values what is seen here is same for 8i plan and for 10g plan. But still the plan differs.
could you please help me to find out the difference using the information from the data dictionary?
Query:
SELECT ROWID ,
id ,
account_number ,
status ,
flags ,
time_created ,
part_key ,
pypl_time_touched ,
carrier ,
vendor ,
currency_code ,
shipper_user_address_id ,
shippee_user_address_id ,
rate_class ,
package_type ,
dimension_unit ,
height ,
length ,
width ,
weight_unit ,
weight ,
payment_date ,
insurance_value ,
quote_base ,
quote_transportation ,
quote_service_options ,
quote_insurance ,
quote_included_insured_value ,
quote_delivery_confirmation ,
quote_on_time_delivery ,
quote_signature_confirmation ,
quote_flat_discount ,
quote_percentage_discount ,
quote_surcharge_total ,
quote_tax_total ,
quote_total ,
ship_label_id ,
service_options ,
non_delivery_option ,
manual_company_id ,
manual_company_other,
shippee_email ,
mailing_date ,
to_shippee_ship_message_id ,
shippee_phone_number ,
addressee_name ,
address1 ,
address2 ,
city ,
state ,
zip ,
country ,
origination_zip ,
applied_preset
FROM BS_APP_SHIPMENT
WHERE ship_label_id = :ship_label_id AND part_key IN ( 2,1,0,5 ) FOR UPDATE
SQL> select partition_name,num_rows, blocks, empty_blocks, avg_space, chain_cnt,avg_row_len, sample_size
from dba_tab_partitions 2
3 where table_name='BS_APP_SHIPMENT';
PARTITION_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE
------------------- ------------ ---------- ------------ ---------- ---------- ----------- -----------
BS_APP_SHIPMENT_P0 0 0 2542 0 0 0
BS_APP_SHIPMENT_P1 0 0 2542 0 0 0
BS_APP_SHIPMENT_P2 0 0 2542 0 0 0
BS_APP_SHIPMENT_P3 0 0 2542 0 0 0
BS_APP_SHIPMENT_P4 5320 1860 682 7595 0 99 41
BS_APP_SHIPMENT_P5 363555 6652 1010 1722 228 111 33443
BS_APP_SHIPMENT_P6 0 0 2542 0 0 0
7 rows selected.
SQL> select partition_name,blevel, leaf_blocks,avg_leaf_blocks_per_key,avg_data_blocks_per_key,
clustering_factor, sample_size, num_rows,distinct_keys, pct_direct_access
from dba_ind_partitions
where index_name ='BS_APP_SHIPMENT_N2';
PARTITION_NAME BLEVEL LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR SAMPLE_SIZE NUM_ROWS DISTINCT_KEYS PCT_DIRECT_ACCESS
------------------- -------- ----------- ----------------------- ----------------------- ----------------- ----------- ---------- ------------- -----------------
BS_APP_SHIPMENT_P0 0 0 0 0 0 0 0
BS_APP_SHIPMENT_P1 0 0 0 0 0 0 0
BS_APP_SHIPMENT_P2 0 0 0 0 0 0 0
BS_APP_SHIPMENT_P3 0 0 0 0 0 0 0
BS_APP_SHIPMENT_P4 1 4 1 1 877 951 951 938
BS_APP_SHIPMENT_P5 1 1044 1 1 405147 44796 421324.541 418126
BS_APP_SHIPMENT_P6 0 0 0 0 0 0 0
7 rows selected.
SQL> select column_name,column_position
from dba_ind_columns
where index_name 2 3 ='BS_APP_SHIPMENT_N2';
COLUMN_NAME COLUMN_POSITION
------------------------------ ---------------
SHIP_LABEL_ID 1
SQL> select column_name,num_distinct, low_value, high_value, density, num_nulls,avg_col_len, num_buckets, sample_size
from dba_tab_columns 2
3 where table_name='BS_APP_SHIPMENT';
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS AVG_COL_LEN NUM_BUCKETS SAMPLE_SIZE
------------------------------ ------------ ---------- ---------- ---------- ---------- ----------- ----------- -----------
APPLIED_PRESET
ID 368875 C104 C3255B2F 2.7109E-06 0 4 1
PART_KEY 2 C104 C105 .5 0 2 1
ACCOUNT_NUMBER 8390 CA0210201B CA031F3A5C .00011919 0 11 1
61155B4355 2D17355D04
4A 52
STATUS 5 43 52 .2 0 1 1
FLAGS 3 C102 C106 .333333333 37634 2 1
TIME_CREATED 230255 C50C3D4116 C50C3F591C .000004343 0 6 1
60 4B
PYPL_TIME_TOUCHED 41925 C50C3D411D C50C3F591C .000023852 0 6 1
23 58
CARRIER 1 C103 C103 1 0 2 1
VENDOR 2 C102 C104 .5 0 2 1
CURRENCY_CODE 1 555344 555344 1 0 3 1
SHIPPER_USER_ADDRESS_ID 8481 80 C5033D5B1D .000117911 0 6 1
46
SHIPPEE_USER_ADDRESS_ID 157799 C21846 C5033E0208 6.3372E-06 14795 6 1
49
RATE_CLASS 10 80 C20307 .1 0 2 1
PACKAGE_TYPE 6 80 C106 .166666667 0 2 1
DIMENSION_UNIT 0 0 368875 1 0
HEIGHT 1 80 80 1 368691 1 1
LENGTH 1 80 80 1 368691 1 1
WIDTH 1 80 80 1 368691 1 1
WEIGHT_UNIT 1 4F5A 4F5A 1 0 2 1
WEIGHT 145 80 C21045 .006896552 0 2 1
PAYMENT_DATE 368875 C50C3B1248 C50C3F5914 2.7109E-06 0 6 1
60 60
INSURANCE_VALUE 3677 80 C4023D .000271961 0 3 1
SERVICE_OPTIONS 8 80 C30F4B59 .125 207879 2 1
NON_DELIVERY_OPTION 0 0 368875 1 0
QUOTE_BASE 245 80 C3020B51 .004081633 216473 2 1
QUOTE_TRANSPORTATION 1 80 80 1 366247 1 1
QUOTE_SERVICE_OPTIONS 1 80 80 1 366247 1 1
QUOTE_INSURANCE 8 80 C20733 .125 216473 1 1
QUOTE_INCLUDED_INSURED_VALUE 2 80 C302 .5 216473 1 1
QUOTE_DELIVERY_CONFIRMATION 2 80 C10F .5 291426 1 1
QUOTE_ON_TIME_DELIVERY 1 80 80 1 366247 1 1
QUOTE_SIGNATURE_CONFIRMATION 2 80 C20224 .5 364486 1 1
QUOTE_FLAT_DISCOUNT 1 80 80 1 366247 1 1
QUOTE_PERCENTAGE_DISCOUNT 1 80 80 1 366247 1 1
QUOTE_SURCHARGE_TOTAL 1 80 80 1 366247 1 1
QUOTE_TAX_TOTAL 1 80 80 1 366247 1 1
QUOTE_TOTAL 395 80 C302111A .002531646 216473 2 1
SHIP_LABEL_ID 12583 80 C5041D5B62 .000079472 230016 3 1
4F
MAILING_DATE 0 0 368875 1 0
TO_SHIPPEE_SHIP_MESSAGE_ID 13427 80 C30F3128 .000074477 221702 2 1
SHIPPEE_EMAIL 0 0 368875 1 0
SHIPPEE_PHONE_NUMBER 0 0 368875 1 0
ADDRESSEE_NAME 2021 3420412057 79736F6F6E .000494805 346450 1 1
4952454C45 2072
5353
ADDRESS1 1456 23536A6F20 7572622E76 .000686813 352712 1 1
3132303335 696C6C6120
6361726F6C
696E612063
616C6C6520
233220442D
3334
ADDRESS2 1880 23203433 73616D65 .000531915 366995 1 1
CITY 1093 2E 79756B6F6E .000914913 352712 1 1
STATE 52 414B 5759 .019230769 352712 1 1
ZIP 1350 3030373832 3939363534 .000740741 352723 1 1
COUNTRY 1 5553 5553 1 352712 1 1
ORIGINATION_ZIP 1785 3031303032 3939383335 .000560224 323394 1 1
MANUAL_COMPANY_ID 5 C102 C107 .2 367397 1 1
MANUAL_COMPANY_OTHER 172 2D 78787878 .005813953 367397 1 1
53 rows selected.
8i plan
------------------------------------------------------------------ |
| Id | Operation | Name |
------------------------------------------------------------------ |
| 0 | SELECT STATEMENT | |
| 1 | FOR UPDATE | |
| 2 | PARTITION RANGE INLIST | | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| BS_APP_SHIPMENT | |
| 4 | INDEX RANGE SCAN | BS_APP_SHIPMENT_N2 | |
------------------------------------------------------------------ <
10g plan
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | FOR UPDATE | |
| 2 | PARTITION RANGE INLIST| |
| 3 | TABLE ACCESS FULL | BS_APP_SHIPMENT |
---------------------------------------------------
Thanks
-S
April 02, 2008 - 2:27 pm UTC
why don't you just give me what I asked for - plans with card=values.
do not expect me to "become the optimizer" and derive - using the optimizers algorithms - the card=values.
I just want a plan with cardinalities.
plans with cardinalities,
A reader, April 02, 2008 - 3:37 pm UTC
sorry about that...
I have plans with cardinatilities for both 8i and 10g
8i plan
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 1210 | 20 | | |
| 1 | FOR UPDATE | | | | | | |
| 2 | PARTITION RANGE INLIST | | | | |KEY(I) |KEY(I) |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| BS_APP_SHIPMENT | 11 | 1210 | 20 |KEY(I) |KEY(I) |
| 4 | INDEX RANGE SCAN | BS_APP_SHIPMENT_N2 | 11 | | 7 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------------------
10g plan
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 110 | 4 | | |
| 1 | FOR UPDATE | | | | | | |
| 2 | PARTITION RANGE INLIST| | 1 | 110 | 4 |KEY(I) |KEY(I) |
| 3 | TABLE ACCESS FULL | BS_APP_SHIPMENT | 1 | 110 | 4 |KEY(I) |KEY(I) |
-------------------------------------------------------------------------------------------
Thanks
-S
April 02, 2008 - 7:53 pm UTC
give the cost of full scanning is only 4 - I have to presume
THERE IS NO DATA LOADED
or
STATISTICS ARE WAY OUT OF DATE
which looks accurate, most of your partitions appear empty.
optimizer using RULE not CHOOSE
dxl, November 03, 2008 - 1:12 pm UTC
Hi
We are using database 10.2.0.1.
i have a (relatively) slow running query that i have traced using tkprof.
It is a very large query and whilst it runs in less than a second it is being called from a larger process many times and therefore needs to be quicker ( another version of this query runs in less than .01 of a second..so i know it is possible).
Anyway the tkprof result is below and I just want to know why the optimizer mode is RULE when I have analyzed the schema prior to running the query?? As i understood it if we have up to date statistics then the optimizer mode should be CHOOSE shouldn't it?
********************************************************************************
SELECT DUMMY_DATE AS EVENT_DATE, DUMMY_PERIOD AS PERIOD, SHORT_CODE, CASE
WHEN TO_CHAR(DUMMY_DATE, 'DAY') IN ('SATURDAY', 'SUNDAY') THEN 0 ELSE CAST(
STAFF_TIME_AVAILABLE_HOURS AS NUMBER(9,2) ) END AS STAFF_AVAILABLE_HOURS
FROM
( SELECT DUMMY_DATE, DUMMY_PERIOD, SHORT_CODE, CASE WHEN
(STAFF_TIME_AVAILABLE_HOURS) < 0 THEN 0 ELSE STAFF_TIME_AVAILABLE_HOURS END
AS STAFF_TIME_AVAILABLE_HOURS FROM ( SELECT SHORT_CODE, DUMMY_DATE,
DUMMY_PERIOD, (STAFF_TIME_CONTRIBUTION - (PROD_TIME_CONTRIBUTION +
(STAFF_EVENT_CONTRIBUTION) ) ) AS STAFF_TIME_AVAILABLE_HOURS FROM ( SELECT
C.SHORT_CODE, C.DUMMY_DATE, C.DUMMY_PERIOD, C.STAFF_TIME_CONTRIBUTION,
C.PROD_TIME_CONTRIBUTION, NVL( D.STAFF_EVENT_CONTRIBUTION, 0 ) AS
STAFF_EVENT_CONTRIBUTION FROM ( SELECT A.SHORT_CODE, A.DUMMY_DATE,
A.DUMMY_PERIOD, A.STAFF_TIME_CONTRIBUTION, NVL( B.PROD_TIME_CONTRIBUTION, 0
) AS PROD_TIME_CONTRIBUTION FROM ( SELECT SHORT_CODE, CASE WHEN S.PART_TIME
= 'Y' THEN CASE WHEN TO_CHAR(DUMMY_DATE, 'DAY')||DUMMY_PERIOD = 'MONDAYAM'
THEN TO_NUMBER(S.MON_AM_HRS) WHEN TO_CHAR(DUMMY_DATE, 'DAY')||DUMMY_PERIOD =
'TUESDAYAM' THEN TO_NUMBER(S.TUE_AM_HRS) WHEN TO_CHAR(DUMMY_DATE, 'DAY')
||DUMMY_PERIOD = 'WEDNESDAYAM' THEN TO_NUMBER(S.WED_AM_HRS) WHEN
TO_CHAR(DUMMY_DATE, 'DAY')||DUMMY_PERIOD = 'THURSDAYAM' THEN
TO_NUMBER(S.THUR_AM_HRS) WHEN TO_CHAR(DUMMY_DATE, 'DAY')||DUMMY_PERIOD =
'FRIDAYAM' THEN TO_NUMBER(S.FRI_AM_HRS) WHEN TO_CHAR(DUMMY_DATE, 'DAY')
||DUMMY_PERIOD = 'MONDAYPM' THEN TO_NUMBER(S.MON_PM_HRS) WHEN
TO_CHAR(DUMMY_DATE, 'DAY')||DUMMY_PERIOD = 'TUESDAYPM' THEN
TO_NUMBER(S.TUE_PM_HRS) WHEN TO_CHAR(DUMMY_DATE, 'DAY')||DUMMY_PERIOD =
'WEDNESDAYPM' THEN TO_NUMBER(S.WED_PM_HRS) WHEN TO_CHAR(DUMMY_DATE, 'DAY')
||DUMMY_PERIOD = 'THURSDAYPM' THEN TO_NUMBER(S.THUR_PM_HRS) WHEN
TO_CHAR(DUMMY_DATE, 'DAY')||DUMMY_PERIOD = 'FRIDAYPM' THEN
TO_NUMBER(S.FRI_PM_HRS) ELSE 0 END ELSE TO_NUMBER(S.WTE)/10 END AS
STAFF_TIME_CONTRIBUTION, DUMMY_DATE, DUMMY_PERIOD FROM ( SELECT SHORT_CODE,
PART_TIME, MON_AM_HRS, TUE_AM_HRS, WED_AM_HRS, THUR_AM_HRS, FRI_AM_HRS,
MON_PM_HRS, TUE_PM_HRS, WED_PM_HRS, THUR_PM_HRS, FRI_PM_HRS, WTE FROM
STAFF_T WHERE 1=1 AND SHORT_CODE IN ( SELECT VALUE FROM SESSION_CONTEXT
WHERE NAMESPACE = 'WTM_STAFF_CTX' AND ATTRIBUTE LIKE
'SHORT\_CODE\_IN\_LIST\_%' ESCAPE '\' ) ) S, ( SELECT DUMMY_DATE,
DUMMY_PERIOD FROM ( SELECT CAST( TRUNC((:B1 ) + (ROWNUM/2) - 1/2) AS DATE)
AS DUMMY_DATE, CASE WHEN MOD(ROWNUM, 2) = 0 THEN 'PM' ELSE 'AM' END AS
DUMMY_PERIOD FROM ALL_TAB_COLUMNS WHERE ROWNUM <= ( ((:B2 - (:B1 )) + 1)*2 )
) WHERE DUMMY_PERIOD = :B4 OR DUMMY_PERIOD = :B3 ) DT ) A, ( SELECT
SUM(NVL(TIME_CONTRIBUTION, 0)) AS PROD_TIME_CONTRIBUTION, P.SHORT_CODE,
P.EVENT_DATE, P.PERIOD FROM PRODUCT_SUPPORT_CAL_EVENT_T P, STAFF_SKILLS_T
SS WHERE P.PRODUCT_CODE = SS.PRODUCT_CODE AND P.SHORT_CODE = SS.SHORT_CODE
AND P.SHORT_CODE IN ( SELECT VALUE FROM SESSION_CONTEXT WHERE NAMESPACE =
'WTM_STAFF_CTX' AND ATTRIBUTE LIKE 'SHORT\_CODE\_IN\_LIST\_%' ESCAPE '\' )
GROUP BY P.SHORT_CODE, P.EVENT_DATE, P.PERIOD ) B WHERE A.SHORT_CODE =
B.SHORT_CODE (+) AND A.DUMMY_DATE = B.EVENT_DATE (+) AND A.DUMMY_PERIOD =
B.PERIOD (+) ) C, ( SELECT SHORT_CODE, STAFF_CAL_DATE, STAFF_CAL_PERIOD,
CASE WHEN SUM( STAFF_EVENT_CONTRIBUTION ) >= 3.75 THEN 3.75 ELSE SUM(
STAFF_EVENT_CONTRIBUTION ) END AS STAFF_EVENT_CONTRIBUTION FROM ( SELECT
SCE.SHORT_CODE, DT2.STAFF_CAL_DATE, DT2.STAFF_CAL_PERIOD, CASE WHEN
SCE.START_DTS IS NULL OR SCE.END_DTS IS NULL THEN 0 WHEN ( SCE.START_DTS >=
DT2.EVENT_DATE_START_DTS AND SCE.START_DTS <= DT2.EVENT_DATE_END_DTS AND
SCE.END_DTS >= DT2.EVENT_DATE_START_DTS AND SCE.END_DTS <=
DT2.EVENT_DATE_END_DTS ) THEN (SCE.END_DTS - SCE.START_DTS) * 24 WHEN (
SCE.START_DTS < DT2.EVENT_DATE_START_DTS AND SCE.END_DTS >=
DT2.EVENT_DATE_START_DTS AND SCE.END_DTS <= DT2.EVENT_DATE_END_DTS ) THEN
(SCE.END_DTS - DT2.EVENT_DATE_START_DTS) * 24 WHEN ( SCE.START_DTS >=
DT2.EVENT_DATE_START_DTS AND SCE.START_DTS <= DT2.EVENT_DATE_END_DTS AND
SCE.END_DTS > DT2.EVENT_DATE_END_DTS ) THEN (DT2.EVENT_DATE_END_DTS -
SCE.START_DTS) * 24 WHEN ( SCE.START_DTS < DT2.EVENT_DATE_START_DTS AND
SCE.END_DTS > DT2.EVENT_DATE_END_DTS ) THEN (DT2.EVENT_DATE_END_DTS -
DT2.EVENT_DATE_START_DTS) * 24 ELSE 0 END AS STAFF_EVENT_CONTRIBUTION FROM
( SELECT STAFF_CAL_DATE, STAFF_CAL_PERIOD, CASE WHEN STAFF_CAL_PERIOD =
'AM' THEN STAFF_CAL_DATE + 8/24 WHEN STAFF_CAL_PERIOD = 'PM' THEN
STAFF_CAL_DATE + 13/24 END AS EVENT_DATE_START_DTS, CASE WHEN
STAFF_CAL_PERIOD = 'AM' THEN STAFF_CAL_DATE + 13/24 WHEN STAFF_CAL_PERIOD =
'PM' THEN STAFF_CAL_DATE + 18/24 END AS EVENT_DATE_END_DTS FROM ( SELECT
STAFF_CAL_DATE, STAFF_CAL_PERIOD FROM ( SELECT CAST( TRUNC((:B1 ) +
(ROWNUM/2) - 1/2) AS DATE) AS STAFF_CAL_DATE, CASE WHEN MOD(ROWNUM, 2) = 0
THEN 'PM' ELSE 'AM' END AS STAFF_CAL_PERIOD FROM ALL_TAB_COLUMNS WHERE
ROWNUM <= ( ((:B2 - (:B1 )) + 1)*2 ) ) WHERE STAFF_CAL_PERIOD = :B4 OR
STAFF_CAL_PERIOD = :B3 ) ) DT2, STAFF_CAL_EVENT_T SCE WHERE SCE.SHORT_CODE
IN ( SELECT VALUE FROM SESSION_CONTEXT WHERE NAMESPACE = 'WTM_STAFF_CTX'
AND ATTRIBUTE LIKE 'SHORT\_CODE\_IN\_LIST\_%' ESCAPE '\' ) AND ( (
SCE.START_DTS >= DT2.EVENT_DATE_START_DTS AND SCE.START_DTS <=
DT2.EVENT_DATE_END_DTS AND SCE.END_DTS >= DT2.EVENT_DATE_START_DTS AND
SCE.END_DTS <= DT2.EVENT_DATE_END_DTS ) OR ( SCE.START_DTS <
DT2.EVENT_DATE_START_DTS AND SCE.END_DTS >= DT2.EVENT_DATE_START_DTS AND
SCE.END_DTS <= DT2.EVENT_DATE_END_DTS ) OR ( SCE.START_DTS >=
DT2.EVENT_DATE_START_DTS AND SCE.START_DTS <= DT2.EVENT_DATE_END_DTS AND
SCE.END_DTS > DT2.EVENT_DATE_END_DTS ) OR ( SCE.START_DTS <
DT2.EVENT_DATE_START_DTS AND SCE.END_DTS > DT2.EVENT_DATE_END_DTS ) ) )
GROUP BY SHORT_CODE, STAFF_CAL_DATE, STAFF_CAL_PERIOD ) D WHERE
C.SHORT_CODE = D.SHORT_CODE (+) AND C.DUMMY_DATE = D.STAFF_CAL_DATE (+) AND
C.DUMMY_PERIOD = D.STAFF_CAL_PERIOD (+) ) ) ORDER BY DUMMY_DATE,
DUMMY_PERIOD, SHORT_CODE )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.04 0.03 0 0 0 0
Fetch 2 0.58 0.56 0 100242 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.62 0.60 0 100242 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 68 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=100242 pr=0 pw=0 time=566106 us)
1 MERGE JOIN OUTER (cr=100242 pr=0 pw=0 time=566066 us)
1 MERGE JOIN OUTER (cr=99789 pr=0 pw=0 time=561555 us)
1 SORT JOIN (cr=19959 pr=0 pw=0 time=114853 us)
1 VIEW (cr=19959 pr=0 pw=0 time=114821 us)
1 NESTED LOOPS (cr=19959 pr=0 pw=0 time=114811 us)
1 NESTED LOOPS (cr=2 pr=0 pw=0 time=149 us)
1 VIEW VW_NSO_3 (cr=0 pr=0 pw=0 time=103 us)
1 SORT UNIQUE (cr=0 pr=0 pw=0 time=98 us)
1 FIXED TABLE FULL X$CONTEXT (cr=0 pr=0 pw=0 time=72 us)
1 TABLE ACCESS BY INDEX ROWID STAFF_T (cr=2 pr=0 pw=0 time=42 us)
1 INDEX UNIQUE SCAN STAFF_T_PK (cr=1 pr=0 pw=0 time=26 us)(object id 52626)
1 VIEW (cr=19957 pr=0 pw=0 time=114656 us)
2 COUNT STOPKEY (cr=19957 pr=0 pw=0 time=114610 us)
2 FILTER (cr=19957 pr=0 pw=0 time=114600 us)
2 NESTED LOOPS OUTER (cr=19950 pr=0 pw=0 time=114281 us)
2 NESTED LOOPS OUTER (cr=19950 pr=0 pw=0 time=114267 us)
2 NESTED LOOPS OUTER (cr=19950 pr=0 pw=0 time=114251 us)
2 NESTED LOOPS OUTER (cr=19946 pr=0 pw=0 time=114216 us)
2 NESTED LOOPS (cr=19942 pr=0 pw=0 time=114188 us)
9945 NESTED LOOPS (cr=9993 pr=0 pw=0 time=59746 us)
1 TABLE ACCESS FULL USER$ (cr=3 pr=0 pw=0 time=47 us)
9945 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=9990 pr=0 pw=0 time=49753 us)
9945 INDEX RANGE SCAN I_OBJ2 (cr=77 pr=0 pw=0 time=9969 us)(object id 37)
2 TABLE ACCESS CLUSTER COL$ (cr=9949 pr=0 pw=0 time=65280 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=9947 pr=0 pw=0 time=42454 us)(object id 3)
0 TABLE ACCESS CLUSTER COLTYPE$ (cr=4 pr=0 pw=0 time=24 us)
2 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=4 pr=0 pw=0 time=28 us)(object id 257)
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=12 us)
0 INDEX RANGE SCAN I_OBJ3 (cr=0 pr=0 pw=0 time=6 us)(object id 38)
0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=10 us)
0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=5 us)(object id 11)
0 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=20 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=7 us)(object id 3)
0 NESTED LOOPS (cr=4 pr=0 pw=0 time=61 us)
2 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=31 us)
0 INDEX RANGE SCAN I_OBJAUTH2 (cr=4 pr=0 pw=0 time=27 us)(object id 104)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=192 us)
0 SORT JOIN (cr=79830 pr=0 pw=0 time=446697 us)
0 VIEW (cr=79830 pr=0 pw=0 time=446683 us)
0 SORT GROUP BY (cr=79830 pr=0 pw=0 time=446680 us)
0 CONCATENATION (cr=79830 pr=0 pw=0 time=446657 us)
0 MERGE JOIN (cr=19973 pr=0 pw=0 time=112433 us)
15 SORT JOIN (cr=19973 pr=0 pw=0 time=112333 us)
21 TABLE ACCESS BY INDEX ROWID STAFF_CAL_EVENT_T (cr=19973 pr=0 pw=0 time=112191 us)
23 NESTED LOOPS (cr=19960 pr=0 pw=0 time=2464790 us)
1 VIEW (cr=19957 pr=0 pw=0 time=111239 us)
2 COUNT STOPKEY (cr=19957 pr=0 pw=0 time=111154 us)
2 FILTER (cr=19957 pr=0 pw=0 time=111146 us)
2 NESTED LOOPS OUTER (cr=19950 pr=0 pw=0 time=110913 us)
2 NESTED LOOPS OUTER (cr=19950 pr=0 pw=0 time=110900 us)
2 NESTED LOOPS OUTER (cr=19950 pr=0 pw=0 time=110882 us)
2 NESTED LOOPS OUTER (cr=19946 pr=0 pw=0 time=110854 us)
2 NESTED LOOPS (cr=19942 pr=0 pw=0 time=110832 us)
9945 NESTED LOOPS (cr=9993 pr=0 pw=0 time=49782 us)
1 TABLE ACCESS FULL USER$ (cr=3 pr=0 pw=0 time=44 us)
9945 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=9990 pr=0 pw=0 time=39793 us)
9945 INDEX RANGE SCAN I_OBJ2 (cr=77 pr=0 pw=0 time=9955 us)(object id 37)
2 TABLE ACCESS CLUSTER COL$ (cr=9949 pr=0 pw=0 time=64882 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=9947 pr=0 pw=0 time=42155 us)(object id 3)
0 TABLE ACCESS CLUSTER COLTYPE$ (cr=4 pr=0 pw=0 time=17 us)
2 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=4 pr=0 pw=0 time=20 us)(object id 257)
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=11 us)
0 INDEX RANGE SCAN I_OBJ3 (cr=0 pr=0 pw=0 time=7 us)(object id 38)
0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=11 us)
0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=4 us)(object id 11)
0 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=17 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=6 us)(object id 3)
0 NESTED LOOPS (cr=4 pr=0 pw=0 time=41 us)
2 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=14 us)
0 INDEX RANGE SCAN I_OBJAUTH2 (cr=4 pr=0 pw=0 time=19 us)(object id 104)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=187 us)
21 INDEX RANGE SCAN IDX_STAFF_CAL_ST_ED_DTS (cr=3 pr=0 pw=0 time=876 us)(object id 68605)
0 SORT JOIN (cr=0 pr=0 pw=0 time=99 us)
1 VIEW VW_NSO_1 (cr=0 pr=0 pw=0 time=61 us)
1 SORT UNIQUE (cr=0 pr=0 pw=0 time=58 us)
1 FIXED TABLE FULL X$CONTEXT (cr=0 pr=0 pw=0 time=41 us)
0 MERGE JOIN (cr=19952 pr=0 pw=0 time=110326 us)
0 SORT JOIN (cr=19952 pr=0 pw=0 time=110322 us)
0 TABLE ACCESS BY INDEX ROWID STAFF_CAL_EVENT_T (cr=19952 pr=0 pw=0 time=110308 us)
2 NESTED LOOPS (cr=19952 pr=0 pw=0 time=110240 us)
1 VIEW (cr=19950 pr=0 pw=0 time=110281 us)
2 COUNT STOPKEY (cr=19950 pr=0 pw=0 time=110198 us)
2 FILTER (cr=19950 pr=0 pw=0 time=110194 us)
2 NESTED LOOPS OUTER (cr=19950 pr=0 pw=0 time=110233 us)
2 NESTED LOOPS OUTER (cr=19950 pr=0 pw=0 time=110217 us)
2 NESTED LOOPS OUTER (cr=19950 pr=0 pw=0 time=110202 us)
2 NESTED LOOPS OUTER (cr=19946 pr=0 pw=0 time=110177 us)
2 NESTED LOOPS (cr=19942 pr=0 pw=0 time=110159 us)
9945 NESTED LOOPS (cr=9993 pr=0 pw=0 time=49760 us)
1 TABLE ACCESS FULL USER$ (cr=3 pr=0 pw=0 time=22 us)
9945 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=9990 pr=0 pw=0 time=39790 us)
9945 INDEX RANGE SCAN I_OBJ2 (cr=77 pr=0 pw=0 time=9954 us)(object id 37)
2 TABLE ACCESS CLUSTER COL$ (cr=9949 pr=0 pw=0 time=64734 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=9947 pr=0 pw=0 time=42369 us)(object id 3)
0 TABLE ACCESS CLUSTER COLTYPE$ (cr=4 pr=0 pw=0 time=13 us)
2 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=4 pr=0 pw=0 time=19 us)(object id 257)
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=10 us)
0 INDEX RANGE SCAN I_OBJ3 (cr=0 pr=0 pw=0 time=6 us)(object id 38)
0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=9 us)
0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=2 us)(object id 11)
0 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=17 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=6 us)(object id 3)
0 NESTED LOOPS (cr=4 pr=0 pw=0 time=41 us)
2 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=14 us)
0 INDEX RANGE SCAN I_OBJAUTH2 (cr=4 pr=0 pw=0 time=19 us)(object id 104)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=187 us)
0 INDEX RANGE SCAN IDX_STAFF_CAL_ST_ED_DTS (cr=0 pr=0 pw=0 time=0 us)(object id 68605)
0 SORT JOIN (cr=0 pr=0 pw=0 time=0 us)
0 VIEW VW_NSO_1 (cr=0 pr=0 pw=0 time=0 us)
0 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us)
0 FIXED TABLE FULL X$CONTEXT (cr=0 pr=0 pw=0 time=0 us)
0 MERGE JOIN (cr=0 pr=0 pw=0 time=0 us)
0 SORT JOIN (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID STAFF_CAL_EVENT_T (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL USER$ (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN I_OBJ2 (cr=0 pr=0 pw=0 time=0 us)(object id 37)
0 TABLE ACCESS CLUSTER COL$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_OBJ# (cr=0 pr=0 pw=0 time=0 us)(object id 3)
0 TABLE ACCESS CLUSTER COLTYPE$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=0 pr=0 pw=0 time=0 us)(object id 257)
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN I_OBJ3 (cr=0 pr=0 pw=0 time=0 us)(object id 38)
0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)(object id 11)
0 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=17 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=6 us)(object id 3)
0 NESTED LOOPS (cr=4 pr=0 pw=0 time=41 us)
2 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=14 us)
0 INDEX RANGE SCAN I_OBJAUTH2 (cr=4 pr=0 pw=0 time=19 us)(object id 104)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=187 us)
0 INDEX RANGE SCAN IDX_STAFF_CAL_ST_ED_DTS (cr=0 pr=0 pw=0 time=0 us)(object id 68605)
0 SORT JOIN (cr=0 pr=0 pw=0 time=0 us)
0 VIEW VW_NSO_1 (cr=0 pr=0 pw=0 time=0 us)
0 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us)
0 FIXED TABLE FULL X$CONTEXT (cr=0 pr=0 pw=0 time=0 us)
0 MERGE JOIN (cr=0 pr=0 pw=0 time=0 us)
0 SORT JOIN (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID STAFF_CAL_EVENT_T (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL USER$ (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN I_OBJ2 (cr=0 pr=0 pw=0 time=0 us)(object id 37)
0 TABLE ACCESS CLUSTER COL$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_OBJ# (cr=0 pr=0 pw=0 time=0 us)(object id 3)
0 TABLE ACCESS CLUSTER COLTYPE$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=0 pr=0 pw=0 time=0 us)(object id 257)
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN I_OBJ3 (cr=0 pr=0 pw=0 time=0 us)(object id 38)
0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)(object id 11)
0 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=17 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=6 us)(object id 3)
0 NESTED LOOPS (cr=4 pr=0 pw=0 time=41 us)
2 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=14 us)
0 INDEX RANGE SCAN I_OBJAUTH2 (cr=4 pr=0 pw=0 time=19 us)(object id 104)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=187 us)
0 INDEX RANGE SCAN IDX_STAFF_CAL_ST_ED_DTS (cr=0 pr=0 pw=0 time=0 us)(object id 68605)
0 SORT JOIN (cr=0 pr=0 pw=0 time=0 us)
0 VIEW VW_NSO_1 (cr=0 pr=0 pw=0 time=0 us)
0 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us)
0 FIXED TABLE FULL X$CONTEXT (cr=0 pr=0 pw=0 time=0 us)
0 SORT JOIN (cr=0 pr=0 pw=0 time=0 us)
0 VIEW (cr=0 pr=0 pw=0 time=0 us)
0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 VIEW VW_NSO_2 (cr=0 pr=0 pw=0 time=0 us)
0 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us)
0 FIXED TABLE FULL X$CONTEXT (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID PRODUCT_SUPPORT_CAL_EVENT_T (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN IDX_PRDSPTCALEVT_SHRTCD_PRDCD (cr=0 pr=0 pw=0 time=0 us)(object id 68606)
0 INDEX UNIQUE SCAN STAFF_SKILLS_T_PK (cr=0 pr=0 pw=0 time=0 us)(object id 52622)
********************************************************************************
many thanks
November 10, 2008 - 3:18 pm UTC
well, my first approach would be to fix the algorithm (you already know what the real problem is.... if you do something fast - a lot - it takes a LONG LONG LONG TIME)
secondly, you or your application can override the init.ora setting. In 10g we would default to all_rows, you could change that.
and the application could change that.
so, ask your application "why are you forcing RULE to be used"
they probably did an alter session.
db_cache_size influence on optimizer
Vladimir Sitnikov, November 06, 2008 - 1:35 pm UTC
Hello,
I am a bit confused regarding your statement that db_cache_size does not influence the optimizer. Basically, I get strong impact on the cost as db_cache_size changes.
Optimizer gives different costs for index range scan in case index "can fit" and "can not" fit into db_cache_size. Looks like the optimizer ignores optimizer_index_caching parameter when db_cache_size is smaller than index size.
Is it an intentional behaviour?
Here is the testcase (I've created it using 10.2.0.3 64-bit, Solaris)
conn / as sysdba
alter system set optimizer_index_caching=90;
alter system set optimizer_index_cost_adj=5;
create user db_cache identified by "_size";
grant connect, resource to db_cache;
conn db_cache/_size;
create table test (
x number
) nologging;
insert /*+ append */ into test select 1001*1001*1001*1001+mod(rownum, 3) from dual connect by level<10000000;
create index xx on test(x) nologging;
exec dbms_stats.gather_table_stats(user, 'test', estimate_percent=>100, cascade=>true);
select segment_name, bytes/1024/1024 mb from user_segments;
conn / as sysdba
alter system set db_cache_size = 1G scope=spfile;
shutdown immediate;
startup;
conn db_cache/_size;
delete from plan_table;
explain plan for select /*+ index(test xx) */ * from test where x in (1001*1001*1001*1001+1, 1001*1001*1001*1001+2);
select * from table(dbms_xplan.display);
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
|* 2 | INDEX RANGE SCAN| XX | 6666K| 57M| 116 (20)| 00:00:02 |
conn / as sysdba
alter system set db_cache_size = 200M scope=spfile;
shutdown immediate;
startup;
conn db_cache/_size;
delete from plan_table;
explain plan for select /*+ index(test xx) */ * from test where x in (1001*1001*1001*1001+1, 1001*1001*1001*1001+2);
select * from table(dbms_xplan.display);
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
|* 2 | INDEX RANGE SCAN| XX | 6666K| 57M| 133 (18)| 00:00:02 |
November 11, 2008 - 2:24 pm UTC
ok, it only takes one counter case to show something was wrong.
thanks, you've done that. This is where test cases come in nicely.
CBO
A reader, July 24, 2009 - 7:01 pm UTC
July 26, 2009 - 7:42 am UTC
instead of picking on the article itself, ask yourself
a) can I ask the author a question easily regarding the information they took so much time to publish?
b) are there any facts - any examples - any EVIDENCE that what they say is "true". Any sort of case studies? Any "for examples"?
I'm big on this - when you say something - make sure that people can see SOMETHING (numbers, evidence, anything) to back it up.
article
A reader, July 28, 2009 - 11:25 pm UTC
Tom:
You are right he did not back it up with anything.
I assumed he was right based on his Rocket Scientist Look.
Looks can be deceiving sometimes!
There were a few good articles by them although i think they use oracle manulas as a source.
On CBO RBO
Sujit, December 01, 2009 - 2:12 pm UTC
I have recently joined in a project , and found that there are very extensive use of RULE hints. While looking deeper into it and also discussing with the senior people in the project , it comes out that CBO does not work well here. It will be hard for me to demonstrate with an example as the select query involve 7-8 table join and each of those having 5-10 million or more rows , and select query is also quite complicated.
I have done dbms_stats.gather_table_stats on those concerned tables,flush shared pool , but still CBO is unable to get the most efficient plan , but while I use the RULE hints or LEADING hint or index hint , I do get much better performance.
To note I am using Oracle 10g in Windows.
Can you please give me some idea , like what are things I should look into to determine why CBO is unable to peroform. And what are the things I should do to make sure CBO perform well.
One thing I have noticed here is that the huge table columns we are joining does not have any foreign key relationship (None of those ) and the columns in the join has an btree index in it (each one separately , or a combined index when two columns are joined) . And out of few million rows from table , query retrieve few thousands rows based on filter condition. So most of the time the kind of plan work well is-- Index range Scan --> Table Acccess by Index Row id --> and then nested loop join.
I know without an example it will be hard for you to suggest something , but will appreciate your any input and any advice on the areas to look at.
December 01, 2009 - 2:24 pm UTC
I would start by comparing the estimated row counts versus the actuals.
for example, consider the following table:
ops$tkyte%ORA10GR2> drop table t;
Table dropped.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t
2 as select decode( mod(rownum,2), 0, 'N', 'Y' ) flag1,
3 decode( mod(rownum,2), 0, 'Y', 'N' ) flag2, a.*
4 from all_objects a
5 where rownum <= 50000
6 /
Table created.
ops$tkyte%ORA10GR2> create index t_idx on t(flag1,flag2);
Index created.
ops$tkyte%ORA10GR2> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T',
4 method_opt=>'for all indexed columns size 254' );
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set serveroutput off
ops$tkyte%ORA10GR2> select /*+ gather_plan_statistics */ * from t where flag1='N' and flag2='N';
no rows selected
<b>Now, you know and I know that N/N has zero rows in the table, they are all N/Y or Y/N, but the CBO will guess "flag1 = n has 50% of the rows, flag2 = n has 50% of the rows, therefore flag1=n and flag2=n = 50%*50% = 25% of the rows and we can see that:</b>
ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------
SQL_ID 74kcy6ahv7frs, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where flag1='N' and flag2='N'
Plan hash value: 1601196873
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| T | 1 | 12500 | 0 |00:00:00.01 | 720 |
------------------------------------------------------------------------------------
<b>E-rows are the estimate, A-rows are the actuals - when Erows is very different from Arows - we'll probably get the wrong plan, as we did here - we should have used the index...</b>
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("FLAG1"='N' AND "FLAG2"='N'))
17 rows selected.
ops$tkyte%ORA10GR2> select /*+ gather_plan_statistics dynamic_sampling(t 3) */ * from t where flag1='N' and flag2='N';
no rows selected
<b>so, we use a feature, dynamic sampling (good feature to have on in a warehouse, at about level 3 - see link below for more information)</b>
ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------
SQL_ID d2cgm7ubpqvhh, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling(t 3) */ * from t where flag1='N'
and flag2='N'
Plan hash value: 470836197
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 4 | 0 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | 4 | 0 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------
<b>Now the Erows are close to Arows and we get the right efficient plan...</b>
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG1"='N' AND "FLAG2"='N')
Note
-----
- dynamic sampling used for this statement
23 rows selected.
see
https://www.oracle.com/technetwork/issue-archive/2009/09-jan/o19asktom-086775.html