Oracle_性能查询

1、2022-2-202022-2-2022022-2-2032022-2-2042022-2-205- Get the top 100 queries (mergeJoinQueries) by total number of disk_reads_deltaSELECT *FROM (SELECT SQL_ID, SUM(disk_reads_delta) AS Disk Reads FROM DBA_HIST_SQLSTAT S WHERE SNAP_ID 209 AND SNAP_ID = 0 ORDER BY 2 DESC) DERIVED_TABLEWHERE ROWNUM 209 A

2、ND SNAP_ID = 0 ORDER BY 2 DESC) DERIVED_TABLEWHERE ROWNUM 209 AND SNAP_ID = 210 AND S.MODULE = ClaimCenter GROUP BY SQL_ID HAVING SUM(rows_processed_delta) = 0 ORDER BY 3 DESC) DERIVED_TABLEWHERE ROWNUM 209 and e.snap_id 0 or sum(e.physical_reads_delta) 0 or sum(e.physical_writes_delta) 0 order by l

3、ogical_reads desc) d ) rwhere n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.dbid = r.dbid and n.owner = MKTGorder by logical_reads desc;2022-2-2010- logical_reads, physical_reads, physical_writes by tablespace and table.SELECT owner, tablespace_name, table_name, sum(logical_reads) as LOGICAL REA

4、DS, sum(logical_reads_ratio) as logical_reads_ratio, sum(physical_reads) as PHYSICAL READS, sum(physical_reads_ratio) as physical_reads_ratio, sum(physical_writes) as PHYSICAL WRITES, sum(physical_writes_ratio) as physical_writes_ratioFROM (select n.owner, n.tablespace_name, case when n.object_type

5、= TABLE then n.object_name when n.object_type = INDEX then (SELECT table_name from dba_indexes WHERE index_name = n.object_name) when n.object_type = LOB then (SELECT table_name from dba_lobs WHERE segment_name = n.object_name) else N/A end as Table_name, n.object_name, n.object_type, r.logical_read

6、s, round(r.logical_reads_ratio * 100, 2) logical_reads_ratio, r.physical_reads, round(r.physical_reads_ratio * 100, 2) physical_reads_ratio, r.physical_writes, round(r.physical_writes_ratio * 100, 2) physical_writes_ratio from dba_hist_seg_stat_obj n, (select * from (select e.dataobj#, e.obj#, e.dbi

7、d, sum(e.logical_reads_delta) logical_reads, ratio_to_report(sum(e.logical_reads_delta) over () logical_reads_ratio, sum(e.physical_reads_delta) physical_reads, ratio_to_report(sum(e.physical_reads_delta) over () physical_reads_ratio, sum(e.physical_writes_delta) physical_writes, ratio_to_report(sum

8、(e.physical_writes_delta) over () physical_writes_ratio from dba_hist_seg_stat e where e.snap_id 209 and e.snap_id 0 or sum(e.physical_reads_delta) 0 or sum(e.physical_writes_delta) 0 order by logical_reads desc) d ) r where n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.dbid = r.dbid AND n.owner

9、 = MKTG) cgroup by owner, tablespace_name, table_name order by 4 desc;2022-2-2011- logical_reads, physical_reads, physical_writes by table.SELECT owner, table_name, sum(logical_reads) as Logical Reads, sum(logical_reads_ratio) as Logical Reads Ratio, sum(physical_reads) as Physical Reads, sum(physic

10、al_reads_ratio) as Physical Reads Ratio, sum(physical_writes) as Physical Writes, sum(physical_writes_ratio) as Physical Writes RatioFROM (select n.owner, case when n.object_type = TABLE then n.object_name when n.object_type = INDEX then (SELECT table_name from dba_indexes WHERE index_name = n.objec

11、t_name) when n.object_type = LOB then (SELECT table_name from dba_lobs WHERE segment_name = n.object_name) else N/A end as Table_name, n.object_name, n.object_type, r.logical_reads, round(r.logical_reads_ratio * 100, 2) logical_reads_ratio, r.physical_reads, round(r.physical_reads_ratio * 100, 2) ph

12、ysical_reads_ratio, r.physical_writes, round(r.physical_writes_ratio * 100, 2) physical_writes_ratio from dba_hist_seg_stat_obj n, (select * from (select e.dataobj#, e.obj#, e.dbid, sum(e.logical_reads_delta) logical_reads, ratio_to_report(sum(e.logical_reads_delta) over () logical_reads_ratio, sum(

13、e.physical_reads_delta) physical_reads, ratio_to_report(sum(e.physical_reads_delta) over () physical_reads_ratio, sum(e.physical_writes_delta) physical_writes, ratio_to_report(sum(e.physical_writes_delta) over () physical_writes_ratio from dba_hist_seg_stat e where e.snap_id 209 and e.snap_id 0 or sum(e.physical_reads_delta) 0 or sum(e.physical_writes_delta) 0 order by logical_reads desc) d ) r where n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.dbid = r.dbid and n.owner = MKTG) cgroup by owner, table_nameorder by 3 desc;20