Differences

This shows you the differences between two versions of the page.

--- performancetuning [2015/07/21 08:46] – z0hpvk
+++ performancetuning [2025/04/01 08:34] (current) – z0hpvk
@@ Line 1: / Line 1: @@
 ===== Performance Tuning =====
-Get Query
+==== Simple Explain Plan ====
+<code sql>EXPLAIN PLAN FOR SELECT * FROM dba_users;
+SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
+</code>
+==== Get Active Sessions ====
+<code>SET LINES 2000 TRIMSPOOL ON PAGES 10000
+COLUMN EVENT FORMAT A30
+COLUMN secw FORMAT 99999
+COLUMN username FORMAT A15
+COLUMN sqlid_chno FORMAT A20
+ACCEPT usr PROMPT 'Username (blank for all): '
+SELECT  sw.SID,
+        s.username,
+		sw.SEQ#,
+		sw.EVENT,
+		sw.WAIT_TIME,
+		sw.SECONDS_IN_WAIT secw,
+		SQL_ID||' '||SQL_CHILD_NUMBER sqlid_chno,
+		sw.STATE,
+		sw.P1,
+		sw.P2
+FROM v$session_wait sw,
+     v$session s
+WHERE s.sid = sw.sid
+AND sw.event != 'SQL*Net message from client'
+AND s.username LIKE UPPER('%&&usr%');</code>
+==== Get Query ====
 <code>set lines 10000 trimspool on pages 0 feed off verify off
 set serverout on size 10000
@@ Line 29: / Line 60: @@
 SPOOL OFF</code>
+==== Get Explain Plan ====
+<code>SET LINES 2000 TRIMSPOOL ON PAGES 10000
+SET SERVEROUT ON SIZE 1000000
+UNDEFINE sql_id
+UNDEFINE child_id
+ACCEPT sql_id PROMPT 'sql_id: '
+SET VERIFY OFF FEED OFF
+SELECT sql_id,child_number, TO_CHAR(last_active_time,'DDMonYY:HH24:MI:SS') last_active
+FROM v$sql
+WHERE sql_id = '&&sql_id'
+ORDER BY last_active_time;
+DEFINE child_id = '&&child_id'
+spool /home/oracle/xp_&&sql_id._&&child_id..txt
+SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&&sql_id',NVL('&&child_id',0)));
+DECLARE
+l_bv_str   VARCHAR2(32767) := NULL;
+BEGIN
+FOR l_bv_rec IN (SELECT bc.name, bc.value_string
+                 FROM v$SQL_BIND_CAPTURE bc
+                 WHERE bc.sql_id = '&&sql_id'
+                 AND   bc.child_number = '&&child_id'
+                 ORDER BY bc.position) LOOP
+    l_bv_str := l_bv_str||' '||l_bv_rec.name||'='||''''||l_bv_rec.value_string||'''';
+END LOOP;
+IF l_bv_str IS NULL THEN
+   l_bv_str := 'n/a';
+END IF;
+DBMS_OUTPUT.PUT_LINE ('Bind values: '||l_bv_str);
+END;
+/
+COLUMN rp        FORMAT a10
+COLUMN cpu       FORMAT a10
+COLUMN ela       FORMAT a10
+COLUMN la        FORMAT a22
+COLUMN uo        FORMAT a6
+COLUMN fetches   FORMAT a8
+COLUMN ex        FORMAT a8
+COLUMN eofetches FORMAT a10
+COLUMN ue        FORMAT a6
+COLUMN loads     FORMAT a6
+SELECT               TO_CHAR(s.rows_processed,'999999990') rp,
+                     LTRIM(TO_CHAR(s.cpu_time/1000000,'999990.9')) cpu,
+                     LTRIM(TO_CHAR(s.elapsed_time/1000000,'999999990.9')) ela,
+                     LTRIM(TO_CHAR(s.last_active_time,'DD-MON-YYYY HH24:MI:SS')) la,
+                     TO_CHAR(s.users_opening,'9990') uo,
+                     TO_CHAR(s.fetches,'9999990') fetches,
+                     TO_CHAR(s.executions,'9999990') ex,
+                     TO_CHAR(s.end_of_fetch_count,'9999990') eofetches,
+                     TO_CHAR(s.users_executing,'9990') ue,
+                     TO_CHAR(s.loads,'9990') loads
+from v$sql s
+where s.sql_id = '&&sql_id'
+and s.child_number = '&&child_id';
+spool off
+SET VERIFY ON FEED ON</code>