-
性能监控之监控SQL语句
-
-
分析表
-
analyze TABLE tablename compute statistics FOR ALL indexes;
-
analyze TABLE tablename compute statistics FOR ALL indexed COLUMNS;
-
analyze TABLE tablename compute statistics FOR TABLE;
-
-
监控事例的等待
-
-
SELECT event ,sum (decode (wait_Time ,0 ,0 , 1 ) ) "Prev" ,
-
sum (decode (wait_Time ,0 , 1 ,0 ) ) "Curr" ,count ( * ) "Tot"
-
FROM v$session_Wait
-
GROUP BY event ORDER BY 4;
-
-
查看碎片程度高的表
-
-
SELECT segment_name table_name , COUNT ( * ) extents
-
FROM dba_segments WHERE owner NOT IN (SYS , SYSTEM ) GROUP BY segment_name
-
HAVING COUNT ( * ) = ( SELECT MAX ( COUNT ( * ) FROM dba_segments GROUP BY segment_name );
-
-
表、索引的存储情况检查
-
-
SELECT segment_name ,sum (bytes ) ,count ( * ) ext_quan FROM dba_extents WHERE
-
tablespace_name =&tablespace_name AND segment_type = TABLE GROUP BY tablespace_name ,segment_name;
-
-
SELECT segment_name ,count ( * ) FROM dba_extents WHERE segment_type = INDEX AND owner =&owner
-
GROUP BY segment_name;
-
-
找使用CPU多的用户session
-
-
12是cpu used BY this session
-
-
SELECT a .sid ,spid , STATUS ,substr (a .program , 1 , 40 ) prog ,a .terminal ,osuser ,value / 60 / 100 value
-
FROM v$session a ,v$process b ,v$sesstat c
-
WHERE c .statistic #=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
-
-
监控表空间的 I /O 比例
-
-
SELECT df .tablespace_name name ,df .file_name "file" ,f .phyrds pyr ,
-
f .phyblkrd pbr ,f .phywrts pyw , f .phyblkwrt pbw
-
FROM v$filestat f , dba_data_files df
-
WHERE f .file # = df.file_id
-
ORDER BY df .tablespace_name;
-
-
-
回滚段的争用情况
-
-
SELECT name , waits , gets , waits /gets "Ratio"
-
FROM v$rollstat a , v$rollname b
-
WHERE a .usn = b .usn;
-
-
在某个用户下找所有的索引
-
-
SELECT user_indexes .table_name , user_indexes .index_name ,uniqueness , column_name
-
FROM user_ind_columns , user_indexes
-
WHERE user_ind_columns .index_name = user_indexes .index_name
-
AND user_ind_columns .table_name = user_indexes .table_name
-
ORDER BY user_indexes .table_type , user_indexes .table_name ,
-
user_indexes .index_name , column_position;
-
-
-
监控文件系统的 I /O 比例
-
-
SELECT substr (a .file #,1,2) "#", substr(a.name,1,30) "Name",
-
a . STATUS , a .bytes , b .phyrds , b .phywrts
-
FROM v$datafile a , v$filestat b
-
WHERE a .file # = b.file#;
-
-
监控 SGA 中字典缓冲区的命中率
-
-
SELECT parameter , gets ,Getmisses , getmisses / (gets +getmisses ) * 100 "miss ratio" ,
-
( 1 - (sum (getmisses ) / (sum (gets ) +sum (getmisses ) ) ) ) * 100 "Hit ratio"
-
FROM v$rowcache
-
WHERE gets +getmisses <>0
-
GROUP BY parameter , gets , getmisses;
-
-
监控 SGA 中共享缓存区的命中率,应该小于 1%
-
-
SELECT sum (pins ) "Total Pins" , sum (reloads ) "Total Reloads" ,
-
sum (reloads ) /sum (pins ) * 100 libcache
-
FROM v$librarycache;
-
-
SELECT sum (pinhits -reloads ) /sum (pins ) "hit radio" ,sum (reloads ) /sum (pins ) "reload percent"
-
FROM v$librarycache;
-
-
监控 SGA 的命中率
-
-
SELECT a .value + b .value "logical_reads" , c .value "phys_reads" ,
-
round ( 100 * ( (a .value +b .value ) -c .value ) / (a .value +b .value ) ) "BUFFER HIT RATIO"
-
FROM v$sysstat a , v$sysstat b , v$sysstat c
-
WHERE a .statistic # = 38 and b.statistic# = 39
-
AND c .statistic # = 40;
-
-
监控 SGA 中重做日志缓存区的命中率,应该小于 1%
-
-
SELECT name , gets , misses , immediate_gets , immediate_misses ,
-
Decode (gets ,0 ,0 ,misses /gets * 100 ) ratio1 ,
-
Decode (immediate_gets +immediate_misses ,0 ,0 ,
-
immediate_misses / (immediate_gets +immediate_misses ) * 100 ) ratio2
-
FROM v$latch WHERE name IN (redo allocation , redo copy );
-
-
显示所有数据库对象的类别和大小
-
-
SELECT count (name ) num_instances ,type ,sum (source_size ) source_size ,
-
sum (parsed_size ) parsed_size ,sum (code_size ) code_size ,sum (error_size ) error_size ,
-
sum (source_size ) +sum (parsed_size ) +sum (code_size ) +sum (error_size ) size_required
-
FROM dba_object_size
-
GROUP BY type ORDER BY 2;
-
-
监控内存和硬盘的排序比率,最好使它小于 .10,增加 sort_area_size
-
-
SELECT name , value FROM v$sysstat WHERE name IN (sorts (memory ) , sorts (disk ) );
-
-
-
监控当前数据库谁在运行什么SQL语句
-
-
SELECT osuser , username , sql_text FROM v$session a , v$sqltext b
-
WHERE a .sql_address =b .address ORDER BY address , piece;
-
-
监控字典缓冲区
-
-
SELECT (SUM (PINS - RELOADS ) ) / SUM (PINS ) "LIB CACHE" FROM V$LIBRARYCACHE;
-
SELECT (SUM (GETS - GETMISSES - USAGE - FIXED ) ) / SUM (GETS ) "ROW CACHE" FROM V$ROWCACHE;
-
SELECT SUM (PINS ) "EXECUTIONS" , SUM (RELOADS ) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
-
-
后者除以前者 ,此比率小于 1% ,接近0%为好。
-
-
SELECT SUM (GETS ) "DICTIONARY GETS" ,SUM (GETMISSES ) "DICTIONARY CACHE GET MISSES"
-
FROM V$ROWCACHE
-
-
监控 MTS
-
-
SELECT busy / (busy +idle ) "shared servers busy" FROM v$dispatcher;
-
-
此值大于 0.5时,参数需加大
-
-
SELECT sum (wait ) /sum (totalq ) "dispatcher waits" FROM v$queue WHERE type =dispatcher;
-
SELECT count ( * ) FROM v$dispatcher;
-
SELECT servers_highwater FROM v$mts;
-
-
servers_highwater接近mts_max_servers时,参数需加大
-
-
碎片程度
-
-
SELECT tablespace_name ,count (tablespace_name ) FROM dba_free_space GROUP BY tablespace_name
-
HAVING count (tablespace_name ) > 10;
-
-
ALTER tablespace name coalesce;
-
ALTER TABLE name deallocate unused;
-
-
CREATE OR REPLACE VIEW ts_blocks_v AS
-
SELECT tablespace_name ,block_id ,bytes ,blocks ,free space segment_name FROM dba_free_space
-
union ALL
-
SELECT tablespace_name ,block_id ,bytes ,blocks ,segment_name FROM dba_extents;
-
-
SELECT * FROM ts_blocks_v;
-
-
SELECT tablespace_name ,sum (bytes ) ,max (bytes ) ,count (block_id ) FROM dba_free_space
-
GROUP BY tablespace_name;
oracle性能监控
最新推荐文章于 2024-09-25 09:21:39 发布