来源 |公众号 JiekeXu DBA之路
如需转载请联系授权
大家好,我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看一线运维 DBA 五年经验常用 SQL 大全(三)
本文作为常用 SQL 系列的第三篇,本文涉及到的 SQL 及相关命令均是在运维工作中总结整理而成的,对于运维 DBA 来说可提高很大的工作效率,值得收藏下来慢慢看。
前面两篇 SQL 常用命令链接,感兴趣的可点击下方超链接查看,
SQL 大全一 https://www.modb.pro/doc/22598 SQL 大全二 https://www.modb.pro/db/45337
墨天轮链接:https://www.modb.pro/doc/91589
Oracle DBA 日常维护 SQL 脚本大全(收藏版) https://www.modb.pro/db/44364
本文 SQL 均是在运维工作中总结整理而成的,部分 SQL 来源于各个网站,但现在不知道具体来源哪个网站,如有侵权,可联系我及时删除。对于运维 DBA 来说可提高很大工作效率,当然如果你全部能够背下来那就牛逼了,如果不能,建议收藏下来慢慢看,每条 SQL 的使用频率都很高,肯定能够帮助到你。
目 录
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%';
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;
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;
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(*);
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;
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;
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;
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;
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;
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
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;
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;
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对应的实例上去执行
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;
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;
select * from sys.dba_role_privs where granted_role='XXX';
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;
通常由于记录审计信息造成
truncate table sys.aud$;
-- 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
lsnrctl status XXX
--查看是否长期出现监听的子进程及其pid
ps -ef|grep tnslsnr
--对目标监听进程和子进程,收集至少2次进程堆栈
pstack <listener_pid>
--收集strace的输出
strace -frT -o /tmp/strace-lsnr.log -p <listener_pid>
Begin
dbms_job.broken(43,true);
commit;
end;
/
Job相关试图:
dba_scheduler_running_jobs
dba_jobs(_running)
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;
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;
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;
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;
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);
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;
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;
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;
/
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;
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;
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 ;
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;
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:平均逻辑读数量
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;
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 ————————————————————————————