OraFAQ Forum: Performance Tuning » why this query is utilizing my undo so rapidly?

Home » RDBMS Server » Performance Tuning » why this query is utilizing my undo so rapidly?

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

why this query is utilizing my undo so rapidly? [message #584292]

Sat, 11 May 2013 03:44

piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa

Senior Member

hi,

i can't find reason why this query is utilizing my undo space. when I am running this query stright from sqlplus it executes fast, but in v$undostat it is identified as long running - but why? it is some kind of oracle background query from streams module, but on this database I am not running streams.

SQL> SELECT s.SQL_TEXT, s.FETCHES, s.EXECUTIONS, st.begin_time, st.end_time, st.UNDOBLKS, st.TXNCOUNT, st.maxquerylen
  2  FROM v$undostat st INNER JOIN
  3  v$sql s ON s.sql_id = st.maxqueryid
  4  ;

SQL_TEXT                FETCHES EXECUTIONS BEGIN_TI END_TIME   UNDOBLKS   TXNCOUNT MAXQUERYLEN
-------------------- ---------- ---------- -------- -------- ---------- ---------- -----------
select 1 from obj$ w          2          2 13/05/11 13/05/11         24        303         857
here name='DBA_QUEUE
_SCHEDULES'

select 1 from obj$ w          2          2 13/05/11 13/05/11        605        986         556
here name='DBA_QUEUE
_SCHEDULES'

select 1 from obj$ w          2          2 13/05/11 13/05/11        431       1091        1158
here name='DBA_QUEUE
_SCHEDULES'

SQL_TEXT                FETCHES EXECUTIONS BEGIN_TI END_TIME   UNDOBLKS   TXNCOUNT MAXQUERYLEN
-------------------- ---------- ---------- -------- -------- ---------- ---------- -----------

select 1 from obj$ w          2          2 13/05/11 13/05/11        450       1312         555
here name='DBA_QUEUE
_SCHEDULES'

select 1 from obj$ w          2          2 13/05/11 13/05/11         62        700        1158
here name='DBA_QUEUE
_SCHEDULES'

select 1 from obj$ w          2          2 13/05/11 13/05/11        659       2034         556
here name='DBA_QUEUE

SQL_TEXT                FETCHES EXECUTIONS BEGIN_TI END_TIME   UNDOBLKS   TXNCOUNT MAXQUERYLEN
-------------------- ---------- ---------- -------- -------- ---------- ---------- -----------
_SCHEDULES'

[Updated on: Sat, 11 May 2013 03:49]

Report message to a moderator

Re: why this query is utilizing my undo so rapidly? [message #584294 is a reply to message #584292]

Sat, 11 May 2013 03:51

piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa

Senior Member

the question is about column MAXQUERYLEN. why this column identifies that this query is so long running. when I am running it on another session it works quickly.
most of my v$undostat table (about 95%) contain sql_id pointing at the fact that this query is the most long running in my all database.

[Updated on: Sat, 11 May 2013 03:52]

Report message to a moderator

Re: why this query is utilizing my undo so rapidly? [message #584302 is a reply to message #584294]

Sat, 11 May 2013 08:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It appear that you suffer from Compulsive Tuning Disorder.
Why do you look for problems where none exist?

FWIW - I get similar results from my DB.

SQL_TEXT
--------------------------------------------------------------------------------
   FETCHES EXECUTIONS BEGIN_TIM END_TIME    UNDOBLKS   TXNCOUNT MAXQUERYLEN
---------- ---------- --------- --------- ---------- ---------- -----------
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'
       435        435 07-MAY-13 07-MAY-13         15         89        1183

select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'
       435        435 07-MAY-13 07-MAY-13         15         95         579


368 rows selected.

Report message to a moderator

Re: why this query is utilizing my undo so rapidly? [message #584304 is a reply to message #584302]

Sat, 11 May 2013 09:19

piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa

Senior Member

maybe I am sick (like you are saying) BlackSwan, but I am trying to find answer why this query is described as long running query in v$undostat. it should not appear because executing this query from another session takes miliseconds to get result. I am 100% percent sure that my database is executing much more utilizing queries but for sure not this one should appear in v$undostat in 95% cases.

[Updated on: Sat, 11 May 2013 09:20]

Report message to a moderator

Re: why this query is utilizing my undo so rapidly? [message #584305 is a reply to message #584304]

Sat, 11 May 2013 09:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I am 100% percent sure
You can be sure & wrong at the same time.

When you start with the wrong question, no matter how good an answer you get, it won't matter very much.

What will you do differently after you have an answer that satisfies you?

Report message to a moderator

Re: why this query is utilizing my undo so rapidly? [message #584306 is a reply to message #584305]

Sat, 11 May 2013 10:25

piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa

Senior Member

so why this query (and mostly only this) appears as long running in v$undostat? this database is today completly not utilized. Why should not I be worried about this statistics?

Report message to a moderator

Re: why this query is utilizing my undo so rapidly? [message #584307 is a reply to message #584306]

Sat, 11 May 2013 11:17

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

Senior Member
Account Moderator

I think it is a bug in 11.2 (although you didn't post your version as it is requested and, given the time you are here and the number of questions you posted you should do).
If you carefully look at the sql text, you'll see it could not be through from outside Oracle binaries. I think it is just a probe or something like that in the code.

Regards
Michel

Report message to a moderator

Re: why this query is utilizing my undo so rapidly? [message #584309 is a reply to message #584307]

Sat, 11 May 2013 11:51

piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa

Senior Member

thanks Michel. I missed filling version not intentionally (11.2.0.3) - I just forgot about that, and remainded about it too late to fill this.
I was sure that this query is from backgroud oracle processes, which I described in my first post, but still it wasn't make sense for me that this query is reported as long running and utilizing so much my undo tablespace - its simple kind of query from (like you said) inside oracle binaries which works instantaneously when I am running it from another session.
anyway.. thanks for answer.

Report message to a moderator

Re: why this query is utilizing my undo so rapidly? [message #584313 is a reply to message #584309]

Sat, 11 May 2013 14:31

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

Senior Member

Quote:

this query is reported as long running and utilizing so much my undo tablespace

I have no idea why this query is reported as long running (I get the same result, 11.2.0.3 Win32) but I must correct the second part of your statement above: the figures for UNDOBLKS are the number of blocks of undo generated in the ten minute period by all work in the database: nothing to do with the query.

Report message to a moderator

Re: why this query is utilizing my undo so rapidly? [message #584314 is a reply to message #584313]

Sat, 11 May 2013 14:39

piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa

Senior Member

yes John. UNDOBLKS and numbers in this columns are not high in my example - I wasn't worried about that. the only issue for me was MAXQUERYLEN column which seems for me very strange considering fact that this query is realy simple.
i think that maybe this issue happens because some kind of bug like you are saying.

thanks a lot for helping me.

[Updated on: Sat, 11 May 2013 14:41]

Report message to a moderator

Previous Topic:	delete is very slow
Next Topic:	Is there any way to have the DB articulate why PDML is blocked

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 18 01:49:33 CST 2025