ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 资深Oracle优化工程师常用的34个脚本汇总（附下载）

资深Oracle优化工程师常用的34个脚本汇总（附下载）

原创 Oracle 作者：enmotech 时间：2021-06-23 11:23:48 0 删除 编辑

附下载链接：https://www.modb.pro/download/43926

目录一览

  • 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 10000SELECT
    SQL_PLAN_HASH_VALUE,    event,
    sql_plan_line_id,    COUNT(*)FROM
    dba_hist_active_sess_historyWHERE
    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,eventORDER BY
    4 DESC;

awr_db_time.txt

set linesize 220 pagesize 1000col begin_interval_time for a30col end_interval_time for a30col stat_name for a40WITH 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 1000select METRIC_NAME from V$SYSMETRIC_SUMMARY where lower(METRIC_NAME) like '%&metric_name%';set linesize 220 pagesize 1000col begin_interval_time for a30col end_interval_time for a30col METRIC_NAME for a45select 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 10000set long 999999999col MODULE for a40col sql_id for a30col PARSING_SCHEMA_NAME for a20alter 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 260set long 999999set pagesize 1000select dbms_metadata.get_ddl(upper('&object_type'),upper('&object_name'),upper('&owner')) FROM DUAL;

dml_get.txt

set linesize 220 pagesize 10000alter 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 a20select a.*,sysdate from dba_tab_modifications a where table_name=upper('&table_name');

fra_get.txt

set echo offset lines 300set pagesize 1000col reclaimable for a20COL used for a20COL QUOTA FOR A20COL NAME FOR A30col 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 1000col ksppinm for a40col ksppstvl for a40col ksppdesc for a100select 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,999select 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 260set pagesize 1000col sid for 99999col spid for a8col event for a30col module for a35col machine for a15col username for a10col holder for a10col final for a10col sql_id for a15col exec_gets for 99999999col seconds for a5col object_id for 999999col param for a30col sql_text for a6col PGA_USE for 9999alter 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 260set pagesize 1000col sid for 99999col spid for a8col event for a30col module for a35col machine for a15col username for a10col holder for a10col final for a10col sql_id for a15col exec_gets for 99999999col seconds for a5col object_id for 999999col param for a30col sql_text for a6col PGA_USE for 9999alter 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 1000select pool, name, bytes / 1024 / 1024 / 1024 GB      from v$sgastat     where name like 'free memory'
    ;

sql_monitor.txt

SET LONG 1000000SET LONGCHUNKSIZE 1000000SET LINESIZE 1000SET PAGESIZE 0SET TRIM ONSET TRIMSPOOL ONSET ECHO OFFSET FEEDBACK OFFSELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  SQL_ID       => '&SQL_ID',  TYPE         => 'TEXT',
  REPORT_LEVEL => 'ALL') AS REPORTFROM dual;

tablespace_used.txt

--表空间使用率set linesize 220 pagesize 10000COL SIZE_G FOR A15COL FREE_G FOR A15COL USED_PCT FOR A10COL TABLESPACE_NAME FOR A30SELECT 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 1000col machine for a40col program for a40SELECT 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_IDselect 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 10000column sess       format a21 heading "SESSION"column program    format a18column clnt_pid   format a8column machine    format a25column username   format a12column osuser     format a13column event      format a32column waitsec    format 999999column start_time format a18column sql_id     format a15column clnt_user  format a10column svr_ospid  format a10ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy/mm/dd hh24:mi:ss';set feedback offset echo offset head offselect chr(9) from dual;select 'Waiting Transactions'||chr(10)||'====================' from dual;set head onselect /*+ 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 220set pagesize 1000col username for a20col module for a40col sql_id for a15col status for a10col machine for a20alter 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 220set pagesize 1000select 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 220set pagesize 1000select 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 220set pagesize 1000select 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 220set pagesize 1000select 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 260set pagesize 1000col sid for 99999col spid for a8col event for a30col module for a35col machine for a15col username for a10col holder for a10col final for a10col sql_id for a15col exec_gets for 99999999col seconds for a5col object_id for 999999col param for a30col sql_text for a6col PGA_USE for 9999alter 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 260set pagesize 1000col sid for 99999col spid for a8col event for a30col module for a35col machine for a15col username for a10col holder for a10col final for a10col sql_id for a15col exec_gets for 99999999col seconds for a5col object_id for 999999col param for a30col sql_text for a6col PGA_USE for 9999alter 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
/

因篇幅限制，就不在此一一展示了，大家可通过下面链接下载打包好的脚本。 资源下载：

 

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

——————————————————

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

更多精彩可以前往墨天轮社区：https://www.modb.pro/

关注官方公众号：墨天轮 墨天轮平台 数据库国产化  墨天轮成长营  墨天轮资讯


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

0
0
上一篇： 近期数据库动态解读
下一篇： 2021年6月国产数据库大事记
请登录后发表评论 登录
全部评论
enmotech

注册时间：2019-06-19

  • 博文量
    226
  • 访问量
    139115

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593