Oracle VM Site Review - Oracle VM Health Check
view counter

Feed items

Understanding Obsolescence of RMAN Backups

Questions about the the LIST OBSOLETE command keep appearing in RMAN forums.

Given this configuration :

RMAN> show all;


RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

CREATE INDEX ..... PARALLEL

We generally use the PARALLEL keyword to speed up index creation thus : CREATE INDEX ..... PARALLEL n. But also results in the Parallel attribute being set for the index, which means that Oracle may choose to read the index using Parallel Query operators.

Gather Column (Histogram) Stats can use an Index

We use the METHOD_OPT parameter to specify columns where we want specific Histograms when running a GATHER_TABLE_STATS. Oracle can actually make use of an Index to gather such column statistics.

To demonstrate, I run these statements :


23:24:40 SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

23:28:46 SQL> exec dbms_stats.gather_table_stats('HEMANT','GTS_DIRECT',-
23:29:01 > method_opt=>'FOR COLUMNS CUST_ID SIZE 250',estimate_percent=>100);

Does GATHER_TABLE_STATS update Index Statistics ?

In 9i, the GATHER_TABLE_STATS procedure would default CASCADE to FALSE.

In 10g and 11g, the procedure defaults CASCADE to AUTO.

In theory, you would expect that if the optimizer knows that the number of updates to the index since the last gather index stats is not significant, the AUTO behaviour for CASCADE would mean that Oracle would not include Indexes when running a GATHER_TABLE_STATS.

(Of course, if the Index statistics are old and there has been significant DML since then, AUTO would cause Index statistics to be gathered).

Reading an AWR Report -- 3

Continuing the Reading an AWR report series ....

I run these statements :


SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.15
SQL> set serveroutput on
SQL> declare
2 cnt number;
3 begin
4 select sum(sale_qty) into cnt
5 from very_large_fact_table
6 where prod_id = 5;
7 dbms_output.put_line('Sales for Prod 5 : ' || cnt);
8 select sum(sale_qty) into cnt
9 from very_large_fact_table

Singapore RACSIG meeting today

Today's Singapore RACSIG meeting went well. Ravi brought up a "problem" case in RAC Architecture which we discussed for some time. Next was a review of GoldenGate. Here, too, there were many questions and many answers !

We plan to have another workshop in September. Stay Tuned !
Piet de Visser was a guest at our meeting today. I am sure he was happy to have attended the meeting and participated in the discussions.

High value IT jobs moving back to USA.

Not very good news for skilled people in India? TOI: IT firms to suffer as angry America wants its jobs back

Published with Blogger-droid v1.7.4

Reading an AWR -- 2

I have extracted this from an AWR report :

      

Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
277.8 1 277.85 97.6 14.9 59.4 8madvr3cvp929
Module: SQL*Plus
select count(*), sum(sale_qty), sum(discount_rate) from very_large_fact_table


CPU CPU per Elapsed

Oracle 11g RAC Essentials

PACKT Publishing had invited me to review the new 11g RAC book "Oracle 11g R1/R2 Real Application Clusters Essentials". This book, published in May 2011, is an update on the earlier book "Oracle 11g R1/R2 Real Application Clusters Handbook" published in June 2010.

More on COUNT()s -- 2

Continuing with my previous posts "Running a COUNT(Column) versus COUNT(*)" and"More on COUNT()s", I now go on to demonstrate some more "twists" :


SQL> drop table COUNT_ROWS_TBL ;

Table dropped.

SQL> create table COUNT_ROWS_TBL (Column_1 number, Column_2 number, Column_3 varchar2(100)) ;

Table created.

SQL> insert into COUNT_ROWS_TBL
view counter