iis配置网站无法浏览,网站开发如何适应手机现实要求,金华网站制作策划,免费建国外网站希望能对大家有所帮助#xff01; 很多时候大家想查看oracle数据库中的sql语句执行情况#xff0c;但是又不知道如何是好#xff0c;今天在这里为大家提供一个sql语句#xff0c;大家可以通过以下的sql语句查询Oracle数据库中Top Sql情况#xff1a; 查询结果可按照PCT、总… 希望能对大家有所帮助 很多时候大家想查看oracle数据库中的sql语句执行情况但是又不知道如何是好今天在这里为大家提供一个sql语句大家可以通过以下的sql语句查询Oracle数据库中Top Sql情况 查询结果可按照PCT、总耗费时间、CPU使用时间、平均执行时间、执行次数、关联行数等排序。 TOP sql语句如下 select round(100 * a.pct, 2) pct, round(a.elapsed_time/1000000, 2) elapsed_time, round(a.elapsed_time/a.executions/1000) ms_by_exec, round(a.cpu_time/1000000, 2) cpu_time, a.buffer_gets total_cost, round(a.buffer_gets/a.executions) elem_cost, a.executions exec, a.rows_processed nb_rows, s.sql_text
from (select * from (select elapsed_time, ratio_to_report(elapsed_time) over () pct, cpu_time, buffer_gets, executions, rows_processed, address, hash_value from v$sql order by elapsed_time desc) where rownum 26) a, v$sqlarea s
where a.address s.address and a.hash_value s.hash_value and a.executions 0
order by pct desc, cpu_time desc session相关 select sesion.sid, username, osuser, machine, sesion.module, status, optimizer_mode, sql_textfrom v$sqlarea sqlarea, v$session sesion where sesion.sql_hash_value sqlarea.hash_value() and sesion.sql_address sqlarea.address() and sesion.username is not null
order by username, sql_text locks相关 select username, osuser, machine, s.module, l.sid, decode(l.type, MR, Media Recovery, RT, Redo Thread, UN, User Name, TX, Transaction, TM, DML, UL, PL/SQL User Lock, DX, Distributed Xaction, CF, Control File, IS, Instance State, FS, File Set, IR, Instance Recovery, ST, Disk Space Transaction, TS, Temp Segment, IV, Library Cache Invalidation, LS, Log Start or Switch, RW, Row Wait, SQ, Sequence Number, TE, Extend Table, TT, Temp Table, l.type) type, decode(lmode, 0, None, 1, Null, 2, Row-S (SS), 3, Row-X (SX), 4, Share, 5, S/Row-X (SSX), 6, Exclusive, lmode) lmode, decode(request, 0, None, 1, Null, 2, Row-S (SS), 3, Row-X (SX), 4, Share, 5, S/Row-X (SSX), 6, Exclusive, request) request, decode(block, 0, Not Blocking, 1, Blocking, 2, Global, block) block, owner, object_name, a.sql_text
from v$locked_object lo, all_objects ao, v$lock l, v$session s, v$sqlarea a
where lo.object_id ao.object_id and l.sid lo.session_id and s.sid l.sid and a.address s.sql_address() and a.hash_value s.sql_hash_value()
order by username foreignKeysWithoutIndexes相关 select user_cons_columns.table_name || . || user_constraints.constraint_name as Foreign key from user_cons_columns, user_constraints where user_constraints.constraint_type R and user_constraints.constraint_name user_cons_columns.constraint_name and
user_cons_columns.table_name || . || user_cons_columns.column_name not in (select table_name || . || column_name from user_ind_columns) invalid objects相关 select object_name from user_objects where status INVALID disabled Constraints相关 select table_name || . || constraint_name as Constraint from user_constraints where status DISABLED Library Cache Ratio/Row Cache Ratio/Cache Hit Ratio相关 select round(100*(1-sum(reloads)/sum(pins)),2) || % Library Cache Ratio Ratio from v$librarycache union
select round(100*(1-sum(getmisses)/sum(gets)),2) || % Row Cache Ratio from v$rowcache
union
select round(100*(1-(phy.value / (cur.value con.value))),2) || % Cache Hit Ratio
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name db block gets and con.name consistent gets and phy.name physical readsoracle.parameters select * from v$parameter rollback Segment统计相关 Select rn.Name Rollback Segment, rs.RSSize/1024 Size (KB), rs.Gets Gets, rs.waits Waits, (rs.Waits/rs.Gets)*100 % Waits, rs.Shrinks # Shrinks, rs.Extends # Extends
from v$RollName rn, v$RollStat rs
where rn.usn rs.usn statistics 相关 select name statistic, value system,
(select sum(value) from v$sesstat ses where ses.statistic# sys.statistic#) sessions
from v$sysstat sys events 相关 select event, time_waited system,
(select sum(time_waited) from v$session_event ses where ses.event sys.event) sessions
from v$system_event sys
where event ! Null event and event ! rdbms ipc message and event ! pipe get and event ! virtual circuit status and event ! lock manager wait for remote message and event not like % timer and
event not like SQL*Net message from % datafile IO 相关 select df.NAME filename, ts.name tablespace_name, PHYRDS physical_reads, round((PHYRDS / pd.PHYS_READS)*100,2) pct_reads, PHYWRTS physical_writes, round(PHYWRTS * 100 / pd.PHYS_WRTS,2) pct_writes, fs.PHYBLKRD fs.PHYBLKWRT total_block_io
from (select sum(PHYRDS) phys_reads, sum(PHYWRTS) phys_wrts from v$filestat) pd, v$datafile df, v$filestat fs, v$tablespace ts
where df.FILE# fs.FILE# and df.ts# ts.ts#
order by fs.PHYBLKRD fs.PHYBLKWRT desc tablespace相关 select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, MIN_EXTLEN, STATUS, CONTENTS, LOGGING, EXTENT_MANAGEMENT, ALLOCATION_TYPE
from user_tablespaces
order by TABLESPACE_NAME tablespace Freespace相关 select ddf.TABLESPACE_NAME, ddf.BYTES tablespace_size, ddf.BYTES-nvl(DFS.BYTES,0) used, round(((ddf.BYTES-nvl(dfs.BYTES,0))/ddf.BYTES)*100,2) pct_used, nvl(dfs.BYTES,0) free, round((1-((ddf.BYTES-nvl(dfs.BYTES,0))/ddf.BYTES))*100,2) pct_free
from (select t.name TABLESPACE_NAME, sum(BYTES) bytes from v$datafile d, v$tablespace t where t.ts# d.ts# group by t.name) ddf, (select TABLESPACE_NAME, sum(BYTES) bytes from user_free_space group by TABLESPACE_NAME) dfs
where ddf.TABLESPACE_NAMEdfs.TABLESPACE_NAME()
order by ((ddf.BYTES-nvl(dfs.BYTES,0))/ddf.BYTES) desc 转载于:https://www.cnblogs.com/andyspan/p/5805368.html
