首页 > 代码库 > Colored SQL--How to force the AWR to capture a specified SQL even if it is not the top one
Colored SQL--How to force the AWR to capture a specified SQL even if it is not the top one
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 inclusion 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 the 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;
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 inclusion 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 the 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
Colored SQL--How to force the AWR to capture a specified SQL even if it is not the top one
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。