Search This Blog

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 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-13To check if the colored SQL ID is captured, please take the AWR Report over its next interval

Popular posts from this blog

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…

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…

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.