Sundaramurthy Blog

April 13, 2010

How to findout SQLID for given oracle sql query.

Filed under: Oracle 11R2 — sundar5 @ 10:11 pm

Oracle 11.2 version comes with new RDBMS package called dbms_sqltune_util0.sqltext_to_sqlid.
We will test the sqlid is matching with v$sql views.

1.   SQL> select 4*5 from dual ;

4*5
———-
20

2.  Let us find out the sql id for the “select 4*5 from dual”  sql by using old way in the database.

x$kglob

3)  Parent cursor in the x$kglob

SQL> select kglhdpar,kglhdadr,kglobtyp ,kglnaobj,kglnahsh from x$kglob where kglhdpar=kglhdadr and kglnaobj   = ‘select 4*5 from dual’;

KGLHDPAR KGLHDADR   KGLOBTYP KGLNAOBJ                         KGLNAHSH
——– ——– ———- —————————— ———-
4784C05C 4784C05C          0 select 4*5 from dual           3076671810

4) Child cursor in the x$kglob

SQL> select kglhdpar,kglhdadr,kglobtyp ,kglnaobj,kglnahsh from x$kglob where kglhdpar<>kglhdadr and kglnaobj   = ‘select 4*5 from dual’          ;

KGLHDPAR KGLHDADR   KGLOBTYP KGLNAOBJ                         KGLNAHSH
——– ——– ———- —————————— ———-
4784C05C 4793B5A8 0 select 4*5 from dual           3076671810

5) Now we know the Hashvalue of the SQL (3076671810)

6)SQL> select sql_text,sql_id,hash_value from v$sql where hash_value = 3076671810
SQL_TEXT                       SQL_ID        HASH_VALUE
—————————— ————- ———-
select 4*5 from dual           as8n8s6vq4ka2 3076671810

7) select dbms_sqltune_util0.sqltext_to_sqlid(‘select 4*5 from dual’||chr(0))  sql_id from dual
SQL> /

SQL_ID
——————————
as8n8s6vq4ka2

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: