Useful SQL

最新推荐文章于 2024-06-15 09:47:46 发布

cuixu4254

最新推荐文章于 2024-06-15 09:47:46 发布

阅读量62

点赞数

001:I/O 磁盘争夺
col PHYRDS format 999,999,999
col PHYWRTS format 999,999,999
ttitle "Disk Balancing Report"
col READTIM format 999,999,999
col WRITETIM format 999,999,999
col name format a40

select name,phyrds,phywrts,readtim,writetim
from v$filestat a,v$datafile b
where a.file# = b.file#
order by readtim desc
/
002:日志切换频率查看
select b.recid                                      before_recid,
       to_char(b.first_time,'yyyymmdd hh24:mi:ss') start_time,
       a.recid                                      after_recid,
       to_char(b.first_time,'yyyymmdd hh24:mi:ss') end_time,
       round(((a.first_time-b.first_time)*25)*60,2) minutes
from v$log_history a,v$log_history b
where a.recid = b.recid+1
order by a.first_time asc
/

003:监控undo活动
select a.name,
       b.xacts,
       c.sid,
       c.serial#,
       c.username,
       d.sql_text
from v$rollname a ,v$rollstat b , v$session c,v$sqltext d,v$transaction e
where a.usn = b.usn
and   b.usn = e.xidusn
and   c.taddr = e.addr
and   c.sql_address = d.address
and   c.sql_hash_value = d.hash_value
order by a.name,c.sid,d.piece
/

004:常用参数查看
col name for a25
col value for a50

select name,value
from v$parameter
where name in ('sga_max_size','pga_aggregate_target','db_cache_size','shared_pool_size');
/

005:DB Buffer监控
col phys format 999,999,999 heading 'Physical Reads'
col gets format 999,999,999 heading 'DB Block Gets'
col con_gets format 999,999,999 heading 'Consistent Gets'
col hitratio format 999,999,999 heading 'Hit Ratio'

select sum(decode(name,'physical reads',value,0)) phys,
sum(decode(name,'db block gets',value,0)) gets,
sum(decode(name,'consistent gets',value,0)) con_gets,
(1 - (sum(decode(name,'physical reads',value,0)) /
(sum(decode(name,'db block gets',value,0)) +
sum(decode(name,'consistent gets',value,0))))) * 100 hitratio
from v$sysstat
/
006:查看shared_pool_size使用情况(粗)
col value for 999,999,999,999 heading "Shared Pool Size"
col bytes for 999,999,999,999 heading "Free Bytes"
select to_number(b.value) value,a.bytes,
(a.bytes/b.value)*100 "Percent Free"
from v$sgastat a ,v$parameter b
where a.name ='free memory'
and   b.name ='shared_pool_size'
and   a.pool = 'shared pool'
/
007:查看shared_pool_size使用情况(细)
prompt "Free    This is thef contiguous free memory available"
prompt "Freeabl Freeable but ,currently in use"
prompt "Perm    Free memory not yet moved to the free area for use"
prompt "Recr    Allocat memory that is flushable when the shared pool is low on memory"
prompt "R-free This is SHARED_POOL_RESERVED_SIZE (default 5 percent)"
prompt "R-freea reserved memory that is freeable but not flushable"
prompt "R-recr Recreatable chuncks of memory in the reserved pool."
prompt "perm Permanent chucks of memory in the reserved pool"
set echo off
select sum(ksmchsiz) Bytes,ksmchcls Status
from x$ksmsp
group by ksmchcls
/

008:PGA内存设定最佳值
select min(pga_target_for_estimate)/1024/1024 AS " Optimal Size(MB)"
from v$pga_target_advice
where estd_pga_cache_hit_percentage > 95
/

009:隐藏参数查询
set wrap off
set echo off
col ksppinm format a30 heading Parameter|Name
col ksppstvl format a50 heading Current|Value
col ksppstdf format a10 heading Default|Value

select ksppinm ,ksppstvl,ksppstdf
from x$ksppi a,x$ksppcv b
where a.indx = b.indx
and ksppinm like '%&HidParam%'
order by ksppinm
/
010:查找最差性能前5条的SQL文本

011:查出最差性能前5条的SQL的执行计划
012:查看DB一般信息
set echo off
set feedback off
set linesize 250
select * from v$version
/
select name,to_char(created,'yyyymmdd hh24:mi:ss'),log_mode
from v$database
/
013:一般参数查询
set wrap off
set linesize 100
set feedback off

col name format a15
col name format a10

select name,value,isdefault,isses_modifiable,issys_modifiable
from v$parameter
where name like '%&param%'
order by name

014:数据字典命中率
select sum(gets),sum(getmisses),(1 - (sum(getmisses) / (sum(gets)
+ sum(getmisses)))) * 100 HitRate
from v$rowcache
/

015:查找有必要被pinned住PLSQL对象
select name,sharable_mem
from v$db_object_cache
where sharable_mem > 100000
and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
and kept = 'NO'

016:查看当前会话的行为
set echo off
col username format a10
col sid format 9999
select a.sid,a.username,s.sql_text
from v$session a,v$sqltext s
where a.sql_address = s.address
and a.sql_hash_value = s.hash_value
order by a.username,a.sid,s.piece

prompt --------------------------------------------------------------------------------

select a.username,
b.block_gets,
b.consistent_gets,
b.physical_reads,
b.block_changes,
b.consistent_changes
from v$session a , v$sess_io b
where a.sid = b.sid
order by 3 desc
/
017:查看当前哪些对象正在被使用
set echo off
set linesize 200
col sid format 9999
col username format a10
col owner format a10
col object format a20
col b.type format a20
select a.sid,
a.username,
b.owner,
b.object,
b.type
from v$session a , v$access b
where a.sid = b.sid
/

018:查看每个用户的统计资料信息
set echo off
set linesize 400
col username format a10
col name format a50
col value format 999,999,999

select a.username,
c.name,
sum(b.value) value
from v$session a,v$sesstat b , v$statname c
where a.sid = b.sid
and username !='SYS'
and b.statistic# = c.statistic#
and b.value != 0
group by name,username

019:磁盘和文件IO深度分析
set TrimSpool on
set Line 142
set Pages 57
set NewPage 0
set FeedBack off
set Verify off
set Term on
TTitle  off
BTitle  off
Clear Breaks
Break On Tablespace_Name
col TableSpace_Name For A12  Head "Tablespace"
col Name  For A45  Head "File Name"
col Total  For 999,999,990 Head "Total"
col Phyrds  For 999,999,990 Head "Physical|Reads "
col Phywrts  For 999,999,990 Head "Physical|Writes "
col phyblkrd For 999,999,990 Head "Physical|Block Reads"
col phyblkwrt For 999,999,990 Head "Physical|Block Writes"
col Avg_Rd_Time For 90.9999999 Head "Average|Read Time|Per Block"
col Avg_Wrt_Time For 90.9999999 Head "Average|Write Time|Per Block"
col Instance New_Value _Instance NoPrint
col Today  New_Value _Date  NoPrint
select Global_Name Instance,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from global_name;
ttitle on
ttitle left 'Date Run: ' _Date Skip 1-
Center 'Data File I/O' Skip 1-
Center 'Instance Name: ' _Instance Skip 1
select c.tablespace_name,
b.name,
a.phyblkrd+a.phyblkwrt total,
a.phyrds,
a.phywrts,
a.phyblkrd,
a.phyblkwrt
from v$filestat a,v$datafile b,dba_data_files c
where b.file# = a.file#
and b.file# = c.file_id
order by tablespace_name,a.file#
/

select object_name,statistic_name,value
from v$segment_statistics
where value > 100000
order by value
/
set feedback on
set verify on
set term on
ttitle off
btitle off

020:查看整个系统waits event 情况
set trimspool on
set newpage 0
set pages 57
set line 132
set feedback off
set verify off
set term on
ttitle off
btitle off
clear breaks
col seq for 99
col event for a40 heading "Wait Event"
col total_waits for 999,999,990 head "Total Number| of Waits "
col total_timeouts for 999,999,990 head "Total Number|Of TimeOuts"
col tot_time for 999,999,990 head "Total Time|Waited "
col avg_time for 99,990.999 head "Average Time|Per Wait "
col instance new_value _instance noprint
col today new_value _date noprint

select global_name instance,
to_char(sysdate,'yyyy/mm/dd hh24:mi') today
from global_name;

ttitle on
ttitle left 'Date Run: ' _Date skip 1-
center 'System Wide Wait Events' Skip 1-
center 'Instance Name:' _Instance skip 2

select * from (
select rank() over(order by total_waits desc) as Seq,
event,
total_waits,
total_timeouts,
(time_waited /100) tot_time,
(average_wait /100) avg_time
from v$system_event)
where seq <=10
/

021:检测freelists是否发生竞争
set trimspool on
set line 132
set pages 57
set newpage 0
set feedback off
set verify off
set term off
ttitle off
btitle off
col pct for 990.99 heading "% Of |Free List Waits"
col instance new_value _instance noprint
col today new_value _date noprint

select global_name instance,
to_char(sysdate,'yyyy/mm/dd hh24:mi') today
from global_name
/

ttitle on
ttitle left 'Date Run: ' _Date Skip 1-
center 'Free list Contention' Skip 1-
center 'If Percentage is Greater that 1%' Skip 1-
center 'Consider increasing the number of free lists' Skip 1-
center 'Instance Name: ' _Instance

select ((a.count / (b.value +c.value)) * 100) Pct
from v$waitstat a,v$sysstat b,v$sysstat c
where a.class ='free list'
and b.statistic# = (select statistic# from v$statname where name = 'db block gets')
and c.statistic# = (select statistic# from v$statname where name = 'consistent gets')
/
022:会话级的等待事件查询
set feedback on
set verify on
set term on
ttitle off
btitle off
set echo off
select event,
sum(decode(wait_time,0,1,0)) "Waiting Now",
sum(decode(wait_time,0,0,1)) "Previous Waits",
count(*) "Total"
from v$session_wait
group by event
order by count(*)
/

023:和数据文件等待事件相关的信息查询
select /*+ ordered */ sid,
event,
owner,
segment_name,
segment_type,
p1,
p2,
p3
from v$session_wait_history sw,dba_extents de
where de.file_id = sw.p1
and sw.p2 between de.block_id and de.block_id+de.blocks -1
and (event = 'buffer busy waits' or event = 'write complete waits')
and p1 is not null
order by event,sid
/
024:查看Buffer Cache里各类Buffer块的汇总信息
set verify off
col pcttotbcmem for a11
select /*+ ordered */
tot_bc_mem.totbcmem,
decode(state,
  0,'Free',
  1,'Exclusive',
  2,'SharedCurrent',
  3,'ConsistentRead',
  4,'BeingRead',
  5,'InMeiaRecoveryMode',
  6,'InInstanceRecoveryMode',
  7,'BeingWritten',
  8,'Pinned',
  9,'Memory',
  10,'mrite',
  11,'Donated') "BlockState",
sum(blsiz) "SumStateTtypeMem",
count(1)   "NumOfBlksThisTyp",
Round(sum(blsiz)/tot_bc_mem.totbcmem,2)*100||'%' "PctTotBCMem"
from (select sum(blsiz) totBCMem
      from x$bh) tot_bc_mem,
                 x$bh
group by tot_bc_mem.totbcmem,
decode(state,
                0,'Free',
                1,'Exclusive',
                2,'SharedCurrent',
                3,'ConsistentRead',
                4,'BeingRead',
                5,'InMeiaRecoveryMode',
                6,'InInstanceRecoveryMode',
                7,'BeingWritten',
                8,'Pinned',
                9,'Memory',
                10,'mrite',
                11,'Donated')
order by sum(blsiz)
/
clear columns

025:查看Buffer给段对象缓冲比
set wrap off
col owner for a10
col object_type for a10
col object_name for a20
select tot_occ_bufs.totoccbufs,
o.*,
d_o.owner,
d_o.object_name,
object_type,
round((o.buffers/tot_occ_bufs.totoccbufs)*100,2) || '%' PctOccBufs
from (select obj object,count(1) buffers,avg(tch) avg_touches
from x$bh
group by obj) o,
(select count(1) totoccbufs
from x$bh
where state !=0) tot_occ_bufs,
dba_objects d_o
where o.object = d_o.data_object_id
and d_o.owner !='SYS'
order by round((o.buffers /tot_occ_bufs.totoccbufs)*100,2),owner,object_name

026:定位热块
col name for a35
col seq for 999

select * from (
select /*+ ordered */ row_number() over(order by bh.tch desc ) seq , u.username ||'.'|| o.name name,
so.object_type type,
bh.dbablk,
bh.tch touches
from x$bh bh,dba_users u ,sys.obj$ o,sys.sys_objects so
where bh.obj = o.obj#
and bh.obj = so.object_id
and o.owner# = u.user_id
and bh.tch > 10
)
where seq <=10
/
027:查看哪些段正在等待 cache buffers chains latch

028:查看各表空间使用情况
SELECT D.TABLESPACE_NAME,
      SPACE "SUM_SPACE(M)",
   BLOCKS SUM_BLOCKS,
   SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
   ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",
   FREE_SPACE "FREE_SPACE(M)"
   FROM
   (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
   FROM DBA_DATA_FILES
   GROUP BY TABLESPACE_NAME) D,
   (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
   FROM DBA_FREE_SPACE
   GROUP BY TABLESPACE_NAME) F
   WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
   UNION ALL
   SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
   USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
   NVL(FREE_SPACE,0) "FREE_SPACE(M)"
   FROM
   (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
   FROM DBA_TEMP_FILES
   GROUP BY TABLESPACE_NAME) D,
   (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
   ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
   FROM V$TEMP_SPACE_HEADER
   GROUP BY TABLESPACE_NAME) F
   WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
/

029:查看执行计划脚本
select *
from table (dbms_xplan.display);
/
030:查询数据字典
select table_name
from dict
where lower(table_name) like '%&&p%'
union
select name
from v$fixed_table
where lower(name) like '%&p%';
/
031:查询某个会话的语句分析情况

select name,
value
from v$sesstat a ,v$statname b
where a.statistic#=b.statistic#
and a.sid=&sid
and b.name like '%parse%'
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27064837/viewspace-732988/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27064837/viewspace-732988/

cuixu4254

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Useful SQL

001:I/O 磁盘争夺col PHYRDS format 999,999,999col PHYWRTS format 999,999,999ttitle "Disk Balancing Report"col READTIM f...
复制链接

扫一扫