Colored SQL -- How to force the AWR to capture a specified SQL even if it is not the top one, SQL -- howawr

Colored SQL -- How to force the AWR to capture a specified SQL even if it is not the top one, SQL -- howawr
This new feature doesn' t paint the SQL statement in color; rather, it sort of marks it as "important ."

Suppose you are troubleshooting a performance issue and you suspect a specific SQL statement contributing to the problem. you want the SQL statement to be captured in every Automatic Workload Repository (AWR) snapshot. but AWR snapshots do not capture all SQL statements; just the top ones. how can you force a specific SQL to be captured, regardless of its impact sion in the top SQLs?

The procedure add_colored_ SQL () in the package dbms_workload_repository marks the SQL as "colored", or important enough to be captured in every AWR snapshot regardless of whether SQL is in top SQLs. first, identify the SQL statement and then get its SQL _ID. to color it, use:


Begin
Dbms_workload_repository.add_colored_ SQL (
SQL _id => 'ff15115dvgukr'
);
End;


To find out which SQLs have been colored, you can query the AWR table WRM $ _ COLORED_ SQL:


SQL> SELECT * FROM wrm $ _ colored_ SQL;

DBID SQL _ID OWNER CREATE_TI
-----------------------------------------
2965581158 ff15115dvgukr 1 05-APR-08


Thereafter the SQL statement with ID ff15115dvgukr will be captured in every snapshot, even if it's not in the top SQL statements. (Of course, the SQL must be present in the library cache to be captured in the AWR snapshots .)

But what if the SQL ceases to be that colorful-that is, not important enough to be captured? You can turn it off by issuing the obverse of the procedure.

Begin
Dbms_workload_repository.remove_colored_ SQL (
SQL _id => 'ff15115dvgukr'
);
End;

This feature is extremely useful when you want to focus on a specific SQL in your tuning exercises.

For more information, Pls refer to http://www.oracle.com/technetwork/articles/ SQL /11g-misc-091388.html#top