view counter

Feed items

New Presentation : On Nested Loop and Hash Join

I have uploaded a new presentation on Nested Loop and Hash Joins. This is based on the "Nested Loops and Consistent Gets" case study.

.
.
.

Getting the right statistics

Continuing with the post titled "Nested Loops and Consistent Gets" earlier today, what is the Execution Plan with statistics on more columns ?

SQL> exec dbms_stats.gather_table_stats('','TRANSACTIONS',estimate_percent=>100,granularity=>'ALL',-
> method_opt=>'FOR COLUMNS COUNTRY_CD SIZE 250, PRODUCT_CD SIZE 250, TXN_ID SIZE 250 ',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL>

Nested Loop and Consistent Gets

DBAs with a few years of experience and/or having read some material on performance tuning know that eliminating physical reads and achieving a high buffer cache hit ratio isn't necessarily a tuning goal.
Some DBAs have accepted the idea that, rather than physical reads, they should concentrate on logical reads -- consistent gets in case of queries.
But ...... there really is no "absolutely correct optimization target". Everything is relative.

Interpreting an AWR report when the ArchiveLog Dest or FRA was full

It can be very interesting / challenging / confusing / misleading / wrong to attempt to interpret an AWR report for the duration when ArchiveLogs could not be generated. Such a case is when the ArchiveLog Destination directory or FRA (whichever is in use) went to 100% full usage (or hit the db_recovery_file_dest_size and Oracle cannot auto-delete any files in the FRA, all files present being necessary to meet the RETENTION Policy).

Deleting SQL Plan Baselines

Following the example of capturing SQL Plan Baselines, here's a deletion :

SQL> select sql_handle, sql_text from dba_sql_plan_baselines
2 where sql_text like 'SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS %';

SQL_HANDLE SQL_TEXT
------------------------------ --------------------------------------------------------------------------------

Capturing SQL PLAN Baselines

An introductory :

SQL> select count(*) from dba_sql_plan_baselines ;

COUNT(*)
----------
2

SQL>
SQL> declare
2 ret_value pls_integer;
3 sql_id_in varchar2(30);
4 cursor c1 is
5 select distinct sql_id from v$sql,dba_users
6 where parsing_user_id = user_id
7 and username = 'HEMANT'
8 order by 1;
9 begin
10 open c1 ;
11 loop
12 fetch c1 into sql_id_in;
13 exit when c1%NOTFOUND;
14 dbms_output.put_line('For SQL_ID : ' || sql_id_in);

11g OCP

I passed the 1Z0-055 "Oracle Database 11g: New Features for 9i OCPs" examination today.

.
.
.

Getting all the instance parameters

A simple method (in 11g -- here tested in 11.2.0.1) to get all the database instance parameters.

Currently, I am using an SPFILE :

[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ls -ltr
total 32
-rw-r--r-- 1 oracle oracle 2851 May 16 2009 init.ora
drwx------ 2 oracle oracle 4096 Oct 30 2009 peshm_DBUA0_0
-rw-rw---- 1 oracle oracle 1544 Oct 30 2009 hc_DBUA0.dat
drwx------ 2 oracle oracle 4096 Oct 30 2009 peshm_orcl_0
-rw-rw---- 1 oracle oracle 24 Apr 7 2010 lkORCL

RMAN's COPY command

Following some thread on forums, I had logged a documentation query against the 11gR2 RMAN documentation on 31-Jan.

My question to Oracle's 11gR2 documentation team had been

"Why is the COPY command not documented in the Reference ?" (referring to the 11gR2 Backup and Recovery Reference).

I have today received a response from the documentation team :

view counter