OraFAQ Forum: SQL & PL/SQL » Weird problem :(

Home » SQL & PL/SQL » SQL & PL/SQL » Weird problem :(

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Weird problem :( [message #263360]

Wed, 29 August 2007 18:54

lotusdeva
Messages: 201
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!

Report message to a moderator

Re: Weird problem :( [message #263363 is a reply to message #263360]

Wed, 29 August 2007 20:31

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)?

Report message to a moderator

Re: Weird problem :( [message #263365 is a reply to message #263363]

Wed, 29 August 2007 20:44

lotusdeva
Messages: 201
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!

Report message to a moderator

Re: Weird problem :( [message #263369 is a reply to message #263365]

Wed, 29 August 2007 20:55

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.

Report message to a moderator

Re: Weird problem :( [message #263375 is a reply to message #263369]

Wed, 29 August 2007 21:21

lotusdeva
Messages: 201
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

Report message to a moderator

Previous Topic:	oracle datatye
Next Topic:	Retrieve Multiple result

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 18:40:15 CST 2024