===========================
对于每个SQL语句，可以使用不同的方法来检索所需的数据。 优化是基于对许多不同标准的评估，选择最有效的方式来检索此数据的过程。 CBO的优化选择基于预先收集的表和索引统计信息，而RBO则基于一组规则做出决策，而不依赖任何统计信息。 CBO对统计数据的依赖使其比RBO灵活得多，因为只要保持最新的统计数据，它就可以准确反映实际数据量。 Oracle10g不支持RBO。
要在会话级别收集10046跟踪：更改会话集timed_statistics = true;
更改会话集statistics_level = all;
更改会话集max_dump_file_size =无限;
更改会话集事件“永远为10046跟踪名称上下文，级别12”；
DBMS_SUPPORT软件包的特征=============================
在本文中，我描述了Oracle内未记录的功能，不能保证结果
将与所有版本完全相同。
安装DBMS软件包
-----------------------
[oracle @ localhost管理员] $ ls -ltr * supp *
-rw-r ----- 1 oracle oracle 1546 2001年2月27日dbmssupp.sql
-rw-r ----- 1 oracle oracle 1198 2005年9月19日prvtsupp.plb
SQL> @ $ ORACLE_HOME / rdbms / admin / dbmssupp
---------------------------------------------
-运行您的选择-
SQL>执行DBMS_SUPPORT。START_TRACE；
PL / SQL过程成功完成。
SQL> <==您的查询
。
。
。
SQL>执行DBMS_SUPPORT.STOP_TRACE;
PL / SQL过程成功完成。
系统名称：Linux
节点名称：localhost.localdomain
发行：2.6.18-53.el5xen
版本：＃1 SMP，2007年11月10日星期六19:46:12
机器：x86_64
实例名称：orcl
此实例安装的重做线程：1
Oracle进程号：16
Unix进程pid：4947，图像：oracle@localhost.localdomain（TNS V1-V3）
*** 2008-01-21 12：00：25.204
***服务名称：（SYS $ USERS）2008-01-21 12：00：25.204
***会话ID：（158.3）2008-01-21 12：00：25.204
=====================
在光标＃6中解析len = 198 dep = 1 uid = 0 oct == 3 lid = 0 tim = 1172745727738352 hv = 4125641360 ad ='6c2b8cc0'
从obj $中选择obj＃，type＃，ctime，mtime，stime，status，dataobj＃，flags，oid $，spare1，spare2，其中owner＃=：1和name =：2和namespace =：3，remoteowner为null
并且linkname为null和subname为null
STMT结束
解析＃6：c = 0，e = 620，p = 0，cr = 0，cu = 0，mis = 1，r = 0，dep = 1，og = 4，ti m = 1172745727738344
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727740552 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
PARSE＃3：c = 0，e = 587，p = 0，cr = 0，cu = 0，mis = 1，r = 0，dep = 2，og = 3，ti m = 1172745727740544
EXEC＃3：c = 0，e = 2148，p = 0，cr = 0，cu = 0，mis = 1，r = 0，dep = 2，og = 3，t im = 1172745727742876
等待＃3：nam ='db文件顺序读取'ela = 46文件＃= 1块＃= 1888块= 1 obj＃=-1 tim = 1172745727743123
FETCH＃3：c = 0，e = 334，p = 1，cr = 3，cu = 0，mis = 0，r = 12，dep = 2，og = 3，t im = 1172745727743301
STAT＃3 id = 1 cnt = 12 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 3 pr = 1 pw = 0时间= 359 us）'
STAT＃3 id = 2 cnt = 12 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 3 pr = 1 pw = 0时间= 310 us）'
STAT＃3 id = 3 cnt = 1 pid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 2 pr = 0 pw = 0 time = 51 us）'
=====================
在光标＃3中解析len = 210 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727744437 hv = 864012087 ad ='6c1e0010'
选择/ * +规则* / bucket_cnt，row_cnt，cache_cnt，null_cnt，timestamp＃，sample_size，最小值，最大值，distcnt，lowval，hival，密度，col＃，spare1，reserve2
，来自hist_head $的avgcln，其中obj＃=：1和intcol＃=：2
STMT结束
PARSE＃3：c = 0，e = 533，p = 0，cr = 0，cu = 0，mis = 1，r = 0，dep = 2，og = 3，ti m = 1172745727744429
执行＃3：c = 0，e = 935，p = 0，cr = 0，cu = 0，mis = 1，r = 0，dep = 2，og = 3，ti m = 1172745727745569
FETCH＃3：c = 0，e = 94，p = 0，cr = 3，cu = 0，mis = 0，r = 1，dep = 2，og = 3，tim = 1172745727745746
STAT＃3 id = 1 cnt = 1 pid = 0 pos = 1 obj = 255 op ='通过索引行HIST_HEAD $访问表（cr = 3 pr = 0 pw = 0时间= 96 us）'
STAT＃3 id = 2 cnt = 1 pid = 1 pos = 1 obj = 257 op ='INDEX RANGE SCAN I_HH_OBJ＃_INTCOL＃（cr = 2 pr = 0 pw = 0 time = 45 us）'
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727745997 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
EXEC＃3：c = 0，e = 49，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727745991
FETCH＃3：c = 8000，e = 242，p = 0，cr = 3，cu = 0，mis = 0，r = 20，dep = 2，og = 3，tim = 1172745727746335
STAT＃3 id = 1 cnt = 32 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 6 pr = 1 pw = 0时间= 570 us）'
STAT＃3 id = 2 cnt = 32 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 6 pr = 1 pw = 0时间= 455 us）'
STAT＃3 id = 3 cnt = 2 pid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 4 pr = 0 pw = 0 time = 76 us）'
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727746666 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
执行＃3：c = 0，e = 68，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727746659
FETCH＃3：c = 0，e = 124，p = 0，cr = 3，cu = 0，mis = 0，r = 9，dep = 2，og = 3，ti m = 1172745727746916
STAT＃3 id = 1 cnt = 41 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 9 pr = 1 pw = 0时间= 718 us）'
STAT＃3 id = 2 cnt = 41 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 9 pr = 1 pw = 0时间= 548 us）'
STAT＃3 id = 3 cnt = 3 pid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 6 pr = 0 pw = 0 time = 105 us）'
=====================
在光标＃3中解析len = 210 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727747246 hv = 864012087 ad ='6c1e0010'
选择/ * + Rule * /
，来自hist_head $的avgcln，其中obj＃=：1和intcol＃=：2
STMT结束
执行＃3：c = 0，e = 49，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727747240
FETCH＃3：c = 0，e = 50，p = 0，cr = 3，cu = 0，mis = 0，r = 1，dep = 2，og = 3，tim = 1172745727747400
STAT＃3 id = 1 cnt = 2 pid = 0 pos = 1 obj = 255 op ='通过索引行HIST_HEAD $访问表（cr = 6 pr = 0 pw = 0时间= 148 us）'
STAT＃3 id = 2 cnt = 2 pid = 1 pos = 1 obj = 257 op ='INDEX RANGE SCAN I_HH_OBJ＃_INTCOL＃（cr = 4 pr = 0 pw = 0 time = 71 us）'
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727747609 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
EXEC＃3：c = 0，e = 42，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727747603
等待＃3：nam ='db文件顺序读取'ela = 47文件＃= 1块＃= 56504块= 1 obj＃=-1 tim = 1172745727747808
等待＃3：nam ='db文件顺序读取'ela = 24文件＃= 1块＃= 2006块= 1 obj＃=-1 tim = 1172745727747900
FETCH＃3：c = 0，e = 348，p = 2，cr = 4，cu = 0，mis = 0，r = 20，dep = 2，og = 3，t im = 1172745727748053
STAT＃3 id = 1 cnt = 61 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 13 pr = 3 pw = 0时间= 1132 us）'
STAT＃3 id = 2 cnt = 61 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 13 pr = 3 pw = 0时间= 878 us）'
STAT＃3 id = 3 cnt = 4 pid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 8 pr = 0 pw = 0 time = 125 us）'
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727748297 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
执行＃3：c = 0，e = 44，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727748291
FETCH＃3：c = 0，e = 156，p = 0，cr = 4，cu = 0，mis = 0，r = 20，dep = 2，og = 3，t im = 1172745727748549
STAT＃3 id = 1 cnt = 81 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 17 pr = 3 pw = 0时间= 1336 us）'
STAT＃3 id = 2 cnt = 81 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 17 pr = 3 pw = 0时间= 1009 us）'
STAT＃3 id = 3 cnt = 5 pid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 10 pr = 0 pw = 0 time = 147 us）'
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727748769 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
执行＃3：c = 0，e = 39，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727748763
FETCH＃3：c = 0，e = 149，p = 0，cr = 4，cu = 0，mis = 0，r = 20，dep = 2，og = 3，t im = 1172745727749013
STAT＃3 id = 1 cnt = 101 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 21 pr = 3 pw = 0时间= 1550 us）'
STAT＃3 id = 2 cnt = 101 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 21 pr = 3 pw = 0时间= 1158 us）'
STAT＃3 id = 3 cnt = 6 pid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 12 pr = 0 pw = 0 time = 164 us）'
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727749231 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
执行＃3：c = 0，e = 39，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727749225
FETCH＃3：c = 0，e = 149，p = 0，cr = 4，cu = 0，mis = 0，r = 20，dep = 2，og = 3，t im = 1172745727749475
STAT＃3 id = 1 cnt = 121 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 25 pr = 3 pw = 0时间= 1763 us）'
STAT＃3 id = 2 cnt = 121 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 25 pr = 3 pw = 0时间= 1311 us）'
STAT＃3 id = 3 cnt = 7 pid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 14 pr = 0 pw = 0 time = 183 us）'
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727749693 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
执行＃3：c = 0，e = 39，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727749687
FETCH＃3：c = 0，e = 150，p = 0，cr = 4，cu = 0，mis = 0，r = 20，dep = 2，og = 3，t im = 1172745727749961
STAT＃3 id = 1 cnt = 141 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 29 pr = 3 pw = 0时间= 1978 us）'
STAT＃3 id = 2 cnt = 141 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 29 pr = 3 pw = 0时间= 1452 us）'
STAT＃3 id = 3 cnt = 8 pid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 16 pr = 0 pw = 0 time = 202 us）'
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727750204 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
执行＃3：c = 0，e = 50，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727750197
FETCH＃3：c = 0，e = 159，p = 0，cr = 4，cu = 0，mis = 0，r = 20，dep = 2，og = 3，t im = 1172745727750471
STAT＃3 id = 1 cnt = 161 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 33 pr = 3 pw = 0时间= 2206 us）'
STAT＃3 id = 2 cnt = 161 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 33 pr = 3 pw = 0时间= 1604 us）'
STAT＃3 id = 3 cnt = 9 pid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 18 pr = 0 pw = 0 time = 224 us）'
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727750701 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
执行＃3：c = 0，e = 42，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727750695
FETCH＃3：c = 0，e = 151，p = 0，cr = 4，cu = 0，mis = 0，r = 20，dep = 2，og = 3，t im = 1172745727750945
STAT＃3 id = 1 cnt = 181 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 37 pr = 3 pw = 0时间= 2421 us）'
STAT＃3 id = 2 cnt = 181 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 37 pr = 3 pw = 0时间= 1755 us）'
STAT＃3 id = 3 cnt = 10 pid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 20 pr = 0 pw = 0 time = 243 us）'
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727751162 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
执行＃3：c = 0，e = 39，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727751156
FETCH＃3：c = 0，e = 150，p = 0，cr = 4，cu = 0，mis = 0，r = 20，dep = 2，og = 3，t im = 1172745727751404
STAT＃3 id = 1 cnt = 201 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 41 pr = 3 pw = 0时间= 2635 us）'
STAT＃3 id = 2 cnt = 201 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 41 pr = 3 pw = 0时间= 1928 us）'
STAT＃3 id = 3 cnt = 11 pid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 22 pr = 0 pw = 0 time = 261 us）'
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727751620 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
执行＃3：c = 0，e = 38，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727751614
FETCH＃3：c = 0，e = 149，p = 0，cr = 4，cu = 0，mis = 0，r = 20，dep = 2，og = 3，t im = 1172745727751863
STAT＃3 id = 1 cnt = 221 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 45 pr = 3 pw = 0时间= 2848 us）'
STAT＃3 id = 2 cnt = 221 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 45 pr = 3 pw = 0时间= 2106 us）'
STAT＃3 id = 3 cnt = 12 pid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 24 pr = 0 pw = 0 time = 278 us）'
=====================
在光标＃3中解析len = 121 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1172745727752081 hv = 3150898423 ad ='6c1ddee0'
从histgrm $中选择/ * + rule * /存储桶，端点，col＃，epvalue，其中obj＃=：1和intcol＃=：2以及row＃=：3按存储桶顺序
STMT结束
执行＃3：c = 0，e = 39，p = 0，cr = 0，cu = 0，mis = 0，r = 0，dep = 2，og = 3，tim = 1172745727752075
FETCH＃3：c = 0，e = 150，p = 0，cr = 4，cu = 0，mis = 0，r = 20，dep = 2，og = 3，t im = 1172745727752325
STAT＃3 id = 1 cnt = 241 pid = 0 pos = 1 obj = 0 op ='排序顺序为（cr = 49 pr = 3 pw = 0时间= 3064 us）'
STAT＃3 id = 2 cnt = 241 pid = 1 pos = 1 obj = 253 op ='表访问集群历史记录（cr = 49 pr = 3 pw = 0时间= 2270 us）'
STAT＃3 id = 3 cnt = 13 ppid = 2 pos = 1 obj = 252 op ='INDEX UNIQUE SCAN I_OBJ＃_INTCOL＃（cr = 26 pr = 0 pw = 0 time = 296 us）'
=====================
-更多-（4％）
== ------------------------------------------------ ------------------------------------------------
如何解释内部跟踪输出STAT行报告说明编号<CURSOR>的计划统计信息。
PARSE解析语句
EXEC执行预分析的语句。
FETCH从游标获取行。
--
--
--
--
--
--
这是解释和调查队列分析，等待事件的简短说明。
维诺德·萨达南丹（Vinod Sadanandan）
甲骨文数据库管理员
From: https://bytes.com/topic/oracle/insights/761559-roadmap-query-tuning
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
