Oracle VM Consulting
view counter

Feed items

RMAN Tips -- 1

Restoring ArchiveLogs to an alternate location


RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
806 1 237 A 11-SEP-11

Outer Join Queries

Outer Join queries are difficult to understand initially. Effectively, an Outer Join query allows the developer to create dummy rows (with NULLs for the returned columns) for a table when a join to another table would fail. Thus, the developer can see rows in the other table for which there are no corresponding entries in the "join failed" table.

Splitting a Range Partitioned Table

Here's a simple demonstration of splitting a Range Partitioned Table :


SQL> --- Create a Table with a MAXVALUE partition
SQL> drop table sales purge;

Table dropped.

SQL> create table sales
2 (order_id number not null,
3 sale_date date,
4 cust_id number,
5 prod_id number,
6 remarks varchar2(128))
7 partition by range (sale_date)
8 (partition P_2007 values less than (to_date('01-JAN-2008')),

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

view counter