Home » RDBMS Server » Performance Tuning » why oracle choose NL in this case?
| why oracle choose NL in this case? [message #318833] |
Thu, 08 May 2008 03:01  |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Hi all:
I have s test about UNIQUE INDEX.
Could someone explain this for me,why oracle choose NL in this case,the statistics part shows than HJ scans much less block than the NL.
SQL> create table test as select * from user_objects ;
SQL> create table test1 as select * from dba_objects;
SQL> create unique index i_test_1 on test(object_id);
SQL> analyze table test compute statistics for indexes for all indexed columns;;
SQL> analyze table test1 compute statistics for indexes for all indexed columns;;
SQL> select test1.* from test,test1 where test.object_id=test1.object_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=162 Card=23087 Byt
es=2100917)
1 0 NESTED LOOPS (Cost=162 Card=23087 Bytes=2100917)
2 1 TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=154 Card=49
966 Bytes=4347042)
3 1 INDEX (UNIQUE SCAN) OF 'I_TEST_1' (INDEX (UNIQUE)) (Cost
=0 Card=1 Bytes=4)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
53715 consistent gets ------>cr =53715
0 physical reads
0 redo size
1106821 bytes sent via SQL*Net to client
17433 bytes received via SQL*Net from client
1541 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23087 rows processed
SQL> select * from test,test1 where test.object_id=test1.object_id;
已选择23087行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=563 Card=23087 Byt
es=3924790)
1 0 HASH JOIN (Cost=563 Card=23087 Bytes=3924790)
2 1 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=69 Card=2308
7 Bytes=1916221)
3 1 TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=154 Card=49
966 Bytes=4347042)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2514 consistent gets ------------>cr here is 2514 much less than the NL does.
0 physical reads
0 redo size
1934645 bytes sent via SQL*Net to client
17433 bytes received via SQL*Net from client
1541 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23087 rows processed
SQL> drop index i_test_1;
SQL> create index i_test_1 on test(object_id);
---I created a normal index
SQL> analyze table test compute statistics for indexes for all indexed columns;;
SQL> select test1.* from test,test1 where test.object_id=test1.object_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=168 Card=23087 Byt
es=2100917)
1 0 HASH JOIN (Cost=168 Card=23087 Bytes=2100917)
2 1 INDEX (FAST FULL SCAN) OF 'I_TEST_1' (INDEX) (Cost=13 Ca
rd=23087 Bytes=92348)
3 1 TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=154 Card=49
966 Bytes=4347042)
---------it does a HJ
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2264 consistent gets
0 physical reads
0 redo size
1106821 bytes sent via SQL*Net to client
17433 bytes received via SQL*Net from client
1541 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23087 rows processed
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ---------
_optimizer_cost_model string CPU
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
thanks
Alan
|
|
|
|
|
|
| Re: why oracle choose NL in this case? [message #318840 is a reply to message #318837] |
Thu, 08 May 2008 03:16  |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Dear Michel
Here is a part of 10053 trace file,
could have a look ?
Thanks for you kindly help!
Alan
****************
QUERY BLOCK TEXT
****************
select /*dd*/ test1.* from test,test1 where test.object_id=test1.object_id
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=0 objn=56203 hint_alias="TEST"@"SEL$1"
fro(1): flg=0 objn=56204 hint_alias="TEST1"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 1017 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TEST1 Alias: TEST1
#Rows: 49966 #Blks: 686 AvgRowLen: 97.00
Column (#4): OBJECT_ID(NUMBER)
AvgLen: 4.00 NDV: 49966 Nulls: 0 Density: 2.0014e-005 Min: 2 Max: 56204
***********************
Table Stats::
Table: TEST Alias: TEST
#Rows: 23087 #Blks: 302 AvgRowLen: 92.00
Column (#3): OBJECT_ID(NUMBER)
AvgLen: 4.00 NDV: 23087 Nulls: 0 Density: 4.3314e-005 Min: 2 Max: 56203
Index Stats::
Index: I_TEST_1 Col#: 3
LVLS: 1 #LB: 48 #DK: 23087 LB/K: 1.00 DB/K: 1.00 CLUF: 333.00
***************************************
SINGLE TABLE ACCESS PATH
Table: TEST Alias: TEST
Card: Original: 23087 Rounded: 23087 Computed: 23087.00 Non Adjusted: 23087.00
Access Path: TableScan
Cost: 68.54 Resp: 68.54 Degree: 0
Cost_io: 68.00 Cost_cpu: 6537205
Resp_io: 68.00 Resp_cpu: 6537205
Access Path: index (index (FFS))
Index: I_TEST_1
resc_io: 12.00 resc_cpu: 3112269
ix_sel: 0.0000e+000 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 12.26 Resp: 12.26 Degree: 1
Cost_io: 12.00 Cost_cpu: 3112269
Resp_io: 12.00 Resp_cpu: 3112269
Access Path: index (FullScan)
Index: I_TEST_1
resc_io: 49.00 resc_cpu: 4966351
ix_sel: 1 ix_sel_with_filters: 1
Cost: 49.41 Resp: 49.41 Degree: 1
Best:: AccessPath: IndexFFS Index: I_TEST_1
Cost: 12.26 Degree: 1 Resp: 12.26 Card: 23087.00 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
Table: TEST1 Alias: TEST1
Card: Original: 49966 Rounded: 49966 Computed: 49966.00 Non Adjusted: 49966.00
Access Path: TableScan
Cost: 154.00 Resp: 154.00 Degree: 0
Cost_io: 152.00 Cost_cpu: 24372048
Resp_io: 152.00 Resp_cpu: 24372048
Best:: AccessPath: TableScan
Cost: 154.00 Degree: 1 Resp: 154.00 Card: 49966.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]: TEST[TEST]#0 TEST1[TEST1]#1
***************
Now joining: TEST1[TEST1]#1
***************
NL Join
Outer table: Card: 23087.00 Cost: 12.26 Resp: 12.26 Degree: 1 Bytes: 4
Inner table: TEST1 Alias: TEST1
Access Path: TableScan
NL Join: Cost: 3510610.02 Resp: 3510610.02 Degree: 0
Cost_io: 3464507.00 Cost_cpu: 562680580751
Resp_io: 3464507.00 Resp_cpu: 562680580751
Best NL cost: 3510610.02
resc: 3510610.02 resc_io: 3464507.00 resc_cpu: 562680580751
resp: 3510610.02 resp_io: 3464507.00 resp_cpu: 562680580751
Join Card: 23087.00 = outer (23087.00) * inner (49966.00) * sel (2.0014e-005)
Join Card - Rounded: 23087 Computed: 23087.00
SM Join
Outer table:
resc: 12.26 card 23087.00 bytes: 4 deg: 1 resp: 12.26
Inner table: TEST1 Alias: TEST1
resc: 154.00 card: 49966.00 bytes: 87 deg: 1 resp: 154.00
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 118 Area size: 131072 Max Area size: 20971520
Degree: 1
Blocks to Sort: 43 Row size: 15 Total Rows: 23087
Initial runs: 2 Merge passes: 1 IO Cost / pass: 24
Total IO sort cost: 67 Total CPU sort cost: 28340888
Total Temp space used: 566000
SORT resource Sort statistics
Sort width: 118 Area size: 131072 Max Area size: 20971520
Degree: 1
Blocks to Sort: 649 Row size: 106 Total Rows: 49966
Initial runs: 2 Merge passes: 1 IO Cost / pass: 354
Total IO sort cost: 1003 Total CPU sort cost: 63325284
Total Temp space used: 12051000
SM join: Resc: 1243.76 Resp: 1243.76 [multiMatchCost=0.00]
SM cost: 1243.76
resc: 1243.76 resc_io: 1234.00 resc_cpu: 119150490
resp: 1243.76 resp_io: 1234.00 resp_cpu: 119150490
SM Join (with index on outer)
Access Path: index (FullScan)
Index: I_TEST_1
resc_io: 49.00 resc_cpu: 4966351
ix_sel: 1 ix_sel_with_filters: 1
Cost: 49.41 Resp: 49.41 Degree: 1
Outer table:
resc: 49.41 card 23087.00 bytes: 4 deg: 1 resp: 49.41
Inner table: TEST1 Alias: TEST1
resc: 154.00 card: 49966.00 bytes: 87 deg: 1 resp: 154.00
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 118 Area size: 131072 Max Area size: 20971520
Degree: 1
Blocks to Sort: 649 Row size: 106 Total Rows: 49966
Initial runs: 2 Merge passes: 1 IO Cost / pass: 354
Total IO sort cost: 1003 Total CPU sort cost: 63325284
Total Temp space used: 12051000
SM join: Resc: 1211.59 Resp: 1211.59 [multiMatchCost=0.00]
HA Join
Outer table:
resc: 12.26 card 23087.00 bytes: 4 deg: 1 resp: 12.26
Inner table: TEST1 Alias: TEST1
resc: 154.00 card: 49966.00 bytes: 87 deg: 1 resp: 154.00
using dmeth: 2 #groups: 1
Cost per ptn: 1.19 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 167.45 Resp: 167.45 [multiMatchCost=0.00]
HA cost: 167.45
resc: 167.45 resc_io: 164.00 resc_cpu: 42046393
resp: 167.45 resp_io: 164.00 resp_cpu: 42046393
Best:: JoinMethod: Hash
Cost: 167.45 Degree: 1 Resp: 167.45 Card: 23087.00 Bytes: 91
***********************
Best so far: Table#: 0 cost: 12.2550 card: 23087.0000 bytes: 92348
Table#: 1 cost: 167.4451 card: 23087.0000 bytes: 2100917
***********************
Join order[2]: TEST1[TEST1]#1 TEST[TEST]#0
***************
Now joining: TEST[TEST]#0
***************
NL Join
Outer table: Card: 49966.00 Cost: 154.00 Resp: 154.00 Degree: 1 Bytes: 87
Inner table: TEST Alias: TEST
Access Path: TableScan
NL Join: Cost: 3327797.96 Resp: 3327797.96 Degree: 0
Cost_io: 3301033.00 Cost_cpu: 326662351082
Resp_io: 3301033.00 Resp_cpu: 326662351082
Access Path: index (index (FFS))
Index: I_TEST_1
resc_io: 10.50 resc_cpu: 3112269
ix_sel: 0.0000e+000 ix_sel_with_filters: 1
Inner table: TEST Alias: TEST
Access Path: index (FFS)
NL Join: Cost: 537539.46 Resp: 537539.46 Degree: 0
Cost_io: 524796.00 Cost_cpu: 155532010898
Resp_io: 524796.00 Resp_cpu: 155532010898
Access Path: index (UniqueScan)
Index: I_TEST_1
resc_io: 0.00 resc_cpu: 1900
ix_sel: 4.3314e-005 ix_sel_with_filters: 4.3314e-005
NL Join: Cost: 161.78 Resp: 161.78 Degree: 1
Cost_io: 152.00 Cost_cpu: 119307448
Resp_io: 152.00 Resp_cpu: 119307448
Access Path: index (AllEqUnique)
Index: I_TEST_1
resc_io: 0.00 resc_cpu: 1900
ix_sel: 4.3314e-005 ix_sel_with_filters: 4.3314e-005
NL Join: Cost: 161.78 Resp: 161.78 Degree: 1
Cost_io: 152.00 Cost_cpu: 119307448
Resp_io: 152.00 Resp_cpu: 119307448
Best NL cost: 161.78
resc: 161.78 resc_io: 152.00 resc_cpu: 119307448
resp: 161.78 resp_io: 152.00 resp_cpu: 119307448
Join Card: 23087.00 = outer (49966.00) * inner (23087.00) * sel (2.0014e-005)
Join Card - Rounded: 23087 Computed: 23087.00
SM Join
Outer table:
resc: 154.00 card 49966.00 bytes: 87 deg: 1 resp: 154.00
Inner table: TEST Alias: TEST
resc: 12.26 card: 23087.00 bytes: 4 deg: 1 resp: 12.26
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 118 Area size: 131072 Max Area size: 20971520
Degree: 1
Blocks to Sort: 649 Row size: 106 Total Rows: 49966
Initial runs: 2 Merge passes: 1 IO Cost / pass: 354
Total IO sort cost: 1003 Total CPU sort cost: 63325284
Total Temp space used: 12051000
SORT resource Sort statistics
Sort width: 118 Area size: 131072 Max Area size: 20971520
Degree: 1
Blocks to Sort: 43 Row size: 15 Total Rows: 23087
Initial runs: 2 Merge passes: 1 IO Cost / pass: 24
Total IO sort cost: 67 Total CPU sort cost: 28340888
Total Temp space used: 566000
SM join: Resc: 1243.76 Resp: 1243.76 [multiMatchCost=0.00]
SM cost: 1243.76
resc: 1243.76 resc_io: 1234.00 resc_cpu: 119150490
resp: 1243.76 resp_io: 1234.00 resp_cpu: 119150490
HA Join
Outer table:
resc: 154.00 card 49966.00 bytes: 87 deg: 1 resp: 154.00
Inner table: TEST Alias: TEST
resc: 12.26 card: 23087.00 bytes: 4 deg: 1 resp: 12.26
using dmeth: 2 #groups: 1
Cost per ptn: 253.71 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 419.96 Resp: 419.96 [multiMatchCost=0.00]
HA Join (swap)
Outer table:
resc: 12.26 card 23087.00 bytes: 4 deg: 1 resp: 12.26
Inner table: TEST1 Alias: TEST1
resc: 154.00 card: 49966.00 bytes: 87 deg: 1 resp: 154.00
using dmeth: 2 #groups: 1
Cost per ptn: 1.19 #ptns: 1
hash_area: 0 (max=0) Hash join: Resc: 167.45 Resp: 167.45 [multiMatchCost=0.00]
HA cost: 167.45
resc: 167.45 resc_io: 164.00 resc_cpu: 42046393
resp: 167.45 resp_io: 164.00 resp_cpu: 42046393
Best:: JoinMethod: NestedLoop
Cost: 161.78 Degree: 1 Resp: 161.78 Card: 23087.00 Bytes: 91
***********************
Best so far: Table#: 1 cost: 153.9969 card: 49966.0000 bytes: 4347042
Table#: 0 cost: 161.7754 card: 23087.0000 bytes: 2100917
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
(newjo-save) [0 1 ]
Final - All Rows Plan: Best join order: 2
Cost: 161.7754 Degree: 1 Card: 23087.0000 Bytes: 2100917
Resc: 161.7754 Resc_io: 152.0000 Resc_cpu: 119307448
Resp: 161.7754 Resp_io: 152.0000 Resc_cpu: 119307448
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "TEST1"."OWNER" "OWNER","TEST1"."OBJECT_NAME" "OBJECT_NAME","TEST1"."SUBOBJECT_NAME" "SUBOBJECT_NAME","TEST1"."OBJECT_ID" "OBJECT_ID","TEST1"."DATA_OBJECT_ID" "DATA_OBJECT_ID","TEST1"."OBJECT_TYPE" "OBJECT_TYPE","TEST1"."CREATED" "CREATED","TEST1"."LAST_DDL_TIME" "LAST_DDL_TIME","TEST1"."TIMESTAMP" "TIMESTAMP","TEST1"."STATUS" "STATUS","TEST1"."TEMPORARY" "TEMPORARY","TEST1"."GENERATED" "GENERATED","TEST1"."SECONDARY" "SECONDARY" FROM "SYS"."TEST" "TEST","SYS"."TEST1" "TEST1" WHERE "TEST"."OBJECT_ID"="TEST1"."OBJECT_ID"
kkoqbc-end
: call(in-use=48256, alloc=0), compile(in-use=38948, alloc=0)
apadrv-end: call(in-use=48256, alloc=0), compile(in-use=39536, alloc=0)
sql_id=8x63ah79zauv7.
Current SQL statement for this session:
select /*dd*/ test1.* from test,test1 where test.object_id=test1.object_id
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 162 | |
| 1 | NESTED LOOPS | | 23K | 2052K | 162 | 00:00:02 |
| 2 | TABLE ACCESS FULL | TEST1 | 49K | 4245K | 154 | 00:00:02 |
| 3 | INDEX UNIQUE SCAN | I_TEST_1| 1 | 4 | 0 | |
--------------------------------------+-----------------------------------+
Predicate Information:
|
|
|
|
|
|
| Re: why oracle choose NL in this case? [message #318848 is a reply to message #318841] |
Thu, 08 May 2008 03:53 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Consistent gets is not an absolute measure of performance. Just because it did more does not guarantee that it was slower.
The Consistent Gets is only showing you stats on memory access to the SGA, not the PGA. It does not report the cost of building or looking up the hash table in the PGA for a HJ, therefore it is incomplete.
If there was a separate line that represented that cost, it would be 0 for the NL join, and significant for the HJ. This would even things out.
A more interesting case would be to increase your data volumes so that the SGA could not cache the entire index/table. NL would really start to suffer then.
Ross Leishman
|
|
|
|
| Re: why oracle choose NL in this case? [message #318851 is a reply to message #318841] |
Thu, 08 May 2008 04:06 |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
| Michel Cadot wrote on Thu, 08 May 2008 03:24 | I don't have time to analyze in deep your trace but given the statistics Oracle has it estimates:
Best NL cost: 161.78
HA cost: 167.45
So NL is the logical choice for it.
Regards
Michel
|
Michel
you are correct,the cost of HJ is bigger than NL. but ：
the HJ runs much fast than NL,why?
and another thing is ,When I create a none unique index,oracle will choose HJ.
Regards
Alan
[Updated on: Thu, 08 May 2008 04:07] Report message to a moderator |
|
|
|
| Re: why oracle choose NL in this case? [message #318856 is a reply to message #318851] |
Thu, 08 May 2008 04:37 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
When you create a unique index, the CBO knows that for every row in the driving table, there is at most one matching row that it needs to select.
When the index is not unique, the CBO loses this guarantee, and so the NL cost will be higher.
|
|
|
|
| Re: why oracle choose NL in this case? [message #319063 is a reply to message #318856] |
Thu, 08 May 2008 19:34 |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
| JRowbottom wrote on Thu, 08 May 2008 04:37 | When you create a unique index, the CBO knows that for every row in the driving table, there is at most one matching row that it needs to select.
When the index is not unique, the CBO loses this guarantee, and so the NL cost will be higher.
|
JRowbottom
Thanks for your reply,but the thing I want to knows is
In this case,when i create a unique index, CBO think the cost of NL is less than HJ,so it choose NL,but the fact is HJ runs much faster than NL,does it means CBO got wrong here?
another question:
why the card for INDEX (UNIQUE SCAN) OF 'I_TEST_1' is not 23087 ,but 1?
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=162 Card=23087 Byt
es=2100917)
1 0 NESTED LOOPS (Cost=162 Card=23087 Bytes=2100917)
2 1 TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=154 Card=49
966 Bytes=4347042)
3 1 INDEX (UNIQUE SCAN) OF 'I_TEST_1' (INDEX (UNIQUE)) (Cost
=0 Card=1 --why it is not 23087 ,but 1???? Bytes=4)
Regards
Alan
[Updated on: Thu, 08 May 2008 19:39] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Thu Jun 27 20:15:47 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
