10053 诊断事件
来源：互联网 发布：游戏音效设计 软件 编辑：程序博客网 时间：2024/05/17 23:17
</pre><pre name="code" class="sql">SQL> select count(distinct(msgid)),count(*) from messagein ;COUNT(DISTINCT(MSGID)) COUNT(*)---------------------- ----------978619 978619说明msgid列时唯一的搜集直方图信息:SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'MBFE', tabname => 'MESSAGEIN', estimate_percent => 100, method_opt => 'for columns MSGID size skewonly', no_invalidate => FALSE, degree => 16, cascade => TRUE); END; 2 3 4 5 6 7 8 9 10 /SQL> set linesize 200SQL> select a.column_name, b.num_rows, a.num_distinct Cardinality, round(a.num_distinct / b.num_rows * 100, 2) selectivity, a.histogram, a.num_buckets from dba_tab_col_statistics a, dba_tables b where a.owner = b.owner and a.table_name = b.table_name and a.owner = 'MBFE' and a.table_name = upper('messagein'); 2 3 4 5 6 7 8 9 10 11 COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS------------------------------ ---------- ----------- ----------- --------------- -----------ID 978619 978619 100 NONE 1MSGID 978619 978619 100 FREQUENCY 2STATUS 978619 1 0 NONE 1PROCCNT 978619 2 0 NONE 1ACCEPTTIME 978619 653888 66.82 NONE 1MSGBODY 978619 0 0 NONE 0QUEUE 978619 4 0 NONE 17 rows selected.SQL> select msgid from messagein where rownum<10;MSGID----------------------------------------------------------------------------------------------------ID:414d5120514d454d42464520202020204c24b44120263403ID:414d5120514d453333313020202020204c224a3c200c1de7ID:414d5120514d454d42464520202020204c24b44120263404ID:414d5120514d453333313020202020204c224a3c200c49cbID:414d5120514d454d42464520202020204c24b44120263703ID:414d5120514d453333313020202020204c224a3c200fa81dID:414d5120514d453333313020202020204c224a3c200e15d0ID:414d5120514d453333313020202020204c224a3c200ff871ID:414d5120514d453333313020202020204c224a3c200cd0119 rows selected.SQL> select messagein0_.proccnt AS col_0_0_ FROM mbfe.messagein messagein0_ WHERE messagein0_.msgID ='ID:414d5120514d454d42464520202020204c24b44120263403' 2 3 ;Execution Plan----------------------------------------------------------Plan hash value: 2678161237-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 454K| 23M| 43978(1)| 00:08:48 ||* 1 | TABLE ACCESS FULL| MESSAGEIN | 454K| 23M| 43978(1)| 00:08:48 |-------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("MESSAGEIN0_"."MSGID"='ID:414d5120514d454d4246452020202020 4c24b44120263403')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 161610 consistent gets 161607 physical reads 0 redo size526 bytes sent via SQL*Net to client520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed强制走索引呢？SQL> select /*+ index(messagein0_,INDEXMSGIN_1)*/ messagein0_.proccnt AS col_0_0_ FROM mbfe.messagein messagein0_ WHERE messagein0_.msgID ='ID:414d5120514d454d42464520202020204c24b44120263403' 2 3 ;Execution Plan----------------------------------------------------------Plan hash value: 4204216909--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 454K|23M| 426K (1)| 01:25:20 || 1 | TABLE ACCESS BY INDEX ROWID| MESSAGEIN | 454K|23M| 426K (1)| 01:25:20 ||* 2 | INDEX RANGE SCAN | INDEXMSGIN_1 | 454K| | 4029 (1)| 00:00:49 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("MESSAGEIN0_"."MSGID"='ID:414d5120514d454d42464520202020204c24b4412026 3403')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 5 consistent gets 1 physical reads 0 redo size526 bytes sent via SQL*Net to client520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed可以看到Rows都是454K，看来是CBO出现问题了里面10053进行跟踪：[oracle@devrac1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 5 08:05:23 2015Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> oradebug setmypid;Statement processed.SQL> alter session set events='10053 trace name context forever,level 1';Session altered.SQL> select messagein0_.proccnt AS col_0_0_ FROM mbfe.messagein messagein0_ WHERE messagein0_.msgID ='ID:414d5120514d454d42464520202020204c24b44120263403'; 2 3 COL_0_0_---------- 1SQL> alter session set events '10053 trace name context off';Session altered.SQL> oradebug close_trace;Statement processed.SQL> oradebug tracefile_name/oracle/app/diag/rdbms/devrac/devrac1/trace/devrac1_ora_467.trc---------------------------------------------------------------------------------------------***************************************BASE STATISTICAL INFORMATION***********************Table Stats:: Table: MESSAGEIN Alias: MESSAGEIN0_ #Rows: 978619 #Blks: 162129 AvgRowLen: 1077.00 ChainCnt: 0.00Index Stats:: Index: INDEXMSGIN_1 Col#: 2 LVLS: 2 #LB: 8661 #DK: 978619 LB/K: 1.00 DB/K: 1.00 CLUF: 910071.00 Index: MESSAGEIN_PK Col#: 1 LVLS: 2 #LB: 2045 #DK: 978619 LB/K: 1.00 DB/K: 1.00 CLUF: 888635.00 Index: SYS_IL0000209895C00006$$ Col#: (NOT ANALYZED) LVLS: 1 #LB: 25 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 800.00Access path analysis for MESSAGEIN***************************************SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for MESSAGEIN[MESSAGEIN0_] Column (#2): NewDensity:0.232079, OldDensity:0.000001 BktCnt:978619, PopBktCnt:978619, PopValCnt:2, NDV:978619 Column (#2): MSGID( AvgLen: 52 NDV: 978619 Nulls: 0 Density: 0.232079 Histogram: Freq #Bkts: 2 UncompBkts: 978619 EndPtVals: 2 Table: MESSAGEIN Alias: MESSAGEIN0_ Card: Original: 978619.000000 Rounded: 454234 Computed: 454233.50 Non Adjusted: 454233.50 Access Path: TableScan Cost: 43978.03 Resp: 43978.03 Degree: 0 Cost_io: 43912.00 Cost_cpu: 1388057486 Resp_io: 43912.00 Resp_cpu: 1388057486 Access Path: index (AllEqRange) Index: INDEXMSGIN_1 resc_io: 426440.00 resc_cpu: 3223102814 ix_sel: 0.464158 ix_sel_with_filters: 0.464158 Cost: 426593.33 Resp: 426593.33 Degree: 1 Best:: AccessPath: TableScan Cost: 43978.03 Degree: 1 Resp: 43978.03 Card: 454233.50 Bytes: 0 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS***************************************GENERAL PLANS***************************************Considering cardinality-based initial join order.Permutations for Starting Table :0Join order[1]: MESSAGEIN[MESSAGEIN0_]#0***********************Best so far: Table#: 0 cost: 43978.0316 card: 454233.5000 bytes: 24982870***********************(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000*********************************Number of join permutations tried: 1*********************************Enumerating distribution method (advanced)Trying or-Expansion on query block SEL$1 (#0)Transfer Optimizer annotations for query block SEL$1 (#0)id=0 frofand predicate="MESSAGEIN0_"."MSGID"='ID:414d5120514d454d42464520202020204c24b44120263403'Final cost for query block SEL$1 (#0) - All Rows Plan: Best join order: 1 Cost: 43978.0316 Degree: 1 Card: 454234.0000 Bytes: 24982870 Resc: 43978.0316 Resc_io: 43912.0000 Resc_cpu: 1388057486 Resp: 43978.0316 Resp_io: 43912.0000 Resc_cpu: 1388057486kkoqbc-subheap (delete addr=0x7f346a6efaa0, in-use=14096, alloc=16408)kkoqbc-end: : call(in-use=12912, alloc=49184), compile(in-use=59680, alloc=63568), execution(in-use=2616, alloc=4032)kkoqbc: finish optimizing query block SEL$1 (#0)apadrv-end : call(in-use=12912, alloc=49184), compile(in-use=60592, alloc=63568), execution(in-use=2616, alloc=4032)Starting SQL statement dumpuser_id=0 user_name=SYS module=sqlplus@devrac1 (TNS V1-V3) action=sql_id=81u8ngrax001k plan_hash_value=-1616806059 problem_type=3----- Current SQL Statement for this session (sql_id=81u8ngrax001k) ----- select messagein0_.proccnt AS col_0_0_ FROM mbfe.messagein messagein0_ WHERE messagein0_.msgID ='ID:414d5120514d454d42464520202020204c24b44120263403'sql_text_length=154sql= select messagein0_.proccnt AS col_0_0_ FROM mbfe.messagein messagein0_ WHERE messagein0_.msgID ='ID:414d5120514d454d42464520202020204c24b44120263403'----- Explain Plan Dump ---------- Plan Table -----============Plan Table============--------------------------------------+-----------------------------------+| Id | Operation | Name | Rows | Bytes | Cost | Time |--------------------------------------+-----------------------------------+| 0 | SELECT STATEMENT | | | | 43K | || 1 | TABLE ACCESS FULL | MESSAGEIN| 444K | 24M | 43K | 00:09:48 |--------------------------------------+-----------------------------------+Predicate Information:----------------------1 - filter("MESSAGEIN0_"."MSGID"='ID:414d5120514d454d42464520202020204c24b44120263403')Content of other_xml column=========================== db_version : 11.2.0.3 parse_schema : SYS plan_hash : 2678161237 plan_hash_2 : 999486991 Outline Data: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "MESSAGEIN0_"@"SEL$1") END_OUTLINE_DATA */ Column (#2): MSGID( AvgLen: 52 NDV: 978619 Nulls: 0 Density: 0.232079 Histogram: Freq #Bkts: 2 UncompBkts: 978619 EndPtVals: 2其中NDV 表示 number of distinct的值,选择性是0.232079 这里选择性明显是100不清楚这个Density: 0.232079是怎么计算的？ 0 0
- 10053 诊断事件
- Oracle诊断事件列表
- ORACLE诊断事件
- ORACLE诊断事件
- Oracle诊断事件列表
- oracle的诊断事件
- ORACLE诊断事件
- Oracle诊断事件列表
- ORACLE诊断事件
- Oracle事件诊断列表
- Oracle诊断事件列表
- Oracle常用诊断事件
- Oracle诊断事件列表
- Oracle诊断事件列表
- Oracle诊断事件列表
- Oracle诊断事件列表
- oracle诊断事件
- SQL诊断------10046事件
- 「编程思维」究竟是一种什么思维？
- 冬令营之算法--结束
- POJ 2063 Investment （完全背包）
- POJ 1014 Dividing 多重背包
- 又是a++和++a
- 10053 诊断事件
- poj 1704 Georgia and Bob 阶梯博弈
- 4种CSS文字竖排方法
- SQL 创建标量函数 获取 类别 深度
- 系统休眠、待机和睡眠
- log4j的基本配置参数
- Python-OpenCV人脸检测(代码)
- 微服务(Microservices)
- PL/SQL(Procedural Language/SQL)编程简介 游标，过程，函数，包，触发器
