1.验证全表扫描的成本计算公式，贴出执行计划和计算公式。
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects where 1=0 ;
Table created.
SQL> alter table t pctfree 99 pctused 1;
Table altered.
SQL> insert into t select * from dba_objects where rownum<2;
1 row created.
SQL> alter table t minimize records_per_block;
Table altered.
SQL> insert into t select * from dba_objects where rownum<1000;
999 rows created.
SQL> commit;
Commit complete.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(user,'T');
PL/SQL procedure successfully completed.
SQL> select blocks,last_analyzed,sysdate from dba_tables where table_name='T' and owner=user;
BLOCKS LAST_ANAL SYSDATE
---------- --------- ---------
1000 18-DEC-13 18-DEC-13
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- --------------------
db_file_multiblock_read_count integer 128
SQL> set autotrace on
SQL> select count(*) from t;
COUNT(*)
----------
1000
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 272 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1000 | 272 (1)| 00:00:04 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1009 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
成本的计算方式如下：
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
CPUCycles / cpuspeed
) / sreadtime
#SRds - number of single block reads
#MRds - number of multi block reads
#CPUCyles - number of CPU cycles
sreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per second
注意：如果没有收集过系统统计信息，那么Oracle采用非工作量统计，如果收集了，Oracle采用工作量统计的计算方法
SQL> set autot off
SQL> select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
PNAME PVAL1
-------------------- ----------
CPUSPEEDNW 646
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM 0
MREADTIM
CPUSPEED 1440
MBRC
MAXTHR
SLAVETHR
9 rows selected.
SQL>
这里因为MBRC 为0，所以CBO采用了非工作量(noworkload)来计算成本
#SRds=0,因为是全表扫描，单块读为0
#MRds=表的块数/多块读参数=1000/128
mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed
select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
(select value from v$parameter where name = 'db_file_multiblock_read_count') *
(select value from v$parameter where name = 'db_block_size') /
(select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "mreadtim"
from dual;
SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
2 (select value from v$parameter where name = 'db_file_multiblock_read_count') *
3 (select value from v$parameter where name = 'db_block_size') /
4 (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "mreadtim"
5 from dual;
mreadtim
----------
266
SQL>
sreadtim=ioseektim+db_block_size/iotfrspeed
select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
(select value from v$parameter where name = 'db_block_size') /
(select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"
from dual;
SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +
2 (select value from v$parameter where name = 'db_block_size') /
3 (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"
4 from dual;
sreadtim
----------
12
SQL>
CPUCycles 等于 PLAN_TABLE里面的CPU_COST
SQL> truncate table plan_table;
Table truncated.
SQL> select cpu_cost from plan_table;
no rows selected
SQL> explain plan for select count(*) from t;
Explained.
SQL> select cpu_cost from plan_table;
CPU_COST
----------
7271440
7271440
SQL>
cpuspeed 等于 CPUSPEEDNW = 646
那么COST=(0+1000/128*266+7271440/646/1000)/12
SQL> select ceil((0+1000/128*266+7271440/646/1000)/12) from dual;
CEIL((0+1000/128*266+7271440/646/1000)/12)
------------------------------------------
175
SQL>
最后算出来的结果对不上,不知道是不是因为采用了自动表空间管理的问题.
参考:
http://blog.csdn.net/robinson1988/article/details/6603016
--EOF--
2.给出B-tree索引 Unique scan的成本计算公式，贴出执行计划和计算公式。
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 as select * from dba_objects where object_id is not null ;
Table created.
SQL> alter table t1 add primary key(object_id);
Table altered.
SQL> set autot traceonly
SQL> select object_name from t1 where object_id=100;
Execution Plan
----------------------------------------------------------
Plan hash value: 278582351
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 79 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C001104405 | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Statistics
----------------------------------------------------------
227 recursive calls
0 db block gets
43 consistent gets
2 physical reads
0 redo size
346 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autot off
SQL> select INDEX_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR,NUM_ROWS from user_ind_statistics where table_name='T1';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS
------------------------------ ---------- ----------- ----------------- ----------
SYS_C001104405 2 843 5906 393396
SQL>
Index Unique Scan的cost=blevel+1
从user_ind_statistics中看到unique index SYS_C001104405的blevel=2
cost＝blevel+1＝3，和执行计划中估算的成本一致。
参考:
http://f.dataguru.cn/thread-150776-1-1.html
--EOF--
3.通过10053事件分析一个SQL执行计划的产生过程，需要贴出trace中的相关信息和必要的文字说明。
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> create index idx_t_objid on t(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
Session altered.
SQL> select count(*) from t where object_id = 99;
COUNT(*)
----------
1
SQL> ALTER SESSION SET EVENTS '10053 trace name context off';
Session altered.
SQL> select name,value from v$diag_info where name like 'Default Trace%';
NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
Default Trace File
/ebs/oracle/ORCL/db/tech_st/11.1.0/admin/ORCL_localhost/diag/rdbms/orcl/
ORCL/trace/ORCL_ora_9470.trc
SQL>
view /ebs/oracle/ORCL/db/tech_st/11.1.0/admin/ORCL_localhost/diag/rdbms/orcl/ORCL/trace/ORCL_ora_9470.trc
Trace file /ebs/oracle/ORCL/db/tech_st/11.1.0/admin/ORCL_localhost/diag/rdbms/orcl/ORCL/trace/ORCL_ora_9470.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production #数据库与OS信息
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /ebs/oracle/ORCL/db/tech_st/11.1.0
System name: Linux
Node name: localhost.example.com
Release: 2.6.32-300.10.1.el5uek
Version: #1 SMP Wed Feb 22 17:37:40 EST 2012
Machine: x86_64
Instance name: ORCL
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 9470, image: oracle@localhost.example.com (TNS V1-V3)
*** 2013-12-19 19:01:58.707 #session信息
*** SESSION ID:(354.13989) 2013-12-19 19:01:58.707
*** CLIENT ID:() 2013-12-19 19:01:58.707
*** SERVICE NAME:(SYS$USERS) 2013-12-19 19:01:58.707
*** MODULE NAME:(SQL*Plus) 2013-12-19 19:01:58.707
*** ACTION NAME:() 2013-12-19 19:01:58.707
Registered qb: SEL$1 0xe91cbeb0 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=3326788 hint_alias="T"@"SEL$1"
SPM: statement not found in SMB
DOP: Automatic degree of parallelism is disabled: Parameter.
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
****************************************** #sql语句
----- Current SQL Statement for this session (sql_id=8zsvg3vy69zy8) -----
select count(*) from t where object_id = 99
*******************************************
Legend
The following abbreviations are used by optimizer trace. #缩略词解释
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
ST - star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
1: no partitioning required
2: value partitioned
4: right is random (round-robin)
128: left is random (round-robin)
8: broadcast right and partition left
16: broadcast left and partition right
32: partition left using partitioning of right
64: partition right using partitioning of left
256: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
*************************************** #优化器使用的参数
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
_sort_elimination_cost_ratio = 5
_pga_max_size = 209700 KB
_b_tree_bitmap_plans = false
_fast_full_scan_enabled = false
_index_join_enabled = false
_like_with_bind_as_equality = true
optimizer_secure_view_merging = false
Bug Fix Control Environment
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
Compilation Environment Dump
optimizer_mode_hinted = false
... (为减少版面,省略部分参数)
_bloom_pushing_max = 524288
Bug Fix Control Environment
fix 3834770 = 1
fix 3746511 = enabled
...
fix 7295298 = enabled
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
Considering Query Transformations on query block SEL$1 (#0)
************************** #查询转换与重写
Query transformations (QT)
**************************
CBQT bypassed for query block SEL$1 (#0): no complex view or sub-queries.
CBQT: Validity checks failed for 8zsvg3vy69zy8.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: OBYE bypassed: no order by to eliminate.
CVM: Considering view merge in query block SEL$1 (#0)
CNT: Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: COUNT() to COUNT(*) not done.
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
CBQT bypassed for query block SEL$1 (#0): no complex view or sub-queries.
CBQT: Validity checks failed for 8zsvg3vy69zy8.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$1 (#0).
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SJC: Considering set-join conversion in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"T"."OBJECT_ID"=99
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "T"."OBJECT_ID"=99
apadrv-start sqlid=10368252946011193288
:
call(in-use=592, alloc=16344), compile(in-use=46496, alloc=49248), execution(in-use=2112, alloc=4032)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "APPS"."T" "T" WHERE "T"."OBJECT_ID"=99
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=640, alloc=16344), compile(in-use=47408, alloc=49248), execution(in-use=2112, alloc=4032)
kkoqbc-subheap (create addr=0x7f87e91d12a8)
**************** #查询语句
QUERY BLOCK TEXT
****************
select count(*) from t where object_id = 99
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 objn=3326788 hint_alias="T"@"SEL$1"
----------------------------- #系统统计信息
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 646 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: T Alias: T
#Rows: 393484 #Blks: 5638 AvgRowLen: 97.00
Index Stats::
Index: IDX_T_OBJID Col#: 4
LVLS: 2 #LB: 898 #DK: 392128 LB/K: 1.00 DB/K: 1.00 CLUF: 5915.00
Access path analysis for T
*************************************** #访问路径与成本估计
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Table: T Alias: T
Card: Original: 393484.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
Cost: 1547.37 Resp: 1547.37 Degree: 0
Cost_io: 1529.00 Cost_cpu: 142456519
Resp_io: 1529.00 Resp_cpu: 142456519
Access Path: index (AllEqRange)
Index: IDX_T_OBJID
resc_io: 3.00 resc_cpu: 21764
ix_sel: 0.000003 ix_sel_with_filters: 0.000003
Cost: 3.00 Resp: 3.00 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_T_OBJID
Cost: 3.00 Degree: 1 Resp: 3.00 Card: 1.00 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
*************************************** #连接置换与成本计算
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: T[T]#0
***********************
Best so far: Table#: 0 cost: 3.0028 card: 1.0032 bytes: 6
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofkks[i] (index start key) predicate="T"."OBJECT_ID"=99
id=0 frofkke[i] (index stop key) predicate="T"."OBJECT_ID"=99
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1
Cost: 3.0028 Degree: 1 Card: 1.0000 Bytes: 6
Resc: 3.0028 Resc_io: 3.0000 Resc_cpu: 21764
Resp: 3.0028 Resp_io: 3.0000 Resc_cpu: 21764
kkoqbc-subheap (delete addr=0x7f87e91d12a8, in-use=12056, alloc=13576)
kkoqbc-end:
:
call(in-use=7000, alloc=32712), compile(in-use=48232, alloc=49248), execution(in-use=2112, alloc=4032)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
:
call(in-use=7000, alloc=32712), compile(in-use=49176, alloc=53392), execution(in-use=2112, alloc=4032)
Starting SQL statement dump
user_id=65 user_name=APPS module=SQL*Plus action=
sql_id=8zsvg3vy69zy8 plan_hash_value=572429185 problem_type=3
----- Current SQL Statement for this session (sql_id=8zsvg3vy69zy8) -----
select count(*) from t where object_id = 99
sql_text_length=44
sql=select count(*) from t where object_id = 99
----- Explain Plan Dump -----
----- Plan Table -----
============ #执行计划
Plan Table
============
----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | INDEX RANGE SCAN | IDX_T_OBJID| 1 | 6 | 3 | 00:00:01 |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("OBJECT_ID"=99)
Content of other_xml column
===========================
db_version : 11.1.0.7
parse_schema : APPS
plan_hash : 572429185
plan_hash_2 : 2577914714
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_fast_full_scan_enabled' 'false')
OPT_PARAM('_index_join_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted = false
...
_bloom_pushing_max = 524288
Bug Fix Control Environment
fix 3834770 = 1
fix 3746511 = enabled
...
fix 7295298 = enabled
Query Block Registry:
SEL$1 0xe91cbeb0 (PARSER) [FINAL]
:
call(in-use=9984, alloc=32712), compile(in-use=77056, alloc=139080), execution(in-use=5256, alloc=8088)
End of Optimizer State Dump
====================== END SQL Statement Dump ======================
--EOF--
4.当统计信息不准确时，CBO可能产生错误的执行计划，请给出这样的一个例子，在10053 trace中找到CBO出错的位置，并给出必要的文字说明。
SQL> drop table t purge;
Table dropped.
SQL> create table t as select rownum id, o.* from dba_objects o where rownum <= 100;
Table created.
SQL> create index idx_t_id on t(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL procedure successfully completed.
SQL> update t set id=99 where id !=1;
99 rows updated.
SQL> select id,count(*) from t group by id;
ID COUNT(*)
---------- ----------
1 1
99 99
SQL> commit;
Commit complete.
SQL> alter system flush shared_pool; #只有硬解析的情况下才会产生10053trace.
System altered.
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL> select * from t where id=99;
...
99 rows selected.
SQL>
SQL> alter session set events '10053 trace name context off';
Session altered.
SQL> select name,value from v$diag_info where name like 'Default Trace%';
NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
Default Trace File
/ebs/oracle/ORCL/db/tech_st/11.1.0/admin/ORCL_localhost/diag/rdbms/orcl/
ORCL/trace/ORCL_ora_13290.trc
SQL>
view /ebs/oracle/ORCL/db/tech_st/11.1.0/admin/ORCL_localhost/diag/rdbms/orcl/ORCL/trace/ORCL_ora_13290.trc
...
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T Alias: T
#Rows: 100 #Blks: 5 AvgRowLen: 78.00
Index Stats::
Index: IDX_T_ID Col#: 1
LVLS: 0 #LB: 1 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Table: T Alias: T
Card: Original: 100.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
Cost: 3.01 Resp: 3.01 Degree: 0
Cost_io: 3.00 Cost_cpu: 55907
Resp_io: 3.00 Resp_cpu: 55907
Access Path: index (AllEqRange)
Index: IDX_T_ID
resc_io: 2.00 resc_cpu: 14893
ix_sel: 0.010000 ix_sel_with_filters: 0.010000
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_T_ID
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0 #统计信息里index的card是错的
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: T[T]#0
***********************
Best so far: Table#: 0 cost: 2.0019 card: 1.0000 bytes: 78
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofkks[i] (index start key) predicate="T"."ID"=99
id=0 frofkke[i] (index stop key) predicate="T"."ID"=99
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1
Cost: 2.0019 Degree: 1 Card: 1.0000 Bytes: 78
Resc: 2.0019 Resc_io: 2.0000 Resc_cpu: 14893
Resp: 2.0019 Resp_io: 2.0000 Resc_cpu: 14893
kkoqbc-subheap (delete addr=0x7f4cf8f08248, in-use=12088, alloc=13672)
kkoqbc-end:
:
call(in-use=21336, alloc=49080), compile(in-use=57408, alloc=58792), execution(in-use=2112, alloc=4032)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
:
call(in-use=21336, alloc=49080), compile(in-use=58320, alloc=58792), execution(in-use=2112, alloc=4032)
Starting SQL statement dump
user_id=65 user_name=APPS module=SQL*Plus action=
sql_id=4qxbkhkmkh6yc plan_hash_value=514881935 problem_type=3
----- Current SQL Statement for this session (sql_id=4qxbkhkmkh6yc) -----
select * from t where id=99
sql_text_length=28
sql=select * from t where id=99
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============ #选择了错误的执行计划
-----------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 78 | 2 | 00:00:01 |
| 2 | INDEX RANGE SCAN | IDX_T_ID| 1 | | 1 | 00:00:01 |
-----------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("ID"=99)
Content of other_xml column
===========================
...
--EOF--
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
