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

本文链接：https://blog.csdn.net/JiekeXu/article/details/138645779

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是 JiekeXu,江湖人称“强哥”，很高兴又和大家见面了,今天和大家一起来看看 MOP 三种主流数据库常用 SQL(一)，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的微信公众号，标星或置顶，更多干货才能第一时间到达，谢谢！

前言

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

Oracle 常用 SQL 大全

    按 Buffer Gets 降序排列 TOP SQL
    按执行次数降序排列 TOP SQL
    按解析次数降序排列 TOP SQL
    按物理读降序排列 TOP SQL
    按使用内存降序排列 TOP SQL
    根据单次执行中的 Buffer Gets 来查找 TOP SQL
    查询选择性高需要创建索引的列
    查找数据库中正在运行的 SQL
    行锁监控
    SQL 执行信息
    SQL 历史执行信息
    查看表详细信息

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

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

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

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

SQL 大全（四）
(转)Oracle DBA 日常维护 SQL 脚本大全（收藏版） https://www.modb.pro/db/44364

本文 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;

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

分享几个数据库备份脚本

一文搞懂 Oracle 统计信息

我的 Oracle ACE 心路历程

MOP 系列|MOP 三种主流数据库索引简介

Oracle 主流版本不同架构下的静默安装指南

关机重启导致 ASM 磁盘丢失数据库无法启动

Oracle SQL 性能分析(SPA)原理与实战演练

Oracle 11g 升级到 19c 需要关注的几个问题

Windows 10 环境下 MySQL 8.0.33 安装指南

SQL 大全（四）|数据库迁移升级时常用 SQL 语句

OGG|使用 OGG19c 迁移 Oracle11g 到 19C(第二版)

Oracle 大数据量导出工具——sqluldr2 的安装与使用

从国产数据库调研报告中你都能了解哪些信息及我的总结建议

使用数据泵利用 rowid 分片导出导入 lob 大表及最佳实践

在归档模式下直接 rm dbf 数据文件并重启数据库还有救吗？

欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
————————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————