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