Solved

Oracle Join Query (Slow Response.....)

Posted on 2004-04-30
11
1,067 Views
Last Modified: 2008-03-10
Hi,

Enclosed Join Query takes longer execution time in our Client Server VB/ADO/Oracle 9i application.  Please suggest how we can improve execution times.

Currently it takes about 70 - 80 Seconds and this is unacceptable in Production environment.

******************* QUERY *********************************************

SELECT DISTINCT S.product_id, S.prod_yr, S.prod_issue, D.short_name,  I.start_date,  V.ebd
FROM rates_dir_info D, rates_ver_specific V, rates_prod_scheme S, directory_issue I
WHERE D.fk_product_code = s.product_id
AND D.fk_product_code = V.fk_product_code
AND V.access_dir_code = I.DIRECTORY_CODE
AND (V.access_issue_num = I.dir_issue_num OR (V.access_issue_num IS NULL))
AND S.PROD_YR= '2004'
AND TRIM(V.fk_product_code) = TRIM(S.product_id)
AND V.product_year = S.prod_yr
AND V.product_issue = S.prod_issue
AND (D.delete_flag IS NULL OR D.delete_flag = '')
AND (V.delete_flag IS NULL OR V.delete_flag = '')
AND (S.delete_flag IS NULL OR S.delete_flag = '')
ORDER BY S.product_id;


******************* QUERY *********************************************

Tables Used
rates_prod_scheme ( 4.5 Million Records)
PK Columns ( product_id, prod_yr, prod_issue, udac_7 )

rates_dir_info ( 1190 records)
PK Columns ( fk_product_code)

directory_issue (7727 records)
PK Columns ( directory_code, dir_issue_num )

rates_ver_specific (10597)
PK Columns ( fk_product_code, product_year, access_issue_num, product_issue )

Regards
Pramod
0
Comment
Question by:pramodsrao
11 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 50 total points
Comment Utility
well, first off, since you are using oracle9i,

I assume you are using WORKAREA_SIZE_POLICY =auto and set PGA_AGGREGATE_SIZE  in the init.ora

1. Now to your query: you use DISTINCT and Order by, so you will have a lot of sort activity,
try to increase PGA_AGGREGATE_SIZE to a larger value (for example 60M) if not set that big already since you have millions of records to sort.

2. If tables statistics are not up-to-date, recollect the statistics on the tables and indexes .

In oracle9i, don't use ANALYZE table compute stasticstics... USE dbms_STATS package instead.


3.
On these lines in where clause:

AND (D.delete_flag IS NULL OR D.delete_flag = '')
AND (V.delete_flag IS NULL OR V.delete_flag = '')
AND (S.delete_flag IS NULL OR S.delete_flag = '')


change these lines to :


AND D.delete_flag IS NULL
AND V.delete_flag IS NULL
AND S.delete_flag IS NULL


It's sufficient to do this since they are really overlapping and using OR is the worst.
0
 
LVL 15

Assisted Solution

by:andrewst
andrewst earned 50 total points
Comment Utility
If you could lose the TRIM on v.fk_product_code the query could use the primary key index on v - otherwise it can't - probably resulting in a full scan of 10,000 rows from v for every row in s!

Data should be trimmed on insert, not when selected.  If there is a problem with the data you could fix it and then add a trigger to trim the data whenever you records are inserted.
0
 
LVL 12

Assisted Solution

by:geotiger
geotiger earned 50 total points
Comment Utility

I think that you really need to convert rates_prod_scheme ( 4.5 Million Records) table into partition table with query overwrite option is set to Yes. It probably takes the most of time to scan this table.

Here is an example of creating partition table:


-- create the data table
CREATE TABLE s000df_atjnl (
 DFSTUDY             NUMBER(3)     NOT NULL,
 CENTERNO            NUMBER(5)     NOT NULL,
 RECORD_TYPE         CHAR(1)       NOT NULL,
 DFSTATUS            NUMBER(1)     NOT NULL,
 DFVALID             NUMBER(1)     NOT NULL,
 DFRASTER            VARCHAR2(12)  NOT NULL,
 DFPLATE             NUMBER(3)     NOT NULL,
 DFSEQ               NUMBER(5)     NOT NULL,
 DFPATID             NUMBER(10)    NOT NULL,
 JNL_USER            VARCHAR2(12),
 JNL_DATE            DATE,
 CRT_USER            VARCHAR2(12),
 CRT_DATE            DATE,
 MOD_USER            VARCHAR2(12),
 MOD_DATE            DATE,
 RES_USER            VARCHAR2(12),
 RES_DATE            DATE
)
/* Below are the default partition storage attributes.
   Partitions without any defined attributes take one the
   attributes defined on these lines. */
PCTFREE  10
PCTUSED  60
STORAGE ( INITIAL 10k NEXT 10k)
/* The table partition clause and the whole rest of the syntax
   make up the partition specificaiton. */
/* dfstudy is the partition key. */
PARTITION    BY RANGE (dfstudy)
SUBPARTITION BY HASH  (jnl_date)
SUBPARTITIONS 12 STORE IN (dfd_ph01, dfd_ph02, dfd_ph03, dfd_ph04,
    dfd_ph05, dfd_ph06) (
/* Now the partition description clauses:
   df_p01 - records from studies   1- 24
   df_p02 - records from studies  24- 49
   df_p03 - records from studies  50- 74
   df_p04 - records from studies  75- 99
   df_p05 - records from studies 100-255 including null study number
*/
PARTITION df_p01 VALUES LESS THAN (25),
PARTITION df_p02 VALUES LESS THAN (50),
PARTITION df_p03 VALUES LESS THAN (75),
PARTITION df_p04 VALUES LESS THAN (100),
PARTITION df_p05 VALUES LESS THAN (125),
PARTITION df_p06 VALUES LESS THAN (MAXVALUE)
)
/


GT

0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 50 total points
Comment Utility
1. Never use "distinct" in an Oracle query unless you need it, and have tested it and are sure it gives you the results you want.  "Distinct" will always cause a sort operation that you may not need, and the "distinctness" of the records returned may not be what you expect.

2. Is the "PROD_YR" column in the rates_prod_scheme table actually a varchar2 (or char) column, or is it a number column?
If that is a number column, you have a serious flaw in the query, since the value provided has single quotes around it.  This could cause Oracle to do an implicit conversion of the data in "PROD_YR" to characters, and this will prevent the use of the index that includes this column.

3. I agree that partitioning this table could help, but it looks to me like the only partitioning that would help this query is partitioning it by year since that is the only value you are providing in the query to help limit which rows should be returned from the largest table.

4. As andrewst pointed out, you need to either remove the "trim" on (V.fk_product_code), or add a function-based index that is based on TRIM(V.fk_product_code) *AND* make sure your query uses that index!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Expert Comment

by:riazpk
Comment Utility
And please also post execution plan of query.
0
 

Author Comment

by:pramodsrao
Comment Utility
Hello everybody,

First of all thank you all for wonderful suggestions. Its working and has improved the response time from 4 mins to 2 mins in Production DB.

I have made the following changes.

1) Removed TRIM across the whole Query
2) Modified statments
AND (D.delete_flag IS NULL OR D.delete_flag = '')
AND (V.delete_flag IS NULL OR V.delete_flag = '')
AND (S.delete_flag IS NULL OR S.delete_flag = '')
to
AND (D.delete_flag IS NULL OR D.delete_flag = '')
AND (V.delete_flag IS NULL OR V.delete_flag = '')
AND (S.delete_flag IS NULL OR S.delete_flag = '')

AND D.delete_flag IS NULL
AND V.delete_flag IS NULL
AND S.delete_flag IS NULL


3) All DBA related suggestions have been passed on to our DB team and I haven't got any response from them. I shall also discuss with them following DBA related suggestions as you all have described.

a) Run Update Statistics
b) I am not sure about WORKAREA_SIZE_POLICY and PGA_AGGREGATE_SIZE  in the init.ora
b) Partition rates_prod_scheme table

4) PROD_YR is defined as CHAR(4) in DB Schema and not Numeric.

Regards
Pramod
0
 
LVL 13

Expert Comment

by:riazpk
Comment Utility
from ur changes

AND (D.delete_flag IS NULL OR D.delete_flag = '')
AND (V.delete_flag IS NULL OR V.delete_flag = '')
AND (S.delete_flag IS NULL OR S.delete_flag = '')

AND D.delete_flag IS NULL
AND V.delete_flag IS NULL
AND S.delete_flag IS NULL


I will STRONGLY OPPOSE THIS APPROACH. You are telling optimizer:

give me the rows where d.delete_flag is null
and on the other hand,
you are asking for rows where d.delete_flag is null OR d.delete_flag='' (contains some value implies not null)

So please please please elaborate your requirement first. You should either use:

AND D.delete_flag IS NULL
AND V.delete_flag IS NULL
AND S.delete_flag IS NULL

OR

AND trim(D.delete_flag) IS NULL (logically equivalent to d.delete_flag=null or d.delete_flag='')
AND trim(V.delete_flag) IS NULL
AND trim(S.delete_flag) IS NULL

PLEASE DON'T USE TWO CONTRADICTORY STATEMENTS TOGETHER (I WILL STOP SHOUTING NOW).
0
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 50 total points
Comment Utility
So your final query shoul look like:

SELECT DISTINCT S.product_id, S.prod_yr, S.prod_issue, D.short_name,  I.start_date,  V.ebd
FROM rates_dir_info D, rates_ver_specific V, rates_prod_scheme S, directory_issue I
WHERE D.fk_product_code = s.product_id
AND D.fk_product_code = V.fk_product_code
AND V.access_dir_code = I.DIRECTORY_CODE
AND V.access_issue_num = I.dir_issue_num
AND S.PROD_YR= '2004'
AND V.fk_product_code = S.product_id
AND V.product_year = S.prod_yr
AND V.product_issue = S.prod_issue
AND D.delete_flag IS NULL
AND V.delete_flag IS NULL
AND S.delete_flag IS NULL
ORDER BY S.product_id;

OR

SELECT DISTINCT S.product_id, S.prod_yr, S.prod_issue, D.short_name,  I.start_date,  V.ebd
FROM rates_dir_info D, rates_ver_specific V, rates_prod_scheme S, directory_issue I
WHERE D.fk_product_code = s.product_id
AND D.fk_product_code = V.fk_product_code
AND V.access_dir_code = I.DIRECTORY_CODE
AND V.access_issue_num = I.dir_issue_num
AND S.PROD_YR= '2004'
AND V.fk_product_code = S.product_id
AND V.product_year = S.prod_yr
AND V.product_issue = S.prod_issue
AND trim(D.delete_flag) IS NULL
AND trim(V.delete_flag) IS NULL
AND trim(S.delete_flag) IS NULL
ORDER BY S.product_id;

(you will have to choose one as per your needs).
0
 
LVL 13

Expert Comment

by:riazpk
Comment Utility
Also do (always) post the explain plan for your queries...it will help a lot in understanding how the query is executed.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now