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