一线运维 dba 五年经验常用 sql 大全（三） -m6米乐安卓版下载

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  
--收集strace的输出
strace -frt -o /tmp/strace-lsnr.log -p 

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
————————————————————————————