MOP 系列|MOP 三种主流数据库常用 SQL(一)

JiekeXu之路

发布于 2024-05-09 14:26:01

960

发布于 2024-05-09 14:26:01

前言

MOP 不用多说，指的就是 MySQL、Oracle、PostgreSQL 三种目前最主流的数据库，MOP 系列打算更新 MOP 三种数据库的索引知识、高可用架构及常用 SQL 语句等等，上面已经更新了 MOP 索引相关的文章，今天打算整理一下这三种数据库的常用 SQL 知识，但由于文章过长，今天分享 Oracle 篇。

本文 SQL 均是在运维工作中总结整理而成的,非个人独创，部分 SQL 来源于互联网，但现在已经不知道具体是来源哪个网站、哪个人，如有侵权，可联系我及时删除，谢谢！

1、按 Buffer Gets 降序排列 TOP SQL

set linesize 150
set pagesize 100
col sql_text form a50
SELECT * FROM
(SELECT SQL_ID,substr(sql_text,1,50) sql_text,trunc(ELAPSED_TIME/executions) ELAPSED_TIME_PER,
        executions,buffer_gets,trunc(buffer_gets/executions) gets_per        
   FROM V$SQLSTATS
  WHERE buffer_gets > 10000 and executions<>0
 ORDER BY buffer_gets DESC)
WHERE rownum <=20;

--注意：Elapsed time 的单位是微秒，一微秒等于一百万分之一秒。

2、按执行次数降序排列 TOP SQL

set linesize 150
set pagesize 100
col sql_text form a50
SELECT * FROM
(SELECT SQL_ID,substr(sql_text,1,50) sql_text,trunc(ELAPSED_TIME/executions) ELAPSED_TIME_PER,
        executions,rows_processed,trunc(rows_processed/executions) rows_per        
   FROM V$SQLSTATS
  WHERE executions> 100 and executions<>0
 ORDER BY executions DESC)
WHERE rownum <=20;

3、按解析次数降序排列 TOP SQL

set linesize 150
set pagesize 100
col sql_text form a50
SELECT * FROM
(SELECT SQL_ID,substr(sql_text,1,50) sql_text,trunc(ELAPSED_TIME/executions) ELAPSED_TIME_PER,
        executions,parse_calls     
   FROM V$SQLSTATS
  WHERE parse_calls> 100 and executions<>0
 ORDER BY parse_calls DESC)
WHERE rownum <=20;

4、按物理读降序排列 TOP SQL

set linesize 150
set pagesize 100
col sql_text form a50
SELECT * FROM
(SELECT SQL_ID,substr(sql_text,1,50) sql_text,trunc(ELAPSED_TIME/executions) ELAPSED_TIME_PER,
        executions,disk_reads,trunc(disk_reads/executions) reads_per        
   FROM V$SQLSTATS
  WHERE disk_reads> 1000 and executions<>0
 ORDER BY disk_reads DESC)
WHERE rownum <=20;

5、按使用内存降序排列 TOP SQL

set linesize 150
set pagesize 100
col sql_text form a50
SELECT * FROM
(SELECT SQL_ID,substr(sql_text,1,50) sql_text,trunc(ELAPSED_TIME/executions) ELAPSED_TIME_PER,
        executions,sharable_mem
   FROM V$SQLSTATS
  WHERE sharable_mem> 1048576 and executions<>0
 ORDER BY sharable_mem DESC)
WHERE rownum <=20;

6、根据单次执行中的 Buffer Gets 来查找 TOP SQL

set linesize 150
set pagesize 100
col sql_text form a50
SELECT * FROM
(SELECT SQL_ID,substr(sql_text,1,50) sql_text,trunc(ELAPSED_TIME/executions) ELAPSED_TIME_PER,
        executions,buffer_gets,trunc(buffer_gets/executions) gets_per        
   FROM V$SQLSTATS
  WHERE buffer_gets > 100 and executions<>0
 ORDER BY gets_per  DESC)
WHERE rownum <=20;

7、查询选择性高需要创建索引的列

select owner,
TABLE_NAME,
column_name,
num_rows,
Cardinality,
selectivity,
'Need index' as notice
from ( --选择性大于20%的列
select b.owner,
a.TABLE_NAME,
a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name 
and  b.num_rows > 0 
and round(a.num_distinct / b.num_rows * 100, 2)>20
and a.owner = '&OWNER'
and a.table_name = '&TAB_NAME'
)
where 
column_name not in (
--查询字段名是否存在索引
select column_name
 from dba_ind_columns
where table_owner = '&OWNER'
 and table_name = '&TAB_NAME'
 )
and column_name in
 (
--查询语句就可以查询出哪个表的哪个列出现在 where 条件中
select c.name
 from sys.col_usage$ u, sys.obj$ o, sys.col$ c
 where o.obj# = u.obj#
 and c.obj# = u.obj#
 and c.col# = u.intcol#
 and equality_preds+equijoin_preds+nonequijoin_preds+range_preds+like_preds+null_preds>0
/*equality_preds, ---等值过滤
equijoin_preds, ---等值 JOIN 比如 where a.id=b.id
nonequijoin_preds, ----不等 JOIN
range_preds, ----范围过滤次数 > >= < <= between and
like_preds, ----LIKE 过滤
null_preds, ----NULL 过滤*/
);

8、查找数据库中正在运行的 SQL

col inst_sid heading "INST_ID|:SID" format a7
col username format a10
col machine format a12
col sql_exec_start   heading "SQL|START|D HH:MM:SS" format a11
col sql_id format a13
col sql_text format a40
col event format a33
col wait_sec heading "WAIT|(SEC)" format 99999
set linesize 200

select ses.inst_id||chr(58)||ses.sid as inst_sid
   ,username
   ,(sysdate - sql_exec_start) day(1) to second(0) as sql_exec_start
   ,ses.sql_id
   ,substr(sql.sql_text,1,40) sql_text
   ,substr
      (case time_since_last_wait_micro
         when 0 then (case wait_class when 'Idle' then 'IDLE: '||event else event end)
         else 'ON CPU'
         end
      ,1,33) event
   ,(case time_since_last_wait_micro
      when 0 then wait_time_micro
      else time_since_last_wait_micro
      end) /1000000 wait_sec
from gv$session ses,gv$sqlstats sql 
where ses.inst_id||chr(58)||ses.sid <> sys_context ('USERENV','INSTANCE')||chr(58)||sys_context ('USERENV','SID')
   and username is not null
   and status='ACTIVE'
   and ses.sql_id=sql.sql_id (+)
order by sql_exec_start,
   username,ses.sid,
   ses.sql_id;

9、行锁监控

select sysdate,source_sid,source_sql_id,source_sql_text,blocking_sid,blocking_sql_id,blocking_sql_text
  from (select b.sid source_sid,d.sql_id source_sql_id,
               d.sql_text source_sql_text,a.sid blocking_sid,
               a.sql_id blocking_sql_id,e.sql_text blocking_sql_text,
               (select object_name
                  from dba_objects
                 where object_id = a.row_wait_obj#) object_name
          from v$session     a,
               v$session     b,
               v$transaction c,
               v$sqlarea     d,
               v$sqlarea     e
         where a.event = 'enq: TX - row lock contention'
           and a.blocking_session = b.sid
           and b.taddr = c.addr
           and to_date(c.start_time, 'mm/dd/yy hh24:mi:ss') = d.last_active_time
           and d.command_type in (2, 3, 6)
           and b.user# = d.parsing_schema_id
           and a.sql_id = e.sql_id)
 where instr(upper(source_sql_text), object_name) > 0;

--原文链接：https://blog.csdn.net/robinson1988/article/details/106204387

select distinct  'ALTER SYSTEM KILL SESSION ' || '''' || sid || ',' ||  serial# || ',@' || inst_id || ''''  ||' IMMEDIATE;' 
  from (select a.inst_id, a.sid, a.serial#,
               a.sql_id,
               a.event,
               a.status,
               connect_by_isleaf as isleaf,
               sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree,
               level as tree_level
          from gv$session a
         start with a.blocking_session is not null
        connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))
 where isleaf = 1;

10、SQL 执行信息

SELECT plan_hash_value,
       ROUND(SUM(elapsed_time)/SUM(executions)/1e6, 3) avg_et_secs,
       ROUND(SUM(cpu_time)/SUM(executions)/1e6, 3) avg_cpu_secs,
       ROUND(SUM(user_io_wait_time)/SUM(executions)/1e6, 3) avg_io_secs,
       ROUND(SUM(buffer_gets)/SUM(executions)) avg_buffer_gets,
       ROUND(SUM(disk_reads)/SUM(executions)) avg_disk_reads,
       ROUND(SUM(rows_processed)/SUM(executions)) avg_rows,
       SUM(executions) executions
  FROM gv$sql
 WHERE sql_id = TRIM('&sql_id')
   AND executions > 0
 GROUP BY
       plan_hash_value
 ORDER BY
       2, 3;

SELECT plan_hash_value,
       ROUND(SUM(elapsed_time_total)/SUM(executions_total)/1e6, 3) avg_et_secs,
       ROUND(SUM(cpu_time_total)/SUM(executions_total)/1e6, 3) avg_cpu_secs,
       ROUND(SUM(iowait_total)/SUM(executions_total)/1e6, 3) avg_io_secs,
       ROUND(SUM(buffer_gets_total)/SUM(executions_total)) avg_buffer_gets,
       ROUND(SUM(disk_reads_total)/SUM(executions_total)) avg_disk_reads,
       ROUND(SUM(rows_processed_total)/SUM(executions_total)) avg_rows,
       SUM(executions_total) executions
  FROM dba_hist_sqlstat
 WHERE sql_id = TRIM('&sql_id')
   AND executions_total > 0
 GROUP BY
       plan_hash_value
 ORDER BY
       2, 3;

11、SQL 历史执行信息

set lines 200
set pages 1000
col shijian for a12
col execu_d for 999999
col bg_d for 9999999999
col dr_d for 9999999999
col et_d for 99999999
col ct_d for 99999999
col io_time for 999999
col clus_time for 999999
col ap_time for 999999
col cc_time for 999999
col et_onetime for 999999

select to_char(b.END_INTERVAL_TIME, 'yyyymmddhh24') shijian,
       plan_hash_value,
       sum(a.EXECUTIONS_DELTA) execu_d,
       sum(a.BUFFER_GETS_DELTA) bg_d,
       sum(a.DISK_READS_DELTA) dr_d,
       sum(a.ELAPSED_TIME_DELTA / 1000000) et_d,
       sum(a.CPU_TIME_DELTA / 1000000) ct_d,
       sum(IOWAIT_DELTA / 1000000) io_time,
       sum(CLWAIT_DELTA / 1000000) clus_time,
       sum(APWAIT_DELTA / 1000000) ap_time,
       sum(ccwait_delta / 1000000) cc_time,
       round(sum(a.BUFFER_GETS_DELTA) /
             greatest(sum(a.EXECUTIONS_DELTA), 1)) get_onetime,
       round(sum(a.rows_processed_delta) /
             greatest(sum(a.EXECUTIONS_DELTA), 1)) rows_onetime,
       round(sum(a.ELAPSED_TIME_DELTA) / 1000 /
             greatest(sum(a.EXECUTIONS_DELTA), 1)) et_ms_once
  from dba_hist_sqlstat a, dba_hist_snapshot b
 where a.SNAP_ID = b.SNAP_ID
   and a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
   and a.sql_id = '&sql_id'
 group by to_char(b.END_INTERVAL_TIME, 'yyyymmddhh24'), plan_hash_value
 order by 1, 2;

12、查看表详细信息

set linesize 200 pagesize 1000
col column_name for a40
col segment_name new_value table_name noprint
col analyzed                      format a16
col owner                         format a16                                                                   
col partition_name                format a18
col index_name                    format a20
col column_name                   format a24
col segment_name                  format a24
col table_name                    format a24
col table_owner                   format a18
prompt "-------------------------"
prompt "segment_size"
prompt "-------------------------"
select owner,segment_name,sum(bytes)/1024/1024 size_m 
from dba_segments 
where segment_name =upper( '&segment_name') 
group by owner, segment_name;
prompt
prompt
prompt "-------------------------"
prompt "table_stats"
prompt "-------------------------"
select owner,
       table_name,
       num_rows,
       blocks,
       avg_row_len,
       partitioned,
       to_char(last_analyzed,'yyyymmdd hh24:mi:ss') as analyzed ,
       num_rows * avg_row_len / 1024 /1024 / 0.9 est_M 
from dba_tables where table_name = '&table_name'
order by 1,2;

prompt
prompt
prompt "----------------"
prompt "Indexes of table"
prompt "----------------"
col index_name format a36
col index_type format a12
col uniqueness format a12
col analyzed format a18
select owner, index_name,index_type,uniqueness,num_rows,
       to_char(last_analyzed,'yyyymmdd hh24:mi') as analyzed,
       status,partitioned,distinct_keys 
from dba_indexes 
where table_name = upper('&table_name')
order by 1,2;

prompt
prompt
prompt "----------------"
prompt "index columns"
prompt "----------------"

col index_owner format a18 
select index_owner, 
       index_name,
       column_name,
       column_position 
from dba_ind_columns 
where table_name = upper('&table_name')
order by 1,2,4;

prompt
prompt
prompt "------------------"
prompt "Index partition analyzed"
prompt "------------------"
select index_owner,
       index_name,
       partition_name,
       to_char(last_analyzed,'yyyymmdd hh24:mi:ss') as analyzed,
       distinct_keys,
       num_rows,
       status
from dba_ind_partitions
where index_name  in (select index_name from dba_part_indexes where table_name  = upper('&table_name'))
order by 1,2,3;


prompt
prompt "----------------"
prompt "index statistics"
prompt "----------------"
select owner,column_name,
       num_distinct,
       histogram,num_distinct,
       num_nulls,
       to_char(last_analyzed,'yyyymmdd hh24:mi') as analyzed 
from dba_tab_col_statistics 
where table_name = upper('&table_name' )
order by 1,2;

prompt
prompt "----------------"
prompt "Related objects created"
prompt "----------------"
select * from (select owner,subobject_name,object_type,to_char(created,'yyyymmdd hh24:mi') as created from dba_objects where object_name= upper('&table_name') order by 2) where rownum<101;
prompt
prompt
prompt "-------------------------"
prompt "partition_type"
prompt "-------------------------"
select owner,PARTITIONING_TYPE,SUBPARTITIONING_TYPE 
from dba_part_tables 
where table_name = upper('&table_name')
order by 1,2;
                                                           
prompt "-------------------------"
prompt "partition column"
prompt "-------------------------"
select 'part' type,a.* from dba_part_key_columns a  where name=upper('&table_name' )      
union all                                                                          
select 'subpart' type,a.* from dba_subpart_key_columns a  where name= upper('&table_name' );

prompt
prompt "-------------------------"
prompt "partition_stats"
prompt "-------------------------"
select table_owner,
       partition_name,
       to_char(last_analyzed,'yyyymmdd hh24:mi') as analyzed,
       num_rows,round(num_rows*avg_row_len/1024/1024) as size_mb 
from dba_tab_partitions 
where table_name = upper('&table_name' )
order by table_owner,partition_name;

prompt
prompt
prompt "-------------------------"
prompt "sub partition_stats"
prompt "-------------------------"
select table_owner,
       partition_name,
       subpartition_name,
       to_char(last_analyzed,'yyyymmdd hh24:mi') as analyzed,num_rows 
from dba_tab_subpartitions 
where table_name = upper('&table_name' )
order by 1,2,3;
prompt
prompt "-------------------------"
prompt "Table analyze history"
col owner format a12
col object_name format a24
col analyzed format a18
col rowcnt format 9999999999
col blkcnt format 99999999
select a.owner,
       a.object_name,
       to_char(b.ANALYZETIME,'yyyymmdd hh24:mi:ss') as analyzed,
       b.rowcnt,
       b.blkcnt
  from dba_objects a,sys.wri$_optstat_tab_history b
 where a.object_type = 'TABLE'
   and a.object_name  = upper('&table_name')
   and a.object_id = b.obj#
  order by 1,2,3;

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2024-05-09，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库