Saturday, April 13, 2013

Colored SQL

Ever troubled to capture a particular SQL statement which is found to be consuming significantly potential database resources, wanted to keep a record of its trend or behaviour over times upon various tuning attempts.  AWR snapshot does not include all SQLs in its report unless it is identified to be a TOP SQL Statement. In such cases, it never used to be possible to capture a desired SQL Statement to monitor its trend over various tuning attempts. 

To give DBA the ability to capture such desired SQL Statements, Oracle has introduced a procedure called "add_colored_sql()" with the package "dbms_workload_repository" in 11g that inturn marks the SQL as "colored", so that SQL ID which is marked as colored will be captured in every AWR snapshot, It does not need to be a top SQL Statement.


To color the SQL Statement,
begin
dbms_workload_repository.add_colored_sql( sql_id => 'f93g1utkcbzy1'
);end;
/

To remove the colored SQL Statement from AWR snapshots,
begin dbms_workload_repository.remove_colored_sql( sql_id => 'f93g1utkcbzy1'
);end;
/

Note : We need the SQL_ID inorder to color it


Following dictionary views/tables can be queries to find the status,
SQL> SELECT TNAME FROM TAB WHERE TNAME LIKE '%COLOR%';
TNAME
------------------------------
WRM$_COLORED_SQL
DBA_HIST_COLORED_SQL



To check the status of any colored SQL Statements,

SQL> select * from DBA_HIST_COLORED_SQL

  2  /
      DBID SQL_ID CREATE_TI
---------- ------------- ---------
 223864828 f93g1utkcbzy1 13-APR-13

To check if the colored SQL ID is captured, please take the AWR Report  over its next interval 






1 comment:

  1. We won’t be 블랙잭 stepping into the dense, mathematical nitty gritty here – thankfully that’s all been worked out by individuals far smarter than you and I . To get into the swing of video poker methods, all want to|you must} do is comply with our video poker trainer and the guidelines we’ve laid out under. Do that, and you’ll be utilising video poker strategy very quickly.

    ReplyDelete

replica sets, Replication with mongoDB

Before we get into details of mongoDB replica sets, let's understand what is replication and why is it needed? Replication is a config...