Put your tips and tricks online - Share your knowledge! Login | Register
 
 
  Search     Advanced search
 

Home | Ask Question | Add tip | My tips | Recent tips & tricks | Suggest a category | FAQ | Forums

 
 
 
 Found 15 tips, Displaying 11 - 15 
 
Sort by 
 
 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

Recent tips & tricks
Miscellaneous > Middleware > Oracle Fusion Middleware > Oracle SOA Suite : Cannot connect to database whe...
Databases > Oracle : How to identify semaphors and ...
Databases > Oracle > Oracle 10g : How to drop tablespace with da...
Databases > Oracle : How to move an index to anothe...

More categories
Databases | Programming | Hardware | Operating Systems | Networking | Internet | ERP / CRM | Games & Multimedia | Graphics & Design | Miscellaneous | Office Software | TipLib FAQ
 

Home |  FAQ |  Terms of Use |  Privacy Policy

© 2005 tiplib.com