<Tuning > SQL Tuning (2) SQL SQL SQL Tuning
ROW(1) ROW(2) ROW(n) update ROW(2) at time 1 & Uncommitted update ROW(2) at time 2 SQLDBA> @ UTLLOCKT WAITING_SESSION TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2 ---------------------------- -------- ----------------------------- ------------------- -------------- --------------- 10 NONE NONE NONE 0 0 20 TX EXCLUSIVE EXCLUSIVE 32678 14091 โถ `10` COMMIT or ROLLBACK โท SQL*DBA Monitor `KILL SESSION` `10` KILL
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * SELECT * FROM EMP WHERE DEPTNO = 10 ORDER BY JOB call count cpu elapsed disk query current rows ----------- -------- ------- ------------ --------- -------- --------- ------- Parse 1 0.04 0.08 0 0 0 0 Execute 1 0.09 0.11 0 0 0 0 Fetch 100 2.54 2.71 22 12510 557 100 ----------- -------- ------- ------------ --------- -------- --------- ------- Toatl 102 2.67 2.90 22 12510 557 100 Misses in Library cache during parse : 1 Parsing user id = 12 (scott( scott) Rows Execution Plan ----------- ------------------------------------------------------------------------ 0 select statement 100 sort (order by) 100 table access by rowid of emp 22560 index range scan of emp_idxq * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
3GL? HOLD_CURSOR? LOOP SQL? OVERALL SQL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------------- --------- --------- ------------ ----------- ---------- ----------- --------- Parse 742 2.94 3.28 24 32 0 0 Execute 1904 1.79 1.91 0 45 0 1190 Fetch 1942 87.54 96.72 322 812510 57 2620 ------------- --------- -------- ------------- ----------- ---------- ----------- --------- Total 4588 92.27 101.91 324 812587 57 3810 Misses in Library cache during parse : 30 62 user SQL statements in session. 9 internal SQL statements in session. 741 SQL statements in session. EXPLAINed 59 statements in this session. OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS count cpu call count cpu elapsed disk query current rows ------------- --------- --------- --------- ------------ ----------- ---------- ----------- --------- Parse 742 2.94 2.94 3.28 24 32 0 0 Execute 1904 1.79 1.91 0 45 0 1190 1904 1.79 Fetch 1942 87.54 96.72 322 812510 57 2620 ------------- -------- -------- ------------- ----------- ---------- ----------- --------- Total 4588 92.27 101.91 324 812587 57 3810 Misses in Library cache during parse : 30 62 user SQL statements in session. 9 internal SQL statements in session. 741 SQL statements in session. EXPLAINed 59 statements in this session. (Parse count * 0.01) cpu
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------------- -------- --------- ------------ ---- ----------- -------- ---------- ---- ----------- --------- Parse 742 2.94 3.28 28 2 24 32 0 0 Execute 1904 1.79 1.91 91 0 0 45 0 1190 Fetch 1942 87.54 96.72 322 812510 57 2620 ------------- -------- -------- ------------- ----------- 322 ---------- ----------- --------- Total 4588 92.27 101.91 324 812587 57 3810 Misses in Library cache during parse : 30 e disk q (Parse count *.0.03) disk 62 user SQL statements in session. 9 internal SQL statements in session. 741 SQL statements in session. EXPLAINed 59 statements in this session. OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------------- -------- --------- ------------ ----------- ------- ---------- --------- ----------- ------- --------- Parse 742 2.94 3.28 24 24 32 32 0 0 0 Execute 1904 1.79 1.91 0 0 45 45 0 01190 Fetch 1942 87.54 96.72 322 322 812510 57 57 2620 ------------- -------- -------- ------------- ----------- ------------- ---------- ----------- --------- Total 4588 92.27 101.91 324 812587 57 3810 Misses in Library cache during parse : 30 62 user SQL statements in session. 9 internal SQL statements in session. 741 SQL statements in session. EXPLAINed 59 statements in this session. disk query curr 24 812587 (Execute disk + Fetch disk) (Execute query + Fetch query +.Execute current + Fetch current) 10%
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------------- -------- --------- ------------ ----------- ---------- ----------- --------- Parse 742 2.94 3.28 2 32 0 0 Execute 1904 1.79 79 1.91 0 45 0 1190 Fetch 1942 87.54 96.72 322 812510 57 2620 ------------- -------- -------- ------------- ----------- ---------- ----------- --------- Total 4588 92.27 101.91 324 812587 57 3810 92.27 101.91 Misses in Library cache during parse : 30 62 user SQL statements in session. 9 internal SQL statements in session. 741 SQL statements in session. EXPLAINed 59 statements in SQL this session. OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------------- -------- --------- ------------ ----------- ---------- ----------- > --------- MON LCACHE 'pins'. Parse 742 2.94 3.28 2 32 0 0 0.95 Execute 1904 1.79 1.91 0 45 0 1190 SQL Fetch 1942 87.54 96.72 322 812510 57 2620 ------------- -------- -------- ------------- ----------- ---------- ----------- --------- Total 4588 92.27 101.91 324 812587 57 3810 324 8125 Misses in Library cache during parse : 30 arse : 30 session. session. 62 user SQL statements in session. 9 internal SQL statements in session. 741 SQL statements in session. EXPLAINed 59 statements in this session.