检查SQL语句是否应用了索引

    检查一条SQL语句的执行计划，以便分析其是否应用了索引

中国网管联盟bitsCN.com

    好找到SQL的瓶颈 中国网管论坛bbs.bitsCN.com

    1。创建表plan 网管网www_bitscn_com

    执行utlxplan.sql

    该文件在$ORACLE_HOME/rdbms/admin下

中国网管论坛bbs.bitsCN.com

    2。在sqlplus中检查SQL语句是否应用了索引

    SQL> explain plan for (回车)
    2 select itemid from NAD_ADitem where adcustomerid<400 and adgroupid<500;
    SQL> @xpls.sql 网管u家u.bitsCN.com

    3。xpls.sql的原文如下： 网管u家u.bitsCN.com

    //----------------------------------------------------------------------------------------------------------------------------------
    Rem
    Rem $Header: utlxpls.sql 28-jun-99.06:02:16 kquinn Exp $
    Rem
    Rem utlxpls.sql
    Rem
    Rem Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
    Rem
    Rem NAME
    Rem utlxpls.sql - UTiLity eXPLain Serial plans
    Rem
    Rem DESCRIPTION
    Rem script utility to display the explain plan of the last explain plan
    Rem command. Do not display information related to Parallel Query
    Rem
    Rem NOTES
    Rem Assume that the PLAN_TABLE table has been created. The script
    Rem utlxplan.sql should be used to create that table

中国网管论坛bbs.bitsCN.com

    Rem
    Rem To avoid lines from truncating or wrapping around:
    Rem 'set charwidth 80' in svrmgrl
    Rem 'set linesize 80' in SQL*Plus
    Rem
    Rem MODIFIED (MM/DD/YY)
    Rem kquinn 06/28/99 - 901272: Add missing semicolon
    Rem bdagevil 05/07/98 - Explain plan script for serial plans
    Rem bdagevil 05/07/98 - Created
    Rem 网管u家u.bitscn@com

    Rem
    Rem Display last explain plan
    Rem
    select '| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |' as "Plan Table" from dual
    union all
    select '--------------------------------------------------------------------------------' from dual
    union all
    select rpad('| '||substr(lpad(' ',1*(level-1))||operation||
    decode(options, null,'',' '||options), 1, 47), 48, ' ')||'|'||
    rpad(substr(object_name||' ',1, 29), 30, ' ')||'|'||
    lpad(decode(cardinality,null,' ',
    decode(sign(cardinality-1000), -1, cardinality||' ',
    decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
    decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
    trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' || 中国网管论坛bbs.bitsCN.com
    lpad(decode(bytes,null,' ',
    decode(sign(bytes-1024), -1, bytes||' ',
    decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
    decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
    trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
    lpad(decode(cost,null,' ',
    decode(sign(cost-10000000), -1, cost||' ',
    decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
    trunc(cost/1000000000)||'G'))), 8, ' ') || '|' ||
    lpad(decode(partition_start, 'ROW LOCATION', 'ROWID',
    decode(partition_start, 'KEY', 'KEY', decode(partition_start,
    'KEY(INLIST)', 'KEY(I)', decode(substr(partition_start, 1, 6),
    'NUMBER', substr(substr(partition_start, 8, 10), 1,
    length(substr(partition_start, 8, 10))-1),
    decode(partition_start,null,' ',partition_start)))))||' ', 7, ' ')|| '|' ||

    lpad(decode(partition_stop, 'ROW LOCATION', 'ROW L',
    decode(partition_stop, 'KEY', 'KEY', decode(partition_stop,
    'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6),
    'NUMBER', substr(substr(partition_stop, 8, 10), 1,
    length(substr(partition_stop, 8, 10))-1),
    decode(partition_stop,null,' ',partition_stop)))))||' ', 7, ' ')||'|' as "Explain plan"
    from plan_table
    start with id=0 and timestamp = (select max(timestamp) from plan_table
    where id=0)
    connect by prior id = parent_id
    and prior nvl(statement_id, ' ') = nvl(statement_id, ' ')
    and prior timestamp <= timestamp
    union all
    select '--------------------------------------------------------------------------------' from dual;
    //----------------------------------------------------------------------------------------------------------------------------------