前 言
MOP 不用多说，指的就是 MySQL、Oracle、PostgreSQL 三种目前最主流的数据库，MOP 系列打算更新 MOP 三种数据库的索引知识、高可用架构及常用 SQL 语句等等，上面已经更新了 MOP 索引相关的文章，今天打算整理一下这三种数据库的常用 SQL 知识，但由于文章过长，今天分享 Oracle 篇。
本文 SQL 均是在运维工作中总结整理而成的,非个人独创，部分 SQL 来源于互联网，但现在已经不知道具体是来源哪个网站、哪个人，如有侵权，可联系我及时删除，谢谢！
1、按 Buffer Gets 降序排列 TOP SQL
代码语言：javascript复制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 SQLset 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
代码语言：javascript复制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
代码语言：javascript复制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
代码语言：javascript复制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
代码语言：javascript复制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、查询选择性高需要创建索引的列
代码语言：javascript复制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
代码语言：javascript复制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、行锁监控
代码语言：javascript复制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 执行信息
代码语言：javascript复制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 历史执行信息
代码语言：javascript复制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、查看表详细信息
代码语言：javascript复制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;全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~
