Skip to main content

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 






Popular posts from this blog

Setting NLS_LANG on Windows for Oracle Database

Sometimes, it is difficult to deal with Windows Platform as it drains the hell out of us!! Recently, I have come across a situation where one of my client's requirement was to input Arabic language into Oracle Database [or] read/retrieve the output into Various client Applications such as PL/SQL Developer/SQL Developer/TOAD etc.. Inputting the non-english language into Database  has never been difficult as we are given plenty of language options within our beloved Oracle Database, but, the problem lies within the complex Windows OS when user wanted to view the data in his/her beloved language, in Applications such as PL/SQL Developer or SQL Developer etc.. This post is all about it.

Character Set, Character encoding, & Code point Yes, it is a group of characters that is recognised by the Hardware through the OS Interface. Every character is allocated a number, called a code point, these code points will be represented in the computer by one or more bytes. So, every character h…

Data Guard Configuration with Oracle DGMGRL

About Data GuardEfficient business operations, quality customer service, compliance with government regulations, and safeguarding corporate information assets all require high levels of data protection and data availability. Thus it is no surprise that data protection and data availability are among the top priorities for enterprises of all sizes and industries.
A set of questions one needs to ask him/herself is what If a disaster impacts ongoing business transactions, How soon can those systems be back to business again, How much Data is affordable to loose, is it acceptable? The only answer for all these questions to position a proper "Business Continuity Plan (BCP)" in place so that it helps business to grow inline with its consumer expectations.  Technically, this is where Oracle's Data Guard plays a vital role that ensures superior reliability, and rock solid performance. DataGuard is Oracle's complete Disaster Recovery Solution which can reliably deliver aggressi…

IPC Send timeout detected - The Story of a Database

Some errors make us panic, nervous, and beyond belief when we positively attempt to resolve a panic situation. Specific to case of Clusterware where it attempts to resolve a conflict by removing dead resources from the cluster but it suffers because of lack of additional resources to complete the task, and makes the whole situation even worst so everything that has in contact with the key resource also suffers. The key resource what we are talking is a RAC Database and the Clusterware under a panic situation is Oracle Clusterware.

Here's the environment
Oracle RDBMS 11.2.0.4.4Oracle GI 11.2.0.4.43 Node RAC Database with vault function enabledNetApp NFS Filer based Storage
What was the issue?
On Instance 3 I have noticed "IPC Send timeout". Fri Aug 14 04:46:05 2015 IPC Send timeout detected. Receiver ospid 20545 [ Fri Aug 14 04:49:55 2015 And then, Oracle has started suspending MMON actions one by one as we noticed below messages in the log Fri Aug 14 10:49:07 2015 Suspending M…