Home » RDBMS Server » Performance Tuning » session doing FTS not appearing in longops (Oracle 10.2.0.4)
session doing FTS not appearing in longops [message #549858] Wed, 04 April 2012 05:39 Go to next message
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
Re: session doing FTS not appearing in longops [message #549873 is a reply to message #549858] Wed, 04 April 2012 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too short to appear in v$session_longops.

Regards
Michel
Re: session doing FTS not appearing in longops [message #549875 is a reply to message #549873] Wed, 04 April 2012 06:10 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
That what my question is!

Both following statements are doing Full table scan
meaning they are accessing same number of blocks (though returning different results)

select * from dbo where object_type='VIEW';
select /*+ full(dbo) */ count(*) from dbo;


then why one statement appears in longops but not the other
i.e. one is short to appear in longops while other does not
Re: session doing FTS not appearing in longops [message #549879 is a reply to message #549875] Wed, 04 April 2012 06:25 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why are you suprised that select count(*) is faster than select * ?
They might have to hit the same number of blocks but the count doesn't need to pull as much data from each block.
Re: session doing FTS not appearing in longops [message #549880 is a reply to message #549879] Wed, 04 April 2012 06:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
In addition the select * has to do extra work to check the predicate.
Re: session doing FTS not appearing in longops [message #549881 is a reply to message #549880] Wed, 04 April 2012 06:33 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Thanks cookiemonster

That looks perfect then

access block - apply filter - pull data - display results

so only accessing block (i.e. number of blocks) is the similarity between these 2 statements.

Thanks

Regards
orapratap

[Updated on: Wed, 04 April 2012 06:35]

Report message to a moderator

Re: session doing FTS not appearing in longops [message #550022 is a reply to message #549881] Thu, 05 April 2012 05:43 Go to previous message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello

One of the good links on monitoring the v$session_longops
http://www.gplivna.eu/papers/v$session_longops.htm

Regards
Orapratap
Previous Topic: speeding up scanning of internal temporary table (of materialize hint)
Next Topic: ora - 04031
Goto Forum:
  


Current Time: Fri Mar 29 10:52:53 CDT 2024