18 积分	0 好友	0 主题

发消息

帮忙给讲讲10053的核心内容

1^#

发表于 2012-4-18 13:25:22 | 查看: 6212| 回复: 6

需要关心的内容有哪些呢？

比如以下边这个10053为例

多谢

mydb_ora_2322.trc.txt

108.52 KB, 下载次数: 896

分享0

收藏0 回复只看该作者道具举报

几番风雨

2^#

发表于 2012-4-18 13:35:44

有点迷糊其中 61-407
61-407

******************************************
Current SQL statement for this session:
select object_id,status from mytest where status=:a
*******************************************

*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
Bind#0
  oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=b7f547e8  bln=32  avl=08  flg=05
  value="INVALIAD"

参数
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
  optimizer_mode_hinted             = false
..........................................................
  _optimizer_star_tran_in_with_clause = true
  _optimizer_complex_pred_selectivity = true
  _gby_hash_aggregation_enabled    = true
  ***************************************
  PARAMETERS IN OPT_PARAM HINT
  ****************************

***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
select object_id,status from mytest where status=:a
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=66933 hint_alias="MYTEST"@"SEL$1"

*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 587 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: MYTEST  Alias: MYTEST
#Rows: 50459  #Blks:  328  AvgRowLen:  39.00
Index Stats::
  Index: IDX_MYTEST_STATUS  Col#: 4
LVLS: 1  #LB: 120  #DK: 2  LB/K: 60.00  DB/K: 158.00  CLUF: 317.00
***************************************

SINGLE TABLE ACCESS PATH
  Column (#4): STATUS(VARCHAR2)
AvgLen: 7.00 NDV: 2 Nulls: 0 Density: 1.0085e-05
Histogram: Freq  #Bkts: 2  UncompBkts: 5404  EndPtVals: 2
  Table: MYTEST  Alias: MYTEST
Card: Original: 50459  Rounded: 28  Computed: 28.01  Non Adjusted: 28.01
  Access Path: TableScan
Cost:  75.19  Resp: 75.19  Degree: 0
   Cost_io: 73.00  Cost_cpu: 15455172
   Resp_io: 73.00  Resp_cpu: 15455172
  Access Path: index (AllEqRange)
Index: IDX_MYTEST_STATUS
resc_io: 2.00  resc_cpu: 26983
ix_sel: 5.5514e-04  ix_sel_with_filters: 5.5514e-04
Cost: 2.00  Resp: 2.00  Degree: 1
  Best:: AccessPath: IndexRange  Index: IDX_MYTEST_STATUS
      Cost: 2.00  Degree: 1  Resp: 2.00  Card: 28.01  Bytes: 0

这里已经是best index啦，但是在下楼继续。。。

[ 本帖最后由几番风雨于 2012-4-18 13:37 编辑 ]

回复只看该作者道具举报

几番风雨

3^#

发表于 2012-4-18 13:36:15

1321-1625

******************************************
Current SQL statement for this session:
EXPLAIN PLAN SET STATEMENT_ID='PLUS46527' FOR select object_id,status from mytest where status=:a
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
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
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)
CPUCSPEED - 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)
  512: 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
  128: use hash partitioning dimension
  256: use range partitioning dimension
  2048: use list partitioning dimension
  1024: run the join in serial
  0: invalid distribution method
sel - selectivity
ptn - partition
*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
Bind#0
  oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1010000 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=b7da0e68  bln=32  avl=00  flg=05
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
  optimizer_mode_hinted             = false
  optimizer_features_hinted          = 0.0.0
......................................................
  _optimizer_star_tran_in_with_clause = true
  _optimizer_complex_pred_selectivity = true
  _gby_hash_aggregation_enabled    = true
  ***************************************
  PARAMETERS IN OPT_PARAM HINT
  ****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
select object_id,status from mytest where status=:a
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 objn=66933 hint_alias="MYTEST"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 587 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: MYTEST  Alias: MYTEST
#Rows: 50459  #Blks:  328  AvgRowLen:  39.00
Index Stats::
  Index: IDX_MYTEST_STATUS  Col#: 4
LVLS: 1  #LB: 120  #DK: 2  LB/K: 60.00  DB/K: 158.00  CLUF: 317.00
***************************************
SINGLE TABLE ACCESS PATH
  Column (#4): STATUS(VARCHAR2)
AvgLen: 7.00 NDV: 2 Nulls: 0 Density: 1.0085e-05
Histogram: Freq  #Bkts: 2  UncompBkts: 5404  EndPtVals: 2
  Table: MYTEST  Alias: MYTEST
Card: Original: 50459  Rounded: 25230  Computed: 25229.50  Non Adjusted: 25229.50
  Access Path: TableScan
Cost:  75.19  Resp: 75.19  Degree: 0
   Cost_io: 73.00  Cost_cpu: 15455172
   Resp_io: 73.00  Resp_cpu: 15455172
  Access Path: index (AllEqRange)
Index: IDX_MYTEST_STATUS
resc_io: 219.00  resc_cpu: 11904745
ix_sel: 0.5  ix_sel_with_filters: 0.5
Cost: 220.69  Resp: 220.69  Degree: 1
  Best:: AccessPath: TableScan
      Cost: 75.19  Degree: 1  Resp: 75.19  Card: 25229.50  Bytes: 0
***************************************

这里best 又成了 tablescan。。。。。。。

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-4-18 13:54:58

when index range scan

  Access Path: index (AllEqRange)
Index: IDX_MYTEST_STATUS
resc_io: 2.00  resc_cpu: 26983
ix_sel: 5.5514e-04  ix_sel_with_filters: 5.5514e-04
Cost: 2.00  Resp: 2.00  Degree: 1
  Best:: AccessPath: IndexRange  Index: IDX_MYTEST_STATUS
      Cost: 2.00  Degree: 1  Resp: 2.00  Card: 28.01  Bytes: 0

When FULL TABLE SCAN

  Access Path: index (AllEqRange)
Index: IDX_MYTEST_STATUS
resc_io: 219.00  resc_cpu: 11904745
ix_sel: 0.5  ix_sel_with_filters: 0.5
Cost: 220.69  Resp: 220.69  Degree: 1
  Best:: AccessPath: TableScan
      Cost: 75.19  Degree: 1  Resp: 75.19  Card: 25229.50  Bytes: 0

ix_sel:  5.5514e-04  VS 0.5

Card 28.01 vs  25229.50

把你的测试过程发出来，仅从10053 trace 可以猜测：

1.统计信息更新过
2. 如果是完全相同的SQL，那么是某些原因造成了再次的hard parse

回复只看该作者道具举报

几番风雨

5^#

发表于 2012-4-18 14:04:40

原帖由 maclean 于 2012-4-18 13:54 发表 when index range scan Access Path: index (AllEqRange) Index: IDX_MYTEST_STATUS resc_io: 2.00 resc_cpu: 26983 ix_sel: 5.5514e-04 ix_sel_with_filters: 5.5514e-04 Cost: 2.00 Resp: ...

测试的绑定变量，跟这个完全一样

http://t.askmaclean.com/thread-735-1-1.html

建表索引以后收集了统计信息，然后flush了shared_pool,这个10053只测试了绑定变量时的sql执行计划

scott@MYDB SQL> var a varchar2(10);
scott@MYDB SQL>
scott@MYDB SQL> exec :a:='INVALIAD';

PL/SQL procedure successfully completed.

scott@MYDB SQL>
scott@MYDB SQL> alter session set events '10053 trace name context forever,level 1';

Session altered.

scott@MYDB SQL> select object_id,status from mytest where status=:a;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1692938441

----------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |       | 25230 | 295K| 75 (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYTEST | 25230 | 295K| 75 (3)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("STATUS"=:A)

Statistics
----------------------------------------------------------
      326  recursive calls
      0  db block gets
      52  consistent gets
      12  physical reads
      0  redo size
      334  bytes sent via SQL*Net to client
      374  bytes received via SQL*Net from client
      1  SQL*Net roundtrips to/from client
      6  sorts (memory)
      0  sorts (disk)
      0  rows processed

scott@MYDB SQL> alter session set events '10053 trace name context off';

Session altered.

回复只看该作者道具举报

几番风雨

6^#

发表于 2012-4-18 14:06:06

scott@MYDB SQL> create table mytest as select rownum id,OBJECT_NAME,OBJECT_ID,STATUS from dba_objects;

Table created.

scott@MYDB SQL>
scott@MYDB SQL> create index idx_mytest_status on mytest(status);

Index created.

scott@MYDB SQL>
scott@MYDB SQL> alter table mytest modify status not null;

Table altered.

scott@MYDB SQL>
scott@MYDB SQL>
scott@MYDB SQL> desc mytest;
Name                                                 Null? Type
----------------------------------------------------- -------- ------------------------------------
ID                                                          NUMBER
OBJECT_NAME                                                 VARCHAR2(128)
OBJECT_ID                                                    NUMBER
STATUS                                              NOT NULL VARCHAR2(7)

scott@MYDB SQL>
scott@MYDB SQL>

scott@MYDB SQL> exec dbms_stats.gather_table_stats(user,'mytest',cascade=>true);

PL/SQL procedure successfully completed.

scott@MYDB SQL>

[ 本帖最后由几番风雨于 2012-4-18 14:14 编辑 ]

回复只看该作者道具举报

Maclean Liu(刘相兵

7^#

发表于 2012-4-18 14:33:19

第一次 hard parse 时是实际的语句在被执行，所以有 bind peek 的信息

*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
Bind#0
  oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=b7f547e8  bln=32  avl=08  flg=05
  value="INVALIAD"

这导致 CBO 利用了histogram 从而得到 ix_sel: 5.5514e-04  这个索引选择率

第二次解析 hard parse 是 autotrace 引起的，它没有代入 bind variable绑定值， optimizer无从 peek 窥视，所以它无法利用histogram ，所以它采用  1/NVD =  1/2=0.5  的选择率，相当于表上一半的行，optimizer 再次解析后给出FULL TABLE SCAN的 plan

SINGLE TABLE ACCESS PATH
  Column (#4): STATUS(VARCHAR2)
AvgLen: 7.00 NDV: 2 Nulls: 0 Density: 1.0085e-05
Histogram: Freq  #Bkts: 2  UncompBkts: 5404  EndPtVals: 2

但是实际上 sql 语句执行仍是使用的 index range scan - access by rowid的执行计划，  这里 autotrace 因为bind variable 给出的执行计划是不准确的。

见  autotrace在绑定变量情况下不准确的问题  http://www.oracledatabase12g.com ... 97%AE%E9%A2%98.html

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册