执行计划相关SQL

来源：互联网发布：oc 获取设备mac地址编辑：程序博客网时间：2024/05/16 10:23

获取执行计划及统计信息

alter session set statistics_level=all;select /*+ my7  */ * emp;select t.* from v$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'ALLSTATS LAST')) t where sql_text like '%my7%';

获取SQL详细的统计信息，包括

parse 次数

磁盘读取次数

buffer读取次数

消耗的CPU时间

流逝时间

IO时间

select substr(sql_text, 1,40),sql_id, to_char(last_active_time, 'yyyy/mm/dd hh24:mi:ss') last_active_time, parse_calls, disk_reads, direct_writes, buffer_gets,rows_processed, round(cpu_time/1000000,2) cpu_seconds, round(elapsed_time/1000000,2) elapsed_second, round(concurrency_wait_time/1000000,2) concurrency_seconds, round(user_io_wait_time/1000000,2) io_seconds,round(plsql_exec_time/1000000,2) plsql_secondsfrom v$sqlstats where --sql_text like 'insert /*+ append */ into usage_event_fact%' andlast_active_time is not nulland trunc(last_active_time)=trunc(sysdate)order by last_active_time desc;

Parallel SQL按照Table Queue统计行数，字节数，将这些数据与执行计划比较，可以看出Oracle优化器的是否有表的准确的信息

column tq format a10select ':TQ' || dfo_number || lpad(tq_id,4,'0') tq, server_type, sum(num_rows) num_rows, sum(bytes) bytesfrom v$pq_tqstatgroup by ':TQ' || dfo_number || lpad(tq_id,4,'0'), server_typeorder by tq, server_type desc;

0 0