SQL_ID in Auto Trace Explain Plan Only

It would be a great enhancement to the nifty and very helpful set autotrace explain plan only statement if it showed the SQL_ID of the query in its output. Currently it shows the SQL Text and the PLAN_HASH_VALUE, but not SQL_ID.

May be an enhancement in Oracle 12cR2 :)

To Get Serial Number of Exadata Rack or Individual Server in Exadata

For the whole Exadata rack serial number:

ipmitool sunoem cli 'show /SP system_identifier'

For the server serial number you are connected to:

ipmitool sunoem cli "show /SYS product_serial_number"

For each component serial number on a single server:

You can also get the individual serial numbers for each component in an Exadata Rack like this:

/opt/oracle.SupportTools/CheckHWnFWProfile -S

Note: Run those commands with root user

Simple Way to Get SQL ID of Large and Similar Queries in Oracle

I frequently get clients' request to tune number of queries related to some application or reports. One of the first things to start the tuning process is to get the SQL_ID of the queries. On a busy system, it can be quite a hassle to find out SQL_ID of a specific query.


The most simple way to get SQL_ID of query is to add comment in the query text and then get the SQL_ID from v$SQL view on the basis of that comment. Here is a working example:

select /* MYCOMMENT */ name,age,salary
from user.mytable
where age > 78 order by name;

COL SQL_TEXT format a45

select /* MYCOMMENT1 */ sql_id, substr(sql_text,1,200) sql_text
from v$sql
where upper(sql_text) like '%MYCOMMENT%'
and sql_text not like '%/* MYCOMMENT1 */%' ;

Enjoy query fishing :)

My First Book Co-authored with Michael Abbey et al is Out from Oracle Press

Writing is a joyful and stressful experience simultaneously. Writing a book is something very different from writing a blog or even a paper.

Thanks to my friend and my colleague at Pythian, Michael S. Abbey for providing me the chance to co-author the book Oracle Database 12c: Install, Configure & Maintain Like a Professional with him among many notable authors like Ian Abramson, Marc Fielding, Michael Mckee et al.

You can get this book from Amazon from here.