[20111214]oradebug快速定位有問題的sql語句

[20111214]oradebug快速定位有問題的sql語句

oradebug自己平時很少使用,但是使用它定位有問題的sql還是非常快捷的，記錄如下:

例子：

1.開啟會話1，建立一個笛卡爾集，在我的測試機器大約需要150秒才能完成：
SQL> select count(*) from emp,emp,emp,emp,emp,emp,emp,emp;

2.使用工具查詢那個程式比較忙，方法很多！

top - 14:59:30 up  6:54,  2 users,  load average: 0.45, 0.26, 0.09
Tasks: 128 total,   2 running, 126 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.4%us,  0.1%sy,  0.0%ni, 98.6%id,  0.8%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4037768k total,  3899216k used,   138552k free,    57524k buffers
Swap:  3911788k total,   768304k used,  3143484k free,   509740k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 8025 oracle    25   0 1778m  29m  25m R 99.1  0.7   1:38.58 oracle
 8105 oracle    15   0 1792m  58m  54m S 11.9  1.5   0:00.53 oracle

或者使用一些GUI工具,比如toad擷取下來的語句，查詢((UPPER (s.status) LIKE 'ACTIVE'))

SELECT ROUND (BITAND (s.ownerid, 65535)) parent_session_sid, ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
       s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username, s.command, s.ownerid, s.taddr, s.lockwait, s.status,
       s.server, s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.port, s.terminal, UPPER (s.program) program, s.TYPE,
       s.sql_address, s.sql_hash_value, s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id, s.prev_sql_addr,
       s.prev_hash_value, s.prev_sql_id, s.prev_child_number, s.prev_exec_start, s.prev_exec_id, s.plsql_entry_object_id,
       s.plsql_entry_subprogram_id, s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash, s.action, s.action_hash,
       s.client_info, s.fixed_table_sequence, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#,
       s.top_level_call#, s.logon_time, s.last_call_et, s.pdml_enabled, s.failover_type, s.failover_method, s.failed_over,
       s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status, s.current_queue_duration, s.client_identifier,
       s.blocking_session_status, s.blocking_instance, s.blocking_session, s.final_blocking_session_status,
       s.final_blocking_instance, s.final_blocking_session, s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
       s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#, s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
       s.wait_time_micro, s.time_remaining_micro, s.time_since_last_wait_micro, s.service_name, s.sql_trace, s.sql_trace_waits,
       s.sql_trace_binds, s.sql_trace_plan_stats, s.session_edition_id, s.creator_addr, s.creator_serial#, s.ecid,
       stat.cpu - stat.cpu_this_call_start cpu_this_call, stat.cpu, stat.uga_memory, stat.pga_memory, stat.commits,
       stat.rollbacks, p.program "OSProgram", p.spid, p.pid
  FROM v$session s,
       v$process p,
       (SELECT   ss.SID stat_sid, SUM (DECODE (sn.NAME, 'CPU used when call started', ss.VALUE, 0)) cpu_this_call_start,
                 SUM (DECODE (sn.NAME, 'CPU used by this session', ss.VALUE, 0)) cpu,
                 SUM (DECODE (sn.NAME, 'session uga memory', ss.VALUE, 0)) uga_memory,
                 SUM (DECODE (sn.NAME, 'session pga memory', ss.VALUE, 0)) pga_memory,
                 SUM (DECODE (sn.NAME, 'user commits', ss.VALUE, 0)) commits,
                 SUM (DECODE (sn.NAME, 'user rollbacks', ss.VALUE, 0)) rollbacks
            FROM v$sesstat ss, v$statname sn
           WHERE ss.statistic# = sn.statistic#
             AND (   sn.NAME = 'CPU used when call started'
                  OR sn.NAME = 'CPU used by this session'
                  OR sn.NAME = 'session uga memory'
                  OR sn.NAME = 'session pga memory'
                  OR sn.NAME = 'user commits'
                  OR sn.NAME = 'user rollbacks'
                 )
        GROUP BY ss.SID) stat
 WHERE ((s.username IS NOT NULL) AND (NVL (s.osuser, 'x') <> 'SYSTEM') AND (s.TYPE <> 'BACKGROUND'))
   AND (s.ownerid = 2147483644)
   AND ((UPPER (s.status) LIKE 'ACTIVE'))
   AND (p.addr(+) = s.paddr)
   AND (stat.stat_sid = s.SID)

獲取程式的pid,spid

開啟另外的會話：
SQL> sqlplus / as sysdba

SQL> oradebug setospid 8025
Oracle pid: 24, Unix process pid: 8025, image: oracle@study (TNS V1-V3)
SQL> oradebug curr
current_date       current_timestamp
SQL> oradebug current_sql
select count(*) from emp,emp,emp,emp,emp,emp,emp,emp

或者
SQL> oradebug setorapid 24
Oracle pid: 24, Unix process pid: 8025, image: oracle@study (TNS V1-V3)
SQL>  oradebug current_sql
select count(*) from emp,emp,emp,emp,emp,emp,emp,emp

很快有問題的定位語句！