Subscriber Count


Subscribe to Posts by Email


Basics: Oracle Enqueue Waits & Causes

1. What are Oracle enqueues? Oracle enqueues are locks at database level that coordinate parallel access to Oracle resources such as objects or data records. For example, enqueues are responsible for several transactions not being able to change the same data record at the same time.Enqueue requests are divided into queues whose requests are then […]

Finding SQL Bind variables and its literal values


In 10g, you can use data dictionary view v$sql_bind_capture or AWR view dba_hist_sqlbind (historical version of v$sql_bind_capture) to find bind variables’ values. However it has some significant limitations:

Captured periodically (_cursor_bind_capture_interval=900 seconds by default), not at real time. Captured under maximum size(_cursor_bind_capture_area_size=400) Only bind variables in WHERE clause are captured (e.g bind variables passed […]

Quick Question: How to invalidate the cursor after statistics gathering

A long running job and you have identified that statistics of a table is causing sub optimal plan and you have gathered the statistics and asked teams to rerun the job. But still picking old plan or the statistics which should not, the reasons are and how to invalidate the cursor in library cache (SQL […]

IMPDP: Another nice option TRANSFORM, explored

Consider this scenario (as explained by My colleague for his requirement, Thanks Rambo)

Source database: Tablespace T1 created with locally managed tablespace with auto segment space management.

Destination Database: Tablespace T1 created with locally managed tablespace with uniform extent allocation.

Read carefully the underline parts,

Now If I import this tablespace T1 into the Destination […]

Why my SQL Profile has not been picked up?

Somtimes you might be into a situation, that you have created an sql profile and unfortunately that has not been picked up?

Well as per my reads and understanding and limited knowledge, these may be the causes.

1) Signature mismatch – SQL statements as converted to signature (with possible extra space truncation, replace literals etc)


prstat –z disaster in crontab (realtime issue) possible hit to you as well, if you are too keen on automations.

Many thanks to Shiva & Swathi for sharing their real time experience and appreciating their wish to share this to in my blog.

Further, the following is very nice work from Swathi with screenshots and well written to understand the situation and how to resolve the same!!!

Read on!!!

Sharing one situation of my […]

Interpreting RAC global workload characteristics in AWR report, understanding RAC related statistics part in AWR

The following sections are key to analyze in the AWR report and identify for RAC global characteristics or load profile.

Number of Instances Instance global cache load profile Global cache efficiency percentages GCS-GES workload characteristics Messaging statistics Service Statistics Service Wait class statistics Top segments related to CR and current blocks Number of Instances:-


ASM : Intelligent Data Placement (IDP)

Starting 11gr2,

ASM now classifies reads/writes into two – COLD and HOT. As you guessed, HOT read/writes are those coming from the blocks which are classified as HOT – meaning placed in the outer sectors. COLD is the default – meaning data placed in inner sectors. Data blocks of most often accessed segments are […]

ACFS Resize: ACFS filesystem resize or adding some disks

Adding Disk to diskgroup and resize of volume with added disk

Remember, in previous post, we have two disks one utilized for /u04/acfstest which is 1.8gb, now am going to add another disk to the existing group which it can increase upto 4gb.

I got /dev/oracleasm/disks/ACFSDISK2

First check what is the size of current /u04/acfstest […]

ACFS: Oracle ACFS Snapshots – A backups inside your OS directories of the directories,

ACFS snapshots are the snapshot of the directories that taken as a snap for particular time period. For example your restore point in database, all changes can be reverted until that point. Similarly with ACFS the directories also can be managed to revert to specific point in time to when the snapshot is created. Let’s […]