SQL不走索引分析
create test as select * from dba_objects;主键列是oject_id
该列上的惟一索引是UNI_T_IDX
还有一个复合索引TEST_IDX在(object_id,object_name,data_object_id)上
收集统计信息后
select object_id from test;
走的竟然是全表扫描
SQL> select object_id from test;
71899 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT| | 71899 | 351K| 289 (2)| 00:00:04 |
| 1 |TABLE ACCESS FULL| TEST | 71899 | 351K| 289 (2)| 00:00:04 |
--------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_d7ctrd1n9v57y97bbe3d0" used for this statement
Statistics
----------------------------------------------------------
8recursive calls
0db block gets
5744consistent gets
34physical reads
0redo size
1312989bytes sent via SQL*Net to client
53246bytes received via SQL*Net from client
4795SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
71899rows processed
----加了HINT，才走索引
SQL> select /*+ index(test,uni_t_idx) */ object_id from test;
71899 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4280883380
------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71899 | 351K| 152 (2)| 00:00:02 |
| 1 |INDEX FULL SCAN | UNI_T_IDX | 71899 | 351K| 152 (2)| 00:00:02 |
------------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_ar6zhqbn2ybha4fb246d7" used for this statement
Statistics
----------------------------------------------------------
27recursive calls
56db block gets
4958consistent gets
1physical reads
14012redo size
1312989bytes sent via SQL*Net to client
53246bytes received via SQL*Net from client
4795SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
71899rows processed
做了一个10053（select object_id from test;）
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST"."OBJECT_ID" "OBJECT_ID" FROM "OVSEE"."TEST" "TEST"
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=1008, alloc=16344), compile(in-use=56080, alloc=58792), execution(in-use=2472, alloc=4032)
kkoqbc-subheap (create addr=0x2a970efb08)
****************
QUERY BLOCK TEXT
****************
select object_id from test
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=73239 hint_alias="TEST"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 630 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: -1 blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TESTAlias: TEST
#Rows: 71899#Blks:1050AvgRowLen:98.00
Index Stats::
Index: TEST_IDXCol#: 4 2 5
LVLS: 1#LB: 420#DK: 71899LB/K: 1.00DB/K: 1.00CLUF: 1066.00
Index: UNI_T_IDXCol#: 4
LVLS: 1#LB: 149#DK: 71899LB/K: 1.00DB/K: 1.00CLUF: 1066.00
Access path analysis for TEST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST
Table: TESTAlias: TEST
Card: Original: 71899.000000Rounded: 71899Computed: 71899.00Non Adjusted: 71899.00
Access Path: TableScan
Cost:288.98Resp: 288.98Degree: 0
Cost_io: 286.00Cost_cpu: 22576302
Resp_io: 286.00Resp_cpu: 22576302
Access Path: index (index (FFS))
Index: TEST_IDX
resc_io: 116.00resc_cpu: 11618885
ix_sel: 0.000000ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost:117.54Resp: 117.54Degree: 1
Cost_io: 116.00Cost_cpu: 11618885
Resp_io: 116.00Resp_cpu: 11618885
Access Path: index (index (FFS))
Index: UNI_T_IDX
resc_io: 42.00resc_cpu: 9688975
ix_sel: 0.000000ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost:43.28Resp: 43.28Degree: 1
Cost_io: 42.00Cost_cpu: 9688975
Resp_io: 42.00Resp_cpu: 9688975
Access Path: index (FullScan)
Index: TEST_IDX
resc_io: 421.00resc_cpu: 17377926
ix_sel: 1.000000ix_sel_with_filters: 1.000000
Cost: 423.30Resp: 423.30Degree: 1
Access Path: index (FullScan)
Index: UNI_T_IDX
resc_io: 150.00resc_cpu: 15448016
ix_sel: 1.000000ix_sel_with_filters: 1.000000
Cost: 152.04Resp: 152.04Degree: 1
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: TEST_IDX
resc_io: 421.00resc_cpu: 17377926
ix_sel: 1.000000ix_sel_with_filters: 1.000000
Cost: 423.30Resp: 423.30Degree: 0
Access Path: index (FullScan)
Index: UNI_T_IDX
resc_io: 150.00resc_cpu: 15448016
ix_sel: 1.000000ix_sel_with_filters: 1.000000
Cost: 152.04Resp: 152.04Degree: 0
****** finished trying bitmap/domain indexes ******
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: TEST_IDX
resc_io: 421.00resc_cpu: 17377926
ix_sel: 1.000000ix_sel_with_filters: 1.000000
Cost: 423.30Resp: 423.30Degree: 0
Access Path: index (FullScan)
Index: UNI_T_IDX
resc_io: 150.00resc_cpu: 15448016
ix_sel: 1.000000ix_sel_with_filters: 1.000000
Cost: 152.04Resp: 152.04Degree: 0
Access Path: index (FullScan)
Index: UNI_T_IDX
resc_io: 150.00resc_cpu: 15448016
ix_sel: 1.000000ix_sel_with_filters: 1.000000
Cost: 152.04Resp: 152.04Degree: 0
Bitmap nodes:
Used UNI_T_IDX
Cost = 190.052466, sel = 1.000000
****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: IndexFFS -----这里分析出来的是FFS最好,COST 43
Index: UNI_T_IDX
Cost: 43.28Degree: 1Resp: 43.28Card: 71899.00Bytes: 0
然而，后面又有继续分析
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ FULL ("TEST") */ "TEST"."OBJECT_ID" "OBJECT_ID" FROM "OVSEE"."TEST" "TEST"---不知这里为什么加了FULL
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=1024, alloc=16344), compile(in-use=59400, alloc=60664), execution(in-use=3512, alloc=4032)
kkoqbc-subheap (create addr=0x2a971dbf60)
****************
QUERY BLOCK TEXT
****************
select object_id from test
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=73239 hint_alias="TEST"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 630 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: -1 blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TESTAlias: TEST
#Rows: 71899#Blks:1050AvgRowLen:98.00
Index Stats::
Index: TEST_IDXCol#: 4 2 5
LVLS: 1#LB: 420#DK: 71899LB/K: 1.00DB/K: 1.00CLUF: 1066.00
Index: UNI_T_IDXCol#: 4
LVLS: 1#LB: 149#DK: 71899LB/K: 1.00DB/K: 1.00CLUF: 1066.00
Access path analysis for TEST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST
Table: TESTAlias: TEST
Card: Original: 71899.000000Rounded: 71899Computed: 71899.00Non Adjusted: 71899.00
Access Path: TableScan
Cost:288.98Resp: 288.98Degree: 0
Cost_io: 286.00Cost_cpu: 22576302
Resp_io: 286.00Resp_cpu: 22576302
Best:: AccessPath: TableScan ---出来的结果是这个最好的 COST 288
Cost: 288.98Degree: 1Resp: 288.98Card: 71899.00Bytes: 0
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order:TEST#0
***********************
Best so far:Table#: 0cost: 288.9842card: 71899.0000bytes: 359495
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
Or-Expansion validity checks failed on query block SEL$1 (#0) because NO_EXPAND hint
Transfer Optimizer annotations for query block SEL$1 (#0)
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1
Cost: 288.9842Degree: 1Card: 71899.0000Bytes: 359495
Resc: 288.9842Resc_io: 286.0000Resc_cpu: 22576302
Resp: 288.9842Resp_io: 286.0000Resc_cpu: 22576302
kkoqbc-subheap (delete addr=0x2a970efb08, in-use=11576, alloc=14408)
kkoqbc-end:
:
call(in-use=7384, alloc=32712), compile(in-use=68128, alloc=72504), execution(in-use=97024, alloc=97560)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
:
call(in-use=7384, alloc=32712), compile(in-use=69040, alloc=72504), execution(in-use=97024, alloc=97560)
Starting SQL statement dump
user_id=84 user_name=OVSEE module=SQL*Plus action=
sql_id=dywdy4jmt6vz9 plan_hash_value=1357081020 problem_type=3
----- Current SQL Statement for this session (sql_id=dywdy4jmt6vz9) -----
EXPLAIN PLAN SET STATEMENT_ID='PLUS120042' FOR select object_id from test
sql_text_length=74
sql=EXPLAIN PLAN SET STATEMENT_ID='PLUS120042' FOR select object_id from test
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id| Operation | Name | Rows| Bytes | Cost| Time |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 289 | |
| 1 |TABLE ACCESS FULL | TEST | 70K |351K | 289 |00:00:04 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
最好选择了会表扫，但从加了HINT 结果来看，是走索引COST比较低的，IO读也是。一直没法弄懂
select object_id from test.你查询所有object_id当然是全表扫描好，为什么要走索引呢，走索引会扫更多的块。
页:
[1]