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). Disadvantage: But the validity of this value needs to be judged by combining first_load_time, because the V$sqlarea or V$sql does not save historical data, 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). Cons: But some of the snapshots do not capture 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)

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

Plsql_ Monitor the number of executions and frequencies of some SQL