| session doing FTS not appearing in longops [message #549858] |
Wed, 04 April 2012 05:39  |
 |
orapratap
Messages: 134
Registered: November 2011
Location: Canada
|
Senior Member |
|
|
Hello
dbo is a table with 1M records and structure similar to dba_objects
The following queries which does FTS, access same number of blocks as observed in v$session_longops
select * from dbo;
and
select * from dbo where object_type='VIEW';
Ex.
select * from dbo where object_type='VIEW';
select * from table(dbms_xplan.display_CURSOR(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
SQL_ID 5mh418w9stax2, child number 0
-------------------------------------
select * from dbo where object_type in('VIEW')
Plan hash value: 2675347415
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
|* 1 | TABLE ACCESS FULL| DBO | 77611 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='VIEW')
I understand that because the query is accessing large number of blocks which exceeds 6 seconds threshold, the session appears in v$session_longops
And this has nothing to do with the time spent in displaying the records on the screen
Now, why the following query does not appear in v$session_longops?
select /*+ full(dbo) */ count(*) from dbo;
select /*+ full(dbo) */ count(*) from dbo;
COUNT(*)
----------
1006525
Elapsed: 00:00:01.36
dv3_erie-dev_08 >select * from table(dbms_xplan.display_CURSOR(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
SQL_ID 4mgjwp3tv70db, child number 0
-------------------------------------
select /*+ full(dbo) */ count(*) from dbo
Plan hash value: 1423969929
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| DBO | 1007K|
--------------------------------------------
The table has all nullable columns and thus I assume the index on ID can't be used for this COUNT
Thus ideally it shall be accessing same number of blocks and thus shall appear in v$session_longops
Thanks and Regards
Oraratap
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
