检查一条SQL语句的执行计划，以便分析其是否应用了索引
好找到SQL的瓶颈
1。创建表plan
执行utlXPlan.sql
该文件在$Oracle_HOME/rdbms/admin下
2。在sqlplus中检查SQL语句是否应用了索引
SQL> explain plan for (回车)
2 select itemid from NAD_ADitem where adcustomerid<400 and adgroupid<500;
SQL> @xpls.sql
3。xpls.sql的原文如下：
//----------------------------------------------------------------------------------------------------------------------------------
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
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
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, ' ') ''
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;
//----------------------------------------------------------------------------------------------------------------------------------
|
