SQL Query performance - help

[bheemsen]

Hi,

I have the following queries in a procedure and the performance is very bad.

I need help in combining these two queries.

1) this is the cursor
SELECT --+FIRST_ROWS
DISTINCT a.cable_num, b.pair_num
FROM tab1 a,
tab2 b,
tab3 c
WHERE a.col1 = b.col1
AND b.col1 = c.col1
AND a.col2 = c.col2
AND a.col3 = b.col3
AND b.pair_num BETWEEN a.pair_low AND a.pair_high
AND a.col1 = <var1>
AND c.da_cd = <var2>
ORDER BY a.cable_num, b.pair_num ;

2) this query is inside the cursor
SELECT *
INTO vtestsol
FROM
(SELECT --+FIRST_ROWS
c.test_sol
FROM tab4 a,
tab5 b,
tab6 c,
tab7 d
WHERE a.wkpkg_key = b.wkpkg_key
AND b.job_id = c.job_id
AND b.job_id = d.job_id(+)
AND a.wire_center = TO_CHAR(<var1>)
AND a.track_unit = <var2>
AND a.delete_flag = 'N'
AND c.db_dca = vCableNum (from 1st query)
AND c.db_dpair = nPairNum (from 1st query) ORDER BY d.db_dn_date DESC NULLS LAST)
WHERE ROWNUM = 1;

The two variables in red above are coming from the cursor. Does anyone know how to combine these two queries into one and just use the cursor itself to achieve everything ?

We are on Oracle 9i. The second query has to parse through the large volume of data, but each time it retrieves only one row. So I can't use the hint PARALLEL too. In second query all tables are remote. So network is involved. Each row is returning fast, but overall the procedure takes about 7 to 10 min. This procedure is called from front-end in an OLTP application. We can't afford to have a very slow performance.

Any help is appreciated in joining these two queries.

Thanks
-BS

 

[SantaMufasa]

BS,

I'm certain we could combine the two queries into one if we knew, for sure, your specifications for the combination.

How about if you "take a stab" at what you want for your combined query, and you can just put in pseudo-code in square brackets "[something your are not sure how to do here]", and we can help out until the query does just what you want.

Regarding performance: the top two performance enhancers typically are 1) ensuring that you have indexes on all columns mentioned in your WHERE-clause conditions and 2) ensuring that you have recently gathered statistics on the tables involved in the query. So, please ensure that you have addressed those two items.

The two variables in red above are coming from the cursor.

Notice that your original post has no variables in red. To create text in a different color, you start the new color using this TGML code: "[COLOR=<some color like RED, BLUE, GREEN, et cetera>]", then end the color with "[/color]".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[bheemsen]

The two variables I was talking about are:

AND c.db_dca = vCableNum (from 1st query)
AND c.db_dpair = nPairNum (from 1st query)

I was able to merge the two queries as follows. All the tables are analyzed.

SELECT --+FIRST_ROWS
e.cable_num, f.pair_num, c.vdsl_summ
FROM tab4 a,
tab5 b,
tab6 c,
tab7 d,
tab1 e,
tab2 f,
tab3 g
WHERE a.wkpkg_key = b.wkpkg_key
AND b.job_id = c.job_id
AND b.job_id = d.job_id(+)
AND a.wire_center = TO_CHAR(p_nWireCntrCd)
AND e.WIRE_CNTR_CD = p_nWireCntrCd
AND a.wire_center = TO_CHAR(e.wire_cntr_cd)
AND e.wire_cntr_cd = f.wire_cntr_cd
AND f.wire_cntr_cd = g.wire_cntr_cd
AND e.serving_trml_name = g.serving_trml_name
AND e.cable_num = f.cable_num
AND f.pair_num BETWEEN e.pair_low AND e.pair_high
AND a.track_unit = p_vDaCd
AND g.da_cd = p_vDaCd
AND a.delete_flag = 'N'
AND c.db_dca = e.cable_num
AND c.db_dpair = f.pair_num
ORDER BY e.cable_num, f.pair_num, d.db_dn_date DESC NULLS LAST;

Note: tab4 to tab7 are remote tables.

Now, I found an interesting thing. When I run my original two queries, and modified merged query independently in SQL plus window, they all run in less than a second. But when they are run from within my procedure they are running for ever and the procedure sometimes finishes after 15 to 20 min.

I do not understnad why this is happening and what is the diffrence in running them stand alone and running them from within a procedure.

Network is involved in both cases. The second query uses all remote tables.

We are on oracle 9i.

Does anyone know how to improve the performance of procedure ?

Thanks
BS

 

[engineer2100]

Do you have this query running inside a loop?

Cut down of multiple SQL calls using the same table(s) into a single one
I have often seen that people use

Select sysdate
into <variable>
from dual;

while a direct assignmenmt would do the trick.

Get the point. Avoid unnecesary calls to SQL Engine.

HTH

 

[Dagon]

When you say it runs for less than a second, do you mean to get the first few rows or to go through the query in its entirety ? It is possible for a query to get the first few rows quickly but for the query as a whole to take a long time e.g. if it is taking a 0.2 of a second for each row, you would see the first 50 rows in 5 seconds, but it would take hours to get through a million rows. Try timing just the cursor in PL/SQL e.g.

for rec_curs in (select ...) loop
null;
end;

 

[Dagon]

Should have been 10 seconds, but you get the point.

 

[engineer2100]

Dagon,

Will this query (within the proc) appear in V$SESSION_LONGOPS?

 

[Dagon]

It depends what sort of operation the database is performing and whether it constitutes a longop or not. Operations like full table scans that are done locally will, but you probably won't see remote operations.