Save explain plan to a custom table and start performance tuning
                                                 Last update (2008-11-27 11:14:48)
                                                                                                                   Date added (2008-02-25 15:55:02)

Summary
Sometimes you may need to start tuning sql queries without having a fancy SQL editor like TOAD. So if you want to focus on specific queries, store their explain plans to a custom table and prepare for the classic steps of performance tuning.

The classic steps are:
Create sql statements for possible table moves and index rebuilds
Create sql statements for possible statistics estimation

1. Create the table to store the explain plans (HELPDESK.S_EXPLAIN_PLANS)
CREATE TABLE HELPDESK.S_EXPLAIN_PLANS
(
  OPERATION    VARCHAR2(200 BYTE),
  OBJECT       VARCHAR2(200 BYTE),
  NUM_ROWS     VARCHAR2(30 BYTE),
  BYTES        VARCHAR2(30 BYTE),
  COST         VARCHAR2(30 BYTE),
  OBJECT_NODE  VARCHAR2(200 BYTE),
  IN_OUT       VARCHAR2(30 BYTE),
  PSTART       VARCHAR2(200 BYTE),
  PSTOP        VARCHAR2(200 BYTE)
)
TABLESPACE HELPDESK
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;
2. Insert the explain plan to the table
INSERT INTO helpdesk.s_explain_plans (operation, OBJECT, num_rows, bytes, COST, object_node, in_out, pstart, pstop) 
SELECT LPAD(' ', 2*depth) || operation || ' ' || options || DECODE (ID, 0, SUBSTR(optimizer, 1,6) || 
       ' Cost=' || TO_CHAR(COST)) operation,  
	   DECODE(v.object_owner || '.' || v.object_name, '.', NULL, v.object_owner || '.' || v.object_name) OBJECT,
	   CARDINALITY "ROWS",
	   bytes, COST, object_node, NULL, partition_start, partition_stop    
       FROM v$sql_plan v
       WHERE hash_value = '2193749925'
You need to know the hash_value of the query, you can easily find it from v$session

Execute to see the explain plan
Select * from helpdesk.s_explain_plans;
3. To create all the sql statements for rebuilding the indexes related to the query use:
SELECT 'alter index ' || owner || '.' || index_name || ' rebuild parallel 8 nologging;' 
FROM dba_indexes 
WHERE table_name IN (
SELECT TABLE_NAME FROM (
SELECT owner, table_name FROM dba_tables WHERE table_name IN (SELECT DISTINCT table_name FROM 
dba_indexes WHERE index_name IN (SELECT DISTINCT SUBSTR(OBJECT, INSTR(OBJECT, '.')+1) FROM helpdesk.s_explain_plans WHERE 
operation LIKE '%INDEX%'))
UNION 
SELECT owner, table_name FROM 
dba_tables WHERE table_name IN (SELECT DISTINCT SUBSTR(OBJECT, INSTR(OBJECT, '.')+1) FROM helpdesk.s_explain_plans WHERE 
operation LIKE '%TABLE%')));
4. To create all the sql statements for estimating statistics for the undelying tables and indexes related to the query use:
SELECT 'execute dbms_stats.gather_table_stats(ownname=>' || '''' || owner || '''' || ', tabname=>' || '''' || table_name || 
'''' || ' , estimate_percent=>10, cascade=>true, degree=>8);' dbms_stats_sql 
--'analyze table ' || owner || '.' || table_name || ' estimate statistics sample 10 percent;' analyze_sql 
FROM (
SELECT owner, table_name FROM dba_tables WHERE table_name IN (SELECT DISTINCT table_name FROM 
dba_indexes WHERE index_name IN (SELECT DISTINCT SUBSTR(OBJECT, INSTR(OBJECT, '.')+1) FROM helpdesk.s_explain_plans WHERE 
operation LIKE '%INDEX%'))
UNION 
SELECT owner, table_name FROM 
dba_tables WHERE table_name IN (SELECT DISTINCT SUBSTR(OBJECT, INSTR(OBJECT, '.')+1) FROM helpdesk.s_explain_plans WHERE 
operation LIKE '%TABLE%'));
Reviews
Categories
Filters
Search