暂无图片
暂无图片
40
暂无图片
暂无图片
暂无图片
首页 / 一线运维 DBA 五年经验常用 SQL 大全（三）

一线运维 DBA 五年经验常用 SQL 大全（三）

原创 JiekeXu All China Database Union 2022-11-30
3521

本文作为常用 SQL 系列的第三篇，本文涉及到的 SQL 及相关命令均是在运维工作中总结整理而成的，对于运维 DBA 来说可提高很大的工作效率，值得收藏下来慢慢看。

作者：JiekeXu
来源 | JiekeXu DBA 之路（ID: JiekeXu_IT）
转载请联系授权 | (微信 ID：JiekeXu_DBA)

SQL 大全一 https://www.modb.pro/doc/22598
SQL 大全二 https://www.modb.pro/db/45337
Oracle DBA 日常维护 SQL 脚本大全（收藏版） https://www.modb.pro/db/44364

附本文 PDF 下载链接：https://www.modb.pro/doc/91589

本文 SQL 均是在运维工作中总结整理而成的，部分 SQL 来源于各个网站，但现在不知道具体来源哪个网站，如有侵权，可联系我及时删除。对于运维 DBA 来说可提高很大工作效率，当然如果你全部能够背下来那就牛逼了，如果不能，建议收藏下来慢慢看，每条 SQL 的使用频率都很高，肯定能够帮助到你。

图片.png

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
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
图片.png

墨力计划sql
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

JiekeXu DBA之路（共222篇）
JiekeXu：来自西北，90 后，获得 11g OCP、11g OCM 认证，墨天轮 MVP，墨天轮 2021 十大突出贡献人物之一，此专栏记录一个菜鸟 DBA 学习成长之路，欢迎关注公众号 JiekeXu DBA之路，一起学习进步。

评论