Plsql_ Monitor the number of executions and frequencies of some SQL

Original: Plsql_ monitor the number of executions and frequencies of some SQL

2014-12-25 Created by Abalone new

I. Summary

In Oracle database application tuning, the number of executions/frequencies of an SQL is often a concern, either because a SQL execution is too frequent, or because the application design is flawed, it needs to be optimized for business logic, or is caused by business specificity.

If you execute frequent SQL, you tend to encounter some concurrency problems.

So how to see the Oracle database of a SQL execution frequency/number, Xiaoxiang hidden classmate collation as follows, Jiehuaxianfo:)

Method 1: View the number of SQL executions by querying the executions of V$sqlarea or v$sql;

Method 2: Find out the number of executions of some SQL by Dba_hist_sqlstat Association Dba_hist_snapshot;

Method 3:awr report to see the number of executions of a SQL;

Two or three methods of analysis

1. View the number of SQL executions by querying the executions of V$sqlarea or V$sql

(1). Disadvantages

However, the validity of this value needs to be judged in conjunction with First_load_time, because historical data is not stored in V$sqlarea or V$sql.

Has a certain timeliness, so if you want to query a long time before a certain number of SQL execution can not be done.

(2). About V$sqlarea Field Introduction

First_load_time VARCHAR2 (+) Timestamp of the parent creation time

Executions number total number of executions, totalled through all the child cursors

(3). How to Query

SELECT sql_id, sql_text,first_load_time, executions  from V$sqlarea WHERE = ' 497wh6n7hu14f '

(4). Summary

If you empty the shared pool at this point, you will see that the number of executions for the corresponding SQL in V$sqlarea is zeroed.

If you want to see how many times the SQL statement was executed, the above SQL statement must be executed during these two time periods, and the difference of two executions indicates the number of times the SQL statement was executed during that time period.

Executions are global, and often cannot see how many times a session or user has executed. This is one of its limitations.

2. Use Dba_hist_sqlstat Association Dba_hist_snapshot to find out the number of executions of some SQL.

(1). Disadvantages

However, some of the snapshots are not captured by some SQL. This also makes it impossible to see the number of executions through the following SQL statement.

It is also said that this method is flawed. The more frequently you execute the statement, the easier it is to be crawled by snapshot.

(2). Execution syntax

  SELECTm.sql_id, To_char (N.begin_interval_time,'YYYY-MM-DD') "DATETIME",           SUM(m.executions_delta) executions fromdba_hist_sqlstat M, Dba_hist_snapshot NWHEREm.snap_id=n.snap_id andM.dbid=N.dbid andM.instance_number=N.instance_number andM.instance_number= 1            andTo_char (N.begin_interval_time,'YYYY-MM-DD')= '2014-12-25'            andm.sql_id= '497wh6n7hu14f'GROUP  bym.sql_id, To_char (N.begin_interval_time,'YYYY-MM-DD')ORDER  bym.sql_id

3. The AWR report looks at the number of executions of a SQL, as in the above, and the AWR report is also affected by snapshot. Does not necessarily capture the SQL that you need to query

4. View the SQL that has the most executions of the current database, for example, query the SQL statement for top 15 that executes most frequently.

SELECT    sql_text, executions  from   (SELECT   sql_text,                   executions,                     Over (ORDERbyDESC) Exec_rank              from      WHERE   <= (a)

I. Summary

When doing the consultation, check the operation of the previous SQL, and the conditions of the query

When querying bound variables, there are hard parsing and soft parsing points, and if it is soft parse, the value of the variable is difficult to find

Several views of the query binding variable are as follows:

• Query V$sql View
• Query V$sql_bind_capture
• Query Dba_hist_sqlbind
• Query Wrh$sqlstat

Second, query V$sql view

1. Query Bind_data

where Sql_text l'%select * from test where id1%';  

Its recording frequency is controlled by the _cursor_bind_capture_interval implicit parameter, the default value of 900, indicating that the binding value is recorded every 900 seconds, can be by alter system set "_cursor_bind_capture_ Interval "= 10;

2. Bind_data

'%from test11%'; 

The data that is queried at this point is worth the form: beda0b2002004f8482d10065ffff0f000000003132303431313, which needs to be converted by dbms_sqltune.extract_binds

Third, query v$sql_bind_capture

With the V$sql_bind_capture view, you can view the bound variable, but this view is not very force-capable, only capturing the value of the bound variable for the last record.

And the interval of two captures has an implicit parameter control. The default is 900 seconds before the capture starts again. Within 900, changes to the value of the bound variable are not reflected in this view.

After 10G, you can view the values of the SQL bound variables recorded in the AWR report in the following ways.

where sql_id='abhf6n1xqgrr0';   

Iv. Inquiry Dba_hist_sqlbind

After 10G, you can view the values of the SQL bound variables recorded in the AWR report in the following ways.

Select snap_id, name, position, value_string,last_captured,was_capturedFrom Dba_hist_sqlbindwhere sql_id=‘576c1s91gua19 and Snap_id= 20433 ' 

The Dba_hist_sqlbind view is powerful in that it records the value of the binding variable of SQL in each AWR report, and of course the value of the bound variable is obtained from the V$sql_bind_capture sample when the AWR is generated.

With this view, we are able to get more bound variable values, which are generally sufficient for us to troubleshoot the problem.

It is also important to note that the binding variables recorded in these two views are only captured by the bindings following the where condition, which requires attention when used.

Query dba_hist_sqlbind value_string Column

Dba_hist_sqlbind is a snapshot of the view v$sql_bind_capture history

V. Inquiry WRH$SQLSTAT

A view of two external query-bound variables

1). value_stringfrom wrh$_sqlstat'88dz0k2qvg876'
----------Increase Dbms_sqltune.extract_bind (Bind_data, 2) based on the number of bound variables. value_string, etc.

Reference: aaaaaaaa2000-http://blog.csdn.net/aaaaaaaa2000/article/details/7401110

Reference: Xiaoxiang Hermit-http://www.cnblogs.com/kerrycode/p/4111746.html (slightly typesetting)

Plsql_ Monitor the number of executions and frequencies of some SQL