performance issue in count(*)

Hi,
I want to find a count(*) of a query..Is it better to find using groupby function or find the distinct values and then find the count(*) of each distinct values without using groupby function..Which one is better in terms of performance..We are having more than 20,000,000 records in each table..

sampath

If I understand correctly you want to know wuich of the following two examples is more efficient:

1.) SELECT COUNT(DISTINCT deptno) FROM scott.emp;
2.) SELECT COUNT(deptno) FROM scott.emp GROUP BY deptno;

Well, they will perform exactly the same, as they have identical explain plans:

Execution Plan
-------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'EMP'

HTH,

Unfortunately, the 2 statements are functionally different.

The first will return the total count of distinct Departments. The second will return a recordset containing a count of employees in each department.

To get equivalent statements, you would need to do

SELECT COUNT(*) FROM
(
SELECT 1 FROM scott.emp GROUP BY deptno
)

Now, the difference between the statements becomes that the COUNT(DISTINCT) will use and index, assuming one exists, while the SELECT 1 will do a table scan.

In an OLTP environment, it is usually preferable to go after the index, because it is more likely that more of the index is in memory than the table itself. Plus, it takes up less memory. However, there are multiple other factors, including how much memory you have, how much of the table is usually in memory vs. how much of the index, what your block size is, how big your multi-block count is, etc. The best idea is to test it yourself, unfortunately. In either case, however, it should be very fast. A single table scan is usually nothing to fret over, although you will most likely be crowding other items out of the cache. If in doubt, the COUNT(DISTINCT) is probably your best bet.

HTH,

- Chris

Chris,

I stand corrected, you are right. I should have test my answer before posting it, sorry :(.

As regarding to what is faster: performing a table scan or an index scan, I aggrea with you that it all depends on many factors. IMHO on larger tables it is *generaly* more eficient to scan a table than to do so on index. I do not agrea with you that table scan will flush out many objects from a buffert cache. It will not, because it will use only a small portion of buffer pool and it will roll table rows in that small number of buffers. However if the system will perform an index range-scan on whole index, than this *will* flush out many other objects from cache, eventually it can occupy the whole cache! The exception to all this is if optimizer chooses to perform FFI (fast full index scan) operation, which behaves much like an ordinal table scan.

Also one must be aware how physical I/O operations are performed during a table scan and an index scan. When table is scanned, the reads are done in batches of blocks (DB_FILE_MULTIBLOCK_READ_COUNT) and the disk head movement ids relativly steady as it reads data in extents sequentialy. On the other hand, disk blocks are always read one at the time (again, FFI is an exception), and sequentialy regarding index values, not regarding the block layout! That means that disk head have to jump much more forth and back, resulting in much slower reads.

In most cases on very large datasets FFI is by far the fastest operation when COUNT(*) without a WHERE condition have to be executed. If FFI can not be executed, then usually table scan will perform better than index scan.

Quote:

---

Originally posted by jmodic
.... On the other hand, disk blocks are always read one at the time ...

---

Ooops, a typo. The above should be:

.... On the other hand, *INDEX* blocks are always read one at the time ...

Good feedback.

Actually, I tried the SELECT (DISTINCT) solution with an INDEX_FFS hint.
- The plan was larger - 3 sort operations (?)
- The first run seemed to do more work than the regular I, leading me to believe that it had to read more of the index than the other statement
NOTE: I am on my own, isolated box with lots of cache and I had already run both of the other statements a few times. Therefore, I would have expected the whole index and table (or as much as necessary) to already be in the cache.
NOTE also that I was testing on a 4 million record table.
- Therefore, this leads me to believe that the regular INDEX scan did not need to read as much as the FFI, since, as I said, I had already done a regular index.
- So, on the first run, the FFI took a good bit longer and did a lot of physical reads. (well, considering it was multi-block, it wasn't *that* many, but still - I expected a 0)
- However, on the second run, the FFI completed faster than either of the other 2. This would further tell me that the FFI did, indeed, pull all that it needed into cache, and not just through a scrolling buffer window. The same thing had occurred with the FULL table scan - ran *much* faster the second time, and with no physical reads.

So, it appeared to me that each statement pulled everything it needed into cache. Therefore, the table would take up the most and crowd out the most. The FFI seemed to pull more than the I, and cost more on the initial run, which is why I didn't really bring that one up - The regular I seemed to do the least, hold the least amount of cache and ran (almost) the fastest.

Mind you, I am not an expert on the cache - I am only extrapolating from the things I saw, so I could certainly be wrong. I'm definitely interested in figuring out more about this, so keep the good info coming!

BTW, for reference, all of them completed in around 1/4 second on the second run - nothing to lose sleep over no matter how you slice it. :)

- Chris

Chris, what version of Oracle are you runing?

I'm sure wWhat I have written about occupying space in buffer cache is true for Oracle7, I'm almost certain taht it is more or less the same for Oracle 8.0, but I know they've made significal changes regarding the LRU algorithm in 8i, so my remarks might actualy not be valid for 8i. I'll have to check that myself (when the time permits...). Quite some time ago (it must have been 7.3.4) I've done some tests. I've totaly filled my buffer cache by scaning large portions of various indexes and tables via index-read ROWIDs. Then I've performed a full scan on a large table that have had no block in buffer cache. After this I checked the contents of buffer cache (V$BH) and found out that only a smal portion of cache was filled by blocks of a table I've scaned. As far as I remember I haven't performed full scan on freshly started database, so it might be true that if large amount of buffer cache is free the table scan will simply use all those free buffers.

In any case I don't think on busy system Oracle allows that one huge table full scan could simply flush out complete buffer cache (if the has not been declaerd as CASHEd table). It simply wouldn't be logical performance-wise. They have preventing this to hapen in earlyer release, so I don't think they would allow it in 8i.

But as allways, I could be wrong....

I'm running 8.1.6.2.

I can't believe you're still running 7.x. You poor soul :) - you're too good a dba to be stuck maintaining an antique :(

Interesting info, again - Thank you.

My cache definitely should not be very empty - I am running 700-statement tests all the time. And these statements hit all of the big tables in my database (24,22,21,20 Million rows, etc.). So though the box is only mine, I do at least try to give it a workout now and then :). I'll have to start looking at the V$BH view to get better aquainted with the actual workings of the cache.

What you say makes sense, though - wouldn't be terribly prudent to try to pull a 2G table into cache, for example, so some kind of re-use is obviously necessary - I just need to figure out how that jives with my observed behavior.

Thanks again for the excellent info,

- Chris

BTW - I'm thinking MSDWSAM123 got a whole lot more info that he was counting on :)

Thanx guys for your useful information.

I think 8i doesnt use LRU anymore


From internal document


Replacement algorithm for buffer cache in 8.1.3
-----------------------------------------------

The new replacement algorithm for managing buffers in the cache is no
longer LRU based. Instead, it is a variation of the "touch count", or
"clock frequency" scheme. It is described below assuming that all
buffers
are linked on a single list (different from implementation, which has
several lists, for keeping track of buffers that are absolutely useless,

those that need to be written, etc).


Basic description assuming all buffers on a single list
-------------------------------------------------------

Buffer's have a "touch count" field that keeps track of the number of
touches (hits) a DBA has encountered while it is in the cache. Hits that

are very "close" (within _db_aging_touch_time seconds) are counted as 1
hit.

You can see the current number of touches per buffer by dumping the
buffer header.

You might alternatively query x$bh as it has been updated to show the
buffer's
touch count:

SQL> SELECT TCH FROM SYS.X$BH WHERE FILE#=4 AND DBABLK=2794;
TCH
-------------
100
1 row selected


What happens when a buffer is touched (hit) in cache?
-----------------------------------------------------

On a hit, the following steps occur:

- Assuming that _db_aging_touch_time seconds has passed since we last
incremented the touch count, the touch count is increased by 1.

- The buffer is NOT moved from its current position in the list. That is

it stays where it is.

- Incrementing the touch count is done without any latching activity.
Thus, we may miss an increment to touch count occasionally as a
result.

So basically, we increment touch count based on the time elapsed since
we last incremented it.

How is a victim selected for replacement?
-----------------------------------------

If we need to read a buffer into the cache, we must first identify a
"victim" to be replaced:

- Victims are selected by scanning the list from the tail of the list.

IF ( touch count of scanned buffer > _db_aging_hot_criteria ) THEN
Give buffer another chance (do not select as a victim)
IF (_db_aging_stay_count >= _db_aging_hot_criteria) THEN
Halve the buffer's touch count
ELSE
Set the buffer's touch count to _db_aging_stay_count
END IF
ELSE
Select buffer as a victim
END IF

Where is a new buffer placed in the list?
-----------------------------------------

Unlike LRU, where a buffer with a new DBA is always moved to the top of
the list (except for long table scans), with this scheme a buffer is
inserted in the "middle" of the cache. "Middle" is specified as a
percentage of the list, and is set for the 3 buffer pools using the
parameters:

_db_percent_hot_default
_db_percent_hot_keep
_db_percent_hot_recycle

To understand this, think of the list being divided up into a "hot"
portion (buffers above the "middle" point) and "cold" buffers (those
that are below the middle point). Here, "hot" and "cold" are very loose
terms since it is possible for buffers with a very high touch count to
trickle down to the cold region, if they are not touched since their
last
series of touches.

Thus, a buffer read into the default cache will (by default) be
positioned
in the middle of the cache (_db_percent_hot_default = 50 by default) .
The reasoning for putting the buffer in the middle, as opposed to the
top, is to make this buffer earn its touch count by getting a few hits
before getting a chance to go to the top of the cache.


Actual Implementation Details
-----------------------------

The actual implementation is a variation of the algorithm to account
for maintaining buffers to be written in separate lists (regular
checkpoint
writes versus ping writes versus writes due to reuse range and reuse
object
calls).

Also, once buffers are written, they are usually "useless", in the sense

that they have aged out. Such buffers are kept aside on a AUXiliary list

rather than the main list, provided there are no waiters (or
foregrounds)
waiting for this buffer. If there are foregrounds that want to access
these buffers, then they are moved to the "middle" portion of the main
after re-setting their touch count.

The victim selection described earlier always prefers a buffer in the
auxiliary list over a buffer on the main list. If there are no buffers
on the auxiliary list, then it uses the algorithm described earlier for
the buffers on the main list.

Also, there is some special processing for CR buffers, which allows
these
buffers to be "cooled", and put at the tail of the main list after
setting
their temperature below the "threshold" (parameters _db_aging_freeze_cr
and _db_aging_cool_count).


Specific cases - full table (long) scans
----------------------------------------

From testing it appears that blocks read by a full scan of a long table
(see ) will be placed at the end of the

list. Thus, these buffers will imediately become victims for
replacement.
It was observed that during such a scan, every
db_file_multiblock_read_count's worth of blocks were placed at the end
of the list (LRU flag "moved_to_tail" bit was set) and were replaced by
the next db_file_multiblock_read_count's batch of blocks.

This effectively mimics the pre-8i behaviour for full table (long) scans

and avoids the cache being flushed by a huge table scan.


Specific cases - full table (short) scans and cached tables
-----------------------------------------------------------

The buffers are not moved to the tail of the list, and thus do not
become
immediate candidates for replacement.


Summary of parameters
---------------------

Parameter name Default Description
------------------------------- -------
----------------------------------
_db_aging_hot_criteria 2 Used to decide victim selection
(threshold)

_db_aging_stay_count 99 Touch count set to this if
(low value) low value < threshold during
victim selection

_db_percent_hot_default 50 % Divides default cache into hot
(middle point) and cold regions; specifies
where
in the cache a new buffer is to
be placed

_db_percent_hot_keep 0 % Same as above, but for keep pool

_db_percent_hot_recycle 0 % Same as above, but for recycle
pool

_db_aging_touch_time 3 secs Touch count not incremented if
(small interval) the buffer is touched within
_db_aging_touch_time seconds of
the last touch

1.) SELECT COUNT(DISTINCT deptno) FROM scott.emp;
2.) SELECT COUNT(deptno) FROM scott.emp GROUP BY deptno;

I did experiment on a large table (1M rows) with index on deptno.
Based on the explain plan output, the first statement executes faster than the 2nd one, the reason is the first one uses Fast Full Index Scan and it never uses table data. And the 2nd execution path uses INDEX scan.

Please note that if an index is not available on DEPTNO, then it will be a different story.