说明:本文为Oracle 11gR2中使用DBMS包查看SQL的统计信息的指导手册
用途:本文只是提供一种方法和思路,更多更高级的使用方法还期待你不断的探索哦
标签:dbms_sqltune.report_sql_detail、查看SQL的统计信息、SQL Details Active Report
温馨提示:如果您发现本文哪里写的有问题或者有更好的写法请留言或私信我进行修改优化
★ 执行测试SQL
※ 温馨提示:添加hint-monitor是为了让Oracle收集所有的SQL,否则默认只收集耗费资源的SQL
sqlplus / as sysdba
select /*tag zzt_006*//*+ monitor */ *
from scott.emp e, scott.dept d
where e.deptno = d.deptno
and empno > 7800;
★ 查看业务SQL信息(本例中SQL_ID:dzy48nhmfm8mx)
sqlplus / as sysdba
select * from v$sqlarea a where lower(a.SQL_TEXT) like '%/*tag zzt_006*/%';
★ 执行完SQL后使用官方工具SQL Details Active Report来获取SQL的统计信息
※ SQL Details Active Report(主要内容:统计信息)
※ 官档:https://www.oracle.com/technetwork/database/manageability/sql-detail-099420.html
※ type参数常用active,还可以选:txt/html/xml
sqlplus / as sysdba
set trimspool on trim on
set pages 0 linesize 1000
set long 1000000 longchunksize 1000000
spool sqlmon_details.html
select dbms_sqltune.report_sql_detail(sql_id => 'dzy48nhmfm8mx',
report_level => 'all',
type => 'active') as report from dual;
spool off
★ HTML输出案例截图
★ HTML输出案例
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<base href="http://download.oracle.com/otn_software/"/>
<script language="javascript" type="text/javascript" src="emviewers/scripts/flashver.js">
<!--Test flash version-->
</script>
<style>
body { margin: 0px; overflow:hidden }
</style>
</head>
<body scroll="no">
<script type="text/xml">
<!--FXTMODEL-->
<report db_version="11.2.0.3.0" elapsed_time="5.88" cpu_time="5.82">
<report_id><![CDATA[/orarep/sql_detail/main?bucket_max_count=128&data_source=auto&event_detail=yes&report_level=all&sql_id=dzy48nhmfm8mx&top_n=10]]></report_id>
<report_parameters>
<sql_id>dzy48nhmfm8mx</sql_id>
<event_detail>yes</event_detail>
<bucket_max_count>128</bucket_max_count>
<top_n>10</top_n>
<report_level>all</report_level>
<data_source>auto</data_source>
</report_parameters>
<target sql_id="dzy48nhmfm8mx" start_time="05/20/2020 12:33:13" end_time="05/20/2020 13:33:13" start_time_stats="05/19/2020 13:33:13" end_time_stats="05/20/2020 13:33:13" inst_id="all" dbid="1462754487" database_role="PRIMARY"/>
<sql_details>
<sql_attributes>
<sql_id>dzy48nhmfm8mx</sql_id>
<force_matching_signature>6853706444147011714</force_matching_signature>
<exact_matching_signature>17496396251173590787</exact_matching_signature>
<text type="full">select /*tag zzt_006*//*+ monitor */ *
from scott.emp e, scott.dept d
where e.deptno = d.deptno
and empno > 7800</text>
</sql_attributes>
<activity_histogram source="memory" bucket_interval="32" bucket_count="128" start_time="05/20/2020 12:33:13" end_time="05/20/2020 13:33:13" duration="3600" total_activity="9" sql_activity="0">
<bucket number="10">
<activity other_activity="other">1</activity>
</bucket>
<bucket number="36">
<activity other_activity="other">1</activity>
</bucket>
<bucket number="44">
<activity other_activity="other">1</activity>
</bucket>
<bucket number="52">
<activity other_activity="other">1</activity>
</bucket>
<bucket number="75">
<activity other_activity="other">1</activity>
</bucket>
<bucket number="87">
<activity other_activity="other">1</activity>
</bucket>
<bucket number="90">
<activity other_activity="other">1</activity>
</bucket>
<bucket number="91">
<activity other_activity="other">1</activity>
</bucket>
<bucket number="95">
<activity other_activity="other">1</activity>
</bucket>
</activity_histogram>
<top_activity/>
<plan_activities/>
<sql_cursors>
<parent_cursor inst_id="1" num_children="1"/>
</sql_cursors>
<report db_version="11.2.0.3.0" elapsed_time="0.04" cpu_time="0.05">
<report_id><![CDATA[/orarep/sqlmonitor/list?report_level=typical -sql_text&sql_id=dzy48nhmfm8mx]]></report_id>
<sql_monitor_list_report version="1" sysdate="05/20/2020 13:33:13">
<sql detail="F" sql_id="dzy48nhmfm8mx" sql_exec_start="05/20/2020 13:32:46" sql_exec_id="16777216">
<status>DONE (ALL ROWS)</status>
<first_refresh_time>05/20/2020 13:32:46</first_refresh_time>
<last_refresh_time>05/20/2020 13:32:46</last_refresh_time>
<refresh_count>5</refresh_count>
<inst_id>1</inst_id>
<session_id>145</session_id>
<session_serial>93</session_serial>
<user_id>0</user_id>
<user>SYS</user>
<module>sqlplus@localhost.localdomain (TNS V1-V3)</module>
<service>SYS$USERS</service>
<program>sqlplus@localhost.localdomain (TNS V1-V3)</program>
<plan_hash>817787759</plan_hash>
<is_cross_instance>N</is_cross_instance>
<stats type="monitor">
<stat name="duration">.000999</stat>
<stat name="elapsed_time">999</stat>
<stat name="cpu_time">999</stat>
<stat name="user_fetch_count">2</stat>
<stat name="buffer_gets">6</stat>
</stats>
</sql>
</sql_monitor_list_report>
</report>
<sql_plans>
<sql_plan plan_hash_value="817787759">
<info_group name="cursor_info" source="memory" info_inst_id="1">
<info name="parsing_schema_name">SYS</info>
<info name="optimizer_mode">ALL_ROWS</info>
<info name="optimizer_cost">5</info>
<info name="optimizer_env_hash_value">4072203010</info>
<info name="module">sqlplus@localhost.localdomain (TNS V1-V3)</info>
<info name="last_active_time">05/20/2020 13:32:46</info>
<info name="first_load_time">05/20/2020 13:32:46</info>
<info name="last_load_time">05/20/2020 13:32:46</info>
<info name="duration">0</info>
</info_group>
<stats type="sql_plan_statistics" source="memory" instance_count="1" version_count="1">
<stat name="sharable_mem">27919</stat>
<stat name="persistent_mem">10896</stat>
<stat name="runtime_mem">8976</stat>
<stat name="typecheck_mem">0</stat>
<stat name="sorts">1</stat>
<stat name="loads">1</stat>
<stat name="invalidations">0</stat>
<stat name="executions">1</stat>
<stat name="fetches">2</stat>
<stat name="px_servers_executions">0</stat>
<stat name="end_of_fetch_count">1</stat>
<stat name="parse_calls">1</stat>
<stat name="disk_reads">0</stat>
<stat name="direct_writes">0</stat>
<stat name="buffer_gets">6</stat>
<stat name="rows_processed">6</stat>
<stat name="io_cell_offload_eligible_bytes">0</stat>
<stat name="io_interconnect_bytes">0</stat>
<stat name="physical_read_requests">0</stat>
<stat name="physical_read_bytes">0</stat>
<stat name="physical_write_requests">0</stat>
<stat name="physical_write_bytes">0</stat>
<stat name="cpu_time">.014997</stat>
<stat name="elapsed_time">.015784</stat>
<stat name="application_wait_time">0</stat>
<stat name="concurrency_wait_time">0</stat>
<stat name="cluster_wait_time">0</stat>
<stat name="user_io_wait_time">0</stat>
<stat name="plsql_exec_time">0</stat>
<stat name="java_exec_time">0</stat>
</stats>
<stats type="sql_plan_active_time" source="memory" duration="0">
<stat name="sorts_ps"/>
<stat name="loads_ps"/>
<stat name="invalidations_ps"/>
<stat name="executions_ps"/>
<stat name="fetches_ps"/>
<stat name="px_servers_executions_ps"/>
<stat name="end_of_fetch_count_ps"/>
<stat name="parse_calls_ps"/>
<stat name="disk_reads_ps"/>
<stat name="direct_writes_ps"/>
<stat name="buffer_gets_ps"/>
<stat name="rows_processed_ps"/>
<stat name="io_cell_offload_eligible_bytes_ps"/>
<stat name="io_interconnect_bytes_ps"/>
<stat name="physical_read_requests_ps"/>
<stat name="physical_read_bytes_ps"/>
<stat name="physical_write_requests_ps"/>
<stat name="physical_write_bytes_ps"/>
<stat name="cpu_time_ps"/>
<stat name="elapsed_time_ps"/>
<stat name="application_wait_time_ps"/>
<stat name="concurrency_wait_time_ps"/>
<stat name="cluster_wait_time_ps"/>
<stat name="user_io_wait_time_ps"/>
<stat name="plsql_exec_time_ps"/>
<stat name="java_exec_time_ps"/>
</stats>
<sql_plan_statistics_history begin_snap="6" end_snap="16" delta_snap="16">
<stats type="snapshot_interval" source="disk" snap_id="7" duration="2102" end_interval_time="05/20/2020 09:00:36">
<stat name="executions"/>
<stat name="fetches"/>
<stat name="px_servers_executions"/>
<stat name="end_of_fetch_count"/>
<stat name="parse_calls"/>
<stat name="disk_reads"/>
<stat name="direct_writes"/>
<stat name="buffer_gets"/>
<stat name="rows_processed"/>
<stat name="io_cell_offload_eligible_bytes"/>
<stat name="io_interconnect_bytes"/>
<stat name="physical_read_requests"/>
<stat name="physical_read_bytes"/>
<stat name="physical_write_requests"/>
<stat name="physical_write_bytes"/>
<stat name="cpu_time"/>
<stat name="elapsed_time"/>
<stat name="application_wait_time"/>
<stat name="concurrency_wait_time"/>
<stat name="cluster_wait_time"/>
<stat name="user_io_wait_time"/>
<stat name="plsql_exec_time"/>
<stat name="java_exec_time"/>
</stats>
<stats type="snapshot_interval" source="disk" snap_id="8" duration="2229" end_interval_time="05/20/2020 09:37:45">
<stat name="executions"/>
<stat name="fetches"/>
<stat name="px_servers_executions"/>
<stat name="end_of_fetch_count"/>
<stat name="parse_calls"/>
<stat name="disk_reads"/>
<stat name="direct_writes"/>
<stat name="buffer_gets"/>
<stat name="rows_processed"/>
<stat name="io_cell_offload_eligible_bytes"/>
<stat name="io_interconnect_bytes"/>
<stat name="physical_read_requests"/>
<stat name="physical_read_bytes"/>
<stat name="physical_write_requests"/>
<stat name="physical_write_bytes"/>
<stat name="cpu_time"/>
<stat name="elapsed_time"/>
<stat name="application_wait_time"/>
<stat name="concurrency_wait_time"/>
<stat name="cluster_wait_time"/>
<stat name="user_io_wait_time"/>
<stat name="plsql_exec_time"/>
<stat name="java_exec_time"/>
</stats>
<stats type="snapshot_interval" source="disk" snap_id="9" duration="791" end_interval_time="05/20/2020 09:50:56">
<stat name="executions"/>
<stat name="fetches"/>
<stat name="px_servers_executions"/>
<stat name="end_of_fetch_count"/>
<stat name="parse_calls"/>
<stat name="disk_reads"/>
<stat name="direct_writes"/>
<stat name="buffer_gets"/>
<stat name="rows_processed"/>
<stat name="io_cell_offload_eligible_bytes"/>
<stat name="io_interconnect_bytes"/>
<stat name="physical_read_requests"/>
<stat name="physical_read_bytes"/>
<stat name="physical_write_requests"/>
<stat name="physical_write_bytes"/>
<stat name="cpu_time"/>
<stat name="elapsed_time"/>
<stat name="application_wait_time"/>
<stat name="concurrency_wait_time"/>
<stat name="cluster_wait_time"/>
<stat name="user_io_wait_time"/>
<stat name="plsql_exec_time"/>
<stat name="java_exec_time"/>
</stats>
<stats type="snapshot_interval" source="disk" snap_id="10" duration="139" end_interval_time="05/20/2020 09:53:15">
<stat name="executions"/>
<stat name="fetches"/>
<stat name="px_servers_executions"/>
<stat name="end_of_fetch_count"/>
<stat name="parse_calls"/>
<stat name="disk_reads"/>
<stat name="direct_writes"/>
<stat name="buffer_gets"/>
<stat name="rows_processed"/>
<stat name="io_cell_offload_eligible_bytes"/>
<stat name="io_interconnect_bytes"/>
<stat name="physical_read_requests"/>
<stat name="physical_read_bytes"/>
<stat name="physical_write_requests"/>
<stat name="physical_write_bytes"/>
<stat name="cpu_time"/>
<stat name="elapsed_time"/>
<stat name="application_wait_time"/>
<stat name="concurrency_wait_time"/>
<stat name="cluster_wait_time"/>
<stat name="user_io_wait_time"/>
<stat name="plsql_exec_time"/>
<stat name="java_exec_time"/>
</stats>
<stats type="snapshot_interval" source="disk" snap_id="11" duration="4047" end_interval_time="05/20/2020 11:00:42">
<stat name="executions"/>
<stat name="fetches"/>
<stat name="px_servers_executions"/>
<stat name="end_of_fetch_count"/>
<stat name="parse_calls"/>
<stat name="disk_reads"/>
<stat name="direct_writes"/>
<stat name="buffer_gets"/>
<stat name="rows_processed"/>
<stat name="io_cell_offload_eligible_bytes"/>
<stat name="io_interconnect_bytes"/>
<stat name="physical_read_requests"/>
<stat name="physical_read_bytes"/>
<stat name="physical_write_requests"/>
<stat name="physical_write_bytes"/>
<stat name="cpu_time"/>
<stat name="elapsed_time"/>
<stat name="application_wait_time"/>
<stat name="concurrency_wait_time"/>
<stat name="cluster_wait_time"/>
<stat name="user_io_wait_time"/>
<stat name="plsql_exec_time"/>
<stat name="java_exec_time"/>
</stats>
<stats type="snapshot_interval" source="disk" snap_id="12" duration="3602" end_interval_time="05/20/2020 12:00:44">
<stat name="executions"/>
<stat name="fetches"/>
<stat name="px_servers_executions"/>
<stat name="end_of_fetch_count"/>
<stat name="parse_calls"/>
<stat name="disk_reads"/>
<stat name="direct_writes"/>
<stat name="buffer_gets"/>
<stat name="rows_processed"/>
<stat name="io_cell_offload_eligible_bytes"/>
<stat name="io_interconnect_bytes"/>
<stat name="physical_read_requests"/>
<stat name="physical_read_bytes"/>
<stat name="physical_write_requests"/>
<stat name="physical_write_bytes"/>
<stat name="cpu_time"/>
<stat name="elapsed_time"/>
<stat name="application_wait_time"/>
<stat name="concurrency_wait_time"/>
<stat name="cluster_wait_time"/>
<stat name="user_io_wait_time"/>
<stat name="plsql_exec_time"/>
<stat name="java_exec_time"/>
</stats>
<stats type="snapshot_interval" source="disk" snap_id="13" duration="3603" end_interval_time="05/20/2020 13:00:47">
<stat name="executions"/>
<stat name="fetches"/>
<stat name="px_servers_executions"/>
<stat name="end_of_fetch_count"/>
<stat name="parse_calls"/>
<stat name="disk_reads"/>
<stat name="direct_writes"/>
<stat name="buffer_gets"/>
<stat name="rows_processed"/>
<stat name="io_cell_offload_eligible_bytes"/>
<stat name="io_interconnect_bytes"/>
<stat name="physical_read_requests"/>
<stat name="physical_read_bytes"/>
<stat name="physical_write_requests"/>
<stat name="physical_write_bytes"/>
<stat name="cpu_time"/>
<stat name="elapsed_time"/>
<stat name="application_wait_time"/>
<stat name="concurrency_wait_time"/>
<stat name="cluster_wait_time"/>
<stat name="user_io_wait_time"/>
<stat name="plsql_exec_time"/>
<stat name="java_exec_time"/>
</stats>
<stats type="snapshot_interval" source="disk" snap_id="14" duration="1109" end_interval_time="05/20/2020 13:19:16">
<stat name="executions"/>
<stat name="fetches"/>
<stat name="px_servers_executions"/>
<stat name="end_of_fetch_count"/>
<stat name="parse_calls"/>
<stat name="disk_reads"/>
<stat name="direct_writes"/>
<stat name="buffer_gets"/>
<stat name="rows_processed"/>
<stat name="io_cell_offload_eligible_bytes"/>
<stat name="io_interconnect_bytes"/>
<stat name="physical_read_requests"/>
<stat name="physical_read_bytes"/>
<stat name="physical_write_requests"/>
<stat name="physical_write_bytes"/>
<stat name="cpu_time"/>
<stat name="elapsed_time"/>
<stat name="application_wait_time"/>
<stat name="concurrency_wait_time"/>
<stat name="cluster_wait_time"/>
<stat name="user_io_wait_time"/>
<stat name="plsql_exec_time"/>
<stat name="java_exec_time"/>
</stats>
<stats type="snapshot_interval" source="disk" snap_id="15" duration="138" end_interval_time="05/20/2020 13:21:34">
<stat name="executions"/>
<stat name="fetches"/>
<stat name="px_servers_executions"/>
<stat name="end_of_fetch_count"/>
<stat name="parse_calls"/>
<stat name="disk_reads"/>
<stat name="direct_writes"/>
<stat name="buffer_gets"/>
<stat name="rows_processed"/>
<stat name="io_cell_offload_eligible_bytes"/>
<stat name="io_interconnect_bytes"/>
<stat name="physical_read_requests"/>
<stat name="physical_read_bytes"/>
<stat name="physical_write_requests"/>
<stat name="physical_write_bytes"/>
<stat name="cpu_time"/>
<stat name="elapsed_time"/>
<stat name="application_wait_time"/>
<stat name="concurrency_wait_time"/>
<stat name="cluster_wait_time"/>
<stat name="user_io_wait_time"/>
<stat name="plsql_exec_time"/>
<stat name="java_exec_time"/>
</stats>
<stats type="snapshot_interval" source="disk" snap_id="16" duration="81" end_interval_time="05/20/2020 13:22:55">
<stat name="executions"/>
<stat name="fetches"/>
<stat name="px_servers_executions"/>
<stat name="end_of_fetch_count"/>
<stat name="parse_calls"/>
<stat name="disk_reads"/>
<stat name="direct_writes"/>
<stat name="buffer_gets"/>
<stat name="rows_processed"/>
<stat name="io_cell_offload_eligible_bytes"/>
<stat name="io_interconnect_bytes"/>
<stat name="physical_read_requests"/>
<stat name="physical_read_bytes"/>
<stat name="physical_write_requests"/>
<stat name="physical_write_bytes"/>
<stat name="cpu_time"/>
<stat name="elapsed_time"/>
<stat name="application_wait_time"/>
<stat name="concurrency_wait_time"/>
<stat name="cluster_wait_time"/>
<stat name="user_io_wait_time"/>
<stat name="plsql_exec_time"/>
<stat name="java_exec_time"/>
</stats>
<stats type="snapshot_interval" source="memory_disk" duration="618" end_interval_time="05/20/2020 13:33:13">
<stat name="executions"/>
<stat name="fetches"/>
<stat name="px_servers_executions"/>
<stat name="end_of_fetch_count"/>
<stat name="parse_calls"/>
<stat name="disk_reads"/>
<stat name="direct_writes"/>
<stat name="buffer_gets"/>
<stat name="rows_processed"/>
<stat name="io_cell_offload_eligible_bytes"/>
<stat name="io_interconnect_bytes"/>
<stat name="physical_read_requests"/>
<stat name="physical_read_bytes"/>
<stat name="physical_write_requests"/>
<stat name="physical_write_bytes"/>
<stat name="cpu_time"/>
<stat name="elapsed_time"/>
<stat name="application_wait_time"/>
<stat name="concurrency_wait_time"/>
<stat name="cluster_wait_time"/>
<stat name="user_io_wait_time"/>
<stat name="plsql_exec_time"/>
<stat name="java_exec_time"/>
</stats>
</sql_plan_statistics_history>
<plan plan_source="memory" plan_inst_id="1" plan_child_address="0000000091DEFC30">
<operation name="SELECT STATEMENT" id="0" depth="0" pos="5">
<cost>5</cost>
</operation>
<operation name="MERGE JOIN" id="1" depth="1" pos="1">
<card>3</card>
<bytes>174</bytes>
<cost>5</cost>
<io_cost>4</io_cost>
<cpu_cost>21053326</cpu_cost>
<time>00:00:01 </time>
<project>"D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]</project>
<qblock>SEL$1</qblock>
<other_xml>
<info type="db_version">11.2.0.3</info>
<info type="parse_schema"><![CDATA["SYS"]]></info>
<info type="plan_hash">817787759</info>
<info type="plan_hash_2">1586662073</info>
<outline_data>
<hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
<hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]></hint>
<hint><![CDATA[DB_VERSION('11.2.0.3')]]></hint>
<hint><![CDATA[ALL_ROWS]]></hint>
<hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
<hint><![CDATA[INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))]]></hint>
<hint><![CDATA[INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."EMPNO"))]]></hint>
<hint><![CDATA[LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")]]></hint>
<hint><![CDATA[USE_MERGE(@"SEL$1" "E"@"SEL$1")]]></hint>
</outline_data>
</other_xml>
</operation>
<operation name="TABLE ACCESS" options="BY INDEX ROWID" id="2" depth="2" pos="1">
<object>DEPT</object>
<card>4</card>
<bytes>80</bytes>
<cost>2</cost>
<io_cost>2</io_cost>
<cpu_cost>15803</cpu_cost>
<time>00:00:01 </time>
<project>"D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]</project>
<qblock>SEL$1</qblock>
<object_alias>D@SEL$1</object_alias>
</operation>
<operation name="INDEX" options="FULL SCAN" id="3" depth="3" pos="1">
<object>PK_DEPT</object>
<card>4</card>
<cost>1</cost>
<io_cost>1</io_cost>
<cpu_cost>7921</cpu_cost>
<time>00:00:01 </time>
<project>"D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]</project>
<qblock>SEL$1</qblock>
<object_alias>D@SEL$1</object_alias>
</operation>
<operation name="SORT" options="JOIN" id="4" depth="2" pos="2">
<card>3</card>
<bytes>114</bytes>
<cost>3</cost>
<io_cost>2</io_cost>
<cpu_cost>21037523</cpu_cost>
<time>00:00:01 </time>
<project>(#keys=1) "E"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]</project>
<predicates type="access">"E"."DEPTNO"="D"."DEPTNO"</predicates>
<predicates type="filter">"E"."DEPTNO"="D"."DEPTNO"</predicates>
</operation>
<operation name="TABLE ACCESS" options="BY INDEX ROWID" id="5" depth="3" pos="1">
<object>EMP</object>
<card>3</card>
<bytes>114</bytes>
<cost>2</cost>
<io_cost>2</io_cost>
<cpu_cost>16203</cpu_cost>
<time>00:00:01 </time>
<project>"EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]</project>
<qblock>SEL$1</qblock>
<object_alias>E@SEL$1</object_alias>
</operation>
<operation name="INDEX" options="RANGE SCAN" id="6" depth="4" pos="1">
<object>PK_EMP</object>
<card>3</card>
<cost>1</cost>
<io_cost>1</io_cost>
<cpu_cost>7921</cpu_cost>
<time>00:00:01 </time>
<project>"E".ROWID[ROWID,10], "EMPNO"[NUMBER,22]</project>
<predicates type="access">"EMPNO">7800</predicates>
<qblock>SEL$1</qblock>
<object_alias>E@SEL$1</object_alias>
</operation>
</plan>
<report db_version="11.2.0.3.0" elapsed_time="5.13" cpu_time="5.08">
<report_id><![CDATA[/orarep/sqlmonitor/main?bucket_max_count=128&event_detail=yes&report_level=typical -xplan -sql_fulltext&sql_id=dzy48nhmfm8mx&sql_plan_hash_value=817787759]]></report_id>
<sql_monitor_report version="4.0" sysdate="05/20/2020 13:32:46">
<report_parameters>
<sql_id>dzy48nhmfm8mx</sql_id>
<bucket_count>2</bucket_count>
<interval_start>05/20/2020 13:32:46</interval_start>
<interval_end>05/20/2020 13:32:47</interval_end>
</report_parameters>
<target instance_id="1" session_id="145" session_serial="93" sql_id="dzy48nhmfm8mx" sql_exec_start="05/20/2020 13:32:46" sql_exec_id="16777216" sql_plan_hash="817787759" db_unique_name="orcl" db_platform_name="Linux x86 64-bit" report_host_name="localhost.localdomain">
<user_id>0</user_id>
<user>SYS</user>
<program>sqlplus@localhost.localdomain (TNS V1-V3)</program>
<module>sqlplus@localhost.localdomain (TNS V1-V3)</module>
<service>SYS$USERS</service>
<sql_fulltext is_full="Y">select /*tag zzt_006*//*+ monitor */ *
from scott.emp e, scott.dept d
where e.deptno = d.deptno
and empno > 7800</sql_fulltext>
<status>DONE (ALL ROWS)</status>
<refresh_count>5</refresh_count>
<first_refresh_time>05/20/2020 13:32:46</first_refresh_time>
<last_refresh_time>05/20/2020 13:32:46</last_refresh_time>
<duration>.000999</duration>
<optimizer_env>
<param name="active_instance_count">1</param>
<param name="is_recur_flags">0</param>
<param name="parallel_autodop">0</param>
<param name="parallel_ddl_mode">enabled</param>
<param name="parallel_ddldml">0</param>
<param name="parallel_degree">0</param>
<param name="parallel_dml_mode">disabled</param>
<param name="parallel_execution_enabled">true</param>
<param name="parallel_max_degree">4</param>
<param name="parallel_query_default_dop">0</param>
<param name="parallel_query_mode">enabled</param>
<param name="pga_aggregate_target">368640 KB</param>
<param name="total_cpu_count">2</param>
</optimizer_env>
</target>
<stats type="monitor">
<stat name="elapsed_time">999</stat>
<stat name="cpu_time">999</stat>
<stat name="user_fetch_count">2</stat>
<stat name="buffer_gets">6</stat>
</stats>
<plan_monitor max_io_reqs="0" max_io_bytes="0">
<operation id="0" name="SELECT STATEMENT" depth="0" position="0">
<stats type="plan_monitor">
<stat name="first_active">05/20/2020 13:32:46</stat>
<stat name="last_active">05/20/2020 13:32:46</stat>
<stat name="duration">1</stat>
<stat name="from_most_recent">0</stat>
<stat name="from_sql_exec_start">0</stat>
<stat name="starts">1</stat>
<stat name="cardinality">6</stat>
</stats>
</operation>
<operation id="1" parent_id="0" name="MERGE JOIN" depth="1" position="1">
<optimizer>
<cardinality>3</cardinality>
<bytes>174</bytes>
<cost>5</cost>
<cpu_cost>21053326</cpu_cost>
<io_cost>4</io_cost>
<time>1</time>
</optimizer>
<stats type="plan_monitor">
<stat name="first_active">05/20/2020 13:32:46</stat>
<stat name="last_active">05/20/2020 13:32:46</stat>
<stat name="duration">1</stat>
<stat name="from_most_recent">0</stat>
<stat name="from_sql_exec_start">0</stat>
<stat name="starts">1</stat>
<stat name="cardinality">6</stat>
</stats>
</operation>
<operation id="2" parent_id="1" name="TABLE ACCESS" options="BY INDEX ROWID" depth="2" position="1">
<object type="TABLE">
<owner>SCOTT</owner>
<name>DEPT</name>
</object>
<optimizer>
<cardinality>4</cardinality>
<bytes>80</bytes>
<cost>2</cost>
<cpu_cost>15803</cpu_cost>
<io_cost>2</io_cost>
<time>1</time>
</optimizer>
<stats type="plan_monitor">
<stat name="first_active">05/20/2020 13:32:46</stat>
<stat name="last_active">05/20/2020 13:32:46</stat>
<stat name="duration">1</stat>
<stat name="from_most_recent">0</stat>
<stat name="from_sql_exec_start">0</stat>
<stat name="starts">1</stat>
<stat name="cardinality">4</stat>
</stats>
</operation>
<operation id="3" parent_id="2" name="INDEX" options="FULL SCAN" depth="3" position="1">
<object type="INDEX (UNIQUE)">
<owner>SCOTT</owner>
<name>PK_DEPT</name>
</object>
<optimizer>
<cardinality>4</cardinality>
<cost>1</cost>
<cpu_cost>7921</cpu_cost>
<io_cost>1</io_cost>
<time>1</time>
</optimizer>
<stats type="plan_monitor">
<stat name="first_active">05/20/2020 13:32:46</stat>
<stat name="last_active">05/20/2020 13:32:46</stat>
<stat name="duration">1</stat>
<stat name="from_most_recent">0</stat>
<stat name="from_sql_exec_start">0</stat>
<stat name="starts">1</stat>
<stat name="cardinality">4</stat>
</stats>
</operation>
<operation id="4" parent_id="1" name="SORT" options="JOIN" depth="2" position="2">
<optimizer>
<cardinality>3</cardinality>
<bytes>114</bytes>
<cost>3</cost>
<cpu_cost>21037523</cpu_cost>
<io_cost>2</io_cost>
<time>1</time>
</optimizer>
<stats type="plan_monitor">
<stat name="first_active">05/20/2020 13:32:46</stat>
<stat name="last_active">05/20/2020 13:32:46</stat>
<stat name="duration">1</stat>
<stat name="from_most_recent">0</stat>
<stat name="from_sql_exec_start">0</stat>
<stat name="starts">4</stat>
<stat name="cardinality">6</stat>
<stat name="max_memory">2048</stat>
</stats>
</operation>
<operation id="5" parent_id="4" name="TABLE ACCESS" options="BY INDEX ROWID" depth="3" position="1">
<object type="TABLE">
<owner>SCOTT</owner>
<name>EMP</name>
</object>
<optimizer>
<cardinality>3</cardinality>
<bytes>114</bytes>
<cost>2</cost>
<cpu_cost>16203</cpu_cost>
<io_cost>2</io_cost>
<time>1</time>
</optimizer>
<stats type="plan_monitor">
<stat name="first_active">05/20/2020 13:32:46</stat>
<stat name="last_active">05/20/2020 13:32:46</stat>
<stat name="duration">1</stat>
<stat name="from_most_recent">0</stat>
<stat name="from_sql_exec_start">0</stat>
<stat name="starts">1</stat>
<stat name="cardinality">6</stat>
</stats>
</operation>
<operation id="6" parent_id="5" name="INDEX" options="RANGE SCAN" depth="4" position="1">
<object type="INDEX (UNIQUE)">
<owner>SCOTT</owner>
<name>PK_EMP</name>
</object>
<optimizer>
<cardinality>3</cardinality>
<cost>1</cost>
<cpu_cost>7921</cpu_cost>
<io_cost>1</io_cost>
<time>1</time>
</optimizer>
<stats type="plan_monitor">
<stat name="first_active">05/20/2020 13:32:46</stat>
<stat name="last_active">05/20/2020 13:32:46</stat>
<stat name="duration">1</stat>
<stat name="from_most_recent">0</stat>
<stat name="from_sql_exec_start">0</stat>
<stat name="starts">1</stat>
<stat name="cardinality">6</stat>
</stats>
</operation>
</plan_monitor>
</sql_monitor_report>
</report>
</sql_plan>
</sql_plans>
</sql_details>
</report>
<!--FXTMODEL-->
</script>
<script language="JavaScript" type="text/javascript" src="emviewers/scripts/loadswf.js">
<!--Load report viewer-->
</script>
<iframe name="_history" frameborder="0" scrolling="no" width="22" height="0">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
<script type="text/javascript" language="JavaScript1.2" charset="utf-8">
var v = new top.Vars(top.getSearch(window));
var fv = v.toString('$_');
</script>
</head>
<body>
<script type="text/javascript" language="JavaScript1.2" charset="utf-8" src="emviewers/scripts/document.js">
<!--Run document script-->
</script>
</body>
</html>
</iframe>
</body>
</html>
※ 如果您觉得文章写的还不错, 别忘了在文末给作者点个赞哦 ~
over