SQL_TUNNING

1. 查找I/O,SWAP 嚴重時候的SQL語句

  SELECT   address,
           hash_value,
           disk_reads / executions disk_reads,
           elapsed_time / 1000000 / executions AS "ELAPSD_TIME(s)",
           buffer_gets / executions bgets_per,
           executions,
           first_load_time AS first_time,
           sql_text
    FROM   v$sql
   WHERE   executions > 0
           AND (disk_reads / executions > 500
                OR buffer_gets / executions > 20000)
           AND command_type = 3
ORDER BY   first_load_time;

2. 查找佔用時間較長的SQL語句

SELECT   sql_text sql_type,
         'Executions=' || executions nl,
         'Expected Response Time in Seconds= '||buffer_gets / DECODE (executions, 0, 1, executions) / 4000 as "Response_Time"
  FROM   v$sql
 WHERE   buffer_gets / DECODE (executions, 0, 1, executions) / 4000 > 10
         AND executions > 0

3. 查找運行時使用TEMP表空間較多的SQL語句

SELECT   sql_text
  FROM   v$sql
 WHERE   disk_reads > 1000
         OR (executions > 0 AND buffer_gets / executions > 30000);

4. 查找當前用戶使用的操作

  SELECT   a.machine,
           a.terminal,
           a.osuser,
           a.username,
           b.sql_text
    FROM   v$session a, v$sqlarea b
   WHERE   a.sql_address = b.address
ORDER BY   b.address

5. 查詢當前運行的SQL

select a.username,a.machine,a.program,b.spid,c.sql_text from v$session a,
      v$process b,v$sqlarea c
where a.paddr=b.addr and  c.hash_value=a.sql_hash_value and c.address=a.
      sql_address
      and b.spid='16323'

6. 查詢當前運行的程序

select a.username,a.machine,a.program,b.spid,a.serial#,a.sid from v$session a,
      v$process b
where a.paddr=b.addr and b.spid in ('20739','14137')

SELECT a.machine,a.terminal,a.osuser, a.username, b.sql_text
from v$session a, v$sqlarea b, v$process  c
where a.sql_address =b.address  and  a.paddr=c.addr
      and c.spid in ('9336','9234')
order by b.address

7. 監控當前數據庫誰有運行什麼SQL

  SELECT   osuser, username, sql_text
    FROM   v$session a, v$sqltext b
   WHERE   a.sql_address = b.address
ORDER BY   address, piece;

8. 查找使用CPU多的用戶SESSION,statistic# = 12 是CPU used by this session

  SELECT   a.sid,
           spid,
           status,
           SUBSTR (a.program, 1, 40) prog,
           a.terminal,
           osuser,
           VALUE / 60 / 100 VALUE
    FROM   v$session a, v$process b, v$sesstat c
   WHERE   c.statistic# = 12 AND c.sid = a.sid AND a.paddr = b.addr
ORDER BY   VALUE DESC;

9. 誰在訪問數據庫

SELECT   c.sid,
         c.serial#,
         c.username,
         a.object_id,
         b.object_name,
         c.program,
         c.status,
         d.name,
         c.osuser
  FROM   v$Locked_object a,
         All_objects b,
         v$session c,
         audit_actions d
 WHERE       a.object_id = b.object_id
         AND a.session_id = c.sid(+)
         AND c.command = d.action;

10. 誰被鎖住

SELECT   a.sid,
         a.serial#,
         a.username,
         a.LOCKWAIT,
         a.status,
         a.program,
         b.name
  FROM   v$session a, audit_actions b
 WHERE   a.command = b.action AND LOCKWAIT IS NOT NULL;

11. 誰在鎖表

SELECT   a.sid,
         a.serial#,
         a.username,
         a.LOCKWAIT,
         a.status,
         a.program,
         b.name
  FROM   v$session a, audit_actions b
 WHERE   a.command = b.action AND STATUS = 'ACTIVE';

12. 查看哪些包要固定

  SELECT   owner,
           name,
           TYPE,
           source_size + code_size + parsed_size + error_size BYPES
    FROM   dba_object_size
   WHERE   TYPE = 'PACKAGE BODY'
ORDER BY   4 DESC;

13. 能知道哪個SID最消耗資源,前提是timed_statistics=true

  SELECT   s.sid, s.VALUE "CPU Used"
    FROM   v$sesstat s, v$statname n
   WHERE       s.statistic# = n.statistic#
           AND n.name = 'CPU used by this session'
           AND s.VALUE > 0
ORDER BY   2 DESC;

14. 怎麼發現是誰鎖住了你需要的一張表

Select object_id from v$locked_object;
Select object_name, object_type from dba_objects where object_id='';

15.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25198367/viewspace-688903/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25198367/viewspace-688903/