| | Explain plan for an SQL query
1. Create the plan table
SQLPLUS> @${ORACLE_HOME}/rdbms/admin/utlxplan.sql
2. Delete old plan from the table and run explain plan.
Replace 'select * from v$database' with the actuall sql statement you want explain plan for.
set lin 180
set pages 0
set feedback on
set echo off
delete from plan_table
where STATEMENT_ID = 'p1'
/
EXPLAIN PLAN SET STATEMENT_ID = 'p1' FOR ... | | More...
10/13/05 | | | | |
|
| | Generate an analyze script for all tables in schema
-- Replace USERNAME with the schema owner who's tables are to be analyzed.
set head off pagesize echo off verify off feedback off linesize 200
spool analyze.sql
select 'set head on echo on verify on feedback on'
from dual
/
select 'spool analyze.log'
from dual
/
select 'exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>' || '''' || owner ||
'''' || ', ' || ... | | More...
10/03/05 | | | | |
|
| | Example of how to trace a session and use tkprof
Enable trace for a user session:
SQLPLUS> exec sys.dbms_system.set_sql_trace_in_session(SID, SERIAL, true);
Use tkprof to analyze the trace file:
tkprof [TRACE FILE] [OUTPUT FILE] explain=/ sort=prsela,exeela,fchela table=ops\$oracle.plan_table
| |  
More...
10/03/05 | | | | |
|
| | Script to detect TX blocking locks
Following script detects TX blocking looks:
set linesize 160
set trimspool on
column name format A28
column type format A18
column sid format 9999
column block format 99
column ctime format 99999
column serial format 99999
column id1 format 99999999
column id2 format 99999999
column LMODE format A12
column request format A12
spool log.lock
select /*+ ordered use_merge(X$KSUSE X$KSQEQ) */
l.ctime ... | | More...
10/03/05 | | | | |
|
| | View sorts per user session
Run this query to see sorts per user session. Replace BLOCKSIZE with block size for your database i.e. if it's 8Kb replace it with 8192
SELECT s.username
,s.sid
,s.serial#
,u.SESSION_ADDR ... | | More...
10/03/05 | | | | |
|
<< 1 | Page 2 |
