欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

dba_indexes視圖的性能分析

 更新時(shí)間：2008年08月16日 21:10:06   作者：  
前端時(shí)間，在監(jiān)控系統(tǒng)中加入了index的狀態(tài)是否為unusable，以及其并行度是否有設(shè)置的監(jiān)控：
select case when status='UNUSABLE' then
        'alter index '||owner||'.'||index_name||' rebuild online compute statistics;'
      when to_number(degree)>1 then
        'alter index /* '||degree ||' */'||owner||'.'||index_name||' noparallel;'
    end case
from (select * from dba_indexes where degree<>‘DEFAULT') a
where status='UNUSABLE'
or to_number(degree)>1
and owner not in ('SYS','SYSTEM','MANAGER','WMSYS');

語句運(yùn)行的速度很快，但是從statspack中發(fā)現(xiàn)這條語句的邏輯讀單次高達(dá)26846。使用set autotrace比較了下9i和10g的執(zhí)行計(jì)劃和統(tǒng)計(jì)信息，發(fā)現(xiàn)9i查詢這個(gè)視圖的代價(jià)非常的高，而10g則有了一定的改善。在Oracle9i中，optimizer_mode默認(rèn)是CHOOSE，所以查詢數(shù)據(jù)字典使用了RBO，而Oracle10g則默認(rèn)為ALL_ROWS，所以采用了CBO。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE  9.2.0.6.0    Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> set autot trace
SQL> select * from dba_indexes;

1242 rows selected.

Execution Plan
----------------------------------------------------------
  0   SELECT STATEMENT Optimizer=CHOOSE
  1  0  NESTED LOOPS (OUTER)
  2  1   NESTED LOOPS (OUTER)
  3  2    NESTED LOOPS
  4  3     NESTED LOOPS
  5  4      NESTED LOOPS (OUTER)
  6  5       NESTED LOOPS
  7  6        NESTED LOOPS (OUTER)
  8  7         NESTED LOOPS
  9  8          TABLE ACCESS (FULL) OF 'OBJ$'
 10  8          TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
 11  10           INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
 12  7         TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
 13  12          INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
 14  6        TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
 15  14         INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
 16  5       TABLE ACCESS (CLUSTER) OF 'USER$'
 17  16        INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
 18  4      TABLE ACCESS (CLUSTER) OF 'USER$'
 19  18       INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
 20  3     TABLE ACCESS (CLUSTER) OF 'USER$'
 21  20      INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
 22  2    TABLE ACCESS (CLUSTER) OF 'SEG$'
 23  22     INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
 24  1   TABLE ACCESS (CLUSTER) OF 'TS$'
 25  24    INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)

Statistics
----------------------------------------------------------
     0 recursive calls
     0 db block gets
   42924 consistent gets
     0 physical reads
     0 redo size
   98000 bytes sent via SQL*Net to client
    1558 bytes received via SQL*Net from client
     84 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
    1242 rows processed


SQL>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE  10.2.0.4.0   Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

21:32:11 SYS@coll>set autot trace
21:32:15 SYS@coll>select * from dba_indexes;

1162 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3901056803

----------------------------------------------------------------------------------------------
| Id | Operation              | Name  | Rows | Bytes | Cost (%CPU)| Time   |
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |    | 1164 |  330K|  237  (3)| 00:00:03 |
|* 1 | HASH JOIN RIGHT OUTER       |    | 1164 |  330K|  237  (3)| 00:00:03 |
|  2 |  TABLE ACCESS FULL         | TS$  |   9 |  189 |   4  (0)| 00:00:01 |
|* 3 |  HASH JOIN RIGHT OUTER       |    | 1164 |  306K|  232  (3)| 00:00:03 |
|  4 |  TABLE ACCESS FULL        | SEG$  | 2635 |  102K|  18  (0)| 00:00:01 |
|* 5 |  HASH JOIN            |    | 1164 |  261K|  214  (3)| 00:00:03 |
|  6 |   TABLE ACCESS FULL        | USER$ |  35 |  560 |   2  (0)| 00:00:01 |
|* 7 |   HASH JOIN            |    | 1164 |  243K|  211  (3)| 00:00:03 |
|  8 |   TABLE ACCESS FULL       | USER$ |  35 |  560 |   2  (0)| 00:00:01 |
|* 9 |   HASH JOIN RIGHT OUTER     |    | 1164 |  225K|  208  (2)| 00:00:03 |
| 10 |    TABLE ACCESS FULL       | USER$ |  35 |  560 |   2  (0)| 00:00:01 |
|* 11 |    HASH JOIN           |    | 1164 |  206K|  206  (2)| 00:00:03 |
|* 12 |    HASH JOIN OUTER       |    | 1164 |  172K|  174  (2)| 00:00:03 |
| 13 |     MERGE JOIN         |    | 1164 |  142K|  142  (2)| 00:00:02 |
|* 14 |     TABLE ACCESS BY INDEX ROWID| IND$  | 1164 |  104K|  109  (0)| 00:00:02 |
| 15 |      INDEX FULL SCAN      | I_IND1 | 1164 |    |   2  (0)| 00:00:01 |
|* 16 |     SORT JOIN         |    | 10589 |  341K|  33  (7)| 00:00:01 |
|* 17 |      TABLE ACCESS FULL     | OBJ$  | 10589 |  341K|  32  (4)| 00:00:01 |
| 18 |     TABLE ACCESS FULL      | OBJ$  | 10592 |  279K|  31  (0)| 00:00:01 |
| 19 |    TABLE ACCESS FULL      | OBJ$  | 10592 |  310K|  31  (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

  1 - access("I"."TS#"="TS"."TS#"(+))
  3 - access("I"."FILE#"="S"."FILE#"(+) AND "I"."BLOCK#"="S"."BLOCK#"(+) AND
       "I"."TS#"="S"."TS#"(+))
  5 - access("IO"."OWNER#"="IU"."USER#")
  7 - access("U"."USER#"="O"."OWNER#")
  9 - access("ITO"."OWNER#"="ITU"."USER#"(+))
 11 - access("I"."BO#"="IO"."OBJ#")
 12 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+))
 14 - filter(BITAND("I"."FLAGS",4096)=0)
 16 - access("O"."OBJ#"="I"."OBJ#")
    filter("O"."OBJ#"="I"."OBJ#")
 17 - filter(BITAND("O"."FLAGS",128)=0)

Statistics
----------------------------------------------------------
     0 recursive calls
     0 db block gets
    876 consistent gets
     0 physical reads
     0 redo size
   92582 bytes sent via SQL*Net to client
    1339 bytes received via SQL*Net from client
     79 SQL*Net roundtrips to/from client
     1 sorts (memory)
     0 sorts (disk)
    1162 rows processed

select /*+ rule */* from dba_indexes;

1162 rows selected.

Elapsed: 00:00:00.55

Execution Plan
----------------------------------------------------------
Plan hash value: 2107813288

--------------------------------------------------------------
| Id | Operation              | Name      |
--------------------------------------------------------------
|  0 | SELECT STATEMENT          |        |
|  1 | NESTED LOOPS OUTER         |        |
|  2 |  NESTED LOOPS OUTER        |        |
|  3 |  NESTED LOOPS           |        |
|  4 |   NESTED LOOPS          |        |
|  5 |   NESTED LOOPS OUTER       |        |
|  6 |    NESTED LOOPS         |        |
|  7 |    NESTED LOOPS OUTER      |        |
|  8 |     NESTED LOOPS        |        |
|* 9 |     TABLE ACCESS FULL     | OBJ$      |
|* 10 |     TABLE ACCESS BY INDEX ROWID| IND$      |
|* 11 |      INDEX UNIQUE SCAN     | I_IND1     |
| 12 |     TABLE ACCESS BY INDEX ROWID | OBJ$      |
|* 13 |     INDEX UNIQUE SCAN     | I_OBJ1     |
| 14 |    TABLE ACCESS BY INDEX ROWID | OBJ$      |
|* 15 |     INDEX UNIQUE SCAN      | I_OBJ1     |
| 16 |    TABLE ACCESS CLUSTER     | USER$     |
|* 17 |    INDEX UNIQUE SCAN      | I_USER#    |
| 18 |   TABLE ACCESS CLUSTER      | USER$     |
|* 19 |    INDEX UNIQUE SCAN       | I_USER#    |
| 20 |   TABLE ACCESS CLUSTER      | USER$     |
|* 21 |   INDEX UNIQUE SCAN       | I_USER#    |
| 22 |  TABLE ACCESS CLUSTER       | SEG$      |
|* 23 |   INDEX UNIQUE SCAN        | I_FILE#_BLOCK# |
| 24 |  TABLE ACCESS CLUSTER       | TS$      |
|* 25 |  INDEX UNIQUE SCAN        | I_TS#     |
--------------------------------------------------------------

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

  9 - filter(BITAND("O"."FLAGS",128)=0)
 10 - filter(BITAND("I"."FLAGS",4096)=0)
 11 - access("O"."OBJ#"="I"."OBJ#")
 13 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+))
 15 - access("I"."BO#"="IO"."OBJ#")
 17 - access("ITO"."OWNER#"="ITU"."USER#"(+))
 19 - access("U"."USER#"="O"."OWNER#")
 21 - access("IO"."OWNER#"="IU"."USER#")
 23 - access("I"."TS#"="S"."TS#"(+) AND "I"."FILE#"="S"."FILE#"(+) AND
       "I"."BLOCK#"="S"."BLOCK#"(+))
 25 - access("I"."TS#"="TS"."TS#"(+))

Note
-----
  - rule based optimizer used (consider using cbo)

Statistics
----------------------------------------------------------
     0 recursive calls
     0 db block gets
   25254 consistent gets
     26 physical reads
     0 redo size
   93977 bytes sent via SQL*Net to client
    1339 bytes received via SQL*Net from client
     79 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
    1162 rows processed

相關(guān)文章

  • MSSQL轉(zhuǎn)MYSQL的一些記錄

    MSSQL轉(zhuǎn)MYSQL的一些記錄

    今天把一個(gè)MSSQL的數(shù)據(jù)庫轉(zhuǎn)成MYSQL，在沒有轉(zhuǎn)換工具的情況下，對(duì)于字段不多的數(shù)據(jù)表我用了如下手功轉(zhuǎn)換的方法，還算方便。MSSQL使用企業(yè)管理器操作，MYSQL用phpmyadmin操作。
    2010-02-02
  • 錯(cuò)誤代碼：1100 Table ''t_depart_info'' was not locked with LOCK TABLES的解決方法

    錯(cuò)誤代碼：1100 Table ''t_depart_info'' was not locked with LOCK T

    這篇文章就是告訴大家如何解決錯(cuò)誤代碼：1100 Table 't_depart_info' was not locked with LOCK TABLES，遇到類似問題的朋友可以參考一下
    2015-10-10
  • 一款高顏值且免費(fèi)的 SQL 開發(fā)工具之Beekeeper Studio詳解

    一款高顏值且免費(fèi)的 SQL 開發(fā)工具之Beekeeper Studio詳解

    今天給大家推薦一款適用于Windows，Linux和Mac的跨平臺(tái)免費(fèi)的開源SQL編輯器和數(shù)據(jù)庫管理應(yīng)用程序 —— beekeeper-studio。對(duì)Beekeeper Studio 安裝使用教程感興趣的朋友一起看看吧
    2021-09-09
  • JDBC常用接口總結(jié)

    JDBC常用接口總結(jié)

    這篇文章主要介紹了JDBC常用接口總結(jié)，以及部分使用代碼語句，需要的朋友可以參考下。
    2017-09-09
  • hadoop map-reduce中的文件并發(fā)操作

    hadoop map-reduce中的文件并發(fā)操作

    hadoop mapreduce最主要的應(yīng)用是基于鍵值對(duì)的數(shù)據(jù)的運(yùn)算，過濾，提取。但除此之外，我們可以順帶利用mapreduce高并發(fā)的特性做一些用常用方法難以處理的問題，比如大量數(shù)據(jù)，大量文件的并發(fā)讀寫
    2014-04-04
  • SQL語句學(xué)習(xí)

    SQL語句學(xué)習(xí)

    丁丁現(xiàn)在在做數(shù)據(jù)庫，可是上學(xué)時(shí)沒有好好的學(xué)習(xí)SQL的語句，現(xiàn)在每天晚上還要問我，可是我又有好多自己的事情要做，不能天天給她講(^_^其實(shí)我的水品也很一般了),所以先把我以前學(xué)習(xí)sql語句所記錄的一些東東留在這里
    2014-06-06
  • 解決mac上Navicat新建數(shù)據(jù)庫3680錯(cuò)誤問題

    解決mac上Navicat新建數(shù)據(jù)庫3680錯(cuò)誤問題

    這篇文章主要介紹了mac上Navicat新建數(shù)據(jù)庫3680錯(cuò)誤解決辦法,很多朋友遇到這個(gè)問題不知道該如何解決，網(wǎng)上一搜一大把，但是不能解決核心問題，下面小編把我的解決過程分享給大家，需要的朋友可以參考下
    2021-11-11
  • 關(guān)系型數(shù)據(jù)庫和非關(guān)系型數(shù)據(jù)庫概述與優(yōu)缺點(diǎn)對(duì)比

    關(guān)系型數(shù)據(jù)庫和非關(guān)系型數(shù)據(jù)庫概述與優(yōu)缺點(diǎn)對(duì)比

    這篇文章介紹了關(guān)系型數(shù)據(jù)庫和非關(guān)系型數(shù)據(jù)庫概述與優(yōu)缺點(diǎn)對(duì)比，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2022-03-03
  • 一個(gè)多表查詢的SQL語句

    一個(gè)多表查詢的SQL語句

    一個(gè)多表查詢的SQL語句...
    2007-06-06
  • 詳細(xì)聊聊關(guān)于sql注入的一些零散知識(shí)點(diǎn)

    詳細(xì)聊聊關(guān)于sql注入的一些零散知識(shí)點(diǎn)

    SQL注入攻擊是通過將惡意的SQL查詢或添加語句插入到應(yīng)用的輸入?yún)?shù)中,再在后臺(tái)SQL服務(wù)器上解析執(zhí)行進(jìn)行的攻擊,它目前是黑客對(duì)數(shù)據(jù)庫進(jìn)行攻擊的最常用的手段之一,這篇文章主要給大家介紹了關(guān)于sql注入的一些零散知識(shí)點(diǎn),需要的朋友可以參考下
    2021-10-10

最新評(píng)論