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…

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…

Rebuilding Standby Database Manually (11g and up)

One of my remote customers has been facing severe network outages between Production and DR sites to reasons unknown and challenges with certain geographic limitations, the bandwidth between sites is limited to 2MBPS and the replication between sites is aching slow. So, Customer has had tentative requirements to rebuild Standby more often between sites until the network issue is fully resolved.

This paper outlines the procedure to rebuild the STANDBY manually provided we have following things in place already between/on sites. And this procedure has a special case for One node RAC, Oracle dynamically applies SID naming convention in case of an online relocation. Due to this fact, I have to mention which SID to export before logging to the Database via SQLPLUS/RMAN/ASMCMD
(PR refers to - Production/Primary and PR1/PR2 are two nodes in RAC,  DR refers to - Disaster Recovery/Standby and DR1/DR2 are two nodes in RAC at DR Site)

A working Dataguard Configuration between Sites (All Paramete…