Ebs_dba_ Optimization: Mastering these features of SQL Monitor, SQL optimization will be like God help! Go

The pain and suffering of SQL analysis

Many DBAs have some questions about the SQL statements on the line, and it's good to look at the cost of executing the plan, but there's a big difference in the actual execution.

For a large SQL statement, looking at the resulting execution plan without knowing where the bottleneck is, the SQL statement is too complex, the execution plan looks more complex, and it is not easy to read the essentials.

Occasionally some friends ask me, how to read an implementation plan, this no matter how thin, seems to have no effect, after all, the pure text description and graphics effects still have a great difference.

If you are in the 11g version, SQL Monitor is a big benefit, the above problems may be resolved here, this feature you are worth mastering, if you have not mastered it, it is too bad.

As for SQL Monitor more details are not described, 11g introduced this feature is actually similar to the slow log in MySQL, MySQL inside the threshold is lower, SQL monitor is 5 seconds. Once this standard is reached, it is collected into the V$sql_monitor view, where detailed session information and execution plans can be obtained.

SQL Monitor is used to monitor the specified

If a statement wants to use SQL Monitor to monitor, it is not difficult to customize it individually. You can use Hint monitor to do this. For example, this form:

Select/*+ Monitor */COUNT (*) from EMP where xxxxx

If you do not need to put the monitoring scope, you can also use No_monitor to customize, such as this form

Select/*+ No_monitor */COUNT (*) from EMP where xxxxx

To see how SQL Monitor reports:

To view the generated monitoring information, you can use the following methods:

Select Dbms_sqltune.report_sql_monitor from dual;

In addition to calling the Dbms_sqltune package to get reports, there are 2 ways to get the SQL Monitor content:

(1) EM: performance-〉 in the lower right corner of SQL monitoring-〉monitored SQL Executions (2) SQL Developer: Tools Monitor SQL

Rich-Format SQL Monitor Report

Of course, these are some of the general knowledge of SQL Monitor, not enough to make my interest open. What I'm interested in is its powerful UI presentation capabilities. Sounds like this seems to have little relevance to this feature, if you see the effect you know, or that sentence, a very complex, abstract things if graphically expressed, far more than the text rich image.

The SQL Monitor report format is broadly available in the following formats. Text,html,active,xml four kinds, but we focus on the first three, although it looks like a variety of formats, we make an analogy easy to understand. Some phones will be labeled with a variety of names.

SQL Monitor's report can be so distinguished, the text format is the standard version, HTML is a high edition, Active is the exclusive version, we do not come virtual, to actually see the effect.

The effect of text formatting is as follows:

The HTML format works as follows:

SQL information, session information, execution plan at a glance, very intimate.

So what does the active format mean, is the most dazzling most comprehensive effect, when opened unexpectedly still have a small flash effect.

The reporting effect in the active format is as follows:

Some friends may see will say, this and the effect of HTML is different Ah, some, I give a picture you will understand.

The execution plan could have been read like this. Full table scan, index Scan, table connection information are all at a glance, the more complex the execution plan the more convenient way.
SQL text and binding variable information, click sql_id will pop up a small window.

Get the script for text,html report

Well, it looks so flashy, it's hard to get the report, it's very simple, just a SQL statement can be done, absolutely no title party meaning.

If you want to write a shell script, you embed an SQL statement, essentially calling Dbms_sqltune.report_sql_monitor. The script reads as follows:

Tmp_sql_id=$1 sqlplus-s/As Sysdba<<eof

Set Trimspool on Trim on

Set pages 0 Linesize 1000

Set long 1000000 longchunksize 1000000 SELECT dbms_sqltune.report_sql_monitor (sql_id = ' ${tmp_sql_id} ', Report_ Level = "All", type=> ' TEXT ') from dual; Eof

The only difference is where the type is. The text,html is set to text,html.

How to get the active format report

If it's in active format, let's just say a little bit about the background.

It is natural for this feature to be viewed in Enterprise Manager, and if you do not install EM, we cannot deploy an EM for this purpose. To achieve the same effect, there are two ways, one is to download the required script through the network, that is, online viewing, the other is offline view, you need to pre-download several scripts to local. So to get a report and show that the work can be done, the way is always more difficult.

If you want to get an online report, you can get it in the following way, the script reads as follows:

Tmp_sql_id=$1 sqlplus-s/As Sysdba<<eof

Set Trimspool on Trim on

Set pages 0 Linesize 1000

Set long 1000000 longchunksize 1000000 SELECT dbms_sqltune.report_sql_monitor (sql_id = ' ${tmp_sql_id} ', Report_ Level = "All", type=> ' ACTIVE ') from dual; Eof

You're not mistaken, this is almost the same way as text,html. There are some differences when you can open in a browser, which is the way to try to download several scripts from the Oracle site, which cannot be opened without a network.

What scripts are downloaded, which involves offline viewing of the content.

We can set up a directory structure locally, set up a similar site http://www.jeanron100 to create a jeanron100 directory locally:

Mkdir-p Jeanron100/sqlmon

Then download the appropriate script:

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

If you download it on Windows, simply enter the URL above.

The active report that generates the SQL statement is available in the following ways:

SELECT dbms_sqltune.report_sql_monitor (sql_id = ' ${tmp_sql_id} ', Report_level = ' all ', type=> ' ACTIVE ',base_path = ' Http://www.jeanron100/sqlmon ') from dual;

Copy to the results in HTML format, open in the local directory, offline reports suggested that with IE open will be smoother, some browsers may have other security restrictions. If you are interested, you can read the generated content, which uses XML parsing in a large number of ways.

Use of SQL Monitor list

If we want to do something on this basis, there are still many, such as the results of our view of V$sql_monitor.

Sql> Select COUNT (*) from V$sql_monitor;

COUNT (*)

----------

160

There are so many SQL statements that we have no way to get an overview of the information.

The following statements are used:

SELECT dbms_sqltune.report_sql_monitor_list (type = ' HTML ', report_level = ' all ') as the report from dual;

This method you use or not are there. Of course, you can extract data directly from the V$sql_monitor can also, overall, the effect is pretty good.

Powerful SQL Detail Report

There is no fun function, some. SQL Detail report, which is richer than the active report for SQL Monitor. The same statement is at a glance at different times of execution, which is especially helpful for analyzing performance issues.

You can also see the number of rows processed during the history execution, the number of executions, the DB time situation, and the number of indicators, very full.

Is it a hassle to get a report, or a SQL statement, but another method is called, note that the report format here is still active.

Set pages 0

Set Linesize 200

Col Comm Format A300

Set Long 99999999

SELECT Dbms_sqltune.report_sql_detail (

sql_id = ' xxxx ',

Report_level = ' All ',

Type=> ' ACTIVE '

) Comm

from dual;

If you view the report information for a specified time range, you can do so in the following way.

SELECT Dbms_sqltune.report_sql_detail (

sql_id = ' xxxx ',

Report_level = ' All ',

Type=> ' ACTIVE ',

Start_time=>to_date (' xxxx ', ' Yyyymmddhh24miss '),

Duration=> ' xxxx '

)

from dual;

Demining links

Finally to sweep a few thunder.

For the report of the text,html format, this dependence on the environment is very low, no reliance on the network, is very stable implementation, recommended to use.

For the SQL Monitor List report, you can actually use V$sql_monitor to come, some environments may run with the following error message.

For SQL Monitor's active format report, the online approach is still recommended, and offline viewing allows the script to be downloaded locally.

If the report shows an error as follows:

For online viewing, it may be a question of the generated report format and see if these options are set beforehand.

Set Trimspool on Trim on

Set pages 0 Linesize 1000

Set Long 1000000 longchunksize 1000000

For offline viewing may be the download of the script path problem, adjust it.

More customization for SQL Monitor

V$sql_monitor similar to the v$session mechanism, the problem caused by an SQL statement has been happening for some time, want to see the previous implementation, V$sql_monitor basically do not know, because the data has been brushed out.

We can enable a job in the background to find out, the data to cache, such as in the form of a flat file to collect, and then through the time stamp to distinguish management.

I believe we have a simple understanding and understanding of SQL Monitor, and I hope it helps.

Author Introduction Yang Jianrong

• Dbaplus Community co-sponsors. Currently working in Sohu, Oracle ace-a, yep members, over 7 years of database development and operations experience, good at telecommunications data Services, database migration and performance tuning. Oracle 10G ocp,ocm,mysql OCP certification, author of the Oracle DBA work note.

Ebs_dba_ Optimization: Mastering these features of SQL Monitor, SQL optimization will be like God help! Go