其他
一线运维 DBA 五年经验常用 SQL 大全（三）
作者 | JiekeXu
来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）
如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)
大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看一线运维 DBA 五年经验常用 SQL 大全（三），欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！
本文作为常用 SQL 系列的第三篇，本文涉及到的 SQL 及相关命令均是在运维工作中总结整理而成的，对于运维 DBA 来说可提高很大的工作效率，值得收藏下来慢慢看。
前面两篇 SQL 常用命令链接，感兴趣的可点击下方超链接查看，
SQL 大全一 https://www.modb.pro/doc/22598
SQL 大全二 https://www.modb.pro/db/45337
由于编辑器显示原因不太友好，附本文 PDF 下载地址：
可直接在公众号后台回复【SQL大全三】获取
墨天轮链接：https://www.modb.pro/doc/91589
Oracle DBA 日常维护 SQL 脚本大全（收藏版） https://www.modb.pro/db/44364
本文 SQL 均是在运维工作中总结整理而成的，部分 SQL
来源于各个网站，但现在不知道具体来源哪个网站，如有侵权，可联系我及时删除。对于运维 DBA
来说可提高很大工作效率，当然如果你全部能够背下来那就牛逼了，如果不能，建议收藏下来慢慢看，每条 SQL 的使用频率都很高，肯定能够帮助到你。
目 录
1、查询表的主外键关联
2、查询一段时间内 sql 单次执行时间
3、查询历史会话阻塞等待情况
4、查询 undo 表空间使用较多的表
5、查询导致 undo 使用量和使用率高的会话
6、查看PGA使用率超过1G的会话
7、临时表空间使用率
8、使用临时表空间排序的会话
9、占用临时表空间的会话
10、检查锁表会话ID和对应操作系统进程号
11、查询导致锁的会话或进程sql
12、查询长时间锁表的会话
13、杀Session
14、查询sql执行计划
15、查询数据文件高水位线和最低可Resize值
16、查看数据库用户权限
17、每日归档量查询
18、System表空间使用率高
19、Oracle 监听日志 listener.log 达到4G
20、监听夯死时收集状态
21、下线 Oracle Job
22、查询碎片程度高的表和索引
23、查询当前会话进程分配使用的pga大小：
24、当前记录的等待事件相关会话数：
25、查看闪回区\快速恢复区空间使用率
26、查看表空间可用百分比
27、绑定变量相关 SQL
28、查询产生热块较多的对象
29、查询7天的db time
30、导出 AWR 报告的SQL语句
31、自动定时任务调整
32、非常详细的查看表空间使用率
33、检查过去7天表和索引的变化情况（输入时间和大写用户名）
34、监控每个TS的变化量
35、查 Oracle TPS
36、查看正在执行的 SQL
37、查看 SQL 的历史执行情况
38、查看索引创建速度
39、selectivity <5 一般选择性小于5% 就属于选择性差
40、如何查看列的选择性和基数呢？
1、查询表的主外键关联
select a.CONSTRAINT_NAME,b.TABLE_NAME 引用表,b.COLUMN_NAME 引用列,c.TABLE_NAME 被引用表,c.COLUMN_NAME 被引用列 fromuser_constraints a,user_cons_columns b,user_cons_columns cwhere a.CONSTRAINT_NAME=b.CONSTRAINT_NAMEand a.R_CONSTRAINT_NAME=c.CONSTRAINT_NAMEand a.CONSTRAINT_TYPE='R'and c.TABLE_NAME like 'T_PROD%';2、查询一段时间内 sql 单次执行时间
select sql_id,s.SQL_TEXT,s.ELAPSED_TIME/s.EXECUTIONSfrom v$sqlstats swhere 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>0order by 3 desc;
--查询一段时间内sql总等待时间select event,a.sql_id,sql_text,sum(time_waited)from v$active_session_history a,v$sql swhere a.SQL_ID=s.SQL_IDand 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_textorder 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 awhere 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 eventorder 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 spidps auxw|head -1ps auxw|grep SPIDps –ef | grep spid11、查询导致锁的会话或进程 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_typefrom gv$session s, gv$locked_object lo, gv$lock l, dba_objects owhere s.sid = l.sidand l.sid = lo.SESSION_IDand lo.OBJECT_ID = o.object_idand 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 XXXXset log_file XXXsave_config20、监听夯死时收集状态
lsnrctl status XXX--查看是否长期出现监听的子进程及其pidps -ef|grep tnslsnr--对目标监听进程和子进程，收集至少2次进程堆栈pstack <listener_pid>--收集strace的输出strace -frT -o /tmp/strace-lsnr.log -p <listener_pid>21、下线 Oracle Job
Begindbms_job.broken(43,true);commit;end;/
Job相关试图：dba_scheduler_running_jobsdba_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;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) freefrom dba_free_spacegroup by tablespace_name) a,(select tablespace_name, sum(bytes) totalfrom dba_data_filesgroup by tablespace_name) bwhere a.tablespace_name = b.tablespace_nameorder 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_textfrom v$SQLwhere FORCE_MATCHING_SIGNATURE in(select /*+ unnest */FORCE_MATCHING_SIGNATUREfrom v$sqlwhere FORCE_MATCHING_SIGNATURE > 0and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATUREgroup by FORCE_MATCHING_SIGNATUREhaving 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_snapshotselect * 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));col WINDOW_NAME for a15col REPEAT_INTERVAL for a60col DURATION for a30set linesize 120
SELECT t1.window_name, t1.repeat_interval, t1.durationFROM dba_scheduler_windows t1, dba_scheduler_wingroup_members t2WHERE t1.window_name = t2.window_nameAND 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:00TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00WEDNESDAY_WINDO freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00SUNDAY_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小时。
---修改自动收集统计信息计划任务时间：--首先停止原来计划;
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."THURSDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."MONDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."TUESDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."WEDNESDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."FRIDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."SATURDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."SUNDAY_WINDOW"');end;/ --修改计划任务的执行时间：
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."THURSDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."MONDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."TUESDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."WEDNESDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."FRIDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."SATURDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."SUNDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."SATURDAY_WINDOW"',attribute => 'DURATION',value =>'+000 04:00:00');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."SUNDAY_WINDOW"',attribute => 'DURATION',value =>'+000 04:00:00');end;/
--启用新的计划任务的执行时间：
BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."THURSDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."MONDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."TUESDAY_WINDOW"');end;/BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."WEDNESDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."FRIDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."SATURDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."SUNDAY_WINDOW"');end;/
禁用 SQL TUNING TASK
BEGINdbms_auto_task_admin.disable(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);END;/
BEGINdbms_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) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY "ACT_USED_RATE(%)" DESC;
vim get_tablespace_used_v3.sql
set pagesize 1000 linesize 180tti 'Tablespace Usage Status'col "TOTAL(GB)" for 99,999,999.999col "USAGE(GB)" for 99,999,999.999col "FREE(GB)" for 99,999,999.999 col "EXTENSIBLE(GB)" for 99,999,999.999col "MAX_SIZE(GB)" for 99,999,999.999col "FREE PCT %" for 999.99col "USED PCT OF MAX %" for 999.99col "NO_AXF_NUM" for 9999col "AXF_NUM" for 999select 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_NUMfrom 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_BYTESfrom dba_data_filesgroup by tablespace_name) a,(select tablespace_name ,sum(bytes) bytesfrom dba_free_spacegroup by tablespace_name) fwhere 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 allselect 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_NUMfrom 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_BYTESfrom dba_temp_filesgroup by tablespace_name) a,(select tablespace_name , sum(bytes_used) bytes from v$temp_extent_poolgroup by tablespace_name) twhere 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 cWHERE begin_interval_time > TRUNC (SYSDATE) - &days_backAND sn.snap_id = a.snap_idAND b.object_id = a.obj#AND b.owner = c.ownerAND b.object_name = c.segment_nameAND 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_MBFROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dtWHERE tsu.tablespace_id= ts.ts#AND tsu.snap_id = sp.snap_idAND ts.tsname = dt.tablespace_nameAND ts.tsname NOT IN ('SYSAUX','SYSTEM')AND TS.tsname='&TBS_NAME'GROUP BY TO_CHAR (sp.begin_interval_time,'YYYY-MM-DD'), ts.tsnameORDER 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 offset pages 100 lines 155 trimspool on trimout on space 1 recsep offcol username format a13col prog format a10 trunccol sql_text format a40 trunccol sid format a12col sql_id format a16col child for 99999col execs format 9999999col sqlprofile format a22col avg_ela for 999999.99col last_ela for 999999col event format a20select /*+ 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_textfrom v$session a, v$sql bwhere status = 'ACTIVE'and username is not nulland a.sql_id = b.sql_idand a.sql_child_number = b.child_numberand 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文本。set echo off feedback off timing off pause off verify offset pages 100 lines 132 trimspool on trimout on space 1 recsep offaccept v_sqlid prompt 'Enter sqlid(default xxx): ' default 'xxxxxxx'accept v_days prompt 'Enter Days ago(default 7): ' default 7col execs for 999,999,999col etime for 999,999,999.9col avg_elas for 999,999.999col avg_cpus for 999,999.999col avg_lios for 999,999,999.9col avg_pios for 9,999,999.9col begin_interval_time for a30col node for 99999break on plan_hash_value on startup_time skip 1select 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_piosfrom(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_daysgroup by to_char(begin_time,'mmdd hh24:mi'),sql_id, plan_hash_valueorder by 1/undefine v_sqlidundefine v_days
--字段含义：BTIME：快照时间点。SQL_ID：sql_idPLAN_HASH_VALUE：sql执行计划的PLAN_HASH_VALUE，如果这里为空，表示遵循上一个PLAN_HASH_VALUE。EXECS：执行次数。AVG_ELAS：平均执行时间。AVG_CPUS：平均cpu时间AVG_LIOS：平均逻辑读数量38、查看索引创建速度
set line 250col 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,MACHINEfrom v$session_longops L,V$SESSION Swhere time_remaining > 0 AND L.SID=S.SIDorder 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;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_bucketsfrom dba_tab_col_statistics a, dba_tables bwhere a.owner = b.ownerand a.table_name = b.table_nameand 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
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————