本文作為常用 SQL 系列的第三篇，本文涉及到的 SQL 及相關命令均是在運維工作中總結整理而成的，對于運維 DBA 來說可提高很大的工作效率，值得收藏下來慢慢看。

作者：JiekeXu
來源 | JiekeXu DBA 之路（ID: JiekeXu_IT）
轉載請聯系授權 | (微信 ID：JiekeXu_DBA)

SQL 大全一 http://m.sunline.cc/doc/22598
SQL 大全二 http://m.sunline.cc/db/45337
Oracle DBA 日常維護 SQL 腳本大全（收藏版） http://m.sunline.cc/db/44364

附本文 PDF 下載鏈接：http://m.sunline.cc/doc/91589

本文 SQL 均是在運維工作中總結整理而成的，部分 SQL 來源于各個網站，但現在不知道具體來源哪個網站，如有侵權，可聯系我及時刪除。對于運維 DBA 來說可提高很大工作效率，當然如果你全部能夠背下來那就牛逼了，如果不能，建議收藏下來慢慢看，每條 SQL 的使用頻率都很高，肯定能夠幫助到你。

1、查詢表的主外鍵關聯

select a.CONSTRAINT_NAME,b.TABLE_NAME 引用表,b.COLUMN_NAME 引用列,c.TABLE_NAME 被引用表,c.COLUMN_NAME 被引用列 from
user_constraints a,
user_cons_columns b,
user_cons_columns c
where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
and a.R_CONSTRAINT_NAME=c.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and c.TABLE_NAME like 'T_PROD%';

2、查詢一段時間內 sql 單次執行時間

select sql_id,s.SQL_TEXT,s.ELAPSED_TIME/s.EXECUTIONS
from v$sqlstats s
where s.LAST_ACTIVE_TIME>to_date('20220124 12:00:00','yyyymmdd hh24:mi:ss')
and s.LAST_ACTIVE_TIME>to_date('20220124 12:00:00','yyyymmdd hh24:mi:ss')
and s.EXECUTIONS>0
order by 3 desc;

--查詢一段時間內sql總等待時間
select event,a.sql_id,sql_text,sum(time_waited)
from v$active_session_history a,v$sql s
where a.SQL_ID=s.SQL_ID
and a.SAMPLE_TIME>to_date('20220124 12:00:00','yyyymmdd hh24:mi:ss')
and a.SAMPLE_TIME<to_date('20220124 12:30:00','yyyymmdd hh24:mi:ss')
group by event,a.sql_id,sql_text
order by 4 desc;

3、查詢歷史會話阻塞等待情況

select a.SAMPLE_TIME,a.SESSION_ID,a.SQL_ID,a.WAIT_TIME,a.BLOCKING_SESSION from v$active_session_history a
where a.MACHINE='41cb3c835bb1'
and a.SAMPLE_TIME>to_date('20220125 15:50:00','yyyymmdd hh24:mi:ss')
and a.SAMPLE_TIME<to_date('20220125 16:10:00','yyyymmdd hh24:mi:ss')
group by event
order by 4 desc;

4、查詢 undo 表空間使用較多的表

select a.segment_name, count(*)
? from dba_undo_extents a,
?????? (SELECT n.name name
????????? FROM V$session s, V$transaction t, V$rollstat r, v$rollname n
???????? WHERE s.saddr = t.ses_addr
?????????? AND t.xidusn = r.usn
?????????? AND r.usn = n.usn) b
?where a.segment_name = b.name
?? and a.status = 'ACTIVE'
?group by a.segment_name
?order by count(*);

5、查詢導致 undo 使用量和使用率高的會話

select b.sid,
?????? b.serial#,
?????? b.username,
?????? b.machine,
?????? b.program,
?????? a.xidusn as "UndoSegID",
?????? a.used_ublk * to_number(rtrim(p.value))/1024/1024 as Undo_mb,
?????? c.name,
?????? d.tablespace_name
? from v$transaction???? a,
?????? v$session???????? b,
?????? v$rollname??????? c,
?????? dba_rollback_segs d,
?????? v$parameter?????? p
?where a.addr = b.taddr
?? and a.xidusn = c.usn
?? and c.name = d.segment_name
?? and p.name = 'db_block_size'
?order by Undo_mb desc;
SELECT s.username,
?????? s.sid,
?????? s.serial#,
?????? t.xidusn,
?????? t.ubafil,
?????? t.ubablk,
?????? t.used_ublk,
?????? t.used_ublk /
?????? (select sum(blocks)
????????? from dba_data_files
???????? where tablespace_name =
?????????????? (select value from v$parameter where name = 'undo_tablespace')) * 100 as "使用率 %"
? FROM v$session s, v$transaction t
?WHERE s.saddr = t.ses_addr;

6、查看PGA使用率超過1G的會話

select s.sid,
?????? s.serial#,
?????? s.username,
?????? s.schemaname,
?????? s.machine,
?????? s.program,
?????? p.PGA_USED_MEM / 1024 / 1024 as "PGS Used(mb)",
?????? p.PGA_ALLOC_MEM / 1024 / 1024 as "PGS Allocate(mb)"
? from v$session s, v$process p
?where p.addr = s.paddr
?? and s.username is not null
?? and p.PGA_USED_MEM / 1024 / 1024 > 1142
?order by p.PGA_USED_MEM;

7、臨時表空間使用率

select tablespace_name,
?????? round(free_space / 1024 / 1024 / 1024, 2) "free(GB)",
?????? round(tablespace_size / 1024 / 1024 / 1024, 2) "total(GB)",
?????? round(nvl(free_space, 0) * 100 / tablespace_size, 3) "Free percent"
? from dba_temp_free_space;

8、使用臨時表空間排序的會話

select se.username,
?????? se.sid,
?????? se.serial#,
?????? se.machine,
?????? se.program,
?????? su.extents,
?????? su.blocks * to_number(rtrim(p.value)) / 1024 / 1024 as Space_mb,
?????? tablespace,
?????? segtype,
?????? s.sql_text
? from v$sort_usage su, v$parameter p, v$session se, v$sql s
?where p.name = 'db_block_size'
?? and s.HASH_VALUE = su.SQLHASH
?? and s.ADDRESS = su.SQLADDR
?order by Space_mb desc;

9、占用臨時表空間的會話

select s.sid,
?????? s.serial#,
?????? s.status,
?????? s.machine,
?????? s.program,
?????? t.username,
?????? t.sql_id,
?????? t.TABLESPACE,
?????? t.SEGTYPE,
?????? blocks
? from gv$session s, gv$tempseg_usage t
?where s.SADDR = t.SESSION_ADDR
?? and s.serial# = t.session_num
?? and s.INST_ID = t.inst_id;

10、檢查鎖表會話ID和對應操作系統進程號

SELECT l.session_id sid,
?????? s.serial#,
?????? l.locked_mode,
?????? l.oracle_username,
?????? l.os_user_name,
?????? s.machine,
?????? s.terminal,
?????? o.object_name,
?????? s.logon_time,
?????? p.spid
? FROM v$locked_object l, all_objects o, gv$session s, v$process p
?WHERE l.object_id = o.object_id
?? AND l.session_id = s.sid
?? AND s.PADDR = p.ADDR
?ORDER BY sid, s.serial#;


--使用spid查詢相應machine的IP和進程啟動時間：
netstat -anp |grep spid
ps auxw|head -1
ps auxw|grep SPID
ps –ef | grep spid

11、查詢導致鎖的會話或進程sql

select s.sql_text,s.sql_id
? from v$sql s, v$session se, v$locked_object l
?where s.hash_value = se.SQL_HASH_VALUE
?? and se.sid = l.session_id；
select username, sql_text, machine, osuser
? from v$session a, v$sqltext_with_newlines b
?where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value) =
?????? b.hash_value
?? and a.sid = &sid
?order by piece;
select ss.SID,
?????? pr.SPID,
?????? ss.action,
?????? sa.SQL_FULLTEXT,
?????? ss.machine,
?????? ss.PROGRAM,
?????? ss.SERIAL#,
?????? ss.USERNAME,
?????? ss.STATUS,
?????? ss.OSUSER,
?????? ss.last_call_et
? from v$process pr, v$session ss, v$sqlarea sa
?where ss.status = 'ACTIVE'
?? and ss.username is not null
?? and pr.ADDR = ss.PADDR
?? and ss.SQL_ADDRESS = sa.ADDRESS
?? and ss.SQL_HASH_VALUE = sa.HASH_VALUE
?? and pr.spid = &spid;

12、查詢長時間鎖表的會話

select s.sid,
s.username,
s.serial#,
s.INST_ID,
'alter system disconnect session '''||s.sid||','||s.serial#||',@'||s.INST_ID||''' immediate;',
s.EVENT,
s.machine,
s.program,
s.sql_id,
l.ctime,
l.type,
l.lmode,
l.request,
o.object_name,
o.object_type
from gv$session s, gv$locked_object lo, gv$lock l, dba_objects o
where s.sid = l.sid
and l.sid = lo.SESSION_ID
and lo.OBJECT_ID = o.object_id
and s.status='ACTIVE'
and l.type in ('TX', 'TM')
and s.USERNAME is not null 
and s.USERNAME<>'SYS'
and ctime > 600;

13、殺Session

select 'alter system disconnect session '''||sid||','||serial#||''' immediate;' from v$session where username='BLUESKY';
alter system disconnect session 'sid,serial#' immediate;
--如果遇到RAC環境，一定要用gv$session來查，并且執行
alter system disconnect session 'sid,serial#'? immediate
--要到RAC對應的實例上去執行

14、查詢sql執行計劃

select * from table(dbms_xplan.display_awr('&&sql'));
select a.hash_value,a.* from v$sql a where sql_id='&sql_id'
select * from table(dbms_xplan.display_cursor(2729381371,0,'advanced'));
select * from table(dbms_xplan.display_awr('91tw3s78z14k3'))；
含順序的
select * from table(xplan.display_cursor('9bd10aujay3gv',0,'advanced'));
不過要先創建xplan包，再執行
SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;
SQL> grant execute on sys.xplan to public;

15、查詢數據文件高水位線和最低可Resize值

select c.tablespace_name,
?????? a.file#,
?????? a.name,
?????? a.bytes / 1024 / 1024 CurrentMB,
?????? ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
?????? (a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
?????? 'alter database datafile ''' || a.name || ''' resize ' ||
?????? ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD
? from v$datafile a,
?????? (select file_id, max(block_id + blocks - 1) HWM
????????? from dba_extents
???????? group by file_id) b,
?????? dba_data_files c
?where a.file# = b.file_id(+)
?? and (a.bytes - HWM * block_size) > 0
?? and a.file# = c.file_id
?order by 2;

16、查看數據庫用戶權限

select * from sys.dba_role_privs where granted_role='XXX';

17、每日歸檔量查詢

SELECT SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 AS "Size(M)",
?????? TRUNC(completion_time)
? FROM v$archived_log
?GROUP BY TRUNC(completion_time);


--三日內歸檔切換頻率查詢：
select sequence#,
?????? to_char(first_time, 'yyyymmdd_hh24:mi:ss') firsttime,
?????? round((first_time - lag(first_time) over(order by first_time)) * 24 * 60,2) minutes
? from v$log_history
?where first_time > sysdate - 3
?order by first_time desc;

18、System表空間使用率高

通常由于記錄審計信息造成

truncate table sys.aud$

19、Oracle 監聽日志 listener.log 達到4G

-- listener 日志將無法再被記錄，同時 listener 也會變得不穩定
lsnrctl set log_status off;
mv listener.log listener.log.1;
lsnrctl set log_status on;
或
set current_listener XXXX
set log_file XXX
save_config

20、監聽夯死時收集狀態

lsnrctl status XXX
--查看是否長期出現監聽的子進程及其pid
ps -ef|grep tnslsnr
--對目標監聽進程和子進程，收集至少2次進程堆棧
pstack? <listener_pid>
--收集strace的輸出
strace -frT -o /tmp/strace-lsnr.log -p?<listener_pid>

21、下線 Oracle Job

Begin
dbms_job.broken(43,true);
commit;
end;
/

Job相關試圖：
dba_scheduler_running_jobs
dba_jobs(_running)

22、查詢碎片程度高的表和索引

SELECT TABLE_NAME,
?????? (BLOCKS * 8192 / 1024 / 1024) "使用大小M",
?????? (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) "實際大小M",
?????? round((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
???????????? (BLOCKS * 8192 / 1024 / 1024),
???????????? 3) * 100 || '%' "實際使用率%"
? FROM USER_TABLES
?where blocks > 100
?? and (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
?????? (BLOCKS * 8192 / 1024 / 1024) < 0.3
?order by (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
????????? (BLOCKS * 8192 / 1024 / 1024) desc;

select name,
?????? del_lf_rows,
?????? lf_rows,
?????? round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) || '%' frag_pct
? from index_stats
?where round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) > 30;

23、查詢當前會話進程分配使用的pga大小：

select round(sum(pga_used_mem) / 1024 / 1024, 0) total_used_M,
?????? round(sum(pga_used_mem) / count(1) / 1024 / 1024, 0) avg_used_M,
?????? round(sum(pga_alloc_mem) / 1024 / 1024, 0) total_alloc_M,
?????? round(sum(pga_alloc_mem) / count(1) / 1024 / 1024, 0) avg_alloc_M
? from v$process;

24、當前記錄的等待事件相關會話數：

select event,
?????? sum(decode(wait_time, 0, 0, 1)) "之前等待會話數",
?????? sum(decode(wait_time, 0, 1, 0)) "正在等待會話數",
?????? count(*)
? from v$session_wait
?group by event
?order by 4 desc;

25、查看閃回區\快速恢復區空間使用率

select sum(percent_space_used)||'%' "已使用空間比例" from V$RECOVERY_AREA_USAGE;

26、查看表空間可用百分比

select a.tablespace_name,b.total / 1024 / 1024 / 1024 total_gb,
a.free/1024/1024/1024 free_gb,
ROUND((total - free) / total, 4) * 100 "使用率%"
from (select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes) total
from dba_data_files
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

--查看ASM磁盤組使用率
select name,
?????? round(total_mb / 1024) "總容量",
?????? round(free_mb / 2) "空閑空間",
?????? round(((total_mb - free_mb) / total_mb) * 100) "使用率"
? from gv$asm_diskgroup;

27、綁定變量相關 SQL

select sql_id, FORCE_MATCHING_SIGNATURE, sql_text
from v$SQL
where FORCE_MATCHING_SIGNATURE in
(select /*+ unnest */
FORCE_MATCHING_SIGNATURE
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 10);

28、查詢產生熱塊較多的對象

SELECT e.owner, e.segment_name, e.segment_type, b.tch
? FROM dba_extents e,
?????? (SELECT *
????????? FROM (SELECT dbarfil, dbablk, tch
????????????????? FROM x$bh
???????????????? ORDER BY tch DESC)
???????? WHERE ROWNUM < 11) b
?WHERE e.relative_fno = b.dbarfil
?? AND e.block_id <= b.dbablk
?? AND e.block_id + e.blocks > b.dbablk;

29、查詢7天的db time

WITH sysstat AS
?(select 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, 1) over(order by ss.snap_id) b_value
??? from dba_hist_sysstat ss, dba_hist_snapshot sn
?? where trunc(sn.begin_interval_time) >= sysdate - 7
???? 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.instance_number = (select instance_number from v$instance)
???? and ss.stat_name = 'DB time')
select to_char(BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') ||
?????? to_char(END_INTERVAL_TIME, ' hh24:mi') date_time,
?????? stat_name,
?????? round((e_value - nvl(b_value, 0)) /
???????????? (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 +
???????????? extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 +
???????????? extract(minute from(end_interval_time - begin_interval_time)) * 60 +
???????????? extract(second from(end_interval_time - begin_interval_time))),
???????????? 0) per_sec
? from sysstat
?where (e_value - nvl(b_value, 0)) > 0
?? and nvl(b_value, 0) > 0;

30、導出 AWR 報告的SQL語句

select * from dba_hist_snapshot
select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid))
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid));

31、自動定時任務調整

col WINDOW_NAME for a15
col REPEAT_INTERVAL for a60
col DURATION for a30
set linesize 120

SELECT t1.window_name, t1.repeat_interval, t1.duration
FROM dba_scheduler_windows t1, dba_scheduler_wingroup_members t2
WHERE t1.window_name = t2.window_name
AND t2.window_group_name IN
('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');

WINDOW_NAME ????REPEAT_INTERVAL ?????????????????????????????????????????????DURATION
--------------- ------------------------------------------------------------ ------------------------------
MONDAY_WINDOW ??freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 ???????+000 04:00:00

TUESDAY_WINDOW ?freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 ???????+000 04:00:00

WEDNESDAY_WINDO freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 ???????+000 04:00:00

THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 ???????+000 04:00:00

FRIDAY_WINDOW ??freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 ???????+000 04:00:00

SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 ????????+000 20:00:00

SUNDAY_WINDOW ??freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 ????????+000 20:00:00


--備注：
#freq=daily：每天收集；
#;byday=SUN：一周之內的星期，例如這里是星期日；
#byhour=22?：每天的時間點時，這里是22時；
#byminute=0：每天的的時間點分，這里是0分，則第1分；
#bysecond=0：每天的時間點秒，這里是0秒，則第1秒；
#+000 20:00:00 ：表示收集信息的時間區間長，這里表示20小時。

---修改自動收集統計信息計劃任務時間：
--首先停止原來計劃;

BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."THURSDAY_WINDOW"');
end;
/

BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."MONDAY_WINDOW"');
end;
/

BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."TUESDAY_WINDOW"');
end;
/

BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."WEDNESDAY_WINDOW"');
end;
/

BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."FRIDAY_WINDOW"');
end;
/

BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."SATURDAY_WINDOW"');
end;
/

BEGIN
DBMS_SCHEDULER.DISABLE(
name => '"SYS"."SUNDAY_WINDOW"');
end;
/
?
--修改計劃任務的執行時間：

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."THURSDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value =>
'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');
end;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."MONDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value =>
'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');
end;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."TUESDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value =>
'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');
end;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."WEDNESDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value =>
'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');
end;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."FRIDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value =>
'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');
end;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."SATURDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value =>
'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');
end;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."SUNDAY_WINDOW"',
attribute => 'REPEAT_INTERVAL',
value =>
'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');
end;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."SATURDAY_WINDOW"',
attribute => 'DURATION',
value =>
'+000 04:00:00');
end;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"SYS"."SUNDAY_WINDOW"',
attribute => 'DURATION',
value =>
'+000 04:00:00');
end;
/

--啟用新的計劃任務的執行時間：?

BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."THURSDAY_WINDOW"');
end;
/

BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."MONDAY_WINDOW"');
end;
/

BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."TUESDAY_WINDOW"');
end;
/
BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."WEDNESDAY_WINDOW"');
end;
/

BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."FRIDAY_WINDOW"');
end;
/

BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."SATURDAY_WINDOW"');
end;
/

BEGIN
DBMS_SCHEDULER.ENABLE(
name => '"SYS"."SUNDAY_WINDOW"');
end;
/

禁用 SQL TUNING TASK

BEGIN
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

BEGIN
dbms_auto_task_admin.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/

32.非常詳細的查看表空間使用率

SET PAGESIZE 9999 LINESIZE 180;
TTI 'Tablespace Usage Status'
COL TABLESPACE_NAME FOR A20;
COL TBS_MAX_SIZE FOR 99999.99;
COL TABLESPACE_SIZE FOR 99999.99;
COL TBS_AVABLE_SIZE FOR 999999.99;
COL "USED_RATE(%)" FOR A16;
COL "ACT_USED_RATE(%)" FOR A16;
COL "FREE_SIZE(GB)" FOR 99999999.99;

SELECT  UPPER(F.TABLESPACE_NAME)                           AS "TABLESPACE_NAME",
        ROUND(D.MAX_BYTES,2)                               AS "TBS_MAX_SIZE" ,
        ROUND(D.AVAILB_BYTES ,2)                           AS "ACT_TABLESPACE_SIZE",
        ROUND((D.AVAILB_BYTES - F.USED_BYTES),2)           AS "TBS_USED_SIZE",
        ROUND(F.USED_BYTES, 2)                             AS "FREE_SIZE(GB)",
        TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,
                     2),
               '999.99')                                   AS "USED_RATE(%)",
        TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES)/D.MAX_BYTES*100,
                     2),
               '999.99')                                   AS "ACT_USED_RATE(%)",
        ROUND(D.MAX_BYTES - D.AVAILB_BYTES +USED_BYTES,2)  AS "TBS_AVABLE_SIZE"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6)  AVAILB_BYTES,
               ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6)   MAX_BYTES
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY "ACT_USED_RATE(%)" DESC;

vim get_tablespace_used_v3.sql

set pagesize 1000 linesize 180
tti 'Tablespace Usage Status'
col "TOTAL(GB)" for 99,999,999.999
col "USAGE(GB)" for 99,999,999.999
col "FREE(GB)" for 99,999,999.999 
col "EXTENSIBLE(GB)" for 99,999,999.999
col "MAX_SIZE(GB)" for 99,999,999.999
col "FREE PCT %" for 999.99
col "USED PCT OF MAX %" for 999.99
col "NO_AXF_NUM" for 9999
col "AXF_NUM" for 999
select d.tablespace_name "TBS_NAME"
      ,d.contents "TYPE"
      ,nvl(a.bytes /1024/1024/1024,0) "TOTAL(GB)"
      ,nvl(a.bytes - nvl(f.bytes,0),0)/1024/1024/1024 "USAGE(GB)"
      ,nvl(f.bytes,0)/1024/1024/1024 "FREE(GB)"
      ,nvl((a.bytes - nvl(f.bytes,0))/a.bytes * 100,0) "FREE PCT %"
      ,nvl(a.ARTACAK,0)/1024/1024/1024 "EXTENSIBLE(GB)"
      ,nvl(a.MAX_BYTES,0)/1024/1024/1024 "MAX_SIZE(GB)"
      ,nvl((a.bytes - nvl(f.bytes,0))/ (a.bytes + nvl(a.ARTACAK,0)) * 100,0) "USED PCT OF MAX %"
      ,a.NO_AXF_NUM
      ,a.AXF_NUM
from sys.dba_tablespaces d,
(select tablespace_name
       ,sum(bytes) bytes
       ,sum(decode(autoextensible,'YES',maxbytes - bytes,0 )) ARTACAK
       ,count(decode(autoextensible,'NO',0))  NO_AXF_NUM
       ,count(decode(autoextensible,'YES',0)) AXF_NUM
       ,sum(decode(maxbytes, 0, BYTES, maxbytes))   MAX_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 like 'LOCAL'and d.contents like 'TEMPORARY')
union all
select d.tablespace_name "TBS_NAME"
      ,d.contents "TYPE"
      ,nvl(a.bytes /1024/1024/1024,0) "TOTAL(GB)"
      ,nvl(t.bytes,0)/1024/1024/1024 "USAGE(GB)"
      ,nvl(a.bytes - nvl(t.bytes,0),0)/1024/1024/1024 "FREE(GB)"
      ,nvl(t.bytes/a.bytes * 100,0) "FREE PCT %"
      ,nvl(a.ARTACAK,0)/1024/1024/1024 "EXTENSIBLE(GB)"
      ,nvl(a.MAX_BYTES,0)/1024/1024/1024 "MAX_SIZE(GB)"
      ,nvl(t.bytes/(a.bytes + nvl(a.ARTACAK,0)) * 100,0) "USED PCT OF MAX %"
      ,a.NO_AXF_NUM
      ,a.AXF_NUM
from sys.dba_tablespaces d,
(select tablespace_name
       ,sum(bytes) bytes
       ,sum(decode(autoextensible,'YES',MAXbytes - bytes,0 )) ARTACAK
       ,count(decode(autoextensible,'NO',0)) NO_AXF_NUM
       ,count(decode(autoextensible,'YES',0)) AXF_NUM
       ,sum(decode(maxbytes, 0, BYTES, maxbytes))   MAX_BYTES
from dba_temp_files
group by tablespace_name
) a,
(select tablespace_name
      , sum(bytes_used) bytes 
from v$temp_extent_pool
group by tablespace_name
) t
where d.tablespace_name = a.tablespace_name(+)
  and d.tablespace_name = t.tablespace_name(+)
  and d.extent_management like 'LOCAL'
  and d.contents like 'TEMPORARY%'
order by 6 desc;

33.檢查過去7天表和索引的變化情況（輸入時間和大寫用戶名）

SELECT *
FROM ( SELECT c.TABLESPACE_NAME,
c.segment_name,
b.object_type,
ROUND (SUM (space_used_delta) / 1024 / 1024, 2) "Growth (MB)"
FROM dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
WHERE begin_interval_time > TRUNC (SYSDATE) - &days_back
AND sn.snap_id = a.snap_id
AND b.object_id = a.obj#
AND b.owner = c.owner
AND b.object_name = c.segment_name
AND c.owner = '&SCHEMANAME'
GROUP BY c.TABLESPACE_NAME, c.segment_name, b.object_type)
ORDER BY 1,4 ASC;

34.監控每個TS的變化量

SELECT TO_CHAR (sp.begin_interval_time,'YYYY-MM-DD') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
AND TS.tsname='&TBS_NAME'
GROUP BY TO_CHAR (sp.begin_interval_time,'YYYY-MM-DD'), ts.tsname
ORDER BY days ;

35. 查 Oracle TPS

select instance_number,
          metric_unit,
           trunc(begin_time) time,
           sum(average*3600) "Transactions Per Day",   --一天的平均總和
           avg(average) "Transactions Per Second"      --某個時間段的平均值
      from DBA_HIST_SYSMETRIC_SUMMARY
     where metric_unit = 'Transactions Per Second'
       and begin_time >=
           to_date('2022-04-18 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
       and begin_time < to_date('2022-04-18 16:00:00', 'yyyy-mm-dd hh24:mi:ss')
    group by instance_number, metric_unit, trunc(begin_time)
order by instance_number;

36.查看正在執行的 SQL

set echo off feedback off timing off pause off
set pages 100 lines 155 trimspool on trimout on space 1 recsep off
col username format a13
col prog format a10 trunc
col sql_text format a40 trunc
col sid format a12
col sql_id format a16
col child for 99999
col execs format 9999999
col sqlprofile format a22
col avg_ela for 999999.99
col last_ela for 999999
col event format a20
select /*+ rule */
       sid||','||serial# sid,
       substr(a.event,1,15) event,
       b.sql_id||','||child_number sql_id,
       plan_hash_value,
       executions execs,
       (elapsed_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_ela,
       last_call_et last_ela,
       sql_text
from v$session a, v$sql b
where status = 'ACTIVE'
and username is not null
and a.sql_id = b.sql_id
and a.sql_child_number = b.child_number
and sql_text not like '%from v$session a, v$sql b%'
and a.program not like '%(P%)'
order by plan_hash_value,last_call_et,sql_id, sql_child_number;

字段含義：
SID：為sid和serial#的值。  EVENT：等待事件。 SQL_ID：為sql_id和child_number的值。
PLAN_HASH_VALUE：sql執行計劃的PLAN_HASH_VALUE。EXECS：執行次數。 AVG_ELA：平均執行時間。LAST_ELA：本次已經執行了多久。   SQL_TEXT：sql文本。

37.查看 SQL 的歷史執行情況

set echo off feedback off timing off pause off verify off
set pages 100 lines 132 trimspool on trimout on space 1 recsep off
accept v_sqlid prompt 'Enter sqlid(default xxx): ' default 'xxxxxxx'
accept v_days prompt 'Enter Days ago(default 7): ' default 7
col execs for 999,999,999
col etime for 999,999,999.9
col avg_elas for 999,999.999
col avg_cpus for 999,999.999
col avg_lios for 999,999,999.9
col avg_pios for 9,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select  to_char(begin_time,'mmdd hh24:mi') btime,
        sql_id,
        plan_hash_value,
        sum(execs) execs,
        sum(etime)/sum(decode(execs,0,1,execs))    avg_elas,
        sum(cpu_time)/sum(decode(execs,0,1,execs)) avg_cpus,
        sum(lio)/sum(decode(execs,0,1,execs))      avg_lios,
        sum(pio)/sum(decode(execs,0,1,execs))      avg_pios
from
(select ss.instance_number node,
        begin_interval_time begin_time,
        sql_id,
        plan_hash_value,
        nvl(executions_delta,0) execs,
        elapsed_time_delta/1000000 etime,
        (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
        buffer_gets_delta lio,
        disk_reads_delta pio,
        cpu_time_delta/1000000 cpu_time,
        (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
        (cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time
  from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
  where sql_id = '&&v_sqlid'
  and ss.snap_id = s.snap_id
  and ss.instance_number = S.instance_number
  and elapsed_time_delta>0
)
where begin_time >= sysdate-&&v_days
group by to_char(begin_time,'mmdd hh24:mi'),sql_id, plan_hash_value
order by 1
/
undefine v_sqlid
undefine v_days

字段含義：
BTIME：快照時間點。
SQL_ID：sql_id
PLAN_HASH_VALUE：sql執行計劃的PLAN_HASH_VALUE，如果這里為空，表示遵循上一個PLAN_HASH_VALUE。
EXECS：執行次數。
AVG_ELAS：平均執行時間。
AVG_CPUS：平均cpu時間
AVG_LIOS：平均邏輯讀數量

38.查看索引創建速度

set line 250
col ssid format 9999 heading SID;
col opname format a15 TRUNCATE ;
col target format a28 TRUNCATE ;
col es format 99999.9 Heading "Time|Ran";
col tr format 99999.90 Heading "Time|Left";
col pct format 999.90 Heading "PCT";
col RATE FORMAT a6 truncate Heading "I/O |Rate/m" ;
col program format a20 TRUNCATE;
col MACHINE format a20 truncate;
select 
L.sid ssid, 
substr(OPNAME,1,15) opname,
target, 
trunc((sofar/totalwork)*100) pct, 
to_char(60*sofar*8192/(24*60*(last_update_time - start_time))/1024/1024/60, '9999.0') Rate,
elapsed_seconds/60 es,
time_remaining/60 tr,PROGRAM,MACHINE
from v$session_longops L,V$SESSION S
where time_remaining > 0 AND L.SID=S.SID
order by start_time;

39.selectivity <5 一般選擇性小于5% 就屬于選擇性差

select a.OWNER,
       a.INDEX_NAME,
       a.TABLE_NAME,
       a.DISTINCT_KEYS Cardinality,
       a.NUM_ROWS,     
       round(a.DISTINCT_KEYS / NUM_ROWS * 100, 2) selectivity
  from dba_ind_statistics a
 where a.NUM_ROWS > 0
   and round(a.DISTINCT_KEYS / NUM_ROWS * 100, 2) <= 5
   and A.OWNER = upper('&owner');
   
--如果統計信息有可能不是最新的 最好使用下面的語句

select table_name,index_name,round(distinct_keys/num_rows * 100, 2) selectivity from user_indexes;

40.如何查看列的選擇性和基數呢？

select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = upper('&owner')
and a.table_name = upper('&table_name')
and a.column_name = upper('&column_name');

歡迎關注我的公眾號【JiekeXu DBA之路】，第一時間一起學習新知識！
————————————————————————————
公眾號：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天輪：http://m.sunline.cc/u/4347
騰訊云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————