The number/frequency of SQL queries in ORACLE and the number of SQL queries in oracle

The number/frequency of SQL queries in ORACLE and the number of SQL queries in oracle

In ORACLE Database Application optimization, the execution Frequency/frequency of an SQL statement is also frequently concerned, because the execution of an SQL statement is too frequent, or because of application design defects, the business logic needs to be optimized or caused by the special nature of the business. If you execute frequent SQL statements, you may encounter concurrency problems.

So how can I view the execution Frequency/times of an SQL statement in an ORACLE database? How can this problem be solved?

Method 1:You can query EXECUTIONS of V $ SQLAREA or V $ SQL to view the number of SQL statements executed. However, the validity of this value must be determined based on FIRST_LOAD_TIME. Because V $ SQLAREA or V $ SQL does not store historical data and has a certain degree of timeliness, it cannot be done if you want to query the number of SQL executions a long time ago.

V $ SQLAREA

FIRST_LOAD_TIME VARCHAR2 (19) Timestamp of the parent creation time

Executions number Total number of executions, totalled over all the child cursors

As shown below, we use an example to demonstrate how to query the number of executions of a statement.

SQL> COL  START_TIME FOR A20;

SQL> SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS') AS START_TIME FROM DUAL;

START_TIME

--------------------

2014-11-20 13:51:21

SQL> 

SQL> SELECT SQL_ID, SQL_TEXT,FIRST_LOAD_TIME, EXECUTIONS

  2  FROM V$SQLAREA

  3  WHERE SQL_TEXT LIKE '%SELECT * FROM TEST%';

SQL_ID                 SQL_TEXT                   RST_LOAD_TIME    EXECUTIONS

----------- -------------------------------------- ---------------- ----------

SQL> SELECT * FROM TEST;

         ID NAME

----------- ----------

SQL> 

SQL> SELECT SQL_ID, SQL_TEXT,FIRST_LOAD_TIME, EXECUTIONS

  2  FROM V$SQLAREA

  3  WHERE SQL_TEXT LIKE '%SELECT * FROM TEST%';

SQL_ID                SQL_TEXT                   FIRST_LOAD_TIME    EXECUTIONS

----------- -------------------------------------- ---------------- --------------

4ntr8ag38ujwd  SELECT * FROM TEST                  2014-11-20/13:51:40      1

SQL> SELECT * FROM TEST;

         ID NAME

----------- ----------

SQL> 

SQL> SELECT SQL_ID, SQL_TEXT,FIRST_LOAD_TIME, EXECUTIONS

  2  FROM V$SQLAREA

  3  WHERE SQL_TEXT LIKE '%SELECT * FROM TEST%';

SQL_ID                SQL_TEXT                 FIRST_LOAD_TIME   EXECUTIONS

------------- -------------------------------- ------------------- ----------

4ntr8ag38ujwd  SELECT * FROM TEST              2014-11-20/13:51:40      2

If the Shared Pool is cleared at this time, you will find that the EXECUTIONS count of the corresponding SQL statement in V $ SQLAREA is cleared.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered

SQL> SELECT * FROM TEST;

         ID NAME

----------- ----------

SQL> 

SQL> SELECT SQL_ID, SQL_TEXT,FIRST_LOAD_TIME, EXECUTIONS

  2  FROM V$SQLAREA

  3  WHERE SQL_TEXT LIKE '%SELECT * FROM TEST%';

SQL_ID                SQL_TEXT                    FIRST_LOAD_TIME      EXECUTIONS

------------- ----------------------------------- ------------------- ----------

4ntr8ag38ujwd  SELECT * FROM TEST                2014-11-20/13:52:38     1

SQL> 

If you want to view the number of times the SQL statement is executed in a certain period of time, you must execute the preceding SQL statement in these two periods. The difference between the two EXECUTIONS statements indicates the number of times the SQL statement is executed during this period of time. EXECUTIONS is global and often cannot view how many times a session or user has performed. This is also one of its limitations.

Method 2:Use DBA_HIST_SQLSTAT to associate DBA_HIST_SNAPSHOT to find the number of SQL statements executed, but some snapshots do not capture some SQL statements. In this way, you cannot view the number of executions using the following SQL statement. That is to say, this method is flawed. The more frequently the statement is executed, the more easily it is captured by SNAPSHOT.

SELECT M.SQL_ID ,

       TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD')   "DATETIME",

       SUM(M.EXECUTIONS_DELTA)  EXECUTIONS

FROM DBA_HIST_SQLSTAT M, DBA_HIST_SNAPSHOT N

WHERE M.SNAP_ID  = N.SNAP_ID

  AND M.DBID = N.DBID

  AND M.INSTANCE_NUMBER = N.INSTANCE_NUMBER

  AND M.INSTANCE_NUMBER=1

  AND TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD') ='2014-11-20'

  AND M.SQL_ID=&SQL_ID

GROUP BY M.SQL_ID , TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD')

ORDER BY M.SQL_ID

Method 3:The AWR report shows the number of times a SQL statement is executed. The same as above, the AWR report is also affected by SNAPSHOT. Not necessarily capture the SQL you want to query

View the SQL statements that run the most frequently in the current database, for example, the TOP 15 most frequently executed SQL statements.

SELECT SQL_TEXT, EXECUTIONS

  FROM (SELECT SQL_TEXT,

               EXECUTIONS,

               RANK() OVER(ORDER BY EXECUTIONS DESC) EXEC_RANK

          FROM V$SQLAREA)

 WHERE EXEC_RANK <= 15;

References:

Http://www.itpub.net/thread-1320984-1-1.html