Vishal desai’s Oracle Blog

December 7, 2012

Monitoring Long Running Query using gv$sql_monitor

Filed under: Exadata, Oracle Enterprise Manager, Performance Tuning — vishaldesai @ 4:13 pm

To proactively monitor long running queries, metric extensions can be created in Oracle Enterprise Manager 12c. I have created metric extension on Exadata platform but it can also be used on non Exadata platform.

Below are steps to create metric extension in Oracle Enterprise Manager 12c.

1. Navigate to Enterprise -> Monitoring -> Metric Extensions

2. Below Metric Extension click on Create

3. General properties tab: Select Target Type as Cluster Database, Specify name and display name, select Adapter as SQL and choose appropriate frequency based on your requirement.

4. Adapter: SQL Query select key,sql_id,username,sql_id,elapsed_time  from gV$SQL_MONITOR where status=’EXECUTING’ and username is not null

Columns: Add following as Name, Display Name, Column Name, Value Type, Comparison Operator, Warning & Critical
key – Key – Key Column – Number
sql_id – sql_id – Data Column – String
username – username – Data Column – String
sql_id – sql_id – Data Column – String
elapsed_time – elapsed_time – Data Column – Number – > 900000000 – 3600000000

Elapsed_time is in micro seconds so choose appropriate values according to your environment. You can choose lower values for aggressive monitoring.

5. Credentials: Database Credentials Use Default Monitoring Credentials.

6. Test Targets: Here you can add couple of targets and test whether Metric Extension is working or not.

7. Review: You can review selections you made on previous screens.

8. Finish: Click on Finish to create Metric Extension.

9. Once the Metric Extension is created, click on action tab and convert template to deployable draft. Again choose Metric Extension and choose Deploy to target.

10. Provided you have setup email/pager notification in your Oracle Enterprise Manager 12c, you will get notified on long running queries.

Example: I got notification for one sql_id that my adhoc user was running on Exadata platform. As you can see from below there was space missing after full hint and query was running in serial mode with smart scan feature turned off. I notified user and he fixed query hint and query ran in 4 minutes instead of 90 plus minutes.

SELECT /*+ parallel(r,16),full(r)*/  MIN(from_dos),MAX(from_dos) 
FROM CSUSER.REPOS_CLAIM_NEW r 
WHERE  r.proc_type ='C'   
AND r.CHARge > 0.99 
AND substr(r.error_flag, 17, 1) IN ('H', 'L', ' ')     
AND nvl(trim(mdr_flag),'~') IN('~','D','C') 
AND substr(error_flag,9,1) IN (' ','O') 

PLAN hash value: 3370285159 

--------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
--------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                    |                           |       |       |   983K(100)|          |       |       | 
|   1 |  SORT AGGREGATE                     |                           |     1 |    83 |            |          |       |       | 
|   2 |   PARTITION RANGE ALL               |                           |  3713M|   287G|   983K  (1)| 03:16:45 |     1 |   119 | 
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| REPOS_CLAIM_NEW           |  3713M|   287G|   983K  (1)| 03:16:45 |     1 |   119 | 
|   4 |     BITMAP CONVERSION TO ROWIDS     |                           |       |       |            |          |       |       | 
|   5 |      BITMAP AND                     |                           |       |       |            |          |       |       | 
|   6 |       BITMAP OR                     |                           |       |       |            |          |       |       | 
|*  7 |        BITMAP INDEX SINGLE VALUE    | REPOS_CLM_CHRG_FLG_IDX    |       |       |            |          |     1 |   119 | 
|*  8 |        BITMAP INDEX SINGLE VALUE    | REPOS_CLM_CHRG_FLG_IDX    |       |       |            |          |     1 |   119 | 
|*  9 |        BITMAP INDEX SINGLE VALUE    | REPOS_CLM_CHRG_FLG_IDX    |       |       |            |          |     1 |   119 | 
|* 10 |       BITMAP INDEX SINGLE VALUE     | REPOS_CLAIM_PROC_TYPE_IDX |       |       |            |          |     1 |   119 | 
--------------------------------------------------------------------------------------------------------------------------------- 

Advertisements

1 Comment »

  1. For parallel queries elapsed time will be different for QC and slaves. Change Adapter SQL to capture maximum elapsed_time as follows:

    select * from
    (select sql_id,sql_exec_id,round(max(elapsed_time)/1000000)
    from gV$SQL_MONITOR where status=’EXECUTING’
    group by sql_exec_id,sql_id);

    Comment by vishaldesai — February 3, 2014 @ 3:40 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: