| long scans and short scans [message #152296] |
Thu, 22 December 2005 05:59  |
alanm
Messages: 284
Registered: March 2005
|
Senior Member |
|
|
Hi All,
I have run a perfstat on my system and in an hour slot it reported many 1,000s of long and short table scans. Does anyone have a script which will let me know which tables are being hit.
regards
Alan.
|
|
|
|
| Re: long scans and short scans [message #154038 is a reply to message #152296] |
Thu, 05 January 2006 08:02  |
Frank Naude
Messages: 4579
Registered: April 1998
|
Senior Member |
|
|
Hi,
You should be able to see it from V_$SEGMENT_STATISTICS.
A long table scan is a scan on a table whose total data blocks are greater than 2% of the number of blocks defined by DB_CACHE_SIZE (the algorithm is more complex, but this will do for now).
Long table scans are mostly bad for OLTP-type systems.
Best regards.
Frank
|
|
|
|
|
|
|
|
| Re: long scans and short scans [message #154118 is a reply to message #152296] |
Thu, 05 January 2006 15:07 |
SQLAREA
Messages: 21
Registered: January 2006
Location: Belgium
|
Junior Member |
|
|
Dear,
Since you are still on 8.1x you haven' t yet the v$segment_statistics view (a shame) neither the init parameter db_cache_size.
May I assume you watched the top 5 wait events of the statspack report and may I assume the IO waits like db file sequential read, db file scattered read, maybe direct path read appear in your top 5 ? If no why you bother about tablescans long or short, it' s normal you have table block reads , isn' t it ?
May I also assume you compared the IO waits by the statistic "CPU used by this session" ( which you find back further in the statspack report, otherwise v$sesstat ) From 9i onwards the cpu waits appeared in the wait list of your statspack reports, but not yet in 8.1.x. It is very important to track down whether your system is IO bound whether it is CPU bound. If your system is CPU bound you may not be served at all by increasing your db_block_buffers.
What should I do right now if I face IO waits (even after comparison with cpu waits) and if I still face a 8.1.x release.
Well in your statspack report there is the top sql ordered by buffer gets, there is the top sql ordered by executions, which is the IO expensive sql (either logical either physical).
Take a look at this sql, who has executed what when, how many times. Can you better the execution plans by sql trace ? , or by better, improved stats ? ( which you collect by dbms_stats )
Let me think, 8.1.7 ... I used to alter the default values for optimizer_index_cost_adj and optimizer_index_caching in order to become improved execution plans.
Also take a look a v$sql, you even can map v$sql with v$session (join columns are sql_address and hash_value) all that in order to track down who is execution what when, how many buffers visited.
Regards
Guy Lambregts
|
|
|
|
|
|
|
|
|
|
| Re: long scans and short scans [message #154428 is a reply to message #154196] |
Sun, 08 January 2006 06:15 |
SQLAREA
Messages: 21
Registered: January 2006
Location: Belgium
|
Junior Member |
|
|
Hi
8.1.7.4 and still RBO ?
I should try to move from RBO to CBO in order to have parsed te best execution plan. Let me give you an excellent white paper written by Mr Tim Gorman
http://www.evdbt.com/SearchIntelligenceCBO.doc
If your system is IO bound, please read my previous post, you should look to the sql ordered by buffer gets and sql ordered by executions and you should try to have parsed a better executions plan for those statements either you should try to rewrite the sql going on in your db. (which is in a lot of cases not possible with thirth party software)
What is your SGA ? shared pool, db_block_buffers, db_block_size
What is your buffer hit ratio, (statspack reports)
If you cannot tune/improve the sql going on in the db
and
If you can not move from RBO to CBO
there is the option left to increase the db_block_buffers parameter , it will only give you the results you are looking for if the hit ratio was very low. (Increasing and increasing the buffer cache is rather low level dba)
Regards
Guy
|
|
|
|
|
|
| Re: long scans and short scans [message #154800 is a reply to message #154780] |
Tue, 10 January 2006 12:47 |
SQLAREA
Messages: 21
Registered: January 2006
Location: Belgium
|
Junior Member |
|
|
Hi
Seems your SGA is properly configured.
Options left for you are
1. moving from RBO to CBO
2. tuning top sql statements (ordered by buffer gets/ executions/disk reads)
Today I suddenly had to face a tuning issue priority 1
the query below was taking to much time
select * from (select * from t1 order by col1 desc,col2 desc) where rownum < 21
The execution plan showed me a full table scan of t1
I suggested our custumer to create a composite index in this way
create index t1_idx1 on t1 (col1 desc,col2 desc);
On a big table in a test instance the number of consistent gets/physical reads decreased with factor 15.000 !
You should try to do the same for your top sql (can be time consuming)
I suppose you are familiar with sql trace, tkprof and autotrace.
Please let me know if the latter is not the case
Regards
Guy Lambregts
[Updated on: Tue, 10 January 2006 12:49] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
