Home » SQL & PL/SQL » SQL & PL/SQL » Weird problem :(
Weird problem :( [message #263360] Wed, 29 August 2007 18:54 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
This is really the weirdest problem I came across with Oracle Sad Maybe someone have an idea on how to go about it? I have a PL/SQL application, a part of which is performing slow. I ran a trace, debugged, etc and finally found sql statement that was taking a long time. The problem is this:

- We are on Oracle 9i with Rule based optimizer with NO statistics on the database (I have no idea why, but DBAs refuse to run them). Hence, applying any hints that would invoke cost based optimizer is pointless...

- Say we have 2 tables: table_A and table_B. My application is doing a delete from Table_A. Table_B is not accessed anywhere in the application. Table_B, however, has a FK that connects it to Table_A. This FK is created without CASCADE option. There are NO triggers on either table_A or table_B. I traced the execution through TOAD and it shows that when there is a delete from table_A the following statement gets executed:

SELECT /*+all_rows*/
count(1)
FROM table_B
where id = :id
and code = :code.

This is crazy. This code is not exlusivelly written in the application, so it seems that Oracle does this dynamically. But why? I cant imagine wihy would Oracle apply a hint dynamically...Since we have rule based optimizer and statistincs are not collected and table_B has millions of rows from like 1995, this statement runs like 2-3 minutes. Does anyone have any idea as to why this can happen?

Thank you!
Re: Weird problem :( [message #263363 is a reply to message #263360] Wed, 29 August 2007 20:31 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
This is not weird at all; although I assuming you are running 9.2?

The query is executed by the server to enforce the foreign key constraint. Before the DELETE is allowed, Oracle ensures you are not trying to remove a parent (from Table_A) if there are child rows (in Table_B).

As for why the hint? It is a product of Oracle's move to the Cost Based Optimizer (CBO). As I am sure you are aware, Oracle strongly recommends migrating to the CBO (see the 9iR2 Performane Tuning Guide). The RBO is provided for backward compatability only in 9i, and Oracle began optimizing server operations to use the CBO for the past several releases prior to 10g. These optimizations include the inclusion of hints such as this.

According to the Performance Tuning Guide (9iR2):
Quote:
ALL_ROWS -
The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).


In 9.2, Oracle will utilize dynamic sampling if statistics are not available.

Hopefully you have an index on TABLE_B (id, code)?
Re: Weird problem :( [message #263365 is a reply to message #263363] Wed, 29 August 2007 20:44 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
This is soooooo helpful!!! It totally makes sense! I have no idea why we are still on RBO, no idea...Thanx!
Re: Weird problem :( [message #263369 is a reply to message #263365] Wed, 29 August 2007 20:55 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Don't bash your DBA too much. It may not be his/her fault. I see many organizations stuck in the RBO world due to 3rd party apps that do not support the CBO. Most of the time the vendors are just being overly cautious and moving to CBO is fine, but it varies.
Re: Weird problem :( [message #263375 is a reply to message #263369] Wed, 29 August 2007 21:21 Go to previous message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Actually, its true. We do have a third party product that comes with RBO, but if I was a DBA I would at least gather statistics on our tables.. I noticed that if I add ALL_ROWS or FIRST_ROWS hint to any of our queries the actually perform slower. This tells me that dynamic sampling is not doing us any good Sad
Previous Topic: oracle datatye
Next Topic: Retrieve Multiple result
Goto Forum:
  


Current Time: Wed Dec 07 10:59:28 CST 2016

Total time taken to generate the page: 0.11574 seconds