Thursday, November 10, 2011
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.
Saturday, October 29, 2011
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.
Sunday, August 14, 2011
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 actionsFollowing 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
Friday, August 12, 2011
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.
Sunday, July 17, 2011
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.
Thursday, July 14, 2011
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.
Saturday, July 02, 2011
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!!!
Tuesday, June 21, 2011
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:
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:
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 xxxxxxxxxxThe 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]
Wednesday, June 01, 2011
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/3108688378The 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 upI 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.
Wednesday, April 06, 2011
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, …
Sunday, March 27, 2011
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. ;)
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. ;)
Wednesday, March 16, 2011
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?"
Saturday, March 12, 2011
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.
Tuesday, March 08, 2011
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.
Sunday, February 20, 2011
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.
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.
Wednesday, February 09, 2011
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.
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.
Monday, January 31, 2011
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.