Oracle11g使用sql
欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入 | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Byte
欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | | | |
| 2 | TABLE ACCESS FULL | EMP | 1 | 3 | | | 1 | | 2 | 49152 | | |
对于数据库中已经捕获的 SQL,通过其 SQL_ID,使用 DBMS_SQLTUNE 程序包中的
REPORT_SQL_MONITOR 函数,我们可以生成更为直观的 SQL 报告输出,辅助分析和诊断。
通常情况下,提供 SQL_ID 等少数参数,即可生成报告,TYPE 参数用于指定报告类型,
这里可以指定生成:TEXT、HTML、XML、ACTIVE 模式的报告。ACTIVE 模式的报告最为
华丽直观。
首先可以通过查询 v$sql_monitor 获得那些被监控收集过的 SQL 信息:
idle> select sql_id from v$sql_monitor;
SQL_ID
-------------
6gvch1xu9ca3g
5zruc4v6y32f9
6jfz01hn2n1mj
53c2k4c43zcfx
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sqlid', type => 'TEXT')
AS report FROM dual;
Enter value for sqlid: 5zruc4v6y32f9
old 1: SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sqlid', type => 'TEXT')
new 1: SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '5zruc4v6y32f9', type => 'TEXT')
SQL Monitoring Report
SQL Text
------------------------------
DECLARE job BINARY_INTEGER := :job;
next_date TIMESTAMP WITH TIME ZONE := :mydate;
broken BOOLEAN := FALSE;
job_name VARCHAR2(30) := :job_name;
job_subname VARCHAR2(30) := :job_subname;
job_owner VARCHAR2(30) := :job_owner;
job_start TIMESTAMP WITH TIME ZONE := :job_start;
job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start;
window_start TIMESTAMP WITH TIME ZONE := :window_start;
window_end TIMESTAMP WITH TIME ZONE := :window_end;
chain_id VARCHAR2(14) := :chainid;
credential_owner varchar2(30) := :credown;
credential_name varchar2(30) := :crednam;
destination_owner varchar2(30) := :destown;
destination_name varchar2(30) := :destnam;
job_dest_id varchar2(14) := :jdestid;
log_id number := :log_id;
BEGIN DECLARE ename VARCHAR2(30);
BEGIN ename := dbms_sqltune.execute_tuning_task( 'SYS_AUTO_SQL_TUNING_TASK');
END; :mydate := next_date;
IF broken THEN :b := 1;
ELSE :b := 0;
END IF; END;
Global Information
------------------------------
Status : DONE
Instance ID : 1
Session : SYS (60:21)
SQL ID : 5zruc4v6y32f9
SQL Execution ID : 16777216
Execution Started : 01/12/2014 10:11:33
First Refresh Time : 01/12/2014 10:11:43
Last Refresh Time : 01/12/2014 10:11:44
Duration : 11s
Module/Action : DBMS_SCHEDULER/ORA$AT_SQ_SQL_SW_63
Service : SYS$USERS
Program : oracle@eagle (J002)
Global Stats
==============================================
| Elapsed | Cpu | IO | Concurrency | PL/SQL | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
=================================================
| 11 | 0.82 | 1.83 | 0.00 | 1.52 | 8.15 | 22904 | 1973 | 30MB | 8 | 224KB |
==================================================
REPORT_SQL_MONITOR_LIST查看在11gR2在v$sql_monitor中的总理性能
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'TEXT',report_level => 'ALL') AS report FROM dual;
SQL Monitoring List
=====================
| Status | Duration | SQL Id | Exec Id | Start | User | Module/Action | Dop | DB Time | IOs | SQL Text |
| DONE | 5.0s | 53c2k4c43zcfx | 16777216 | 01/12/2014 | SYS | DBMS_SCHEDULER/ORA$AT_OS_OPT_S | | 12s | 417 | MERGE /*+ dynamic_sampling(ST 4) |
| | | | | 10:11:44 | | | | | | dynamic_sampling_est_cdn(ST) */ INTO|
STATS_TARGET$ ST USING (SELECT STALENESS, |
OSIZE, OBJ#, TYPE#, AFLAGS, STATUS, SID, |
SERIAL#, PART#, BO# FROM ( SELECT /*|
no_expand… |
| DONE | 11s | 5zruc4v6y32f9 | 16777216 | 01/12/2014 | SYS | DBMS_SCHEDULER/ORA$AT_SQ_SQL_S | | 10s | 1981 | DECLARE job BINARY_INTEGER := :job; |
| | | | | 10:11:33 | | | | | | next_date TIMESTAMP WITH TIME ZONE :|
:mydate; broken BOOLEAN := FALSE; job_name |
VARCHAR2(30) := :job_name; job_subname |
VARCHAR2(30) := :job_subname; job_owner… |
| DONE (ALL | 0.05s | 6jfz01hn2n1mj | 16777216 | 01/12/2014 | SYS | sqlplus@eagle (/- | | 0.05s | 2 | select /*+ monitor */ count(*) from |
| ROWS) | | | | 10:05:04 | | | | | | scott.emp where sal > 5000 |
| DONE | 19s | 6gvch1xu9ca3g | 16777216 | 01/12/2014 | SYS | | | 19s | 1373 | DECLARE job BINARY_INTEGER := :job; |
| | | | | 09:52:27 | | | | | | next_date DATE := :mydate; broken BOOLEAN := |
FALSE; BEGIN |
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); |
:mydate := next_date; IF broken THEN :b := |
1; ELSE :b := 0; … |
=============================================================
(MOS ID 1380492.1)
[1] [2] [3]
上一篇: 2019-11-12