版本1024,9208
昨天一个puber的公司生产数据库有点问题,在执行一个过程的时候IO上到100%,后来找到这个过程中一段很差的sql,具体sql不给出了,把实际的表缩小了10倍,造了相似的数据来模拟。也趁此机会测试一些数字。
测试的版本是1024,具体信息和参数值:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL>
SQL> show parameter targ
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1600
fast_start_io_target integer 0
fast_start_mttr_target integer 0
pga_aggregate_target big integer 187M
sga_target big integer 564M
SQL>
SQL> show parameter cost
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
SQL> show parameters optimizer_dynamic_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
SQL>
SQL> show parameters file_multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
SQL>
create table t_test_a as select policy_code code , a.send_date dt
from t_policy@core a where rownum < 100000;
创建测试表a,字段code有少部分是空值,code和时间上面都有普通索引。
create table t_test_b as select policy_code code , a.send_date dt
from t_policy@core a where rownum < 800000;
创建测试表b,字段code唯一,索引是普通索引,且为null able
update t_test_a a set a.code = '' where rownum < 1000;
commit;
create index ind_test_a_code on t_test_a(code);
create index ind_test_a_dt on t_test_a(dt);
create index ind_test_b_code on t_test_b(code);
先看几个数值
SQL> select count(*) from t_test_a a where a.dt < date '2002-1-13';
COUNT(*)
----------
258
SQL>
SQL> select count(*) from t_test_a a where a.dt < date '2002-1-14';
COUNT(*)
----------
258
SQL> select count(*) from t_test_a a where a.dt < date '2002-1-15';
COUNT(*)
----------
854
SQL>
上面的数据,我们可以认为在2002-1-13这天没有发生业务,而2002-1-14这天比较多。
分析表和索引,看看分析后的信息:
SQL> select a.index_name,
2 a.distinct_keys,
3 a.leaf_blocks,
4 a.clustering_factor,
5 a.status,
6 a.num_rows
7 from dba_indexes a
8 where a.table_name = 'T_TEST_A';
INDEX_NAME DISTINCT_KEYS LEAF_BLOCKS CLUSTERING_FACTOR STATUS NUM_ROWS
------------------------------ ------------- ----------- ----------------- -------- ----------
IND_TEST_A_CODE 40879 155 33425 VALID 40879
IND_TEST_A_DT 18515 260 16451 VALID 97917
SQL> select a.index_name,
2 a.distinct_keys,
3 a.leaf_blocks,
4 a.clustering_factor,
5 a.status,
6 a.num_rows
7 from dba_indexes a
8 where a.table_name = 'T_TEST_B';
INDEX_NAME DISTINCT_KEYS LEAF_BLOCKS CLUSTERING_FACTOR STATUS NUM_ROWS
------------------------------ ------------- ----------- ----------------- -------- ----------
IND_TEST_B_CODE 322435 1217 283993 VALID 322435
可以看看各个索引的factor值,基本上这些值也是可以从构建数据的过程中大致预见的。
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from t_test_a a,t_test_b b
2 where a.code = b.code
3 and a.dt < date '2002-1-14'
4 ;
CODE DT CODE DT
-------------------- --------- -------------------- ---------
000001499157008 08-JAN-02 000001499157008 08-JAN-02
000001033450008 08-JAN-02 000001033450008 08-JAN-02
000001120964008 09-JAN-02 000001120964008 09-JAN-02
000001438262008 10-JAN-02 000001438262008 10-JAN-02
000001132065008 11-JAN-02 000001132065008 11-JAN-02
000001495927008 11-JAN-02 000001495927008 11-JAN-02
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2435190019
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3105 | 127K| 533 (50)| 00:00:01 |
|* 1 | HASH JOIN | | 3105 | 127K| 533 (50)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T_TEST_A | 258 | 5418 | 47 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_TEST_A_DT | 258 | | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T_TEST_B | 909K| 18M| 394 (44)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."CODE"="B"."CODE")
3 - access("A"."DT"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2253 consistent gets
2226 physical reads
0 redo size
906 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from t_test_a a,t_test_b b
2 where a.code = b.code
3 and a.dt < date '2002-1-15'
4 ;
CODE DT CODE DT
-------------------- --------- -------------------- ---------
000001477714008 14-JAN-02 000001477714008 14-JAN-02
000001514461008 14-JAN-02 000001514461008 14-JAN-02
000001513712008 14-JAN-02 000001513712008 14-JAN-02
000001470378008 14-JAN-02 000001470378008 14-JAN-02
000001499157008 08-JAN-02 000001499157008 08-JAN-02
000001033450008 08-JAN-02 000001033450008 08-JAN-02
000001120964008 09-JAN-02 000001120964008 09-JAN-02
000001438262008 10-JAN-02 000001438262008 10-JAN-02
000001132065008 11-JAN-02 000001132065008 11-JAN-02
000001531818008 14-JAN-02 000001531818008 14-JAN-02
000001469522008 14-JAN-02 000001469522008 14-JAN-02
CODE DT CODE DT
-------------------- --------- -------------------- ---------
000001492911008 14-JAN-02 000001492911008 14-JAN-02
000001495927008 11-JAN-02 000001495927008 11-JAN-02
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1839959879
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10276 | 421K| 540 (54)| 00:00:01 |
|* 1 | HASH JOIN | | 10276 | 421K| 540 (54)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T_TEST_A | 854 | 17934 | 54 (45)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_TEST_B | 909K| 18M| 394 (44)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."CODE"="B"."CODE")
2 - filter("A"."DT"
hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
2651 consistent gets
2502 physical reads
0 redo size
1277 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
13 rows processed
SQL>
下面来具体看看执行计划中的每一项,看看为什么cbo优化器产生的执行计划变化了:
SQL> explain plan for
2 select * from t_test_a a,t_test_b b
3 where a.code = b.code
4 and a.dt < date '2002-1-14'
5 ;
Explained.
SQL> select substr(a.id,1,1) id,
2 substr(a.operation,1,17) operation,
3 substr(a.options,1,15) options,
4 substr(a.object_name,1,15) object_name,
5 substr(a.parent_id,1,1) p_id,
6 a.cost,
7 a.cardinality card,
8 a.io_cost,
9 a.cpu_cost
10 from plan_table a;
I OPERATION OPTIONS OBJECT_NAME P COST CARD IO_COST CPU_COST
- ----------------- --------------- --------------- - ---------- ---------- ---------- ----------
0 SELECT STATEMENT 528 3425 264 262656473
1 HASH JOIN 0 528 3425 264 262656473
2 TABLE ACCESS BY INDEX ROWID T_TEST_A 1 42 258 42 385680
3 INDEX RANGE SCAN IND_TEST_A_DT 2 2 258 2 61043
4 TABLE ACCESS FULL T_TEST_B 1 394 909858 222 170748950
SQL>
注意到上面的这些信息:
使用的是hash join,小表使用时间字段上的索引,range scan,全表扫描大表,cost是394,io_cost是222。Hash join操作之后的card为3425,最后的cost是528,表a的card是258。
下面使用/*+ cardinality(a 17) */ hint来设定用以计算的card值,可以发现当a表的card足够小的时候cbo有了一些变化,不再使用hash join而是使用nested loop了,而且很多值发生了很大的变化:
Card值减小了很多,io确是增加了近一倍的cost,cpu cost大大降低了(少了2位),两个表都使用index range scan访问。
通过index range scan访问a表可以有258行返回值,但实际结果可以看到在与大表b join之后只有6行返回了。
SQL> explain plan for
2 select /*+ cardinality(a 17) */* from t_test_a a,t_test_b b
3 where a.code = b.code
4 and a.dt < date '2002-1-14'
5 ;
Explained.
SQL> select substr(a.id,1,1) id,
2 substr(a.operation,1,17) operation,
3 substr(a.options,1,15) options,
4 substr(a.object_name,1,15) object_name,
5 substr(a.parent_id,1,1) p_id,
6 a.cost,
7 a.cardinality card,
8 a.io_cost,
9 a.cpu_cost
10 from plan_table a;
I OPERATION OPTIONS OBJECT_NAME P COST CARD IO_COST CPU_COST
- ----------------- --------------- --------------- - ---------- ---------- ---------- ----------
0 SELECT STATEMENT 522 226 518 3972346
1 TABLE ACCESS BY INDEX ROWID T_TEST_B 0 28 13 28 210980
2 NESTED LOOPS 1 522 226 518 3972346
3 TABLE ACCESS BY INDEX ROWID T_TEST_A 2 42 17 42 385680
4 INDEX RANGE SCAN IND_TEST_A_DT 3 2 258 2 61043
5 INDEX RANGE SCAN IND_TEST_B_CODE 2 2 81 2 20893
6 rows selected.
SQL> rollback;
Rollback complete.
再来看看使用/*+ cardinality(a 18) */后的结果,只是增加了1个card值,也已经产生了很大影响,查询重新回到hash join的方式,b表全表扫描,cost变化不大,cpu也回到了最开始不加hint的时候的数量级,io也回到了原来的值。不过最终评估的card已经降低。
对比一下使用/*+ cardinality(a 17) */和/*+ cardinality(a 18) */这两个查询,就可以发现,系统nested loop的io评估较大,而hash join的 full相对小。
再看看系统统计信息:
SQL> select a.sname,a.pname,trunc(a.pval1,2)
2 from sys.aux_stats$ a where a.sname = 'SYSSTATS_MAIN';
SNAME PNAME TRUNC(A.PVAL1,2)
------------------------------ ------------------------------ ----------------
SYSSTATS_MAIN CPUSPEEDNW 576.86
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 1.78
SYSSTATS_MAIN MREADTIM 2.8
SYSSTATS_MAIN CPUSPEED 556
SYSSTATS_MAIN MBRC 16
SYSSTATS_MAIN MAXTHR 16664576
SYSSTATS_MAIN SLAVETHR
9 rows selected.
SQL>
可以看到MBRC是16,多块读的时间比单块读并高不了多少。
SQL> explain plan for
2 select /*+ cardinality(a 18) */* from t_test_a a,t_test_b b
3 where a.code = b.code
4 and a.dt < date '2002-1-14'
5 ;
Explained.
SQL> select substr(a.id,1,1) id,
2 substr(a.operation,1,17) operation,
3 substr(a.options,1,15) options,
4 substr(a.object_name,1,15) object_name,
5 substr(a.parent_id,1,1) p_id,
6 a.cost,
7 a.cardinality card,
8 a.io_cost,
9 a.cpu_cost
10 from plan_table a;
I OPERATION OPTIONS OBJECT_NAME P COST CARD IO_COST CPU_COST
- ----------------- --------------- --------------- - ---------- ---------- ---------- ----------
0 SELECT STATEMENT 528 239 264 262620473
1 HASH JOIN 0 528 239 264 262620473
2 TABLE ACCESS BY INDEX ROWID T_TEST_A 1 42 18 42 385680
3 INDEX RANGE SCAN IND_TEST_A_DT 2 2 258 2 61043
4 TABLE ACCESS FULL T_TEST_B 1 394 909858 222 170748950
SQL>
SQL> rollback;
Rollback complete.
表a(T_test_a)数据大概10万行,14号之前行数是258行,15号之前的行数也只有854行。看看15号之前的查询:
SQL> explain plan for
2 select /*+ cardinality(a 17) */* from t_test_a a,t_test_b b
3 where a.code = b.code
4 and a.dt < date '2002-1-15'
5 ;
Explained.
SQL> select substr(a.id,1,1) id,
2 substr(a.operation,1,17) operation,
3 substr(a.options,1,15) options,
4 substr(a.object_name,1,15) object_name,
5 substr(a.parent_id,1,1) p_id,
6 a.cost,
7 a.cardinality card,
8 a.io_cost,
9 a.cpu_cost
10 from plan_table a;
I OPERATION OPTIONS OBJECT_NAME P COST CARD IO_COST CPU_COST
- ----------------- --------------- --------------- - ---------- ---------- ---------- ----------
0 SELECT STATEMENT 536 226 506 29459528
1 TABLE ACCESS BY INDEX ROWID T_TEST_B 0 28 13 28 210980
2 NESTED LOOPS 1 536 226 506 29459528
3 TABLE ACCESS FULL T_TEST_A 2 56 17 30 25872862
4 INDEX RANGE SCAN IND_TEST_B_CODE 2 2 81 2 20893
SQL>
SQL> rollback;
Rollback complete.
SQL> explain plan for
2 select /*+ cardinality(a 18) */* from t_test_a a,t_test_b b
3 where a.code = b.code
4 and a.dt < date '2002-1-15'
5 ;
Explained.
SQL> select substr(a.id,1,1) id,
2 substr(a.operation,1,17) operation,
3 substr(a.options,1,15) options,
4 substr(a.object_name,1,15) object_name,
5 substr(a.parent_id,1,1) p_id,
6 a.cost,
7 a.cardinality card,
8 a.io_cost,
9 a.cpu_cost
10 from plan_table a;
I OPERATION OPTIONS OBJECT_NAME P COST CARD IO_COST CPU_COST
- ----------------- --------------- --------------- - ---------- ---------- ---------- ----------
0 SELECT STATEMENT 542 239 252 288107654
1 HASH JOIN 0 542 239 252 288107654
2 TABLE ACCESS FULL T_TEST_A 1 56 18 30 25872862
3 TABLE ACCESS FULL T_TEST_B 1 394 909858 222 170748950
SQL>
SQL> rollback
2 ;
Rollback complete.
分析一下对象统计信息,再看看
SQL> explain plan for
2 select * from t_test_a a,t_test_b b
3 where a.code = b.code
4 and a.dt < date '2002-1-15';
Explained.
SQL> select substr(a.id,1,1) id,
2 substr(a.operation,1,17) operation,
3 substr(a.options,1,15) options,
4 substr(a.object_name,1,15) object_name,
5 substr(a.parent_id,1,1) p_id,
6 a.cost,
7 a.cardinality card,
8 a.io_cost,
9 a.cpu_cost
10 from plan_table a;
I OPERATION OPTIONS OBJECT_NAME P COST CARD IO_COST CPU_COST
- ----------------- --------------- --------------- - ---------- ---------- ---------- ----------
0 SELECT STATEMENT 468 381 252 214442833
1 HASH JOIN 0 468 381 252 214442833
2 TABLE ACCESS FULL T_TEST_A 1 53 381 30 23123220
3 TABLE ACCESS FULL T_TEST_B 1 381 322435 222 158521620
SQL>
下面是14号之前的10046tracefile:
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select * from t_test_a a,t_test_b b
where a.code = b.code
and a.dt < date '2002-1-14'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13 0.41 0.40 2493 2513 0 167
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.42 0.40 2493 2513 0 167
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
167 HASH JOIN (cr=2513 pr=2493 pw=0 time=46167 us)
167 TABLE ACCESS FULL T_TEST_A (cr=282 pr=279 pw=0 time=1967 us)
322435 TABLE ACCESS FULL T_TEST_B (cr=2231 pr=2214 pw=0 time=1935491 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 13 0.00 0.00
db file sequential read 2 0.00 0.00
db file scattered read 176 0.00 0.10
SQL*Net message from client 13 0.00 0.02
********************************************************************************
再看看使用nl的hint之后的运行信息:
*******************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select /*+ use_nl(a,b)*/* from t_test_a a,t_test_b b
where a.code = b.code
and a.dt < date '2002-1-14'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13 0.08 0.08 296 671 0 167
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.08 0.08 296 671 0 167
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
167 TABLE ACCESS BY INDEX ROWID T_TEST_B (cr=671 pr=296 pw=0 time=9155 us)
335 NESTED LOOPS (cr=653 pr=289 pw=0 time=612607 us)
167 TABLE ACCESS FULL T_TEST_A (cr=294 pr=279 pw=0 time=5071 us)
167 INDEX RANGE SCAN IND_TEST_B_CODE (cr=359 pr=10 pw=0 time=8836 us)(object id 69411)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 13 0.00 0.00
db file sequential read 18 0.00 0.00
db file scattered read 27 0.00 0.02
SQL*Net message from client 13 4.82 4.84
********************************************************************************
可以看到无论是cpu,还是耗时,还是磁盘读,逻辑读使用nl都要比使用hash join小的多。
接着对表a做了直方图之后也没有什么起色。
Execution Plan
----------------------------------------------------------
Plan hash value: 1839959879
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 369 | 10332 | 468 (47)| 00:00:01 |
|* 1 | HASH JOIN | | 369 | 10332 | 468 (47)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T_TEST_A | 369 | 5166 | 53 (44)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T_TEST_B | 322K| 4408K| 381 (42)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."CODE"="B"."CODE")
2 - filter("A"."CODE" IS NOT NULL AND "A"."DT"
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
3 - filter("B"."CODE" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2513 consistent gets
2493 physical reads
0 redo size
10052 bytes sent via SQL*Net to client
521 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
167 rows processed
SQL>
这里看到几处谓词中加上了is not null,T_test_b的card值减小了很多,因为有很多空值,这个是前面没有注意的,删之。
再看效果:
********************************************************************************
select * from t_test_a a,t_test_b b
where a.code = b.code
and a.dt < date '2002-1-14'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13 0.56 0.54 2493 2513 0 167
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.56 0.54 2493 2513 0 167
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
167 HASH JOIN (cr=2513 pr=2493 pw=0 time=106404 us)
167 TABLE ACCESS FULL T_TEST_A (cr=282 pr=279 pw=0 time=20395 us)
322435 TABLE ACCESS FULL T_TEST_B (cr=2231 pr=2214 pw=0 time=2580871 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 13 0.00 0.00
db file sequential read 2 0.00 0.00
db file scattered read 176 0.00 0.21
SQL*Net message from client 13 4.44 4.45
********************************************************************************
********************************************************************************
select /*+ use_nl(a,b) */* from t_test_a a,t_test_b b
where a.code = b.code
and a.dt < date '2002-1-14'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13 0.08 0.08 296 671 0 167
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.11 0.11 296 671 0 167
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
167 TABLE ACCESS BY INDEX ROWID T_TEST_B (cr=671 pr=296 pw=0 time=11934 us)
335 NESTED LOOPS (cr=653 pr=289 pw=0 time=726477 us)
167 TABLE ACCESS FULL T_TEST_A (cr=294 pr=279 pw=0 time=5517 us)
167 INDEX RANGE SCAN IND_TEST_B_CODE (cr=359 pr=10 pw=0 time=9576 us)(object id 69415)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 13 0.00 0.00
db file sequential read 18 0.00 0.00
db file scattered read 27 0.00 0.02
SQL*Net message from client 13 3.45 3.47
********************************************************************************
晓得不是最优的情况,但是还是对cbo的具体计算方法不解,待后续分析求解。。。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-673191/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-673191/