Colored SQL in AWR report

Colored SQL in AWR report:

AWR captured all the SQL which was running in database?

No.

MMON captured only top resource consumed queries based on
STATISTICS_LEVEL parameter and dbms_workload_repository.modify_snapshot_settings topnsql variable value.

I want to capture some sql in AWR report. But these queries were not top resource consumed queries.So AWR doesn't capture these queries.

Is there any way to capture less resource consumed queries?

In oracle 10g, we need to increase the threshold very high in topnsql
variable (dbms_workload_repository.modify_snapshot_settings - default is
30 %). It will capture most of the sql in AWR, but it will affect the
snapshot performance.

Oracle 11g provide the new feature to capture less resource intensive
queries very easily. This feature is called Colored SQL.
Using less resource intensive query SQL_ID, We can capture these SQL in
AWR.

exec dbms_workload_repository.add_colored_sql(' 8371e4332dagt');

Remove from our AWR capturing:

exec dbms_workload_repository.remove_colored_sql(' 8371e4332dagt');

Using this feature, we easily track the historical query performance.

I Hope this article helped to you. I am expecting your suggestions/feedback.
It will help to motivate me to write more articles.

Best Regards
Rajabaskar Thangaraj
http://dbarajabaskar.blogspot.com