Oracle TOP SQL&&HIT

Oracle TOP SQL&&HIT

TOP SQL

--- 邏輯讀 (CPU,MEM)

--1

select *

  from (select ---substr(sql_text, 1, 40) sql,

               sql_text,

               buffer_gets,

               executions,

               buffer_gets / executions "Gets/Exec",

               hash_value,

               address

          from v$sqlarea

         where buffer_gets > 0

           and executions > 0

         order by buffer_gets desc)

 where rownum <= 10;

--2

select buffer_gets, sql_text

  from (select sql_text,

               buffer_gets,

               dense_rank() over(order by buffer_gets desc) buffer_gets_rank

          from v$sql)

 where buffer_gets_rank <= 10;

--3

SELECT EXECUTIONS,

       DISK_READS,

       BUFFER_GETS,

       ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) Hit_radio,

       ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run,

       SQL_TEXT

  FROM V$SQLAREA

 WHERE EXECUTIONS > 0

   AND BUFFER_GETS > 0

   AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8

 ORDER BY 4 DESC;

--- 物理讀 (I/O)

SELECT SQL_TEXT

  FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS desc)

 WHERE ROWNUM <= 10;

--1

select *

  from (select ---substr(sql_text, 1, 40) sql,

               sql_text,

               disk_reads,

               executions,

               disk_reads / executions "Reads/Exec",

               hash_value,

               address

          from v$sqlarea

         where disk_reads > 0

           and executions > 0

         order by disk_reads desc)

 where rownum <= 10;

--2

select *

  from (select v.sql_id,

               v.child_number,

               v.sql_text,

               v.elapsed_time,

               v.cpu_time,

               v.disk_reads,

               rank() over(order by v.disk_reads desc) elapsed_rank

          from v$sql v) a

 where elapsed_rank <= 10;

--- 表空間的 I/O 比例

/*

PHYRDS ：已完成的物理讀次數；

PHYBLKRD ：塊讀取數；

PHYWRTS ： DBWR 完成的物理寫次數；

PHYBLKWRT ：寫入磁碟的塊數；

*/

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;

--- 檔案系統 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#;

--- 磁碟碎片高的段

SELECT segment_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);

--- 執行次數

--1

select *

  from (select substr(sql_text, 1, 40) sql,

               sql_text,

               executions,

               rows_processed,

               rows_processed / executions "Rows/Exec",

               hash_value,

               address

          from v$sqlarea

         where executions > 0

         order by executions desc)

 where rownum <= 10;

  --2

select sql_text, executions

  from (select sql_text,

               executions,

               rank() over(order by executions desc) exec_rank

          from v$sql)

 where exec_rank <= 10;

--- 執行時間

select *

  from (select t.sql_fulltext,

               (t.last_active_time -

               to_date(t.first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,

               disk_reads,

               buffer_gets,

               rows_processed,

               t.last_active_time,

               t.last_load_time,

               t.first_load_time

          from v$sqlarea t

         order by t.first_load_time desc)

 where rownum < 10;

 

--- 執行時間長的 SQL

---V$SESSION_LONGOPS 檢視顯示執行超過 6 秒的操作的狀態。

SELECT USERNAME,

       SID,

       OPNAME,

       ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,

       TIME_REMAINING,

       SQL_TEXT

  FROM V$SESSION_LONGOPS, V$SQL

 WHERE TIME_REMAINING <> 0

   AND SQL_ADDRESS = ADDRESS

   AND SQL_HASH_VALUE = HASH_VALUE;

--- 查詢單條語句佔用記憶體

select sum(bytes) from dba_segments;

select sql_text,

       operation_type,

       policy,

       (last_memory_used / 1024 / 1024),

       last_execution,

       last_tempseg_size

  from v$sql i, v$sql_workarea a

 where i.hash_value = a.hash_value

   and sql_text like 'select sum(bytes) from dba_segments%';

---Parse Calls

select *

  from (select substr(sql_text, 1, 40) sql,

               parse_calls,

               executions,

               hash_value,

               address

          from v$sqlarea

         where parse_calls > 0

         order by parse_calls desc)

 where rownum <= 10;

---shared memory

select *

  from (select substr(sql_text, 1, 40) sql,

               sharable_mem,

               executions,

               hash_value,

               address

          from v$sqlarea

         where sharable_mem > 1048576

         order by sharable_mem desc)

 where rownum <= 10;

 

--- 等待事件

SELECT *

  FROM (SELECT *

          FROM V$SYSTEM_EVENT

         WHERE EVENT NOT LIKE 'SQL%'

         ORDER BY TOTAL_WAITS DESC)

 WHERE ROWNUM <= 5;

 

 

HIT

--- 檢查緩衝區命中率

SELECT a.VALUE + b.VALUE logical_reads,

       c.VALUE phys_reads,

       round(100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio

  FROM v$sysstat a, v$sysstat b, v$sysstat c

 WHERE a.NAME = 'db block gets'

   AND b.NAME = 'consistent gets'

   AND c.NAME = 'physical reads';

--- 檢查共享池命中率

select sum(pinhits)/sum(pins)*100 from v$librarycache;

--- 資料字典快取命中率：

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache;

--- 庫快取命中率：

select Sum(Pins)/(Sum(Pins) + Sum(Reloads)) * 100 "Hit Ratio" from V$LibraryCache;

--- 檢查日誌緩衝區

select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries');

--- 檢查排序區

select name,value from v$sysstat where name like '%sort%';

---PGA 記憶體排序命中率：

select a.value "Disk Sorts",

       b.value "Memory Sorts",

       round((100 * b.value) /

             decode((a.value + b.value), 0, 1, (a.value + b.value)),

             2) "Pct Memory Sorts"

  from v$sysstat a, v$sysstat b

 where a.name = 'sorts (disk)'

   and b.name = 'sorts (memory)';

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！