| Full Table Scan Instead of Index Usage [message #334659] |
Thu, 17 July 2008 10:43  |
MartinMM
Messages: 10
Registered: July 2008
|
Junior Member |
|
|
Hi There,
I just run into quite some trouble with a very large select statement. The problem is caused by unnecessary full table scans (What else?).
The following question aroses:
Why is Oracle performing a full table scan instead of using the index?
Idea A:
The table is small! -> Nope it is about 250MB... .
Idea B:
The table is completly cached in memory (a sticky one)! -> Nope it is neither in the IO Cache nor is it hold in memory... .
Idea C:
More then 30% of the size of the table is about to retrieved. -> Nope. I just retrieve a single Integer column of a multi-multi-column page.
Idea D:
No Index exist for the given column. -> Nope its the primary key column and yes I did a double-re-check - one also NASA would be proud of - and the INDEX is in use and has proper statistics.
Well I ran out of Ideas...
Doing further research the problem comes down to:
SELECT ID FROM MyTable
When I process this single statement the plan reports still a full table scan. You know I would expect that the index is used (about 12MB instead of 250MB). I tried hints and stuff but with no luck!
Does anyone have a pointer for me?
Cheers,
Martin (Kersten)
|
|
|
|
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #334680 is a reply to message #334659] |
Thu, 17 July 2008 11:50  |
MartinMM
Messages: 10
Registered: July 2008
|
Junior Member |
|
|
@anacedent
I have checked all those Oracle Documentations (Performance Guide, Concepts, SQL-Developer etc.), Tom Kite's site (and sayings) and have experiences with non-oracle DB's using the index instead of a full table scan (MySql, InnoDB, PostgreSql and alike).
@general
The general problem is really this. I just have a very complex query which almost uses only information found in indexes. Oracle is still using three or four Full Table Scans loading in 4GB of Data (which is of cause slow as in slowest).
The idea is simple: If you combine all Indexes of those tables (all indexes needed) it comes down to about around 30MB. And only when delivering each and every row I would think an row access is needed.
Playing around with the selects and plans I found out that even counting all entries oracle tries a full table scan and counts the entries by hand (1.7 secs). You know all that was needed was just checking the number of entries in the primary key index or the number of stored rows.
(select count(*) from MyTable) -> Full Table Scan
(select count(id) from MyTable) -> Full Table Scan
Since this is happening all time I wonder if there is any way to prevent that.
Idea 5:
The index is a B-Tree! So it is not cost effective to traverse. -> Can't be! (does it?) The traversal of a B-Tree in Sort-Order is O(n) and shouldn't be avoided by forcing a costly full table scan.
Idea 6:
Just don't blaim me on this one, but is there a difference within Enterprise and Standard License regarding the use of Index? I mean is this one of the penalities you get from not using Oracle Enterprise Version?
Is it advised to use materialized views to just narrow the information of a (big big) table to a handy size? I mean the costs for updating this materialized view (table) is nothing compared with the cost a full table scan on the original.
Idea 7:
May Oracle behave differently with other index types?
Cheers,
Martin (Kersten)
PS: The profile isn't sufficiant I guess. The question is simply why does Oracle go for a 250MB (or for the other table 3GB) roundtrip when a 10MB Index is available storing all the necessary information?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #334839 is a reply to message #334773] |
Fri, 18 July 2008 05:44 |
MartinMM
Messages: 10
Registered: July 2008
|
Junior Member |
|
|
It is an Oracle 10g Single (but big) Server with a second one in stand-by. All indexes and tables are up-to-date.
Today I have undeployed the web-app causing complex queries and tried a single request. This request took ages. It was a very same request being fast as hell two days before. There was an analysation of the tables in question the day before the trouble started.
The index related questions: Indexes are valid and up to date. The indexes are around 5 to 20MB the overall table sizes are 250MB and 3GB. The indexes are all utilized but there are bigger than biggest table scans, that are not necessary.
Are there any rules that forces oracle to use table scans? I mean I read all those information about optimizer and hints but it buggs me that Oracle still perform table scans instead of using indexes when everything you want from a table is indeed the information already in index.
Cheers,
Martin (Kersten)
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #334847 is a reply to message #334839] |
Fri, 18 July 2008 06:06 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Is there any chance that you could actually answer the questions that people are asking? We're not asking them for fun - they will provide us with information that we need to diagnose the cause of the problem.
The 10053 trace is the most useful - it will say exactly why the optimizer is choosing the plan that it its.
The most likely cause of this problem is that the stats are old or plain wrong for the table and indices in question. Have a look at the values in DBA_TABLES and DBA_INDICES for this table. This will tell you how many rows Oracle thinks are in the table, when it was analyzed, and a variety of other information.
When you say 'The indexes are up to date' - what do you mean? Do you mean that you've generated statistics for the indexes lately, or do you mean something else?
| Quote: | There was an analysation of the tables in question the day before the trouble started
|
What exact command was used to analyse the tables?
You know all that was needed was just checking the number of entries in the primary key index or the number of stored rows. |
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #335132 is a reply to message #334847] |
Mon, 21 July 2008 02:46 |
MartinMM
Messages: 10
Registered: July 2008
|
Junior Member |
|
|
> What exact command was used to analyse the tables?
The default Oracle JOB.
>Nope - it's got to be a count of the rows. There is no guaranteed up to date count of the rows in a table.
I don't have the serialization isolation. So I dont care for pending transaction but I guess the oracle distribution model since then. So its ok. But at least the index would provide faster access or is the index also 'invalid' in terms of row-existence?
> The 10053 trace is the most useful - it will say exactly why the optimizer is choosing the plan that it its.
Thats right. Thats why I shivered first place. You know having kind of this:
SELECT * FROM MyTable WHERE type IN ('type1', 'type2', ....) AND details LIKE '%Something%'
Oracle currently goes with the details first -> Full Access and query slow as in slowest
But even worth:
SELECT * FROM MyTable WHERE id = 5 (-> Primary Key)
Currently oracle goes with the Full Table Access. No usage of Index or Index Access.
We are going to reanalyse the table today (I Hope). It looks like Oracle isn't aware of the indexes being cheaper after all.
Cheers,
Martin (Kersten)
[Updated on: Mon, 21 July 2008 02:48] Report message to a moderator |
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #335150 is a reply to message #334659] |
Mon, 21 July 2008 04:00 |
MartinMM
Messages: 10
Registered: July 2008
|
Junior Member |
|
|
Additional information:
After a (re)analyzation of the table the primary index now works (is favored instead of full table access).
But the details LIKE '%Test%' is still preferred instead of TYPE IN ('A', 'B', 'C', ...). The LIKE needs a full table access (3GB+) of cause and the TYPE (and NAME) column resides inside an 11MB Index.
The statistics of the table sums down to:
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
TYPE 160 109 HEIGHT BALANCED
DETAILS 633 254 HEIGHT BALANCED
NAME 126 75 HEIGHT BALANCED
The resonse time for:
WHERE TYPE IN ('A1', 'A2', 'A3', ....) AND NAME IN('B1', 'B2', 'B3', .....) AND DETAILS LIKE '%Test%' is > 5min
Without the like using index:
WHERE TYPE IN ('A1', 'A2', 'A3', ....) AND NAME IN('B1', 'B2', 'B3', .....) is < 100ms
Any Idea why the optimizer favors the slow full table access in this scenario?
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #335182 is a reply to message #335150] |
Mon, 21 July 2008 06:07 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
| Quote: | After a (re)analyzation of the table the primary index now works (is favored instead of full table access).
|
Thought so. Old/missing stats.
Can you post SQL and Explain Plans for the two different queries?
Is this a single table query, or is the DETAILS column on a different table?
When you say 'Response Time'. do you mean the time to return the first of the rows, or to return all the rows.
A common mistake made in this sort of situation is to run the query in something like TOAD or SQL*Developer and report the time that it takes to come back with the first set of rows - this can be a VERY different time to the total time taken to return all the data.
Eg, if you have an Order By clause in a query, Oracle will have to retrieve all the data before it can display a single row, yet without the Order by the query will return the first row much quicker and seem to run much faster - unless you take the time to return the last row into consideration.
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #335218 is a reply to message #335182] |
Mon, 21 July 2008 08:52 |
MartinMM
Messages: 10
Registered: July 2008
|
Junior Member |
|
|
It wasn't old but it was new stats causing the problem. The very same day quite some additional types where added. But I cant figure (from the histogram) why oracle can be so wrong.
After doing a 100%-table-analyzation including the newly created indexes (first attempt to speed up the messed plans) everything is now even faster than before.
Thanks for your patience and your help!
> Can you post SQL and Explain Plans for the two different queries?
Since the problem is gone I can not reproduce everything. Since my test-senarios where small here is the conclusion:
SELECT * FROM MyTable WHERE ID = '5'
Before: Full Access Cost ~ 300000 (3GB)
After: Index And Access by Index ~ 27
SELECT * FROM MyTable WHERE TYPE IN (~30 values) AND NAME IN (~10 Values) AND DETAILS LIKE '%Test%'
BEFORE: FULL ACCESS, ITERATOR ~ 300000 (3GB)
AFTER1: Index + Access By Index ~ 57
AFTER2: Index + Access By Index + Iterator ~27
(After2 is After 100% Analyzation leaded to using another Index(TYPE, NAME, ID))
Also Oracle now uses the Index information without performing fullscan. We also solved the very same problem with another table being analyzed just the day after the first problems occured. (Also a relevant table).
So all my critics I throw into the ring (couldnt believe a full table access) is now no more. Seams just to be a sampling problem
while analyzing just a few rows when changing the Histogram of some columns dramatically.
> Is this a single table query, or is the DETAILS column on a different table?
It was the very same table.
One thing bugging me is just that when counting the rows of a table the optimizer uses SORT(AGGREGATE). This is just a bit costly I guess (takes about a minute to work things out using an 80MB Index). Wouldnt it be best to just count and do not aggregate and sort? Is there a way to speed this up? (Instead of writing your own counting function)
|
|
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #335234 is a reply to message #335150] |
Mon, 21 July 2008 09:25 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Here is my 2 cents for what it is worth.
| Quote: |
WHERE TYPE IN ('A1', 'A2', 'A3', ....) AND NAME IN('B1', 'B2', 'B3', .....) AND DETAILS LIKE '%Test%' is > 5min
Without the like using index:
WHERE TYPE IN ('A1', 'A2', 'A3', ....) AND NAME IN('B1', 'B2', 'B3', .....) is < 100ms
|
These two are completely different conditions, in the sense if you use a "%" in front of a literal you are telling oracle get me rows which contains something. I don't think oracle will be able to help you with a b-tree index. In order to achieve what you want with the above syntax try reading on the CTX (Intermedia Text) and see if that helps ( I am not sure so try it out if that be of any help to you). Since your index in defined in this order (Type, Details, Name) and it (optimiser) has to decide the selectivity on the column Type saying is it worth while doing a range scan only on Type since I cannot use the second position composite key (details).
Again this is a guess.
I don't want to re-iterate what others have already mentioned.
Regards
Raj
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #335244 is a reply to message #334659] |
Mon, 21 July 2008 10:32 |
Kriptas
Messages: 118
Registered: June 2008
|
Senior Member |
|
|
ok,
where clause with '%something' can't use index.
you can use select with inline query
like
SELECT *
FROM (SELECT /*+ NO_UNNEST */ * from MyTable
WHERE TYPE IN ('A1', 'A2', 'A3', ....) AND NAME IN('B1', 'B2', 'B3', .....))
WHERE DETAILS LIKE '%Test%'
|
|
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #335251 is a reply to message #334659] |
Mon, 21 July 2008 11:02 |
Kriptas
Messages: 118
Registered: June 2008
|
Senior Member |
|
|
>>you can use select with inline query
>How does this change/impact query performance?
how?
if in MyTable is 1 000 000 records,
and query
SELECT * from MyTable
WHERE TYPE IN ('A1', 'A2', 'A3', ....) AND NAME IN('B1', 'B2', 'B3', .....)
returns 100 records is 0,1s,
then it is best to scan only 100 records for DETAILS LIKE '%Test%'
rather scan 1 000 000 records
don't you think so ?
[Updated on: Mon, 21 July 2008 11:02] Report message to a moderator |
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #335402 is a reply to message #334659] |
Tue, 22 July 2008 01:33 |
MartinMM
Messages: 10
Registered: July 2008
|
Junior Member |
|
|
| Quote: |
>I have checked all those Oracle Documentations (Performance Guide, Concepts, SQL-Developer etc.), Tom Kite's site (and sayings
I am VERY impressed with your ability to speed read all that material in just over 1 hour elapsed time,
but I serious wonder how much useful information was actually absorbed.
|
I just did my homework before-hand. Only thing I was reading between these posts where a resource found about Histograms in action. So nothing to really wonder about.
When you hit such a problem like this one you talk to your DBA, read the DOCs and rely on your academic training including your experience with various other DB. But talking straight, I was facing this kind of problem for the first time as do my DBA so we was kinda puzzled and when you see Oracle using a 3Gigabyte FullScan instead of an obvious (as in obvious) index scan you are kind of face-down.
@Kriptas
That was the first thing the DBA was mentioned as a potential workaround solution. But this required me to change a core query builder and would not solve the issue about the Day-Before-It-Was-Extremly-Fast-And-The-Next-Day-You-Must-Wait-For-Ages-Problem. Also may queries was affected not only the queries I was responsible so I really needed the reason.
| Quote: |
>>you can use select with inline query
>How does this change/impact query performance?
|
Using an inselect does not hurt performance that much, as far as I understand the query concepts. It is just an adhoc-in-memory table storing the temporary results. Just keep it small and you are Ok 
In this example it only makes sence if TYPE and NAME can be accessed using an index, which we created after the problem started to show (just a reflex I guess). If no index is available this would also result in a full scan (but the full-text-search using LIKE is also quite expensive compared to two INs) which is the primary cost-driver here - Pumping 3GB into memory where Oracle try to serve many concurrent queries about a >5 TB database is just to much to keep this table in memory.
To summarize the problem and the solution:
------------------------------------------
1. Everything was fine and worked well before things became odd.
2. One day everything works slowly. Pre- and Postsales Office reports problems -> Database server is on its limits with IO-Waits consuming > 50% time.
3. Checking the long-running queries you find out its about a certain table and affects queries you run every day.
4. Investigating the problem you find out that even Primary Key Indexes are barely used (if at all).
5. Investigating further you find out that your developer image of the DB (just being refreshed monthly or weakly) is still fast.
6. Letting explain queries in doubt you find out that Oracle became stupid overnight. Doing things like favoring LIKE instead of an Index Access and Full Scan seams to be prefareable in first place.
7. Investigating even further you see a table-analyzation happend the very same night Oracle turned stupid about the tables in question.
8. Since the Optimizer (which happend to turn mad) is based on statistics, you know what this mean -> Bad statistics. There are quite a few situations you can run into bad statistics. You let Oracle analyse only a fraction of the actual table-rows to compute statistics (around 10-30%). A lot of new information was added to the table (or selectively removed). The new (or removed) information cause a Histogram change within the column (without having a Live-Histogram attached to the columns).
9. Solution: Do a reanalyzation of the whole table (including its indexes) taking 100% of the table into account (Rule of thumb anyone?).
10. Additional supporting steps: Just add some useful indexes and play with your developer-copy of the DB.
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #335420 is a reply to message #335402] |
Tue, 22 July 2008 02:39 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
There's a fair bit of "My cat is grey so therefore all cats are grey" going on in this thread.
Using a larger sample size is not a solution. It is a tactic that happened to work in your case.
| Rosco's Golden Rule of Histograms | Conjunctions (ANDs) and Disjunctions (ORs) stuff histograms. The more you use them, they more they screw them up.
|
Histograms are pretty useful here; Oracle can make a reasonable estimate of the number of matching rows based on the histogram on the TYPE column.
Histograms are pretty useful here; Oracle can make a reasonable estimate of the number of matching rows based on the histogram on the NAME column.
AND type = 'A1'
AND name = 'B1'
eg. Say 20% of rows have type = 'A1' and 30% have name = 'B1'. There will be anywhere between 0% and 20% that have both. Around 20%, a full scan would be better; closer to 0%, an index scan would be better. Who knows which it will be? Oracle doesn't!
Oracle guesses because histograms are SINGLE COLUMN - they do not capture how many rows have both values.
If there is an INDEX on those two columns alone, then the statistics on the index will give an indication as to how selective any two values are on average, but not THOSE TWO VALUES specifically.
A similar thing happens when you join tables. Will the join succeed? Or will it not match and filter the row out. Histograms don't help.
As you add more predicates and more joins, the statistics get fuzzier and fuzzier until you get to a chaotic situation (you know chaos: a butterfly flaps its wings in Washington and a hurricane wipes out Florida). A small change to the SQL can result in a dramatically different execution plan.
By you own admission, this is a complex SQL. So much so you decline to post the syntax and plan here. I accept that you are changing just one line. But it is ALL THE OTHER LINES that are creating the chaotic situation; the line you change is just the butterfly's wings.
Statistics and CBO are great, but they can't handle everything. When you know something the optimizer doesn't, you have two choices:- Tell the optimizer: gather statistics if they are stale or use CARDINALITY hints. But if it's not that simple...
- Do the optimizer's job for it. If the optimizer CANNOT POSSIBLY understand the complexity of the SQL, use hints to dictate a preferred execution path, or Plan Stability (stored outlines).
Looks to me you are at option 2. You used to have an efficient plan; that was good luck, not good management. It's not a new problem; it's one you've always had but didn't know about.
Ross Leishman
|
|
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #335564 is a reply to message #335432] |
Tue, 22 July 2008 12:59 |
Kriptas
Messages: 118
Registered: June 2008
|
Senior Member |
|
|
there are few things when CBO chooses to use FTS rather RS,
1)index_cost_adj (I think I wrote right, maybe wrong)
this parameter adjust cost of using index, read about it.
2) index clustering_factor if it is big clustering the CBO chooses do not use index
3) there is something skatared_reads and sequentional_read parameter. if you need to get lots of records, going by the index has larger cost.
but i cant tell nothing about query:
select count(id) from mytable
if there is noncomposite index on ID CBO chooses always to use index FFS to count....
|
|
|
|
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #335773 is a reply to message #335762] |
Wed, 23 July 2008 12:52 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
After long time I am back.... last post which i did was in Jan 2005 ....
As this problem is resolved... lets have something more to it....
[Edit MC: Remove copyrighted content of Metalink note 77496.1, don't do that]
NOTE: The bind variable may contain trailing '%' or no '%' at all. Regardless, an index will not be used.
If cost-based optimization is used, Oracle will use full table scans for all of the above cases shown for rule-based optimzation. Additionally, the cost-based optimizer may decide to use full table scans if the table has not been analyzed, if the table is small, if the indexed columns are not selective, or if the optimization goal is set to ALL_ROWS.
To avoid unplanned full table scans, make sure the query can use an index.
Enjoy..
[Updated on: Wed, 23 July 2008 13:21] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #335879 is a reply to message #335778] |
Thu, 24 July 2008 01:26 |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
Most of people dont have access to metalink
Earlier even i don't have that access... it all depend on company/organization you work ....
I think i would have changed that in my wording ...
For the people who don't have access to metalink... its summary of Metalink note 77496.1
[Removed copyrighted Material. Posting metalink material is ILLEGAL.
Mahesh Rajendran]
[Updated on: Thu, 24 July 2008 03:07] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #337554 is a reply to message #337540] |
Thu, 31 July 2008 04:03 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | oracle can't do anything if i paste any metalink content by copying it from there.
|
Against you, no. Against OraFAQ and its owner/administrator, yes.
Do you want OraFAQ been closed?
Do you want Frank has to pay fine or go to jail?
Good guy!
| Quote: | The blog is for free support ... not getting money for that
|
No but you try to get recognition, this is another money.
| Quote: | If some one copy righted "I LOVE YOU" then i cannot copy and paste on any forum.... I LOVE YOU.
|
Except that no one can copyright this but Metalink materials yes.
More I read your post, more I think pretty good things about you (you can use this freely, I will not copyright it).
Regards
Michel
[Updated on: Thu, 31 July 2008 04:05] Report message to a moderator |
|
|
|
|
|
| Re: Full Table Scan Instead of Index Usage [message #449555 is a reply to message #334659] |
Tue, 30 March 2010 07:29 |
elizas
Messages: 2
Registered: March 2010
Location: india
|
Junior Member |
|
|
Sometimes there is a need to get the record count of every table in a SQL Server database. The common method used for achieving this is doing a select count(*) from table_name but this can create a lot of overhead in large tables present in large databases.
The record count of every user table is maintained in the sysindexes system table . There is a indid column in the sysindexes table and every user table will have an entry in sysindexes with indid value of either 0 or 1 , but not both. The rowcnt column corresponding to indid 0 or 1 gives us the total row count of a table.
The User Defined Function given below shows how to retrieve row count for a table by making use of sysindexes.
Any suggestions are welcomed.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
