Slow Statistics purging (SYSAUX grows) - Delete Taking long

Recently i came accross a problem where one delete statement as below was taking almost more than 10 hours :

DELETE /*+ dynamic_sampling(4) */ FROM sys.wri_optstat_histgrm_history WHERE savtime < :1 AND ROWNUM <= NVL (:2, ROWNUM)

It is actually purging the old statistics from sysaux table and pointing towoards a bug


For an workaround we use below, before applying any patch:

Bug 10279045 - Slow Statistics purging (SYSAUX grows) [ID 10279045.8]

SQL> alter table sys.WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace SYSAUX;
Table altered.

SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild nologging parallel 8;
Index altered.

SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST noparallel;
Index altered.
　
SQL> alter index sys.I_WRI$_OPTSTAT_H_ST rebuild nologging parallel 8;
Index altered.

SQL> alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST noparallel;
Index altered.

Datapump - Space Reduction

With the introduction of 11g feature with expdp/expdp compression parameter , we all are using it and greatly helps in reducing our dump size.
But do we know this is even better then gzip and produce half size then gzip. here are details:

The parameter has three options:
METDATA_ONLY - only the metadata is compressed; the data is left as it is (available in Oracle Database 10.2 as well).
... DATA_ONLY - only the data is compressed; the metadata is left alone.
ALL - both the metadata and data are compressed.
NONE - this is the default; no compression is performed.
Here is how you compress the export of the table UNITS_FACT:
$ expdp global/global directory=dump_dir tables=units_fact dumpfile=units_fact_comp.dmp compression=all

For comparison purposes, export without compression:
$ expdp global/global directory=dump_dir tables=units_fact dumpfile=units_fact_uncomp.dmp

Now if you check the files created:
$ ls -l
-rw-r----- 1 oracle dba 2576384 Jul 6 22:39 units_fact_comp.dmp
-rw-r----- 1 oracle dba 15728640 Jul 6 22:36 units_fact_uncomp.dmp

The compression ratio is 100*(15728640-2576384)/15728640 or about 83.61%! That's fairly impressive; the uncompressed dumpfile is 15MB while the compressed one is 1.5MB.
If you compress the dumpfile using gzip:
$ gzip units_factl_uncomp.dmp

-rw-r----- 1 oracle dba 3337043 Jul 6 22:36 units_fact_uncomp.dmp.gz

The compressed file is about 3.2MB, double the size of the compressed file in Data Pump. So, in addition to the compression being potentially more efficient, the decompression really adds value. When importing the dumpfile, the import does not have to decompress the file first; it decompresses as it reads it, making the process really fast.

Colored SQL - 11g New Feature

Colored SQL

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;

This feature is extremely useful when you want to focus on a specific SQL in your tuning exercises