资源下载丨Oracle优化工程师常用的34个脚本

select 'rollback force '||''''||local_tran_id||''''||';' "RollBack"
       from dba_2pc_pending
       where state='prepared';

select 'exec dbms_transaction.purge_lost_db_entry('||''''||local_tran_id||''''||');' "Purge"
       from dba_2pc_pending;

set linesize 260 pagesize 10000
SELECT
    SQL_PLAN_HASH_VALUE,
    event,
    sql_plan_line_id,
    COUNT(*)
FROM
    dba_hist_active_sess_history
WHERE
    sql_id = '&SQL_ID'
    AND sample_time between
        to_date('&date1', 'yyyymmddhh24miss') and
        to_date('&date2', 'yyyymmddhh24miss')
GROUP BY
    SQL_PLAN_HASH_VALUE,sql_plan_line_id,event
ORDER BY
    4 DESC;

set linesize 220 pagesize 1000
col begin_interval_time for a30
col end_interval_time for a30
col stat_name for a40
WITH sysstat
        AS (
SELECT ss.instance_number inst_id,
       sn.begin_interval_time begin_interval_time,
       sn.end_interval_time end_interval_time,
       ss.stat_name stat_name,
       ss.VALUE e_value,
       LAG(ss.VALUE) OVER(partition by ss.instance_number ORDER BY ss.snap_id) b_value
  FROM dba_hist_sys_time_model ss, dba_hist_snapshot sn
 WHERE sn.begin_interval_time >= SYSDATE - &date
   AND ss.snap_id = sn.snap_id
   AND ss.dbid = sn.dbid
   AND ss.instance_number = sn.instance_number
   AND ss.dbid = (SELECT dbid FROM v$database)
   and ss.stat_name = 'DB time'
   and ss.instance_number in (select instance_number from v$instance)
              )
select inst_id,
       begin_interval_time,
       end_interval_time,
       stat_name,
       round((e_value - b_value)/1000/1000/60) value_min
  from sysstat 
 order by 2 desc, 3 desc;

set linesize 220 pagesize 1000
select METRIC_NAME from V$SYSMETRIC_SUMMARY where lower(METRIC_NAME) like '%&metric_name%';

set linesize 220 pagesize 1000
col begin_interval_time for a30
col end_interval_time for a30
col METRIC_NAME for a45
select a.SNAP_ID,
       b.BEGIN_INTERVAL_TIME,
       b.END_INTERVAL_TIME,
       a.METRIC_NAME,
       round(a.AVERAGE, 2) AVERAGE,
       round(a.MAXVAL, 2) MAXVAL
  from dba_hist_sysmetric_summary a, dba_hist_snapshot b
 where a.SNAP_ID = b.SNAP_ID
   and a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
   and a.INSTANCE_NUMBER in (select instance_number from v$instance)
   and a.METRIC_NAME in ('&metric_name')
   and b.BEGIN_INTERVAL_TIME>sysdate-&date
 order by b.BEGIN_INTERVAL_TIME;

set linesize 220 pagesize 10000
set long 999999999
col MODULE for a40
col sql_id for a30
col PARSING_SCHEMA_NAME for a20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
select a.sql_id,
       a.MODULE,
       a.PARSING_SCHEMA_NAME,
       a.last_active_time,
       a.last_load_time,
       a.sql_fulltext,
       b.pool_mb,
       b.cnt
  from v$sqlarea a,
       (select max(sql_id) sql_id,
               FORCE_MATCHING_SIGNATURE,
               round(sum(SHARABLE_MEM / 1024 / 1024)) pool_mb,
               count(1) cnt
          from v$sqlarea
         where FORCE_MATCHING_SIGNATURE > 0
           and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
         group by FORCE_MATCHING_SIGNATURE
        having count(1) > 3
         order by count(1) desc) b
 where a.sql_id = b.sql_id
 order by cnt desc;

declare
    v_address_hash varchar2(128);
begin
    select address||', '||hash_value into v_address_hash
    from v$sqlarea
    where sql_id = '&SQL_ID';
sys.dbms_shared_pool.purge(v_address_hash, 'C');
end;
/

set linesize 260
set long 999999
set pagesize 1000
select dbms_metadata.get_ddl(upper('&object_type'),upper('&object_name'),upper('&owner')) FROM DUAL;

set linesize 220 pagesize 10000
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col table_owner for a20
col table_name for a30
col partition_name for a20
col subpartition_name for a20
select a.*,sysdate from dba_tab_modifications a where table_name=upper('&table_name');

set echo off
set lines 300
set pagesize 1000
col reclaimable for a20
COL used for a20
COL QUOTA FOR A20
COL NAME FOR A30
col used1 for 99999 heading 'USED%';
prompt "RECOVERY FILE DEST AND SIZE"
SELECT substr(name, 1, 30) name, round(space_limit/1024/1024)||'M' AS quota,
       round(space_used/1024/1024)||'M'        AS used,round(100*space_used/space_limit) used1,
       round(space_reclaimable/1024/1024)||'M' AS reclaimable,
        number_of_files   AS files
  FROM  v$recovery_file_dest
  /

Select file_type, percent_space_used,percent_space_reclaimable,number_of_files as "number" from v$flash_recovery_area_usage
/

set linesize 220 pagesize 1000
col ksppinm for a40
col ksppstvl for a40
col ksppdesc for a100
select a.ksppinm, a.ksppdesc,b.ksppstvl,a.inst_id
  from sys.x$ksppi a, sys.x$ksppcv b
 where upper(a.ksppinm) like upper('%&param%')
   and a.indx = b.indx 
   order by a.ksppinm;

col owner for a15
col segment_name for a29
col partition_name for a30
col tablespace_name for a29
col size_m for 999,999,999
col blocks for 999,999,999
select owner,segment_name, partition_name,tablespace_name,bytes/1024/1024 size_m,blocks from dba_segments where segment_name=UPPER('&segment_name') order by 1;

set linesize 260
set pagesize 1000
col sid for 99999
col spid for a8
col event for a30
col module for a35
col machine for a15
col username for a10
col holder for a10
col final for a10
col sql_id for a15
col exec_gets for 99999999
col seconds for a5
col object_id for 999999
col param for a30
col sql_text for a6
col PGA_USE for 9999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select a.sid,
       a.username,
       a.machine,
       a.module,
       a.event,
       a.sql_id,
       round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets,
       a.ROW_WAIT_OBJ# object_id,
       a.BLOCKING_INSTANCE||'_'||a.blocking_session  holder,
       a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final,
       to_char(LAST_CALL_ET) seconds,
       a.p1 || '_' || a.p2 || '_' || a.p3 param,
       b.spid,
       trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE,
       substr(c.sql_text,0,6) sql_text
  from v$session a, v$process b,v$sql c
 where a.paddr = b.addr(+)
   and a.status = 'ACTIVE'
   and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and
        a.wait_class = 'Idle')
   and a.sql_id=c.sql_id(+)
   and a.sql_child_number=c.CHILD_NUMBER(+)
   and a.sid='&SID'
 order by a.sql_id, a.machine
/

set linesize 260
set pagesize 1000
col sid for 99999
col spid for a8
col event for a30
col module for a35
col machine for a15
col username for a10
col holder for a10
col final for a10
col sql_id for a15
col exec_gets for 99999999
col seconds for a5
col object_id for 999999
col param for a30
col sql_text for a6
col PGA_USE for 9999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select a.sid,
       a.username,
       a.machine,
       a.module,
       a.event,
       a.sql_id,
       round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets,
       a.ROW_WAIT_OBJ# object_id,
       a.BLOCKING_INSTANCE||'_'||a.blocking_session  holder,
       a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final,
       to_char(LAST_CALL_ET) seconds,
       a.p1 || '_' || a.p2 || '_' || a.p3 param,
       b.spid,
       trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE,
       substr(c.sql_text,0,6) sql_text
  from v$session a, v$process b,v$sql c
 where a.paddr = b.addr(+)
   and a.status = 'ACTIVE'
   and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and
        a.wait_class = 'Idle')
   and a.sql_id=c.sql_id(+)
   and a.sql_child_number=c.CHILD_NUMBER(+)
   and b.spid='&SPID'
 order by a.sql_id, a.machine
/

set linesize 260 pagesize 1000
select pool, name, bytes / 1024 / 1024 / 1024 GB
      from v$sgastat
     where name like 'free memory'
    ;

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  SQL_ID       => '&SQL_ID',
  TYPE         => 'TEXT',
  REPORT_LEVEL => 'ALL') AS REPORT
FROM dual;

--表空间使用率
set linesize 220 pagesize 10000
COL SIZE_G FOR A15
COL FREE_G FOR A15
COL USED_PCT FOR A10
COL TABLESPACE_NAME FOR A30
SELECT d.tablespace_name,
           to_char(nvl(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.00') size_g,
           to_char(nvl(f.bytes, 0) / 1024 / 1024 / 1024, '99,999,990.00') free_g,
           to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '990.00') || '%' used_pct
    FROM   dba_tablespaces d,
           (SELECT tablespace_name, SUM(bytes) bytes
            FROM   dba_data_files
            GROUP  BY tablespace_name) a,
           (SELECT tablespace_name, SUM(bytes) bytes
           FROM   dba_free_space
           GROUP  BY tablespace_name) f
   WHERE  d.tablespace_name = a.tablespace_name(+)
          AND d.tablespace_name = f.tablespace_name(+)
          AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
   ORDER  BY 4 DESC;

--查询temp表空间使用率：
select  df.tablespace_name "Tablespace",
       df.totalspace "Total(MB)",
       nvl(FS.UsedSpace, 0)  "Used(MB)",
       (df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
       round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM  (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
        FROM   dba_TEMP_files
        GROUP  BY tablespace_name) df,
       (SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024)  UsedSpace
        FROM   gV$temp_extent_pool
        GROUP  BY tablespace_name) fs  WHERE  df.tablespace_name = fs.tablespace_name(+);

--查询temp表空间使用率：
select  df.tablespace_name "Tablespace",
       df.totalspace "Total(MB)",
       nvl(FS.UsedSpace, 0)  "Used(MB)",
       (df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
       round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM  (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
        FROM   dba_TEMP_files
        GROUP  BY tablespace_name) df,
       (SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024)  UsedSpace
        FROM   gV$temp_extent_pool
        GROUP  BY tablespace_name) fs  WHERE  df.tablespace_name = fs.tablespace_name(+)

--查询实时使用temp表空间的sql_id和sid：

set linesize 260 pagesize 1000
col machine for a40
col program for a40
SELECT se.username,
       sid,
       serial#,
       se.sql_id
       machine,
       program,
       tablespace,
       segtype,
       (su.BLOCKS*8/1024/1024) GB
  FROM v$session se, v$sort_usage su
 WHERE se.saddr = su.session_addr
 order by su.BLOCKS desc;

--需要注意的是这里查询sql_id要用v$session视图的sql_id，而不要用v$sort_usage视图的sql_id，v$sort_usage视图里面的sql_id是不准确的

--查询历史的temp表空间的使用的SQL_ID

select a.SQL_ID,
       a.SAMPLE_TIME,
       a.program,
       sum(trunc(a.TEMP_SPACE_ALLOCATED / 1024 / 1024)) MB
  from v$active_session_history a
 where TEMP_SPACE_ALLOCATED is not null 
 and sample_time between
 to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
 to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
 group by a.sql_id,a.SAMPLE_TIME,a.PROGRAM
 order by 2 asc,4 desc;

set linesize 260 pagesize 10000
column sess       format a21 heading "SESSION"
column program    format a18
column clnt_pid   format a8
column machine    format a25
column username   format a12
column osuser     format a13
column event      format a32
column waitsec    format 999999
column start_time format a18
column sql_id     format a15
column clnt_user  format a10
column svr_ospid  format a10

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy/mm/dd hh24:mi:ss';

set feedback off
set echo off

set head off
select chr(9) from dual;
select 'Waiting Transactions'||chr(10)||'====================' from dual;
set head on
select /*+ rule */
       lpad(nvl(s.username,' '),8)||'('||s.sid||','||s.serial#||')' as sess,
       p.spid as svr_ospid,
       nvl(osuser,' ') as clnt_user,
       s.process as clnt_pid,
       substr((case instr(s.PROGRAM, '@')
                 when 0 then
                   s.program
                 else
                   case instr(s.PROGRAM, '(TNS V1-V3)')
                     when 0 then
                       substr(s.program, 1, instr(s.PROGRAM, '@') - 1) || substr(s.program, instr(s.PROGRAM, '(') - 1)
                     else
                       substr(s.program, 1, instr(s.PROGRAM, '@') - 1)
                   end
                                                         end),
              1, 18) as program,
       (case 
            when length(s.MACHINE) > 8 then substr(s.machine,1,8)||'~'
            else s.machine
        end
       ) || '('||nvl(s.client_info, 'Unknown IP')||')' as machine, s.sql_id,
       substr(s.event, 1, 32) as event,
       s.seconds_in_wait      as waitsec
  from v$transaction t,v$session s,v$process p
 where t.ses_addr=s.saddr and s.paddr=p.addr
 order by s.seconds_in_wait, s.program, s.machine;

--实时的undo使用量
set linesize 220
set pagesize 1000
col username for a20
col module for a40
col sql_id for a15
col status for a10
col machine for a20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select *
  from (select start_time,
               username, 
               s.MACHINE, 
               s.OSUSER, 
               r.name, 
               ubafil, --Undo block address (UBA) filenum  
               ubablk, --UBA block number  
               t.status,   
               (used_ublk * 8192 / 1024) kbtye,   
               used_urec,   
               s1.SQL_ID,   
               substr(s1.SQL_TEXT,0,20)
          from v$transaction t, v$rollname r, v$session s, v$sqlarea s1
         where t.xidusn = r.usn
           and s.saddr = t.ses_addr
           and s.sql_id = s1.sql_id(+)
         order by 9 desc)
 where rownum <= 10;

set linesize 220
set pagesize 1000
select inst_id,event,count(*)
  from gv$session a
 where a.status='ACTIVE'
 and not (a.type = 'BACKGROUND' and a.state='WAITING' and  a.wait_class='Idle')
 group by inst_id,event
 order by a.inst_id,count(*) desc
;

set linesize 220
set pagesize 1000
select inst_id,event,sql_id,BLOCKING_INSTANCE,blocking_session,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION, count(*)
  from gv$session a
 where a.status='ACTIVE'
 and not (a.type = 'BACKGROUND' and a.state='WAITING' and  a.wait_class='Idle') and upper(event) like upper('%&event%')
 group by inst_id,event,sql_id,BLOCKING_INSTANCE,blocking_session,FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_SESSION
 order by inst_id ,count(*) desc, sql_id
;

set linesize 220
set pagesize 1000
select a.event, c.plan_hash_value,max(round(decode(c.executions,0,c.buffer_gets,c.buffer_gets/c.executions))) exec_gets,count(*)
  from gv$session a,gv$sql c
 where a.status='ACTIVE'
 and not (a.type = 'BACKGROUND' and a.state='WAITING' and  a.wait_class='Idle')
 and a.sql_id=c.sql_id(+)
 and a.sql_child_number=c.CHILD_NUMBER(+)
 and a.inst_id=c.inst_id
 group by a.inst_id,a.event, c.plan_hash_value
 order by a.inst_id,count(*) desc, c.plan_hash_value
;

set linesize 220
set pagesize 1000
select a.inst_id,a.event, a.sql_id,max(round(decode(c.executions,0,c.buffer_gets,c.buffer_gets/c.executions))) exec_gets, count(*)
  from gv$session a,gv$sql c
 where a.status='ACTIVE'
 and not (a.type = 'BACKGROUND' and a.state='WAITING' and  a.wait_class='Idle')
 and a.sql_id=c.sql_id(+)
 and a.sql_child_number=c.CHILD_NUMBER(+)
 and a.inst_id=c.inst_id
 group by a.inst_id,a.event, a.sql_id
 order by a.inst_id,count(*) desc, a.sql_id
;

set linesize 260
set pagesize 1000
col sid for 99999
col spid for a8
col event for a30
col module for a35
col machine for a15
col username for a10
col holder for a10
col final for a10
col sql_id for a15
col exec_gets for 99999999
col seconds for a5
col object_id for 999999
col param for a30
col sql_text for a6
col PGA_USE for 9999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select a.sid,
       a.username,
       a.machine,
       a.module,
       a.event,
       c.plan_hash_value,
       round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets,
       a.ROW_WAIT_OBJ# object_id,
       a.BLOCKING_INSTANCE||'_'||a.blocking_session  holder,
       a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final,
       to_char(LAST_CALL_ET) seconds,
       a.p1 || '_' || a.p2 || '_' || a.p3 param,
       b.spid,
       trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE,
       substr(c.sql_text,0,6) sql_text
  from v$session a, v$process b,v$sql c
 where a.paddr = b.addr(+)
   and a.status = 'ACTIVE'
   and a.sql_id=c.sql_id(+)
   and a.sql_child_number=c.CHILD_NUMBER(+)
   and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and
        a.wait_class = 'Idle')
 order by c.plan_hash_value, a.machine
/

set linesize 260
set pagesize 1000
col sid for 99999
col spid for a8
col event for a30
col module for a35
col machine for a15
col username for a10
col holder for a10
col final for a10
col sql_id for a15
col exec_gets for 99999999
col seconds for a5
col object_id for 999999
col param for a30
col sql_text for a6
col PGA_USE for 9999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select a.sid,
       a.username,
       a.machine,
       a.module,
       a.event,
       a.sql_id,
       round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets,
       a.ROW_WAIT_OBJ# object_id,
       a.BLOCKING_INSTANCE||'_'||a.blocking_session  holder,
       a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final,
       to_char(LAST_CALL_ET) seconds,
       a.p1 || '_' || a.p2 || '_' || a.p3 param,
       b.spid,
       trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE,
       substr(c.sql_text,0,6) sql_text
  from v$session a, v$process b,v$sql c
 where a.paddr = b.addr(+)
   and a.status = 'ACTIVE'
   and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and
        a.wait_class = 'Idle')
   and a.sql_id=c.sql_id(+)
   and a.sql_child_number=c.CHILD_NUMBER(+)
 order by a.sql_id, a.machine
/