Sangam 2011: Less than a month now

Sangam11, AIOUG's annual conference is starting in the first week of December-2011. To be precise it's on 9th and 10th of December.

The IT capital of India will be hosting 3rd annual meetup of AIOUG. World renowned experts like Murali Vallath, Arup Nanda, Hemant Chitale are leading the show.

If you are in India then grab this opportunity to learn and network. For agenda and registrations click here.

Results of the Second International NoCOUG SQL Challenge are Out

The Second International NoCOUG SQL Challenge was published on 2/13/11 in the February 2011 issue of the NoCOUG Journal. The challenge was to find the secret message hidden in a seemingly random collection of words.

Andre Araujo, Rob van Wijk, and Ilya Chuhnakov are the winners of this competition. Pythian was the sponsor of the competition and each winner will receive an Amazon Kindle.

The full announcement can be read in the 100th issue of the NoCOUG Journal.

Resolving “library cache pin” Waits

The old traditional export (exp) utility is hanging indefinitely at “exporting system procedural objects and actions” stage of the export.

 
[oracle@node1 ~]$ /orabackup/exp/prtdb/exp_prtdb.sh

Export: Release 11.2.0.1.0 - Production on Sun Aug 14 21:39:03 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit                                          Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,                                          OLAP,
Data Mining and Real Application Tes
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions

Following are the details from V$SESSION view of this session:

SID:				190
SERIAL#:			379
USERNAME:			EXPIMP
STATUS:				ACTIVE
PROGRAM:			exp@node1 (TNS V1-V3)
LAST_CALL_ET:			1363
BLOCKING SESSION STATUS:	NOT IN WAIT
EVENT#:				280
EVENT:				library cache pin
WAIT CLASS:			Concurrency
STATE:				WAITED SHORT TIME
BLOCKING SESSION:						
  

As you can see the session is waiting on “library cache pin” wait event. This means there is at least one other session holding a lock on some stored procedure which “exp” is trying to pin. The “BLOCKSING SESSION” column does not report which session is holding a lock (or for that matter blocking).

I used “oradebug” utility to analyze the situation. Following is an excerpt from the log file generated by “oradebug”:

    Oracle session identified by:
    {
                instance: 1 (prtdb.prtdb1)
                   os id: 24885
              process id: 49, oracle@node1
              session id: 13
        session serial #: 839
    }
    is waiting for 'Streams AQ: waiting for messages in the queue' with wait info:

Below is the information about session “13” from V$SESSION view:

SID:				13
SERIAL#:			841
USERNAME:			DBSNMP
STATUS:				ACTIVE
PROGRAM:			emagent@node1 (TNS V1-V3)
LAST_CALL_ET:			5
BLOCKING SESSION STATUS:	UNKNOWN
EVENT#:				364
EVENT:			Streams AQ: waiting for messages in the queue
WAIT CLASS:		Idle
STATE:			WAITING
BLOCKING SESSION:						

The culprit session belongs to “DBSNMP” user and it is waiting on an “Idle” wait event.

It was easy to kill this session as it did not belong to any application. After killing the session (13), export happily started working and things were all normal.

. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table                    DEF$_AQCALL
:
:
:

References:

Note:215858.1 - Interpreting HANGANALYZE trace files to diagnose hanging and performance problems

Note: 175006.1 Steps to generate HANGANALYZE trace files

Note: 402983.1 Master Note: How to diagnose Database Performance

Diagnosing Library Cache Latch Contention: A Real Case Study By Arup Nanda

Confused SRVCTL in Oracle 11g R2

OS: RHEL 5.5 64-bit

DB: Oracle Database 11gR2

Grid: Oracle Clusterware 11gR2

A database “adverten” is running on a Linux (64-bit) box. This database happens to be a two-node Oracle RAC database. The instance names of this database are “adverten1” and ““adverten2” respectively on the two nodes.

I ran the RACcheck tool but unfortunately it does not list this database. However other databases running on this server are listed.

Below is the excerpt from “raccheck” tool:

List of running databases registered in OCR

1. clonedb

2. frisdb

3. All

4. None

Believe me; the instance is there and running.

 
[oracle@node1 raccheck]$ ps -ef|grep pmon
grid      8585     1  0 Aug06 ?        00:00:17 asm_pmon_+ASM1
oracle   12351  8299  0 22:16 pts/7    00:00:00 grep pmon
oracle   21959     1  0 Aug09 ?        00:00:31 ora_pmon_clonedb1
oracle   24222     1  0 Aug09 ?        00:00:19 ora_pmon_adverten1
oracle   29006     1  0 Aug07 ?        00:00:32 ora_pmon_frisdb1
[oracle@node1 raccheck]$

It’s time to debug “raccheck” tool.

“is_rdbms_installed_crs ()” procedure of the “raccheck” script is of our interest for now and below are the statements from where it is actually fetching database names.

It grabs the list of databases using the following:

       $CRS/bin/srvctl config database > $db_list_fil

And checks for database status using the following:

 
crs_db_status=$($CRS/bin/srvctl status database -d $db_list|grep -i $loc	alnode|grep -ic "is running")

Okay, running the first command manually lists my database:

 
[grid@node1 ~]$ $CRS_HOME/bin/srvctl config database
advertence
clonedb
frisdb
[grid@node1 ~]$

If you notice, the database name listed above is “advertence” (10 letter word). The database name from V$DATABASE view is “adjudica” (8 letter word), also instances use “adverten”.

 
SQL> select name from v$database;

NAME
---------
ADJUDICA

SQL>

However, the second command could not locate the database instances:

[grid@node1 ~]$ $CRS_HOME/bin/srvctl status database -d advertence
Instance adverten1 is not running on node node1
Instance adverten2 is not running on node node2
[grid@node1 ~]$

Of course “adverten” is not registered resource. So the following will fail:

 
[grid@abis19 ~]$ $CRS_HOME/bin/srvctl status database -d adverten
PRCD-1120 : The resource for database adverten could not be found.
PRCR-1001 : Resource ora. adverten.db does not exist
[grid@abis19 ~]$

This is the reason “raccheck” is not able to list this database.

What might have happened is the question that needs to be answered.

At the time of database creation, “advertence” was keyed in as the database name. But Oracle created a database by trimming it to 8 characters and reserving 1 character for instance number. But for some reason “advertence” database resource was registered.

Apparently, the “ora. advertence.db” resource registered with the cluster is in “OFFLINE” state.

 
[grid@node1 ~]$ crs_stat  ora. advertence.db
NAME=ora. advertence.db
TYPE=ora.database.type
TARGET=OFFLINE
STATE=OFFLINE

[grid@node1 ~]$

It’s all messy here, but things are working.

I will have to create a Service Request with Oracle Support to resolve this issue for this client of mine.

VirtaThon: My Sessions Schedule

BrainSurface's VirtaThon conference has already started. Today is the second day of the virtual conference. These are a lot of interesting sessions from Oracle Expert's around the world.

I am speaking today and following are the details of my session:

RMAN: Recovery Procedures

Time slot: 17 July 14:30 - 15:25 EDT

Virtual Room: # 200

A detailed session schedule could be found here.

Attend4FREE: 6 Days of Expert+ Speakers & Sessions FOR Oracle, MySql and Java technologies

Attend4Free! 100% Virtual - No Travel Required!

What is VirtaThon?

Attend, participate & learn cutting edge knowledge from recognized domain experts from all over the world: All within the realm of your broadband connection at the largest Independent "Virtual Conference" for the Oracle, Java & MySQL Communities. Aimed at a global audience, VirtaThon was conceived to revolutionize Online Conferencing, focusing on the core goal of facilitating the inexpensive dissemination of expert knowledge to the masses.

Almost everyone of us would like to attend world-class physical conference events but, given these globally tough economic times that we live in, can we afford the costs of attendance, travel, time-off, lodging and such? How about attending a LIVE virtual conference from wherever you choose to be in the world?

The biggest prohibiting reasons in attending physical Professional Conferences translate into various inhibiting factors such as travel, time-off, lodging, not to mention the attendance fees that can easily run into the thousands of dollars. VirtaThon bridges this gap and makes it incredibly easy to attend a 6-day Conference event from the comfort of your PC. All you need is a broadband Internet connect and voilà , you are right there: Attending 6 days of cutting-edge expert-level sessions and interacting/benefiting from world renowned experts in the Oracle, Java & MySQL domains.

Keynotes:

With keynotes from Steven Feuerstein, Arup Nanda, Dr. Bert Scalzo, Eddie Awad, Tim Gorman, Jeremy Schneider, David Koelle, Guy Harrison, Tariq Farooq and a 4-hour Gold Star Deep-Dive session from Mike Ault, this a DO-NOT-MISS virtual learning event of a lifetime.

Session Schedule

For a detailed session schedule click here.

How to register for free?

Click here to register.

Hope to see you all there.

Master Notes from Oracle Support

My flight is delayed by 25 minutes, so thought of sharing this post with you all. Below is the list of master notes from Oracle Support. These are the topics that are on my to-do list. I am sure you too would like to read them. Search for “master note” on Oracle Support for a complete list.

11g New Features - Database Core [ID 1226873.1]

Master Note for Diagnosing ORA-4031 [ID 1088239.1]

Master Note for ORA-1555 Errors [ID 1307334.1]

Master Note for Oracle Backup and Recovery [ID 1199803.1]

Master Note for Automatic Storage Management (ASM) [ID 1187723.1]

Master Note of Linux OS Requirements for Database Server [ID 851598.1]

Master Note for Data Guard [ID 1101938.1]

Master Note for Oracle Database Client Installation [ID 1157463.1]

Master Note for the Oracle OLAP Option [ID 1107593.1]

Master Note for Streams Performance Recommendations [ID 335516.1]

Master Note for Handling Oracle Database Corruption Issues [ID 1088018.1]

Master Note for Transparent Data Encryption (TDE) [ID 1228046.1]

Master Note: SQL Query Performance Overview [ID 199083.1]

Master Note: How to diagnose Database Performance - FAQ [ID 402983.1]

Master Note for Query Rewrite [ID 1215173.1]

Master Note for Oracle Flashback Technologies [ID 1138253.1]

Master Note for Oracle Disk Manager [ID 1226653.1]

Master Note for Real Application Clusters (RAC) Oracle Clusterware and Oracle Grid Infrastructure [ID 1096952.1]

Master Note - RDBMS Large Objects (LOBs) [ID 1268771.1]

Master Note for Oracle Database Server Installation [ID 1156586.1]

Master Note for Oracle Database Downgrade [ID 1151427.1]

Master Note for Oracle Recovery Manager (RMAN) [ID 1116484.1]

Master Note for OLTP Compression [ID 1223705.1]

Master Note for Oracle Database Upgrades and Migrations [ID 1152016.1]

Master Note for Partitioning [ID 1312352.1]

Master Note for 11g Diagnosability - ADR and Packaging [ID 1283137.1]

Master Note for Oracle Database Machine and Exadata Storage Server [ID 1187674.1]

Happy reading!!!

Addressing Block corruption that is not part of any segment

Yesterday’s RMAN backup failed with ORA-19566 error reporting block corruption, here’s the error as reported by RMAN:

channel ORA_DISK_1: starting piece 1 at 20-JUN-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =========================================================== 
RMAN-03009: failure of backup command on c1 channel at 20/06/2011 11:55:11
ORA-19566: exceeded limit of 0 corrupt blocks for file xxxxxxxxxx

The next logical step is to run DB Verify (dbv) utility to find out what blocks are corrupt. Dbv confirmed block corruption as shown below:

Corrupt block relative dba: 0x0346c3cf (file 113, block 229845)
Bad check value found during backing up datafile
Data in bad block -
type: 6 format: 2 rdba: 0x0346c3cf

:
:

DBVERIFY - Verification complete

Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
:
:

Below are the OS and database details:

-bash-3.2$ cat /etc/*-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
-bash-3.2$
-bash-3.2$ uname -r
2.6.18-194.el5
-bash-3.2$

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>

Note that Block 229845 is corrupt in datafile 113. The next step in the process is to identify the object by querying DBA_EXTENTS view

SQL> select segment_name, segment_type, owner
  2  from dba_extents
  3  where file_id = 113
  4  and 229845 between block_id and block_id + blocks -1;

no rows selected

SQL>

Apparently, this block is not associated with any of the segments in the database. The next question to ask is where this corrupt block lies in the data file. Is it in between the allocated blocks after the HWM?

SQL> select max(block_id) from dba_extents where file_id = 113;

MAX(BLOCK_ID)
-------------
       182520

SQL>

So, the corrupt block is beyond the maximum allocated block. Think of the data file as shown below (blue=used data, white=empty blocks, red=corrupt block).

The easiest solution is to shrink the datafile below the corrupt block, this way we drop the corrupt block off the datafile.

Identify the datafile size:

SQL> select bytes/1024/1024 size_mb  from dba_data_files where file_id = 113;

   SIZE_MB
----------
      2048

Identify the location of the maximum block in the datafile by multiplying it with the blocksize (8K):

SQL>  select max(BLOCK_ID) * 8/1024 size_mb from dba_extents where file_id = 113;

   SIZE_MB
----------
 1425.9375

SQL>

Also, find out the location of the corrupt block in the datafile:

SQL> select 229845 * 8/1024 size_mb from dual;

   SIZE_MB
----------
1795.66406

SQL>

Resize the datafile beyond the maximum allocated data block and below the corrupt block:

SQL> alter database datafile 113 resize 1450m;

Database altered.

SQL>

After resizing the datafile run DBV utility to check for corrupt blocks:

DBVERIFY - Verification complete

Total Pages Examined         : 188160
Total Pages Processed (Data) : 62277
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 71630
Total Pages Failing   (Index): 0
Total Pages Processed (Lob)  : 9337
Total Pages Failing   (Lob)  : 0
Total Pages Processed (Other): 21867
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 23049
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0) 

There are no corrupt blocks in the datafile. Happy ending. 

But it wouldn’t have been this easy if the corrupt block were somewhere in between the allocated blocks. This solution will not be helpful in that case. Refer to following note from Oracle Support to address this issue:

How to Format Corrupted Block Not Part of Any Segment [ID 336133.1]

Grid Infrastructure Startup Issues

We have a two node RAC (Oracle 11gR2) configuration running on Linux environment. This is a test environment where lot of testing happens. The second node of the two node RAC configuration was down and it refused to start. The OHASD was up but CRS, CSS, and EVMD were offline.

I started troubleshooting the issue in the following order:

1) Reading the ohasd.log and found nothing worth interesting.

2) No file permission issues on the disks were reported.

3) Voting Disks are located in ASM and were also accessible to the second node.

4) Moving ahead, following error was reported in the ocssd.log

2011-04-06 21:13:57.781: [    CSSD][1111677248]clssnmvDHBValidateNCopy: node 1, tptrac1, has a disk HB, but no network HB, DHB has rcfg 183608157, wrtcnt, 41779628, LATS 3108751988, lastSeqNo 41779625, uniqueness 1294698909, timestamp 1302104569/3108688378

The error message clearly says there is no network heart beat between the two nodes. Indeed, it failed when I tried to ping using the private IP address. On node1, “eth1” was messed up as shown below:

 
[root@tptrac1 ~]# ifconfig eth1
eth1      Link encap:Ethernet  HWaddr F4:CE:46:84:F7:CA
          inet6 addr: fe80::f6ce:46ff:fe84:f7ca/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:3244634 errors:0 dropped:0 overruns:0 frame:0
          TX packets:6800251 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:2828581056 (2.6 GiB)  TX bytes:1669807875 (1.5 GiB)
          Interrupt:162 Memory:f6000000-f6012800

[root@tptrac1 ~]#

I reassigned the private IP address as shown below:

ifconfig eth1 10.28.177.1 netmask 255.255.255.128 up

I was able to ping after setting the private IP address on Node1. It was now time to stop and start the cluster.

-bash-3.2$ ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
-bash-3.2$

Well, the cluster happily came up without reporting any errors.

I would have saved all the troubleshooting time if I had checked node reachability in the first place. Anyways, it was a good troubleshooting exercise and I also got something to share on my blog.

Errors when installing “Guest Additions” using Oracle VM Virtual Box & Oracle Enterprise Linux

After upgrading Oracle VM VirtualBox from 3.x to 4.0.4, I created a new virtual machine and installed Oracle Enterprise Linux 5.2 (64-bit). While installing “Guest Additions” on the guest OS, I received the following error message:

***************************************************

Building the VirtualBox Guest Additions kernel modules

The headers for the current running kernel were not found. If the following module compilation fails then this could be the reason.

The missing package can be probably installed with

yum install kernel-devel-2.6.18-92.el5 [FAILED]

***************************************************

The error description message in the “/var/log/vboxadd-install.log” log file was pretty clear. One of the RPM’s was missing.

Below is my first (failed) attempt of “Guest Additions” installation:

 [root@rac1 u01]# ./VBoxLinuxAdditions.run
Verifying archive integrity... All good.
Uncompressing VirtualBox 4.0.4 Guest Additions for Linux.........
VirtualBox Guest Additions installer
Removing installed version 4.0.4 of VirtualBox Guest Additions...
Removing existing VirtualBox DKMS kernel modules           [  OK  ]
Removing existing VirtualBox non-DKMS kernel modules       [  OK  ]
Building the VirtualBox Guest Additions kernel modules
The headers for the current running kernel were not found. If the following module compilation fails then this could be the reason.
The missing package can be probably installed with
yum install kernel-devel-2.6.18-92.el5
                                                           [FAILED]
Your system does not seem to be set up to build kernel modules.
Look at /var/log/vboxadd-install.log to find out what went wrong.
Once you have corrected it, you can run

  /etc/init.d/vboxadd setup

to build them.

Doing non-kernel setup of the Guest Additions              [  OK  ]
Installing the Window System drivers
Installing X.Org 7.1 modules                               [  OK  ]
Setting up the Window System to use the Guest Additions    [  OK  ]
You may need to restart the hal service and the Window System (or just restart
the guest system) to enable the Guest Additions.

Installing graphics libraries and desktop services componen[  OK  ]
[root@rac1 u01]# 

To get the thing working, we need to install the following RPM’s:

• kernel-devel-2.6.18-92.el5.x86_64.rpm

• kernel-headers-2.6.18-92.el5.x86_64.rpm

• glibc-headers-2.5-24.x86_64.rpm

• glibc-devel-2.5-24.x86_64.rpm

• libgomp-4.1.2-42.el5.x86_64.rpm

• gcc-4.1.2-42.el5.x86_64.rpm

I had to install 6 packages as they are interdependent.

 [root@rac1 Server]# rpm -Uvh kernel-devel-2.6.18-92.el5.x86_64.rpm 
Preparing...                ########################################### [100%]
   1:kernel-devel           ########################################### [100%]
[root@rac1 Server]# 
[root@rac1 Server]# 
[root@rac1 Server]# rpm -Uvh kernel-headers-2.6.18-92.el5.x86_64.rpm 
Preparing...                ########################################### [100%]
   1:kernel-headers         ########################################### [100%]
[root@rac1 Server]# 
[root@rac1 Server]# 
[root@rac1 Server]# rpm -Uvh glibc-headers-2.5-24.x86_64.rpm 
Preparing...                ########################################### [100%]
   1:glibc-headers          ########################################### [100%]
[root@rac1 Server]# 
[root@rac1 Server]# 
 [root@rac1 Server]# rpm -Uvh glibc-devel-2.5-24.x86_64.rpm 
Preparing...                ########################################### [100%]
   1:glibc-devel            ########################################### [100%]
[root@rac1 Server]# 
[root@rac1 Server]# 
[root@rac1 Server]# rpm -Uvh libgomp-4.1.2-42.el5.x86_64.rpm 
Preparing...                ########################################### [100%]
   1:libgomp                ########################################### [100%]
[root@rac1 Server]# 
[root@rac1 Server]# 
[root@rac1 Server]# rpm -Uvh gcc-4.1.2-42.el5.x86_64.rpm 
Preparing...                ########################################### [100%]
   1:gcc                    ########################################### [100%]
[root@rac1 Server]# 

After installing the above packages, my “Guest Additions” installation was successful.

[root@rac1 u01]# ./VBoxLinuxAdditions.run
Verifying archive integrity... All good.
Uncompressing VirtualBox 4.0.4 Guest Additions for Linux.........
VirtualBox Guest Additions installer
Removing installed version 4.0.4 of VirtualBox Guest Additions...
Removing existing VirtualBox DKMS kernel modules           [  OK  ]
Removing existing VirtualBox non-DKMS kernel modules       [  OK  ]
Building the VirtualBox Guest Additions kernel modules
Your guest system does not seem to have sufficient OpenGL support to enable
accelerated 3D effects (this requires Linux 2.6.27 or later in the guest
system).  This Guest Additions feature will be disabled.


Building the main Guest Additions module                   [  OK  ]
Building the shared folder support module                  [  OK  ]
Doing non-kernel setup of the Guest Additions              [  OK  ]
Starting the VirtualBox Guest Additions                    [  OK  ]
Installing the Window System drivers
Installing X.Org 7.1 modules                               [  OK  ]
Setting up the Window System to use the Guest Additions    [  OK  ]
You may need to restart the hal service and the Window System (or just restart
the guest system) to enable the Guest Additions.

Installing graphics libraries and desktop services componen[  OK  ]
[root@rac1 u01]# 
[root@rac1 u01]# 

I can now move the mouse freely between the host & guest OS’s, copy & paste, …

Oracle VM VirtualBox Upgrade - Version 4.0.4

I have been running on Oracle VM VirtualBox 3.x for quite sometime. I use to ignore the upgrade dialog box and kept saying to myself that I will upgrade next time. Finally, I made my mind to upgrade to the latest and the greatest version “4.0.4”. The upgrade was pretty straight forward.

When I tried to start one of the Virtual Machines, I was greeted with the following error:

I blamed myself for upgrading the stuff but the error message was quite straight forward. I was asked to install “Oracle VM VirtualBox Extension Pack”. You can download it from here.

When you try to install a Warning pops up as shown below. Click on the “Install” button and you are done.

The next message that pops up is the success of extension pack installation.

I can now start all my VM’s. ;)

Transpose Rows to Columns

In the old Oracle 8i days, transposing rows to columns was not straight forward. As Oracle Developer (working on Oracle Developer 2000), I remember writing a query using MAX and DECODE functions to transpose rows into columns for my previous employer. My efforts were applauded as this query replaced the old database stored procedure (function). Thanks to Tom Kyte for his wonderful asktom.oracle.com site.

Here’s a sample function which was then in use:

SQL> create or replace function test_func(p_deptno in emp.deptno%type)
  2    return varchar2 is
  3    cursor emp_cur is
  4       select deptno, ename
  5         from emp
  6        where deptno = nvl(p_deptno, deptno)
  7        order by ename;
  8    l_ename varchar2(500);
  9  begin
 10    for emp_rec in emp_cur loop
 11      l_ename := l_ename ||','|| emp_rec.ename;
 12    end loop;
 13    return(substr(l_ename, 2));
 14  end;
 15  /

Function created.

SQL>
SQL> column new_col format a50
SQL>
SQL>
SQL> select test_func(10) new_col from dual;

NEW_COL
--------------------------------------------------
CLARK,KING,MILLER

SQL> select test_func(20) new_col from dual;

NEW_COL
--------------------------------------------------
ADAMS,FORD,JONES,SCOTT,SMITH

SQL> select test_func(30) new_col from dual;

NEW_COL
--------------------------------------------------
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>
SQL> select deptno, test_func(deptno) new_col
  2    from (select distinct deptno from emp)
  3    order by deptno;

    DEPTNO NEW_COL
---------- -----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>
SQL>

I stumbled upon Analytic Functions that were introduced with Oracle 8i and came up with the following query:

SQL> select deptno, rtrim(new_col, ',') new_col
  2    from (select deptno,
  3                 max(decode ( rn , 1, ename || ',')) ||
  4                 max(decode ( rn , 2, ename || ',')) ||
  5                 max(decode ( rn , 3, ename || ',')) ||
  6                 max(decode ( rn , 4, ename || ',')) ||
  7                 max(decode ( rn , 5, ename || ',')) ||
  8                 max(decode ( rn , 6, ename )) new_col
  9            from (select ename, deptno,
 10                         row_number() over ( partition by deptno
 11                                             order by rownum) rn
 12                    from emp)
 13            group by deptno)
 14  order by deptno;

    DEPTNO NEW_COL
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

SQL>

The major drawback of the above query is the limitation of number of values it can display for the ENAME column. If a new employee joins department number “30” then this query has to be modified. This query was well suited for the environment where it was written as the number of values was known and were limited.

Then came Oracle 9i introducing SYS_CONNECT_BY_PATH function. The above limitations are overcome using this new function. It is capable of transposing “n” number of rows into columns. The same query can now be rewritten as:

SQL>
SQL> select deptno,
  2         max(substr(sys_connect_by_path (ename, ','),2)) new_col
  3   from (select deptno, ename,
  4                row_number() over (partition by deptno
  5                                    order by ename) rno
  6          from emp)
  7  start with rno = 1
  8  connect by rno = prior rno + 1
  9      and prior deptno = deptno
 10  group by deptno
 11  order by deptno;

    DEPTNO NEW_COL
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>

Well, so far so good. But Oracle 11g introduced LISTAGG function. This function makes life very easy. The query looks very simple, no hierarchies, no connect by ….

Here’s the new query:

SQL> 
SQL> select deptno,
  2         listagg(ename, ',') within group 
  3            (order by ename) new_col
  4    from emp
  5  group by deptno
  6  order by deptno;

    DEPTNO NEW_COL
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>

Isn’t it simple? Over the years Oracle has introduced tons of new features (like the one discussed here) that make life simple and at the same time making the code more performant.

The question is "are we really using these features efficiently?"

Tsunami in Japan

A massive tsunami triggered by one of the largest earthquakes smashed into Japan, killing hundreds of people. It swept away boats, cars and homes.

My heartfelt condolences to the people who have suffered or lost their near and dear ones in the disaster.

Extend your help to the Tsunami victims here.

Massive Deletes Vs Truncating Partitions

This is not a revolutionary post, everything I discuss here has been discussed numerous times. Massive deletes are nothing new in a DBA’s life. They were there, they are there and they will continue to be there. From time to time DBA’s fall prey to the bad application design. My last post was about heavy redo log switches and this post is a continuation.

The application was performing massive DELETE operations resulting in excessive redo being generated. This in turn was responsible for too many redo log switches and severely affecting database performance. It was possible that the same DELETE statement on a non-partitioned table could be translated into a “ALTER TABLE … TRUNCATE PARTITION” statement by partitioning the same table.

In this post, I will demonstrate how one can achieve extreme performance by employing right techniques for the right job.

For this demo, I will create a test table “T” (without any indexes) with a single column. Populate the table with 2 Million records and then DELETE 1 Million records. Before and after the delete we will make a note of the redo generated.

Here we go:

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t (id number) tablespace test_ts;

Table created.

SQL>
SQL> insert into t select level from dual connect by level <=2000000;

2000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Before we delete, let’s make a note of redo generated by our session:

SQL> column name format a20
SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                30.93

SQL>

Now perform the massive DELETE operation:

SQL>
SQL> set timing on
SQL> delete from t where id <= 1000000;

1000000 rows deleted.

Elapsed: 00:02:12.87
SQL> set timing off
SQL>

Make a note of redo generated by the DELETE statement:

SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                257.41

SQL>

So, the DELETE statement generated 226.48 MB (257.41 – 30.93) of redo and it took over 2 minutes to clock time.

Now, let us improvise the purge strategy but partitioning the table. I range partition the table into six partitions as shown:

SQL> drop table t purge;

Table dropped.

SQL> create table t (id number)  tablespace test_ts
  2  partition by range (id) (
  3  partition p1 values less than (500000),
  4  partition p2 values less than (1000000),
  5  partition p3 values less than (1500000),
  6  partition p4 values less than (2000000),
  7  partition p5 values less than (2500000),
  8  partition p6 values less than (MAXVALUE)
  9  );

Table created.

SQL>

Now run the same INSERT statement against the partitioned table:

SQL> insert into t select level from dual connect by level <=2000000;

2000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Make a note of redo before and after executing the purge command:

SQL>
SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                288.53

SQL>
SQL> set timing on
SQL> alter table t truncate partition p1;

Table truncated.

Elapsed: 00:00:00.62
SQL> alter table t truncate partition p2;

Table truncated.

Elapsed: 00:00:00.29
SQL> set timing off
SQL>
SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                288.64

SQL>

Purging of two partitions consumed less than a second and generated only 0.11 MB of redo.

In some situations it may be possible that the redo logs were sized inadequately but in this case it was the application’s bad design responsible for massive redo log generation.

What happens when you have additional columns and indexes associated with the table? You could take that as a further exercise.

Redo Log Switches at their Peak

One of our customers was having extreme performance issues with their Oracle database. During my investigations I found they had 215 redo log switches per hour. This means a 3.65 log switches per minute or a redo log switch every 16.7 seconds.

Although this was not the only reason of performance issues but this was certainly one of the causes. The redo log files were sized pretty small and the poor database was spending most of its time switch the redo log files.

Here’s a screenshot for you guys:

Below is the query I use to find redo log switches. This query lists redo log switches per hour for the last seven days.

column day format a10
column Switches_per_day format 9999
column 00 format 999
column 01 format 999
column 02 format 999
column 03 format 999
column 04 format 999
column 05 format 999
column 06 format 999
column 07 format 999
column 08 format 999
column 09 format 999
column 10 format 999
column 11 format 999
column 12 format 999
column 13 format 999
column 14 format 999
column 15 format 999
column 16 format 999
column 17 format 999
column 18 format 999
column 19 format 999
column 20 format 999
column 21 format 999
column 22 format 999
column 23 format 999

select to_char(first_time,'DD-MON') day,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "00",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "01",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "02",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "03",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "04",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "05",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "06",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "07",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "08",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "09",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "12",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "13",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "14",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "15",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "16",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "17",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "18",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "19",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "20",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "21",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "22",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "23",
count(to_char(first_time,'MM-DD')) Switches_per_day
from v$log_history
where trunc(first_time) between trunc(sysdate) - 6 and trunc(sysdate)
group by to_char(first_time,'DD-MON') 
order by to_char(first_time,'DD-MON') ;

Updated: The redo log files were sized at 25 MB. To relieve the database from this pain, I increased the size of the redo log files to 1 GB each.

Updated-2: Here is the post related to this.

Shared SQL and Cursors

Recently I had a discussion on “Shared SQLs and Cursors” with one of my colleagues and during the discussion it came out that “shared SQL” and “cursors” are misunderstood. I thought of blogging on this topic to help those who still misunderstand these topics.

As per his understanding “Shared SQL” and “Cursors” are the same and shared among all user sessions. But in fact:

Shared SQL is the SQL residing in the shared pool. This SQL statement can be shared among all the database sessions. The shared SQL is at the database level, all sessions can see and use them.

A Cursor points to some shared SQL residing in the shared pool. You may have more than one cursor pointing to the same shared SQL. A cursor is at the session level. So, many database sessions may point to the same shared SQL.

The below diagram should help understand this concept:

Below is a demonstration of the same. I have four database connections, one as “SYS” user and other three connected as user “TEST”. I make a note of SIDs of each database connection as user “TEST”. My session ids are listed below:

“TEST” User ---------> SID

TEST Session – 1 ---------> 30

TEST Session – 2 ---------> 27

TEST Session – 3 ---------> 26

I shall be using table “T1” for this test which is residing in “TEST” schema.

SQL> desc t1
 Name             Null?    Type
 ---------------- -------- -------------
 ID               NOT NULL NUMBER
 NAME             NOT NULL VARCHAR2(30)

SQL>

Below is the query that will help us with our experiment. Make sure that you use the same query in all the sessions. For simplicity you could save this query in a file.

select /*momen*/ * from t1 where id = 1;

Session – SYS:

As user SYS, flush the shared pool and query V$SQL and V$OPEN_CURSOR views for our query:

SQL> alter system flush shared_pool;

System altered.

SQL> select parse_calls, executions, invalidations, loads, sql_id, substr(sql_text, 1, 20) from v$sql where sql_text like '%momen%';

PARSE_CALLS EXECUTIONS INVALIDATIONS      LOADS SQL_ID        SUBSTR(SQL_TEXT,1,20
----------- ---------- ------------- ---------- ------------- --------------------
          1          1             0          1 fnndahay4xbgv select parse_calls,

SQL>
SQL> select sid, sql_id, sql_text from v$open_cursor where user_name='TEST' and sql_text like '%momen%';

no rows selected

SQL>

Rightly, this query is not found in the shared pool.

TEST Session – 1:

Execute the query in session – 1

SQL> select /*momen*/ * from t1 where id = 1;

        ID NAME
---------- ------------------------------
         1 ICOL$a

SQL>

Session – SYS:

As the user SYS again query the V$SQL and V$OPEN_CURSOR view for our query.

SQL> select parse_calls, executions, invalidations, loads, sql_id, substr(sql_text, 1, 20) from v$sql where sql_text like '%momen%';

PARSE_CALLS EXECUTIONS INVALIDATIONS      LOADS SQL_ID        SUBSTR(SQL_TEXT,1,20
----------- ---------- ------------- ---------- ------------- --------------------
          1          1             0          1 fvnwsdghq898n select /*momen*/ * f
          2          2             0          1 fnndahay4xbgv select parse_calls,
          1          1             0          1 6wcgj9dxb3yvx select sid, sql_id,

SQL>
SQL> select sid, sql_id, sql_text from v$open_cursor where user_name='TEST' and sql_text like '%momen%';

       SID SQL_ID        SQL_TEXT
---------- ------------- ------------------------------------------------------------
        30 fvnwsdghq898n select /*momen*/ * from t1 where id = 1

SQL>

Our query now appears in both V$SQL & V$OPEN_CURSOR views. There’s one cursor now pointing to the shared SQL residing in the shared pool.

TEST Session – 2:

Now run the same query in another session.

SQL> select /*momen*/ * from t1 where id = 1;

        ID NAME
---------- ------------------------------
         1 ICOL$a

SQL>

Session – SYS:

Go back to the SQL*Plus session connection as user SYS and execute the same set of queries against V$SQL & V$OPEN_CURSOR.

 SQL> select parse_calls, executions, invalidations, loads, sql_id, substr(sql_text, 1, 20) from v$sql where sql_text like '%momen%';

PARSE_CALLS EXECUTIONS INVALIDATIONS      LOADS SQL_ID        SUBSTR(SQL_TEXT,1,20
----------- ---------- ------------- ---------- ------------- --------------------
          2          2             0          1 fvnwsdghq898n select /*momen*/ * f
          3          3             0          1 fnndahay4xbgv select parse_calls,
          2          2             0          1 6wcgj9dxb3yvx select sid, sql_id,

SQL>
SQL> select sid, sql_id, sql_text from v$open_cursor where user_name='TEST' and sql_text like '%momen%';

       SID SQL_ID        SQL_TEXT
---------- ------------- ------------------------------------------------------------
        30 fvnwsdghq898n select /*momen*/ * from t1 where id = 1
        27 fvnwsdghq898n select /*momen*/ * from t1 where id = 1

SQL>

As seen above, we now have two cursors pointing to the same shared SQL.

TEST Session – 3:

You may stop here or proceed to see how one more cursor is created to point to the same shared SQL in the shared pool.

SQL> select /*momen*/ * from t1 where id = 1;

        ID NAME
---------- ------------------------------
         1 ICOL$a

SQL>

Session – SYS:

SQL> select parse_calls, executions, invalidations, loads, sql_id, substr(sql_text, 1, 20) from v$sql where sql_text like '%momen%';

PARSE_CALLS EXECUTIONS INVALIDATIONS      LOADS SQL_ID        SUBSTR(SQL_TEXT,1,20
----------- ---------- ------------- ---------- ------------- --------------------
          3          3             0          1 fvnwsdghq898n select /*momen*/ * f
          4          4             0          1 fnndahay4xbgv select parse_calls,
          3          3             0          1 6wcgj9dxb3yvx select sid, sql_id,

SQL>
SQL> select sid, sql_id, sql_text from v$open_cursor where user_name='TEST' and sql_text like '%momen%';

       SID SQL_ID        SQL_TEXT
---------- ------------- ------------------------------------------------------------
        30 fvnwsdghq898n select /*momen*/ * from t1 where id = 1
        27 fvnwsdghq898n select /*momen*/ * from t1 where id = 1
        26 fvnwsdghq898n select /*momen*/ * from t1 where id = 1

SQL>

We now have three cursors pointing to the same shared SQL.

So, a cursor and shared SQL are two different things. The same shared SQL can have more than one cursor pointing to it.

Import Table Statistics to Another Table

It’s been a long time since I posted on my blog. This post is in response to the question that I received through email:

<<

Just like to know if I can use dbms_stats.import_table_stats to import table statistics to another table?

I am wondering if I can import table statistics from T1 to T2 using dbms_stats package?.

>>

The simple answer to the above question is “NO”. You cannot copy statistics to a different table even if they are identical. However, there are two ways of achieving this:

1) Temporarily rename the destination table (T_IDENTICAL) as original table (T) and import statistics or

2) Break into the statistics table.

Option -1 is pretty straight forward so we will not discuss that. Let’s see how to break into statistics table.

Let us create the two tables: source table “T” and the destination table “T_IDENTICAL”. I am creating both these tables in the same schema, so as to avoid exporting and importing of statistics table.

SQL>
SQL> drop table t purge;

Table dropped.

SQL> drop table t_identical purge;

Table dropped.

SQL>
SQL> create table t as select rownum id, object_name name from all_objects where rownum < 1001;

Table created.

SQL>
SQL> create table t_identical as select * from t where 1=2;

Table created.

SQL>
SQL>

Collect statistics for the source table (T)

SQL>
SQL> exec dbms_stats.gather_table_stats(user, 't');
PL/SQL procedure successfully completed.
SQL>
SQL> select table_name, num_rows from user_tables where table_name in ('T', 'T_IDENTICAL');
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T                                    1000
T_IDENTICAL
SQL>

Create a statistics table (“MY_STATS_TAB”) and export statistics of the source table “T”.

SQL>
SQL> exec dbms_stats.create_stat_table('TEST','MY_STATS_TAB');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.export_table_stats('TEST','T',NULL,'MY_STATS_TAB');
PL/SQL procedure successfully completed.
SQL>

Now let us try to import statistics to the destination table (T_IDENTICAL):

SQL> exec dbms_stats.import_table_stats('TEST','T_IDENTICAL', null, 'MY_STATS_TAB');
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select table_name, num_rows from user_tables where table_name in ('T', 'T_IDENTICAL');
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T                                    1000
T_IDENTICAL
SQL>

No affect. 

Oracle simply ignored my request without reporting any errors.

As we have source table statistics in a safe place, let us delete statistics from the source table and import them back from the container table. (This step ensures stats import is working as expected)

SQL> exec dbms_stats.delete_table_stats(user, 'T');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables where table_name in ('T', 'T_IDENTICAL');

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T
T_IDENTICAL

SQL>

SQL>
SQL> exec dbms_stats.import_table_stats('TRADE','T', null, 'MY_STATS_TAB');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables where table_name in ('T', 'T_IDENTICAL');

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T                                    1000
T_IDENTICAL

SQL>
SQL>

As you can see, import works only when the source and destination tables are same.

Let us see what’s there in the MY_STATS_TABLE:

SQL>
SQL> select count(*) from my_stats_tab;

  COUNT(*)
----------
         3

SQL>
SQL>
SQL> select c1, c5 from my_stats_tab;

C1                             C5
------------------------------ ------------------
T                              TRADE
T                              TRADE
T                              TRADE

SQL>
SQL>

Columns “C5” and “C1” of the statistics table represent schema owner and table name respectively. Let us update the table name from “T” to “T_IDENTICAL”.

SQL> update my_stats_tab set c1 = 'T_IDENTICAL';

3 rows updated.

SQL> commit;

Commit complete.

SQL>

Now import the table statistics for “T_IDENTICAL” table

SQL> select table_name, num_rows from user_tables where table_name in ('T', 'T_IDENTICAL');

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T                                    1000
T_IDENTICAL

SQL>
SQL> exec dbms_stats.import_table_stats('TRADE','T_IDENTICAL', null, 'MY_STATS_TAB');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables where table_name in ('T', 'T_IDENTICAL');

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T                                    1000
T_IDENTICAL                          1000

SQL>
SQL>

There you go. We were able to successfully move statistics to another table by hacking into the statistics table.

Hope you enjoyed this post.