Home » RDBMS Server » Server Administration » SQL ID (generic)
SQL ID [message #406371] Wed, 03 June 2009 13:18 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi ,please can someone explain this concept.

i been getting ORA-01555 alerts since last couple of days.today i grep my alert and found this.

grep ORA-01555 alertlog

ORA-01555 caused by SQL statement below (SQL ID: bxpz4usaxt15y,..
ORA-01555 caused by SQL statement below (SQL ID: bxpz4usaxt15y,..
ORA-01555 caused by SQL statement below (SQL ID: bxpz4usaxt15y,..
ORA-01555 caused by SQL statement below (SQL ID: bxpz4usaxt15y,..

My question is about SQL ID. why its same always(last 10 days) .does it mean its reusing parsed SQL in shared sql area of shared pool ?
Re: SQL ID [message #406373 is a reply to message #406371] Wed, 03 June 2009 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes or it computes the same sql id.

Regards
Michel
Re: SQL ID [message #406377 is a reply to message #406371] Wed, 03 June 2009 13:51 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
i tried searching oracle documentation on SQL ID ,but didnt get what i wanted.

>>Yes or it computes the same sql id.
Mike,do you mean same SQL always compute same SQL ID ?
Re: SQL ID [message #406381 is a reply to message #406371] Wed, 03 June 2009 14:12 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
i did a test in my local database.it has no activity.

i have connected as two different session and executed same SQL one after another. i could see both has different SQL_ID.here is the output.

select sid,q.sql_id,sql_text
from v$session s, v$sql q
where sid in (1169,49141)
and (
   q.sql_id = s.sql_id or
   q.sql_id = s.prev_sql_id);

--------------------------------------------------------------------------------
      1169 g2vf53xt32r1j
select owner,object_name,object_type,status,to_char(created, 'DD-MON-YYYY HH24:M
I:SS') from dba_objects where object_name like upper('%krsc_staff_profs%') order
 by owner,object_name



select sid,q.sql_id,sql_text
from v$session s, v$sql q
where sid in (1156,44885)
and (
   q.sql_id = s.sql_id or
   q.sql_id = s.prev_sql_id);

      1156 3xxanv0sr0g5r
select owner,object_name,object_type,status,to_char(created, 'DD-MON-YYYY HH24:M
I:SS') from dba_objects where object_name like upper('%KRSC_STAFF_PROFS%') order
 by owner,object_name


I belive different SQL ID means its hardparsing , any idea why this happend.

[Updated on: Wed, 03 June 2009 14:13]

Report message to a moderator

Re: SQL ID [message #406383 is a reply to message #406371] Wed, 03 June 2009 14:21 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
i think upper('%krsc_staff_profs%') was causing the problem,as there was a slight difference.

i re ran same sql again and i could see same SQL ID Smile .now i need to see if its goin to get same SQL ID after a DB restart Smile
Re: SQL ID [message #406385 is a reply to message #406383] Wed, 03 June 2009 14:39 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sql_id is a hash value from the sql statement (binary code of it, so "A" and "a" are different values).

Regards
Michel
Previous Topic: Help Needed - to stop jobs
Next Topic: LMT and Extents size problems
Goto Forum:
  


Current Time: Sun Dec 11 00:20:51 CST 2016

Total time taken to generate the page: 0.07247 seconds