移动驻场Oracle优化工程师常用的34个脚本丨附下载

原创章芋文 2021-02-20

11706

2pc_clean.txt
ash_sql_line_id.txt
awr_db_time.txt
awr_metric_name.txt
bind_noused.txt
cursor_purge.txt
ddl_metadata.txt
dml_get.txt
fra_get.txt
param_get.txt
segment_size.txt
session_sid.txt
session_spid.txt
shared_pool_free.txt
sql_monitor.txt
tablespace_used.txt
temp_used.txt
transaction_get.txt
undo_used.txt
wait_event.txt
wait_event_block.txt
wait_event_hash.txt
wait_event_sqlid.txt
wait_session_hash.txt
wait_session_sqlid.txt
ash_used.txt
sql_profile.txt
tabstat.txt
sqlinfo_total.txt
awr_event_histogram.txt
ash_top_sql_event.txt
sqlhis_awr.txt
session_kill.txt
redo_switch.txt

2pc_clean.txt

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;

ash_sql_line_id.txt

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;

awr_db_time.txt

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;

awr_metric_name.txt

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;

bind_noused.txt

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;

cursor_purge.txt

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;
/

ddl_metadata.txt

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

dml_get.txt

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');

fra_get.txt

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
/

param_get.txt

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;

segment_size.txt

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;

session_sid.txt

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
/

session_spid.txt

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
/

shared_pool_free.txt

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

sql_monitor.txt

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;

tablespace_used.txt

--表空间使用率
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_used.txt

--查询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;

transaction_get.txt

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_used.txt

--实时的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;

wait_event.txt

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
;

wait_event_block.txt

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
;

wait_event_hash.txt

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
;

wait_event_sqlid.txt

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
;

wait_session_hash.txt

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
/

wait_session_sqlid.txt

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
/

因篇幅限制，就不在此一一展示了，大家可通过下面链接下载打包好的脚本。
资源下载：https://www.modb.pro/download/43926

ash_used.txt

sql_profile.txt

tabstat.txt

sqlinfo_total.txt

awr_event_histogram.txt

ash_top_sql_event.txt

sqlhis_awr.txt

session_kill.txt

redo_switch.txt

——————————————————
墨天轮，围绕数据人的学习成长提供一站式的全面服务，打造集新闻资讯、在线问答、活动直播、在线课程、文档阅览、资源下载、知识分享及在线运维为一体的统一平台，持续促进数据领域的知识传播和技术创新。

更多精彩可以前往【墨天轮社区】
关注官方公众号：墨天轮墨天轮平台

oracle

最后修改时间：2021-03-06 00:07:33

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者