-
Table scans from application
Hi,
We have an application where delete statement is taking a long time. When I see the v$session_longops the sql statement is doing a full table scan. But when I do an explain plan on the delete statement it uses the index. The statement is "delete tablename where column_name =:B1". I am new to Oracle any ideas how I should proceed.
Thanks,
SK
-
I would do a 10046 trace so to be sure what the statement is actually doing.
Couple of questions...
1- How many rows out of the total number of rows on the table is supposed to delete the statement?
2- I assume there is an index on the column_name used on the predicate of the delete statement - if this is the case, are table and index statistics up to date?
Hope this helps.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
1. 144 rows.
2. Yes.
We have few systems configured exactly the same. One few of them the delete from the application runs quickly. Today I did a query on v$sql for the system where it runs fine and on the other one. I see this difference, the columns for IS_BIND_SENSITIVE and IS_BIND_AWARE on the slower system is NO and on the one where the sql runs faster is YES. Any clues , I am going on this.
Thanks,
SK
-
Are 144 rows deleted?
How big is the table (number of rows) in the system that runs fast?
How big is the table (number of rows) in the system that runs slow?
Answering your question, IS_BIND_SENSITIVE=Y means that Oracle optimizer may generate different execution plans for the same query depending on the value of the bind variable.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
yes the 144 rows are deleted.
4 million rows on the system that runs fast.
9 million rows on the system that runs slow.
Thanks,
SK
-
Please check how cursor_sharing parameter is set on both environments.
Please also confirm exact Oracle version on both environments.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Yes same exact version and patch level. 11.1.0.7.
cursor_sharing is set to EXACT on both.
Thanks,
SK
-
How are these parameters set? Do they favor full table scans?
Code:
SQL> show parameter optimizer_index
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_index_caching integer 90
optimizer_index_cost_adj integer 10
-
They are set to default values
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
-
as weird as it sounds, you may have to "train" the slow Oracle instance by running the offending queries with different bind variable values (with different cardinalities) until you see on v$sql cursors that are both bind aware and bind sensitive.
Be sure statistics got gathered with column histograms on the target table.
Please check this document: http://iusoltsev.wordpress.com/the-e...in-oracle-11g/
Last edited by PAVB; 05-22-2013 at 11:08 AM.
Reason: typo
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|