This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Dec 7, 2012 9:32 AM by rp0428 Go to original post RSS
  • 15. Re: scripts to identify small tables for keep pool
    ji li Pro
    Currently Being Moderated
    Full Table Scan are not inherently evil.
    
    In some cases FTS is most efficient way to obtain result set.
    
    It appears that you have a solution, in search of a problem.
    It appears you have drifted away from the subject.
    I never said FTSs were bad. I just want to avoid reading them from disk any more often than I have to.
    Please try to keep on the subject instead of trying to make me look bad.
    I can do that just fine myself without any help.
  • 16. Re: scripts to identify small tables for keep pool
    ji li Pro
    Currently Being Moderated
    followup discussion
  • 17. Re: scripts to identify small tables for keep pool
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    ji li wrote:

    One thing is that when I run the query here (Full table scan I do see a lot of objects that have numerous FTSs.
    These are the top ten in the list which I thought might at least be worth looking into.
    OWNER                    NAME                        NUM_ROWS K          BLOCKS         NBR_FTS
    ------------------------ -------------------- --------------- - --------------- ---------------
    ARADMIN                  SERVGRP_BOARD                      5 N               8           29059
    ARADMIN                  T2127                       12644912 N          245888            6079
    ARADMIN                  CONTROLRECORDIDS                   1 N               8            3263
    SYSMAN                   MGMT_VERSIONS                      5 N               8            2872
    ARADMIN                  CHAR_MENU                       2255 N             256            2331
    SYSMAN                   MGMT_NOTIFY_DEVICES                2 N               8            1607
    ARADMIN                  CONTROL                            1 N               8            1590
    ARADMIN                  GROUP_CACHE                      320 N               8            1498
    ARADMIN                  T384                             145 N              40            1375
    ARADMINE                 GROUP_CACHE                       13 N               8            1333
    ARADMIN                  T2119                           4925 N             256            1243
    ARADMIN                  T2154                             96 N               8            1238
    Personally I'd be looking at the AWR (or statspack at level 7) - these figures are since startup, which means they may be relatively small (although the 6,000 report on 245,888 blocks looks interesting). If tablescans are a threat then they tend to show up in CPU time or Elapsed Time or Gets or Reads; and - for the large ones - in the Segments by Scans.

    A few points to consider with the script, though:
    a) It uses segment blocks, not table blocks (which is probably why so many of your tablescans show 8 blocks (? automatic extent management ?)
    b) It misses IOTs, Clustered tables, and will handle partitioned tables incorrectly
    c) Given that some plans can age out of the Llibrary cache very quickly, it may give you a false sense of security
    d) Given that queries with multiple child cursors may have different plans it may over-report to an arbitrary degree
    e) It doesn't cater for queries which use the same table more than once
    f) It doesn't cater for queries that produce plans with tablescans that don't actually run

    Regards
    Jonathan Lewis
  • 18. Re: scripts to identify small tables for keep pool
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    ji li wrote:
    Great articles Jonathan.
    Makes me want to know more about automatic direct path serial tablescans.
    Thanks,
    Do note, though, the dates and version numbers that I've given. Details keep changing

    Regards
    Jonathan Lewis
  • 19. Re: scripts to identify small tables for keep pool
    ji li Pro
    Currently Being Moderated
    Your points are well taken on all accounts (and our database has been up for a while so i was wondering how long v$sqlarea held the information).
    Okay, looking at an AWR report taken for one hour this morning during a busy period, I have:

    physical reads/sec 461
    index fast full scans (full) 1,175
    table scans (direct read) 150
    table scans (long tables) 191,060
    table scans (short tables) 55,991

    I'm assuming this is all relative information and needs to be compared by testing.

    Is there anything specific I should be looking for to determine FTS's?

    If I look at Segments by Table Scans, I see one table that makes up 76% of the table scans (141,405 times).
    This table is roughly 2Mb and contains 2255 rows.

    Edited by: ji li on Dec 6, 2012 2:43 PM

    Also, good follow on discussion by Dion Cho on full table scans.
    http://dioncho.wordpress.com/2009/07/21/disabling-direct-path-read-for-the-serial-full-table-scan-11g/
  • 20. Re: scripts to identify small tables for keep pool
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    ji li wrote:
    Okay, looking at an AWR report taken for one hour this morning during a busy period, I have:

    physical reads/sec 461
    index fast full scans (full) 1,175
    table scans (direct read) 150
    table scans (long tables) 191,060
    table scans (short tables) 55,991

    I'm assuming this is all relative information and needs to be compared by testing.

    Is there anything specific I should be looking for to determine FTS's?

    If I look at Segments by Table Scans, I see one table that makes up 76% of the table scans (141,405 times).
    This table is roughly 2Mb and contains 2255 rows.
    The 191,000 long tablescans looks interesting, as does the fact that a large fraction of them are on one table.
    You need to cross check Top 5 timed events and SQL ordered by XXX. If the tablescans are turning into physical reads you should see them in SQL ordered by Reads and by Elapsed Time; if they are not turning into reads then you should see them in the SQL ordered by CPU and possiblly by Gets - and you have a table_name to check which particular SQL statements are the most likely. (Then check the execution plans from the AWR).

    Regards
    Jonathan Lewis
  • 21. Re: scripts to identify small tables for keep pool
    ji li Pro
    Currently Being Moderated
    Yes, correct. It does appear they physical reads, but if they are sequential reads, wouldn't that indicate they are of indexes?

    Top foreground wait events are:

    db file sequential read 22.72% of DB time
    direct path read 3.20%
    log file sync 2.43%
    others are small and insignificant

    Foreground wait class:

    DB CPU 68.59% of DB time
    User I/O 28.80%
    Commit 2.43%

    I don't see the particular table in either of the SQL order by charts.
  • 22. Re: scripts to identify small tables for keep pool
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    ji li wrote:
    Yes, correct. It does appear they physical reads, but if they are sequential reads, wouldn't that indicate they are of indexes?

    Top foreground wait events are:

    db file sequential read 22.72% of DB time
    direct path read 3.20%
    log file sync 2.43%
    others are small and insignificant

    Foreground wait class:

    DB CPU 68.59% of DB time
    User I/O 28.80%
    Commit 2.43%
    The absence of "db file scattered reads" combined with a large number of table scans (long) suggests that the long tables are buffered and are showing up as CPU. You can also check the statistics about table scan blocks gotten and "prefetched" blocks (I don't remember the exact name of the statistic). You may find that the tablescan blocks is high, but the prefetch is low - for tablescans prefetched will give you an idea of how much of the tablescan turned into physical I/O.

    I don't see the particular table in either of the SQL order by charts.
    Do the figures for "percentage reported" at the top of the section suggest that there might be a lot of missing SQL ?

    Regards
    Jonathan Lewis
  • 23. Re: scripts to identify small tables for keep pool
    rp0428 Guru
    Currently Being Moderated
    >
    In our environment, we have a lot of small tables, and a few large tables that are constantly being pulled into buffer cache thereby causing the smaller tables to be pushed out. If the oracle has to go back to disk frequently to do a full table scan on small tables, then I'm thinking we can improve performance by putting them in the keep pool cache.
    >
    Have you done any investigation to determine which of the full table scans on these small tables should not even be full table scans? I haven't seen any post that settles that question.

    I would question why full table scans are even being done on these small tables. Some of these may be cases where an index is either missing or not being used.

    For cases where a full table scan should not be used keeping the table in the cache isn't addressing that real problem.

    Have you ruled this out?
  • 24. Re: scripts to identify small tables for keep pool
    ji li Pro
    Currently Being Moderated
    Thanks for your comments rp.

    No, I have not addressed reasons for full table scans. I'm focusing here on the small tables that I know would always get a full table scan since it would be faster to do an FTS on small tables rather than looking up the full index scan to find rowid and then lookup rowid to fetch the data.

    For larger tables, absolutely, I agree to address if there are any full table scans, but for now, I'm still focusing on small tables such as the one I noted above that is only 2Mb with 2250 rows and was full table scanned 1330 times within one hour. Since I also see some larger tables that were fully scanned (even more times), I will investigate why they were not accessed via index.

    But my concern is still that the buffer cache is being flushed out so frequently by larger scans that the smaller tables have to be fetched repeatedly from disk which might benefit from pinning.

    Now that I have the tool I originally asked for in this thread, which was a script to provide me a listing of tables that were full table scanned, I can focus on why the larger tables were not using an index. That might solve the problem in itself which would allow the smaller tables to remain in the buffer cache and not get flushed out. Per comment from Jonathan, I've modified that script to only pull data from the previous hour (or day).
  • 25. Re: scripts to identify small tables for keep pool
    rp0428 Guru
    Currently Being Moderated
    I don't want to detract from your goal but this statement youd made is just wrong
    >
    No, I have not addressed reasons for full table scans. I'm focusing here on the small tables that I know would always get a full table scan since it would be faster to do an FTS on small tables rather than looking up the full index scan to find rowid and then lookup rowid to fetch the data.
    >
    Both comments you made about small tables are wrong: 1) that they would always get a full table scan and 2) that it would be faster to do an FTS on a small table instead of use an index.

    Richard Foote blows that myth away in his five part series about indexes on small tables.

    Indexes on Small Tables Part I
    http://richardfoote.wordpress.com/2009/04/16/indexes-on-small-tables-part-i-one-of-the-few/
    >
    A common question I get asked is when is a table too small to benefit from being indexed.

    If a table only has a few blocks for example, which could all be potentially read via a single multiblock read operation, surely there’s no benefit in indexing such a table (except perhaps to police an associated PK constraint).
    >
    The reasons that the full table scans could be relevant to your problem are these:

    1. you don't really know how many rows/blocks of that 'small table' are really needed to satisfy the queries you are using.

    2. only the blocks that those queries actually need would benefit by being kept in the cache; access of the other blocks is wasted resource.

    3. the fewer blocks of those small tables needed the more likely it is they will still be in the cache.

    So if your queries really only need 2 blocks (any blocks) of a 10 block small table then those other 8 blocks being loaded for the full table scan could actually be causing the 2 needed blocks to be dumped from the cache.

    In any event it is much more likely that 2 blocks will still be cached that if all 10 blocks need to be cached. Multiply that by the number of small tables you are talking about and it could very well solve your problem.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points