Performance problem with use of count(*) on dba_segments

[attick111]

I have a monitoring application that is executing select count(*) from sys.dba_extents for an Oracle database with over 15 TB of data and this is taking 20 minutes and as a result is affecting the overall monitoring application's performance.

The goal is to get the total number of extents. Does anyone have a more efficient way of getting this value?

Thanks

 

[oradba101]

Hi, attick

You could try COUNT(1) instead. This is supposed to be faster.

Regards,



William Chadbourne
Oracle DBA

 

[LKBrwnDBA]

You could try COUNT(1) instead. This is supposed to be faster.

Not true, check this.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[pete91z]

I had this problem once, as far as I remember it was related to the size of extents in the temp tablespace, so you may want to review your settings for this.