Home » RDBMS Server » Performance Tuning » use HASH instead of index range scan,why the CBO choose the wrong plan?!!
 use HASH instead of index range scan,why the CBO choose the wrong plan?!! [message #168443] |
Thu, 20 April 2006 08:14  |
wenaini
Messages: 6
Registered: April 2006
|
Junior Member |
|
|
I had found the solution everywhere include metalink,asktom and so on but got nothing.
I have a table with 30 columns and 40 milion rows,which is partitioned,and I have a local composite index on the (trading_date,account_code),but after I used DBMS_STATS.gather_index_stats(OWNER,INDEX,granulity=>ALL) to collect the statistics of index.The execution plan changed from index range scan + nested loop to hash join.And I think it is a worse plan.And when I delete the index statistics,the plan returned to the right plan.could someone give me the reason?and how to calculate the CPU_IO and RSC_IO in the 10053 trace file?I think they are the key to the problem.Here is the 10053 trace and 10046 trace(200306 and 200307 is almost same so I analyze index statistics of 200307 but not analyze index statistics 200306):
********************************************************************************
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 events '10046 trace name context forever,level 12'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 38
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 14.42 14.42
********************************************************************************
select /*+ INDEX(A,IQDETAIL_200307)*/ a.account_code,a.account_name,a.trade_code,
a.trade_volume,a.stock_code,a.bs,a.trade_price
from
Q_DETAIL_200307 A,TRADING_DATE_MONTH_END B where a.stock_code like '600%' and
A.trading_date=B.TRADING_DATE
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 63613 12.11 11.41 451 557045 0 954166
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 63615 12.11 11.41 451 557045 0 954166
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 38
Rows Row Source Operation
------- ---------------------------------------------------
954166 TABLE ACCESS BY LOCAL INDEX ROWID Q_DETAIL_200307
954172 NESTED LOOPS
5 TABLE ACCESS FULL TRADING_DATE_MONTH_END
954166 PARTITION RANGE ITERATOR PARTITION: 83 810
954166 INDEX RANGE SCAN IQDETAIL_200307 PARTITION: 83 810 (object id 47764635)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 63613 0.00 0.04
db file sequential read 451 0.00 0.01
SQL*Net message from client 63613 0.00 28.67
********************************************************************************
select a.account_code,a.account_name,a.trade_code,a.trade_volume,a.stock_code,
a.bs,a.trade_price
from
Q_DETAIL_200307 A,TRADING_DATE_MONTH_END B where a.stock_code like '600%' and
A.trading_date=B.TRADING_DATE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 63613 87.80 85.31 342444 420203 0 954166
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 63615 87.82 85.32 342444 420203 0 954166
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 38
Rows Row Source Operation
------- ---------------------------------------------------
954166 HASH JOIN
5 TABLE ACCESS FULL TRADING_DATE_MONTH_END
29951176 PARTITION RANGE ITERATOR PARTITION: 83 810
29951176 TABLE ACCESS FULL Q_DETAIL_200307 PARTITION: 83 810
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 63613 0.00 0.03
db file scattered read 15371 0.00 13.93
SQL*Net message from client 63613 0.00 28.66
db file sequential read 184 0.00 0.00
********************************************************************************
select a.account_code,a.account_name,a.trade_code,a.trade_volume,a.stock_code,a.bs,a.trade_price
from q_detail_200306 A,TRADING_DATE_MONTH_END B where a.stock_code like '600%' and A.trading_date=B.TRADING_DATE
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 9.2.0
OPTIMIZER_MODE/GOAL = Choose
_OPTIMIZER_PERCENT_PARALLEL = 101
HASH_AREA_SIZE = 40265250
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
SORT_AREA_SIZE = 25165824
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = TRUE
STAR_TRANSFORMATION_ENABLED = TRUE
_COMPLEX_VIEW_MERGING = TRUE
_PUSH_JOIN_PREDICATE = TRUE
PARALLEL_BROADCAST_ENABLED = TRUE
OPTIMIZER_MAX_PERMUTATIONS = 2000
OPTIMIZER_INDEX_CACHING = 0
_SYSTEM_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
OPTIMIZER_DYNAMIC_SAMPLING = 1
_OPTIMIZER_DYN_SMP_BLKS = 32
QUERY_REWRITE_ENABLED = TRUE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = TRUE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = TRUE
ALWAYS_ANTI_JOIN = CHOOSE
ALWAYS_SEMI_JOIN = CHOOSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = TRUE
_PUSH_JOIN_UNION_VIEW = TRUE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = TRUE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = TRUE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = TRUE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
_OPTIMIZER_COST_MODEL = CHOOSE
_GSETS_ALWAYS_USE_TEMPTABLES = FALSE
DB_FILE_MULTIBLOCK_READ_COUNT = 64
_NEW_SORT_COST_ESTIMATE = TRUE
_GS_ANTI_SEMI_JOIN_ALLOWED = TRUE
_CPU_TO_IO = 0
_PRED_MOVE_AROUND = TRUE
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: TRADING_DATE_MONTH_END Alias: B
TOTAL :: CDN: 2 NBLKS: 1 AVG_ROW_LEN: 11
Column: TRADING_DA Col#: 1 Table: TRADING_DATE_MONTH_END Alias: B
NDV: 2 NULLS: 0 DENS: 5.0000e-01 LO: 2452821 HI: 2452832
NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
INDEX NAME: I_TRADING_DATE_MONTH_END COL#: 1
TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800
***********************
Table stats Table: Q_DETAIL_200306 Alias: A
(Using composite stats)
(adjusted for partition skews)
ORIG STATS:: CDN: 32221150 NBLKS: 366868 AVG_ROW_LEN: 100
PARTCNT::
PRUNED: 728 ANALYZED: 728 UNANALYZED: 0
TOTAL :: CDN: 32221150 NBLKS: 346000 AVG_ROW_LEN: 100
-- Index stats
INDEX NAME: IQDETAIL_200306 COL#: 3 1
USING COMPOSITE STATS
TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
Column: STOCK_CODE Col#: 7 Table: Q_DETAIL_200306 Alias: A
NO STATISTICS (using defaults)
NDV: 1006911 NULLS: 0 DENS: 9.9314e-07
NO HISTOGRAM: #BKT: 0 #VAL: 0
TABLE: Q_DETAIL_200306 ORIG CDN: 32221150 ROUNDED CDN: 1611058 CMPTD CDN: 1611058
Access path: tsc Resc: 13363 Resp: 13363
BEST_CST: 13363.00 PATH: 2 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: TRADING_DATE_MONTH_END ORIG CDN: 2 ROUNDED CDN: 2 CMPTD CDN: 2
Access path: tsc Resc: 2 Resp: 2
Access path: index (iff)
Index: I_TRADING_DATE_MONTH_END
TABLE: TRADING_DATE_MONTH_END
RSC_CPU: 0 RSC_IO: 3
IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00
Access path: iff Resc: 3 Resp: 3
Access path: index (no sta/stp keys)
Index: I_TRADING_DATE_MONTH_END
TABLE: TRADING_DATE_MONTH_END
RSC_CPU: 0 RSC_IO: 26
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
BEST_CST: 2.00 PATH: 2 Degree: 1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: TRADING_DATE_MONTH_END [ B] Q_DETAIL_200306 [ A]
Now joining: Q_DETAIL_200306 [ A] *******
NL Join
Outer table: cost: 2 cdn: 2 rcz: 7 resp: 2
Inner table: Q_DETAIL_200306
Access path: tsc Resc: 13363
Join: Resc: 26728 Resp: 26728
Column: TRADING_DA Col#: 3 Table: Q_DETAIL_200306 Alias: A
NDV: 21 NULLS: 0 DENS: 1.5518e-08
FREQUENCY HISTOGRAM: #BKT: 32221150 #VAL: 21
Access path: index (scan)
Index: IQDETAIL_200306
TABLE: Q_DETAIL_200306
RSC_CPU: 0 RSC_IO: 768
IX_SEL: 4.7619e-02 TB_SEL: 4.7619e-02
Join: resc: 1538 resp: 1538
Join cardinality: 153434 = outer (2) * inner (1611058) * sel (4.7619e-02) [flag=0]
Best NL cost: 1538 resp: 1538
SM Join
Outer table:
resc: 2 cdn: 2 rcz: 7 deg: 1 resp: 2
Inner table: Q_DETAIL_200306
resc: 13363 cdn: 1611058 rcz: 75 deg: 1 resp: 13363
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 678 Area size: 17301504 Max Area size: 17301504 Degree: 1
Blocks to Sort: 1 Row size: 18 Rows: 2
Initial runs: 1 Merge passes: 1 IO Cost / pass: 28
Total IO sort cost: 14
Total CPU sort cost: 0
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 678 Area size: 17301504 Max Area size: 17301504 Degree: 1
Blocks to Sort: 18344 Row size: 93 Rows: 1611058
Initial runs: 9 Merge passes: 1 IO Cost / pass: 19100
Total IO sort cost: 18722
Total CPU sort cost: 0
Total Temp space used: 310551000
Merge join Cost: 32102 Resp: 32102
SM Join (with index on outer)
Access path: index (no sta/stp keys)
Index: I_TRADING_DATE_MONTH_END
TABLE: TRADING_DATE_MONTH_END
RSC_CPU: 0 RSC_IO: 26
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
Outer table:
resc: 26 cdn: 2 rcz: 7 deg: 1 resp: 26
Inner table: Q_DETAIL_200306
resc: 13363 cdn: 1611058 rcz: 75 deg: 1 resp: 13363
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 678 Area size: 17301504 Max Area size: 17301504 Degree: 1
Blocks to Sort: 18344 Row size: 93 Rows: 1611058
Initial runs: 9 Merge passes: 1 IO Cost / pass: 19100
Total IO sort cost: 18722
Total CPU sort cost: 0
Total Temp space used: 310551000
Merge join Cost: 32111 Resp: 32111
HA Join
Outer table:
resc: 2 cdn: 2 rcz: 7 deg: 1 resp: 2
Inner table: Q_DETAIL_200306
resc: 13363 cdn: 1611058 rcz: 75 deg: 1 resp: 13363
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 4916 (max=4916) buildfrag: 4917 probefrag: 17110 ppasses: 2
Hash join Resc: 13366 Resp: 13366
Join result: cost: 1538 cdn: 153434 rcz: 82
Best so far: TABLE#: 0 CST: 2 CDN: 2 BYTES: 14
Best so far: TABLE#: 1 CST: 1538 CDN: 153434 BYTES: 12581588
***********************
Join order[2]: Q_DETAIL_200306 [ A] TRADING_DATE_MONTH_END [ B]
Final:
CST: 1538 CDN: 153434 RSC: 1538 RSP: 1538 BYTES: 12581588
IO-RSC: 1538 IO-RSP: 1538 CPU-RSC: 0 CPU-RSP: 0
*** 2006-04-14 15:33:12.526
*** SESSION ID:(30.2137) 2006-04-14 15:33:12.526
alter session set events '10053 trace name context off'
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: TRADING_DATE_MONTH_END Alias: B
TOTAL :: CDN: 2 NBLKS: 1 AVG_ROW_LEN: 11
Column: TRADING_DA Col#: 1 Table: TRADING_DATE_MONTH_END Alias: B
NDV: 2 NULLS: 0 DENS: 5.0000e-01 LO: 2452821 HI: 2452832
NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
INDEX NAME: I_TRADING_DATE_MONTH_END COL#: 1
TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800
***********************
Table stats Table: Q_DETAIL_200307 Alias: A
(Using composite stats)
(adjusted for partition skews)
ORIG STATS:: CDN: 31364510 NBLKS: 358342 AVG_ROW_LEN: 100
PARTCNT::
PRUNED: 736 ANALYZED: 736 UNANALYZED: 0
TOTAL :: CDN: 31364510 NBLKS: 341011 AVG_ROW_LEN: 100
-- Index stats
INDEX NAME: IQDETAIL_200307 COL#: 3 1
USING COMPOSITE STATS
TOTAL :: LVLS: 2 #LB: 132190 #DK: 9793391 LB/K: 1 DB/K: 3 CLUF: 30056179
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
Column: STOCK_CODE Col#: 7 Table: Q_DETAIL_200307 Alias: A
NO STATISTICS (using defaults)
NDV: 980141 NULLS: 0 DENS: 1.0203e-06
NO HISTOGRAM: #BKT: 0 #VAL: 0
TABLE: Q_DETAIL_200307 ORIG CDN: 31364510 ROUNDED CDN: 1568226 CMPTD CDN: 1568226
Access path: tsc Resc: 13170 Resp: 13170
BEST_CST: 13170.00 PATH: 2 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: TRADING_DATE_MONTH_END ORIG CDN: 2 ROUNDED CDN: 2 CMPTD CDN: 2
Access path: tsc Resc: 2 Resp: 2
Access path: index (iff)
Index: I_TRADING_DATE_MONTH_END
TABLE: TRADING_DATE_MONTH_END
RSC_CPU: 0 RSC_IO: 3
IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00
Access path: iff Resc: 3 Resp: 3
Access path: index (no sta/stp keys)
Index: I_TRADING_DATE_MONTH_END
TABLE: TRADING_DATE_MONTH_END
RSC_CPU: 0 RSC_IO: 26
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
BEST_CST: 2.00 PATH: 2 Degree: 1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: TRADING_DATE_MONTH_END [ B] Q_DETAIL_200307 [ A]
Now joining: Q_DETAIL_200307 [ A] *******
NL Join
Outer table: cost: 2 cdn: 2 rcz: 7 resp: 2
Inner table: Q_DETAIL_200307
Access path: tsc Resc: 13170
Join: Resc: 26342 Resp: 26342
Column: TRADING_DA Col#: 3 Table: Q_DETAIL_200307 Alias: A
NDV: 23 NULLS: 0 DENS: 1.5942e-08
FREQUENCY HISTOGRAM: #BKT: 31364510 #VAL: 23
Access path: index (scan)
Index: IQDETAIL_200307
TABLE: Q_DETAIL_200307
RSC_CPU: 0 RSC_IO: 1314010
IX_SEL: 4.3478e-02 TB_SEL: 4.3478e-02
Join: resc: 2628022 resp: 2628022
Join cardinality: 136367 = outer (2) * inner (1568226) * sel (4.3478e-02) [flag=0]
Best NL cost: 26342 resp: 26342
SM Join
Outer table:
resc: 2 cdn: 2 rcz: 7 deg: 1 resp: 2
Inner table: Q_DETAIL_200307
resc: 13170 cdn: 1568226 rcz: 75 deg: 1 resp: 13170
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 678 Area size: 17301504 Max Area size: 17301504 Degree: 1
Blocks to Sort: 1 Row size: 18 Rows: 2
Initial runs: 1 Merge passes: 1 IO Cost / pass: 28
Total IO sort cost: 14
Total CPU sort cost: 0
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 678 Area size: 17301504 Max Area size: 17301504 Degree: 1
Blocks to Sort: 17856 Row size: 93 Rows: 1568226
Initial runs: 9 Merge passes: 1 IO Cost / pass: 18585
Total IO sort cost: 18220
Total CPU sort cost: 0
Total Temp space used: 302293000
Merge join Cost: 31407 Resp: 31407
SM Join (with index on outer)
Access path: index (no sta/stp keys)
Index: I_TRADING_DATE_MONTH_END
TABLE: TRADING_DATE_MONTH_END
RSC_CPU: 0 RSC_IO: 26
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
Outer table:
resc: 26 cdn: 2 rcz: 7 deg: 1 resp: 26
Inner table: Q_DETAIL_200307
resc: 13170 cdn: 1568226 rcz: 75 deg: 1 resp: 13170
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 678 Area size: 17301504 Max Area size: 17301504 Degree: 1
Blocks to Sort: 17856 Row size: 93 Rows: 1568226
Initial runs: 9 Merge passes: 1 IO Cost / pass: 18585
Total IO sort cost: 18220
Total CPU sort cost: 0
Total Temp space used: 302293000
Merge join Cost: 31416 Resp: 31416
HA Join
Outer table:
resc: 2 cdn: 2 rcz: 7 deg: 1 resp: 2
Inner table: Q_DETAIL_200307
resc: 13170 cdn: 1568226 rcz: 75 deg: 1 resp: 13170
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 4916 (max=4916) buildfrag: 4917 probefrag: 16655 ppasses: 2
Hash join Resc: 13173 Resp: 13173
Join result: cost: 13173 cdn: 136367 rcz: 82
Best so far: TABLE#: 0 CST: 2 CDN: 2 BYTES: 14
Best so far: TABLE#: 1 CST: 13173 CDN: 136367 BYTES: 11182094
***********************
Join order[2]: Q_DETAIL_200307 [ A] TRADING_DATE_MONTH_END [ B]
Now joining: TRADING_DATE_MONTH_END [ B] *******
NL Join
Outer table: cost: 13170 cdn: 1568226 rcz: 75 resp: 13170
Inner table: TRADING_DATE_MONTH_END
Access path: tsc Resc: 2
Join: Resc: 3149622 Resp: 3149622
Access path: index (iff)
Index: I_TRADING_DATE_MONTH_END
TABLE: TRADING_DATE_MONTH_END
RSC_CPU: 0 RSC_IO: 3
IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00
Inner table: TRADING_DATE_MONTH_END
Access path: iff Resc: 3
Join: Resc: 4717848 Resp: 4717848
Access path: index (join index)
Index: I_TRADING_DATE_MONTH_END
TABLE: TRADING_DATE_MONTH_END
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 0.0000e+00 TB_SEL: 5.0000e-01
Join: resc: 1581396 resp: 1581396
Join cardinality: 136367 = outer (1568226) * inner (2) * sel (4.3478e-02) [flag=0]
Best NL cost: 1581396 resp: 1581396
SM Join
Outer table:
resc: 13170 cdn: 1568226 rcz: 75 deg: 1 resp: 13170
Inner table: TRADING_DATE_MONTH_END
resc: 2 cdn: 2 rcz: 7 deg: 1 resp: 2
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 678 Area size: 17301504 Max Area size: 17301504 Degree: 1
Blocks to Sort: 17856 Row size: 93 Rows: 1568226
Initial runs: 9 Merge passes: 1 IO Cost / pass: 18585
Total IO sort cost: 18220
Total CPU sort cost: 0
Total Temp space used: 302293000
SORT resource Sort statistics
Sort width: 678 Area size: 17301504 Max Area size: 17301504 Degree: 1
Blocks to Sort: 1 Row size: 18 Rows: 2
Initial runs: 1 Merge passes: 1 IO Cost / pass: 28
Total IO sort cost: 14
Total CPU sort cost: 0
Total Temp space used: 0
Merge join Cost: 31407 Resp: 31407
HA Join
Outer table:
resc: 13170 cdn: 1568226 rcz: 75 deg: 1 resp: 13170
Inner table: TRADING_DATE_MONTH_END
resc: 2 cdn: 2 rcz: 7 deg: 1 resp: 2
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1016 Deg: 1 (sides swapped)
hash_area: 4916 (max=4916) buildfrag: 16655 probefrag: 1 ppasses: 4
Hash join Resc: 14188 Resp: 14188
Outer table:
resc: 2 cdn: 2 rcz: 7 deg: 1 resp: 2
Inner table: Q_DETAIL_200307
resc: 13170 cdn: 1568226 rcz: 75 deg: 1 resp: 13170
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 4916 (max=4916) buildfrag: 4917 probefrag: 16655 ppasses: 2
Hash join Resc: 13173 Resp: 13173
Final:
CST: 13173 CDN: 136367 RSC: 13173 RSP: 13173 BYTES: 11182094
IO-RSC: 13173 IO-RSP: 13173 CPU-RSC: 0 CPU-RSP: 0
just tell me why the rsc_io differs so much???
[Updated on: Thu, 20 April 2006 08:24] Report message to a moderator |
|
|
|
| Re: use HASH instead of index range scan,why the CBO choose the wrong plan?!! [message #168468 is a reply to message #168443] |
Thu, 20 April 2006 10:36  |
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
|
Senior Member |
|
|
You have index on (trading_date,account_code), but your condition is "a.stock_code like '600%' and A.trading_date=B.TRADING_DATE"
Index can be used for "A.trading_date=B.TRADING_DATE" predicate only.
If partitioning granularity is "days", and A.trading_date has truncated date values, the index may be not enough selective for queries "where date=:vdate".
That is why, I think, Oracle prefers FS + HASH.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: use HASH instead of index range scan,why the CBO choose the wrong plan?!! [message #168696 is a reply to message #168595] |
Fri, 21 April 2006 12:19 |
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
|
Senior Member |
|
|
| wenaini wrote on Fri, 21 April 2006 02:51 |
| markmal wrote on Fri, 21 April 2006 00:42 |
oops... actually I wanted to know
select count(distinct TRADING_DATE), count(TRADING_DATE)
from q_detail_200306 ;
|
o,there is about 30 million rows in q_detail_200306,and it is a month table so the trading_date has 22 distinct value,the return data are 0.9 million rows.
|
YOur actual data has very low cardinality.
Oracle "knows" it only after stats gathering. Without stats Oracle optimizer applies default values for rows number, cardinality etc...
These default values are quite optimistic and don't suite your case.
Why did you mentioned that a plan with Index Range Scan + NL is better for your data than FS+HJ? Does it work faster?
|
|
|
|
| Re: use HASH instead of index range scan,why the CBO choose the wrong plan?!! [message #168758 is a reply to message #168696] |
Sat, 22 April 2006 02:24 |
wenaini
Messages: 6
Registered: April 2006
|
Junior Member |
|
|
| markmal wrote on Fri, 21 April 2006 12:19 |
| wenaini wrote on Fri, 21 April 2006 02:51 |
| markmal wrote on Fri, 21 April 2006 00:42 |
oops... actually I wanted to know
select count(distinct TRADING_DATE), count(TRADING_DATE)
from q_detail_200306 ;
|
o,there is about 30 million rows in q_detail_200306,and it is a month table so the trading_date has 22 distinct value,the return data are 0.9 million rows.
|
YOur actual data has very low cardinality.
Oracle "knows" it only after stats gathering. Without stats Oracle optimizer applies default values for rows number, cardinality etc...
These default values are quite optimistic and don't suite your case.
Why did you mentioned that a plan with Index Range Scan + NL is better for your data than FS+HJ? Does it work faster?
|
yes,the execution of INDEX+nl is faster and this the 10046 trace:
********************************************************************************
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 events '10046 trace name context forever,level 12'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 38
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 14.42 14.42
********************************************************************************
select /*+ INDEX(A,IQDETAIL_200307)*/ a.account_code,a.account_name,a.trade_code,
a.trade_volume,a.stock_code,a.bs,a.trade_price
from
Q_DETAIL_200307 A,TRADING_DATE_MONTH_END B where a.stock_code like '600%' and
A.trading_date=B.TRADING_DATE
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 63613 12.11 11.41 451 557045 0 954166
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 63615 12.11 11.41 451 557045 0 954166
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 38
Rows Row Source Operation
------- ---------------------------------------------------
954166 TABLE ACCESS BY LOCAL INDEX ROWID Q_DETAIL_200307
954172 NESTED LOOPS
5 TABLE ACCESS FULL TRADING_DATE_MONTH_END
954166 PARTITION RANGE ITERATOR PARTITION: 83 810
954166 INDEX RANGE SCAN IQDETAIL_200307 PARTITION: 83 810 (object id 47764635)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 63613 0.00 0.04
db file sequential read 451 0.00 0.01
SQL*Net message from client 63613 0.00 28.67
********************************************************************************
select a.account_code,a.account_name,a.trade_code,a.trade_volume,a.stock_code,
a.bs,a.trade_price
from
Q_DETAIL_200307 A,TRADING_DATE_MONTH_END B where a.stock_code like '600%' and
A.trading_date=B.TRADING_DATE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 63613 87.80 85.31 342444 420203 0 954166
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 63615 87.82 85.32 342444 420203 0 954166
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 38
Rows Row Source Operation
------- ---------------------------------------------------
954166 HASH JOIN
5 TABLE ACCESS FULL TRADING_DATE_MONTH_END
29951176 PARTITION RANGE ITERATOR PARTITION: 83 810
29951176 TABLE ACCESS FULL Q_DETAIL_200307 PARTITION: 83 810
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 63613 0.00 0.03
db file scattered read 15371 0.00 13.93
SQL*Net message from client 63613 0.00 28.66
db file sequential read 184 0.00 0.00
********************************************************************************
|
|
|
|
|
|
| Re: use HASH instead of index range scan,why the CBO choose the wrong plan?!! [message #169059 is a reply to message #169001] |
Mon, 24 April 2006 21:55 |
wenaini
Messages: 6
Registered: April 2006
|
Junior Member |
|
|
| markmal wrote on Mon, 24 April 2006 10:19 |
Previously I thought you have partitions by days.
But IQDETAIL_200307 in your hint is saying that you have table partitioned by monthly? Is my assumption correct?
If above is correct, and you have dates truncated to days, then the cardinality can be OK to use index.
Why optimiser still does not use it? May be because you have
HASH_AREA_SIZE = 40265250
HASH_MULTIBLOCK_IO_COUNT = 0
|
in the fact the table is partitioned by stock_code column.
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 15:37:57 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
