Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use SQL Monitor Report", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use SQL Monitor Report" this article.

SQL Monitor Report

Introduction to 1.SQL Monitor

In Oracle Database 11g, the system automatically monitors the SQL that meets the following conditions and collects the details of execution:

1) execute in parallel

2) A single execution consumes more than 5 seconds of CPU or IO

3) by using / * + MONITOR*/ HINT statement

The SQL information collected by the system will be stored in V$SQL_MONITOR and V$SQL_PLAN_MONITOR views

2.SQL Monitor parameter settin

STATISTICS_LEVEL is set to: 'TYPICAL' (default) or' ALL'

CONTROL_MANAGEMENT_PACK_ACCESS is set to: 'DIAGNOSTIC+TUNING'

3.SQL Monitor report acquisition method

SQL Monitoring can be presented in the following three ways:

1) EM:Performance-- > SQL Monitoring in the lower right corner-- > Monitored SQL Executions

2) SQL Developer:Tools-- > Monitor SQL

3) DBMS_SQLTUNE package-> DBMS_SQLTUNE.report_sql_monitor

Its report format is: 'TEXT','HTML','XML',' ACTIVE', of which ACTIVE' is only supported after 11g R2, using HTML and Flash to display dynamic reports, you need to read the associated Javascript and Flash from the oracle official website.

Note:

If you can't connect to Internet and want to see ACTIVE Report, you can download the relevant library files to the local HTTP server, and then use BASE_PATH to determine the location of the library files.

Create a directory on the local HTTP server and download the following files:

Mkdir-p / var/www/html/sqlmon

Cd / var/www/html/sqlmon

Wget-mirror-no-host-directories-cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/flashver.js

Wget-mirror-no-host-directories-cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/loadswf.js

Wget-mirror-no-host-directories-cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/document.js

Wget-mirror-no-host-directories-cut-dirs=1 http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf

Add parameters when calling the function, such as: base_path = > 'http://ipaddr/sqlmon'

4. SQL Monitor report generates an instance

Syntax:

DBMS_SQLTUNE.REPORT_SQL_MONITOR ()

FUNCTION REPORT_SQL_MONITOR RETURNS CLOB

Argument Name Type In/Out Default?

SQL_ID VARCHAR2 IN DEFAULT

SESSION_ID NUMBER IN DEFAULT

SESSION_SERIAL NUMBER IN DEFAULT

SQL_EXEC_START DATE IN DEFAULT

SQL_EXEC_ID NUMBER IN DEFAULT

INST_ID NUMBER IN DEFAULT

START_TIME_FILTER DATE IN DEFAULT

END_TIME_FILTER DATE IN DEFAULT

INSTANCE_ID_FILTER NUMBER IN DEFAULT

PARALLEL_FILTER VARCHAR2 IN DEFAULT

PLAN_LINE_FILTER NUMBER IN DEFAULT

EVENT_DETAIL VARCHAR2 IN DEFAULT

BUCKET_MAX_COUNT NUMBER IN DEFAULT

BUCKET_INTERVAL NUMBER IN DEFAULT

BASE_PATH VARCHAR2 IN DEFAULT

LAST_REFRESH_TIME DATE IN DEFAULT

REPORT_LEVEL VARCHAR2 IN DEFAULT

TYPE VARCHAR2 IN DEFAULT

SQL_PLAN_HASH_VALUE NUMBER IN DEFAULT

4.1Text text format

1) View Sqlplus parameter settings

Show parameter statistics_level

NAME TYPE VALUE

-

Statistics_level string TYPICAL

Show parameter CONTROL_MANAGEMENT_PACK_ACCESS

NAME TYPE VALUE

-

Control_management_pack_access string DIAGNOSTIC+TUNING

2) execute simulated SQL

[oracle@node4 sqlmonitor] $sqlplus-S / nolog

Conn / as sysdba

Select / * + moniotr*/* from scott.dept where deptno '74qqqwntwzxb1'

TYPE = > 'TEXT'

REPORT_LEVEL = > 'ALL') AS REPORT

FROM dual

Spool off

5) display the contents of the report

4.2HTML format

1) generate HTML type report

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

Spool report_sql_monitor_html.html

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR (

SQL_ID = > '74qqqwntwzxb1'

TYPE = > 'HTML'

REPORT_LEVEL = > 'ALL') AS REPORT

FROM dual

Spool off

2) html type report display

4.3Active format

If you cannot connect to the Internet, you need to download the corresponding flash components and scripts. For more information, please see how to obtain SQL Monitor report.

1) active type report generation

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

Spool report_sql_monitor_active.html

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR (

SQL_ID = > '74qqqwntwzxb1'

TYPE = > 'ACTIVE'

REPORT_LEVEL = > 'ALL'

BASE_PATH = > 'http://ipaddr/sqlmon') AS report

FROM dual

Spool off

2) active type report display

You can start the http service, place the files in the release directory and view them in http://ipaddr/sqlmon/report_sql_monitor_active.html form (you need to download the appropriate scripts and components)

Or get windows to check it locally.

5. SQL Monitor report is used by other methods

1) DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST

FUNCTION REPORT_SQL_MONITOR_LIST RETURNS CLOB

Argument Name Type In/Out Default?

SQL_ID VARCHAR2 IN DEFAULT

SESSION_ID NUMBER IN DEFAULT

SESSION_SERIAL NUMBER IN DEFAULT

INST_ID NUMBER IN DEFAULT

ACTIVE_SINCE_DATE DATE IN DEFAULT

ACTIVE_SINCE_SEC NUMBER IN DEFAULT

LAST_REFRESH_TIME DATE IN DEFAULT

REPORT_LEVEL VARCHAR2 IN DEFAULT

AUTO_REFRESH NUMBER IN DEFAULT

BASE_PATH VARCHAR2 IN DEFAULT

TYPE VARCHAR2 IN DEFAULT

Oracle 11g R2 or later is required. This function is used to generate a summary page for the monitoring SQL, similar to "Monitored SQL Executions" in EM.

Common parameters: TYPE and REPORT_LEVEL, usage similar to REPORT_SQL_MONITOR.

For example:

Conn / as sysdba

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SPOOL REPORT_SQL_MONITOR_LIST.HTML

SELECT dbms_sqltune.report_sql_monitor_list (

Type = > 'HTML'

Report_level = > 'ALL') AS report

FROM dual

SPOOL OFF

2) DBMS_SQLTUNE.REPORT_SQL_DETAIL

FUNCTION REPORT_SQL_DETAIL RETURNS CLOB

Argument Name Type In/Out Default?

SQL_ID VARCHAR2 IN DEFAULT

SQL_PLAN_HASH_VALUE NUMBER IN DEFAULT

START_TIME DATE IN DEFAULT

DURATION NUMBER IN DEFAULT

INST_ID NUMBER IN DEFAULT

DBID NUMBER IN DEFAULT

EVENT_DETAIL VARCHAR2 IN DEFAULT

BUCKET_MAX_COUNT NUMBER IN DEFAULT

BUCKET_INTERVAL NUMBER IN DEFAULT

TOP_N NUMBER IN DEFAULT

REPORT_LEVEL VARCHAR2 IN DEFAULT

TYPE VARCHAR2 IN DEFAULT

DATA_SOURCE VARCHAR2 IN DEFAULT

END_TIME DATE IN DEFAULT

DURATION_STATS NUMBER IN DEFAULT

Oracle 11g R2 or later is required. This function is used to generate more detailed SQL reports than using REPORT_SQL_MONITOR based on various conditional parameters (including: start_time, end_time, duration, inst_id, dbid, event_detail, bucket_max_count, bucket_interval, top_n, duration_stats).

For example:

Conn / as sysdba

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SPOOL REPORT_SQL_DETAIL_HTML.HTML

SELECT dbms_sqltune.REPORT_SQL_DETAIL (SQL_ID = > '74qqwntwzxb1'

TYPE = > 'active'

Report_level = > 'ALL') AS report

FROM dual

SPOOL OFF

ERROR:

ORA-13971: Component "sql_detail" unknown

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.DBMS_REPORT", line 166

ORA-06512: at "SYS.DBMS_REPORT", line 612

ORA-06512: at "SYS.DBMS_REPORT", line 1079

ORA-06512: at "SYS.DBMS_REPORT", line 1135

ORA-06512: at "SYS.DBMS_SQLTUNE", line 20101

ORA-06512: at line 1

(the above error is specified in html format and can be adjusted to active format)

Can be targeted at topSQL

Conn / as sysdba

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SPOOL REPORT_SQL_DETAIL.HTML

SELECT dbms_sqltune.report_sql_detail (top_n = > 5)

TYPE = > 'active'

Report_level = > 'ALL') AS report

FROM dual

SPOOL OFF

The above is all the contents of this article "how to use SQL Monitor Report". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.