Find Top N resource absorbing SQL Queries

At times database performance problems arise that require your diagnosis and correction. Sometimes problems are brought to your attention by users who complain about slow performance. Other times you might notice performance spikes in the Host CPU chart on the home page. Suppose user reported slow performance and this occurred in night or in holiday. Now You need to see which sql queries were run at that specific time along with their resources usages details.

With default settings, in every hour a snapshots taken by Automatic Workload Repository (AWR) and those snapshots are available for next 7 days. Here is a SQL query that give the desired top N resource sensitive queries :

SELECT * FROM
(SELECT X.SNAP_ID, X.SQL_ID,SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA,3) AVG_ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000/X.EXECUTIONS_DELTA,3) AVG_CPU_TIME_SEC
,X.ELAPSED_TIME TOTAL_ELAPSED_TIME_MIC_SEC
,X.CPU_TIME TOTAL_CPU_TIME_MIC_SEC
,X.EXECUTIONS_DELTA EXECUTIONS
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SNAP_ID,DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE
BEGIN_INTERVAL_TIME >= TO_DATE('18-feb-2009 18:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('18-feb-2009 18:40', 'dd-mon-yyyy hh24:mi'))
AND PARSING_SCHEMA_NAME='SCOTT'
GROUP BY DHSS.SQL_ID,DHSS.SNAP_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY AVG_ELAPSED_TIME_SEC DESC
) WHERE rownum < =200;

DRIVING_SITE SQL Hint

We all are know about Oracle Data Base Link and I called it dblink in short throughout this article. Usisg dblink you can easily access remote Database Object. Now Please think the following situation:

Situation:

Suppose you have 2 tables: 1. table1 in local DB 2. table2 in remote DB.The table1 size is 10 MB and table2 size is 100 MB. We need to join those two tables and we access only in local DB.

If you now think about performance, it is important to where the sql query executed and the join perform. For above situation, it is better to bring table1 from local DB to Remote DB and execute the sql in remote server and finally bring back the result in local DB.

Lets do the job

The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization. The syntax of this hint is:

/*+DRIVING_SITE(table)*/

where table is the name or alias for the table at which site the execution should take place.

Example:

SELECT /*+DRIVING_SITE(table2)*/ * FROM tabl1, table2@remote_db
WHERE table1.DEPTNO = table2.DEPTNO;

If this query is executed without the hint, rows from table2 will be sent to the local site and the join will be executed there. With the hint, the rows from table1 will be sent to the remote site and the query will be executed there, returning the result to the local DB.