Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

Quiz Post #12: when does your session/sid/statement appears in V$session_longops

We all know that when a query/statement  is running from long time (actually this post is meant to know at what times :)) ,  the access path will be shown in v$session_longops

Some of the access paths are as below

  • Table scan;
  • Index Fast Full Scan;
  • Hash join;
  • Sort/Merge;
  • Sort Output;
  • Rollback;
  • Gather Table's Index Statistics.

 

Before going to each of them, we should need to know about some important columns in v$session_longops,

Time_Remaining

Elapsed_seconds

Total_work

So_FAR

So Time_Remaining = ElapsedSeconds * (TotalWork-Sofar)/ Sofar

Note:- SOFAR and Total work are blocks not in the seconds.

So now, at what thresholds (can be time or certain number of blocks) for each of above access paths. The wide known answer is 6 seconds

TableScan:-

The common and widely known criteria to appear a full tablescan in v$session_longops is 6 seconds, but there is another threshold also but for count of blocks which is 10000 blocks. if the table has less than 10000 blocks but table scan runs more than 6 seconds that’s not enough.

Index Fast Full Scan:-

The criteria for this access path is 1000 index blocks i.e 10 times lesser than tablescan criteria. then only this access path will appear.

Hash Join:-

Hash joins are usually used joining large data sets. The performance of hash joins strongly depends on available memory either allocated by pga_aggregate_target (if using workarea_size_policy = auto) or hash_area_size (if using workarea_size_policy = manual). Block is the unit to measure hash join work in v$session_longops.

It depends, but most of the cases 6 seconds timing is the criteria but it depends on largely the sort and work area size due to usage of PGA memory for hashing. But even smaller size of blocks usually appear (say for example 20 blocks etc) will get appear in v$session_longops.

Further, Oracle consider the long operations as linear operations, so the estimate time may not accurate if its the operation is not real.

-Thanks

Geek DBA

Comments are closed.