OraFAQ Forum: Server Administration » how long does the SQL stays in the v$sqlarea or dba_hist

Home » RDBMS Server » Server Administration » how long does the SQL stays in the v$sqlarea or dba_hist_sqltext (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how long does the SQL stays in the v$sqlarea or dba_hist_sqltext [message #678932]

Wed, 22 January 2020 14:38

wtolentino
Messages: 422
Registered: March 2005

Senior Member

how long does the SQL text stays in the v$sqlarea or dba_hist_sqltext? i had a SQL that was in there but after a couple of hours it was not in there anymore. is there also a query to figure out how long it will stay in there? so far the closes that i can see was to check the v$librarycache but could not figure out from there which/how. please advise. thank you in advance.

Report message to a moderator

Re: how long does the SQL stays in the v$sqlarea or dba_hist_sqltext [message #678933 is a reply to message #678932]

Wed, 22 January 2020 14:46

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It stays until it is aged out due to lack of space in the SGA.

Report message to a moderator

Re: how long does the SQL stays in the v$sqlarea or dba_hist_sqltext [message #678938 is a reply to message #678932]

Thu, 23 January 2020 01:34

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

The DBA_HIST% views will show information depending on the AWR retention policy. Default is 8 days. Some DBAs increase this to 30 or 60, I've never heard of anyone reducing it.

Report message to a moderator

Re: how long does the SQL stays in the v$sqlarea or dba_hist_sqltext [message #678943 is a reply to message #678938]

Thu, 23 January 2020 09:39

wtolentino
Messages: 422
Registered: March 2005

Senior Member

thank you all. looks like in the view dba_hist_sqltext the sql is no longer in there also.

Report message to a moderator

Re: how long does the SQL stays in the v$sqlarea or dba_hist_sqltext [message #678945 is a reply to message #678943]

Thu, 23 January 2020 10:34

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

wtolentino wrote on Thu, 23 January 2020 15:39

thank you all. looks like in the view dba_hist_sqltext the sql is no longer in there also.

That is not right. When you say "no longer there" could it actually be "was never there"? dba_hist_sqltext is not a record of all SQLs, only those that happened to be captured by the AWR snapshots. v$sql is everything - but not for very long.

Report message to a moderator

Re: how long does the SQL stays in the v$sqlarea or dba_hist_sqltext [message #678949 is a reply to message #678945]

Thu, 23 January 2020 10:50

wtolentino
Messages: 422
Registered: March 2005

Senior Member

most of the time I normally looked into the v$sqlarea or v$sql. when they are no longer in one of those I check the dba_hist_sqltext. but looks like it happens fast that even in the dba_hist_sqltext it is not in there also.

[Updated on: Thu, 23 January 2020 12:05]

Report message to a moderator

Previous Topic:	Out of Memory - Runaway Memory, not releasing ORA-04036: PGA memory used by the instance exceeds
Next Topic:	SPU Patching doubt

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 02:24:09 CDT 2025