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.

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.

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.

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/

Hi Paul,

One thing I don't understand is when I run the same delete statement with the Bind Variable ( different values) in Toad or sqlplus, the delete's are quick and the index is being used. But from the application it takes > 20-30 seconds . I asked the developer if the datatype of the bind variable was same as the indexed column and he confirmed it. Is there something we need to check in the application code.

Thanks,

SK

When executing from SQLPlus... are you actually using a bind variable or are you using a literal? e.g. the actual value?

using bind variable