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)
how long does the SQL stays in the v$sqlarea or dba_hist_sqltext [message #678932] Wed, 22 January 2020 14:38 Go to next message
wtolentino
Messages: 421
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
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 Go to previous messageGo to next message
John Watson
Messages: 8960
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 Go to previous messageGo to next message
wtolentino
Messages: 421
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 Go to previous messageGo to next message
John Watson
Messages: 8960
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 Go to previous message
wtolentino
Messages: 421
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 30 23:18:32 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.