Re: longops vs sql

> My question is what is the difference in SQL that takes longer than 10
> secs for disk reads and the sql from the longops view? Some of the disk
> read sql doesn't show in the longops view, but I thought that SQL
> statements that ran longer than 6 secs were supposed to be in longops.

No, not at all. From Oracle docs "V$SESSION_LONGOPSThis view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release." Unfortunately I've found that query execution is very partially included. I.e. operations like sorts, hash joins, table full scans, index full scans are included, but nested loops can run round the clock, but they won't be there. So for example if your query execution plan is 2 table hash join with full scan of each table, you'll see 2 full scans and then hash join. I've sometimes seen hash joins that was run for some hours but closely looking at them they computed source for example using some user defined function that queried another table(s) and therefore hash joins seemed so slow. If you'll have nested loop join with two table full scans (oh yea I've seen this with completely wrong statistics) you'll see many full scans of second join table. Next warning - it seems that Oracle supposes that long operation be it sort, hash join, full scan or whatever will be linear. Of course in reality it isn't. I've seen cases when in the first ~30 minutes Oracle is predicting that it will go for ~40 hours and in reality it completes in less than hour as well as initial optimistic prediction of some minutes and then growing, growing, growing...

So looking at promising statements in above mentioned oracle docs probably in Oracle 12z you'll have longops for nested loops as well :)

Gints Plivna
http://www.gplivna.eu Received on Thu Sep 07 2006 - 14:10:25 CDT