Jump to content
Arunachalam.S

Sql tuning

Recommended Posts

Hi Please tell me how to tune the below query.

Attached the explain plan for reference.

Also please note the object names po, prline, rfq, rfqline are views.

These views are having two select statements by using union all. The first select statement refers another schema table of same database and another select refers table from another database by using database link.

The scenario is like this...

I'm archiving old data into another database and current database has only few records to improve the performance of application. There are some users which required to generate reports by using two database (Production and Archived database.)

So the view is created like this..

Create or replace view schema2.v1

select col1,col2

from schema1.table1

union all

select col1, col2

from schema.table1@dblink;

The above scenario is explained for IBM Maximo application and we are using IBM OPTIM for archiving. Also the maximo is generating the sql and sending to database like below queries.

SELECT Count(*)
FROM   dummy_table
WHERE  EXISTS(SELECT 1
              FROM   po
              WHERE  ponum IN (SELECT ponum
                               FROM   prline
                               WHERE  ponum IS NOT NULL
                                      AND prnum = 'D-8178')
                      OR ponum IN (SELECT ponum
                                   FROM   rfqline
                                   WHERE  rfqnum IN (SELECT rfqnum
                                                     FROM   prline
                                                     WHERE  prnum = 'D-8178')))
        OR EXISTS(SELECT 1
                  FROM   rfq
                  WHERE  rfqnum IN (SELECT rfqnum
                                    FROM   prline
                                    WHERE  prnum = 'D-8178'));

Please help me out how to overcome these kind of problems.

Explain_plan.html

Share this post


Link to post
Share on other sites

Hi,

Start by looking at the consistent gets and physical reads using SQL*Plus:

http://www.dba-oracle.com/t_OracleAutotrace.htm

This is not a trivial query because of the views.  Can you run the query against the base tables?  Please read carefully:

http://www.dba-oracle.com/t_tuning_sql_execution_views.htm

i would start by trying hints to alter the execution plan:

http://dba-oracle.com/googlesearchsite_proc.htm?cx=000522505899594707971%3A4-ldikxixw4&cof=FORID%3A10&ie=UTF-8&q=Hint+tuning&sa=Search&siteurl=www.dba-oracle.com%2Ft_OracleAutotrace.htm&ref=www.google.com%2F&ss=5280j2995794j11

For the full details, see my Oracle SQL tuning book:

http://rampant-books.com/book_1001_oracle_sql_tuning.htm

Good luck!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×