DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: performance issue in count(*)

  1. #1
    Join Date
    Mar 2001
    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..


  2. #2
    Join Date
    Dec 2000
    Ljubljana, Slovenia
    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
    1 0 SORT (GROUP BY)

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Baltimore, MD USA
    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 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.


    - Chris

  4. #4
    Join Date
    Dec 2000
    Ljubljana, Slovenia

    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 2000
    Ljubljana, Slovenia
    [QUOTE][i]Originally posted by jmodic [/i]
    [B].... On the other hand, disk blocks are always read one at the time ...[/B][/QUOTE]

    Ooops, a typo. The above should be:

    .... On the other hand, *INDEX* blocks are always read one at the time ...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Nov 2000
    Baltimore, MD USA
    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

  7. #7
    Join Date
    Dec 2000
    Ljubljana, Slovenia
    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....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Nov 2000
    Baltimore, MD USA
    I'm running

    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 :)

  9. #9
    Join Date
    Mar 2001
    Thanx guys for your useful information.

  10. #10
    Join Date
    Jun 2000
    Madrid, Spain
    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
    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

    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
    touch count:

    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

    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
    Set the buffer's touch count to _db_aging_stay_count
    END IF
    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


    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
    series of touches.

    Thus, a buffer read into the default cache will (by default) be
    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
    writes versus ping writes versus writes due to reuse range and reuse

    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
    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
    buffers to be "cooled", and put at the tail of the main list after
    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 <Parameter:small_table_threshold>) will be placed at the end of the

    list. Thus, these buffers will imediately become victims for
    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
    immediate candidates for replacement.

    Summary of parameters

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

    _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
    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

    _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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width