• 博客访问: 644418
  • 博文数量: 318
  • 用 户 组: 普通用户
  • 注册时间: 2008-12-27 08:46
个人简介

暂无介绍

文章分类

全部博文(318)

文章存档

2017年(1)

2014年(1)

2013年(7)

2012年(41)

2011年(36)

2010年(132)

2009年(98)

2008年(2)

我的朋友

分类: Linux操作系统

2010-09-10 19:34:49

版本10249208

昨天一个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,全表扫描大表,cost394io_cost222Hash join操作之后的card3425,最后的cost528,表acard258

 

下面使用/*+ cardinality(a 17) */ hint来设定用以计算的card值,可以发现当a表的card足够小的时候cbo有了一些变化,不再使用hash join而是使用nested loop了,而且很多值发生了很大的变化:

Card值减小了很多,io确是增加了近一倍的costcpu 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) */后的结果,只是增加了1card值,也已经产生了很大影响,查询重新回到hash join的方式,b表全表扫描,cost变化不大,cpu也回到了最开始不加hint的时候的数量级,io也回到了原来的值。不过最终评估的card已经降低。

对比一下使用/*+ cardinality(a 17) *//*+ cardinality(a 18) */这两个查询,就可以发现,系统nested loopio评估较大,而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>

可以看到MBRC16,多块读的时间比单块读并高不了多少。

 

 

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

********************************************************************************

 

再看看使用nlhint之后的运行信息:

*******************************************************************************

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 nullT_test_bcard值减小了很多,因为有很多空值,这个是前面没有注意的,删之。

再看效果:

********************************************************************************

 

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的具体计算方法不解,待后续分析求解。。。

 

 

 

 

阅读(2396) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~

杨奇龙2010-09-13 09:24:58

拜读了:victory:

评论热议
请登录后评论。

登录 注册