資深Oracle最佳化工程師常用的34個指令碼彙總（附下載）
附下載連結：
目錄一覽
-
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('%¶m%') 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
——————————————————
墨天輪，圍繞資料人的學習成長提供一站式的全面服務，打造集新聞資訊、線上問答、活動直播、線上課程、文件閱覽、資源下載、知識分享及線上運維為一體的統一平臺，持續促進資料領域的知識傳播和技術創新。
更多精彩可以前往墨天輪社群：
關注官方公眾號：墨天輪 墨天輪平臺 資料庫國產化 墨天輪成長營 墨天輪資訊
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69933133/viewspace-2777936/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 34個Android常用adb shell命令彙總Android
- 常用的vi指令彙總
- SQL Performance Analyzer SPA常用指令碼彙總SQLORM指令碼
- 34 個 常用 Linux Shell 指令碼，運維必備！Linux指令碼運維
- 資料統計指令碼（彙總）指令碼
- Oracle 常用方法彙總Oracle
- 【轉載】監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- oracle 常用語句彙總Oracle
- oracle常用函式彙總Oracle函式
- 8個DBA最常用的監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- 8個DBA最常用的監控Oracle資料庫的常用shell指令碼--Oracle資料庫指令碼
- 8個DBA最常用的監控Oracle資料庫的常用shell指令碼--轉Oracle資料庫指令碼
- 常用的Oracle指令碼參考Oracle指令碼
- 監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- 常用的幾個典型指令碼指令碼
- Solr/Elasticsearch常用資源彙總SolrElasticsearch
- Oracle常用的16個最佳化技巧Oracle
- mysql資料庫最佳化彙總MySql資料庫
- ORACLE常用SQL指令碼2OracleSQL指令碼
- 開始你的第一個JMeter指令碼：利用Jmeter錄製指令碼的2種方法彙總JMeter指令碼
- [轉]監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- 監控Oracle資料庫的常用shell指令碼(轉)Oracle資料庫指令碼
- python - [11] 日常指令碼彙總Python指令碼
- 10個免費下載PHP指令碼的網站PHP指令碼網站
- 常用js彙總JS
- 資料庫常用的sql語句彙總資料庫SQL
- 15個常用Linux命令列總彙Linux命令列
- Linux運維常用的20個命令彙總!Linux運維
- 【Oracle指令碼】-很不錯的Windows下資料庫備份EXP指令碼Oracle指令碼Windows資料庫
- Oracle DBA常用監控指令碼Oracle指令碼
- ORACLE一些不常用的指令碼Oracle指令碼
- 【管理】Oracle 常用的V$ 檢視指令碼Oracle指令碼
- 前端常用外掛、工具類庫彙總（下）前端
- 【轉載】EEG中常用的功能連線指標彙總指標
- ARM彙編指令集彙總
- 10個提供免費PHP指令碼下載的網站PHP指令碼網站
- 建庫指令碼下載指令碼
- C# FTP 上傳 下載（彙總）C#FTP