Home » SQL & PL/SQL » SQL & PL/SQL » SQL Performance question
| SQL Performance question [message #240745] |
Fri, 25 May 2007 11:17  |
bheemsen
Messages: 13
Registered: February 2006
|
Junior Member |
|
|
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
|
|
|
|
|
|
| Re: SQL Performance question [message #240802 is a reply to message #240749] |
Fri, 25 May 2007 20:38  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The FIRST_ROWS hints wont be helping in either case, because you have DISTINCT in the first query and ORDER BY in the second. These cause all of the rows to be identified and sorted before one can be returned. FIRST_ROWS only really helps when Oracle has a choice not to identify all of the rows before the first one can be returned.
- How many rows does the first cursor return?
- When running alone (without the 2nd cursor), how long does it take?
- How long does the second cursor take to run for a single input?
- How many rows would it return if you didn't restrict it to just the first?
- Attach Explain Plans for both queries.
Since both queries are retrieving every row (even though they are not "fetching" them back to PL/SQL) it would hardly be any harm to just join all 7 tables, order by a.cable_num, b.pair_num, d.db_dn_date DESC NULLS LAST, and then ignore every row except upon change of cable_num/pair_num. The overhead is that you will be returning more rows (maybe many more) back across to PL/SQL, but at least you'll just be running a single SQL.
If that doesn't help, answer all 5 questions above.
Ross Leishman
|
|
|
|
| Re: SQL Performance question [message #241441 is a reply to message #240802] |
Tue, 29 May 2007 10:59 |
bheemsen
Messages: 13
Registered: February 2006
|
Junior Member |
|
|
Based on your reply, I just came up with the following one query merging the two queries. What do you think on this query ?
I think it should work. As you said, I need to ignore every row except upon change of cable_num/pair_num. Only thing is that the query will be returning more rows (maybe many more) back across to PL/SQL, but at least I'll just be running a single SQL.
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: I replaced some columns with their original names.
|
|
|
|
| Re: SQL Performance question [message #241449 is a reply to message #240745] |
Tue, 29 May 2007 11:24 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
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
Eliminate out of the FROM clause tab4 a, tab5 b, tab7 e, tab3g because they contribute NOTHING to the SELECT clause.
They can be subordinated into the WHERE clause
|
|
|
|
|
|
| Re: SQL Performance question [message #241525 is a reply to message #241460] |
Tue, 29 May 2007 21:43 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I concur. Sub-queries had more impact pre-9i before unnesting was automated in the optimiser.
Using EXISTS can still deliver a performance imporvement in improperly modelled environments with n<=>m joins. These are usually characterised by a DISTINCT in queries for no readily apparent reason.
Having said all that, we acknowlege that this query will be returning more rows than required, so the jury's out on what improvements a sub-query might bring.
@bheemsen, we don't know your data model, so it's hard to comment on whether your SQL is good or not. It is certainly the approach I suggested though - whether it will help only testing will tell.
If you want me to comment on its likelihood of helping, then you should get rid of the FIRST_ROWS hint, post the Explain Plan, and answer the 5 questions I asked ealier.
Ross Leishman
|
|
|
|
| Re: SQL Performance question [message #242084 is a reply to message #240745] |
Thu, 31 May 2007 09:55 |
bheemsen
Messages: 13
Registered: February 2006
|
Junior Member |
|
|
Ross,
Here are the answers to your five questions:
1.How many rows does the first cursor return?
50 to 60 rows
2. When running alone (without the 2nd cursor), how long does it take?
0.5 sec (query ran stand alone)
3.How long does the second cursor take to run for a single input?
0.5 sec
4.How many rows would it return if you didn't restrict it to just the first?
3 to 5 (varies)
5.Attach Explain Plans for both queries.
Explain plan for 1st query:
SELECT STATEMENT 20 1 85
SORT (UNIQUE) 19 1 85
NESTED LOOPS 17 1 85
NESTED LOOPS 9 4 280
TABLE ACCESS (BY INDEX ROWID)--RP_SERVING_TERMINAL 3 3 93
INDEX (RANGE SCAN)--SKEC_RP_SERVING_TERMINAL_WCD (NON-UNIQUE) 1 3 0
INDEX (RANGE SCAN)--XPKRP_PAIR_APPEARANCE (UNIQUE) 2 1 39
INDEX (RANGE SCAN)--XPKRP_CABLE_PAIR (UNIQUE) 2 1 15
" SELECT STATEMENT" 20 1 85
" SORT (UNIQUE)" 19 1 85
" NESTED LOOPS" 17 1 85
" NESTED LOOPS" 9 4 280
" TABLE ACCESS (BY INDEX ROWID)--RP_SERVING_TERMINAL" 3 3 93
" INDEX (RANGE SCAN)--SKEC_RP_SERVING_TERMINAL_WCD (NON-UNIQUE)" 1 3 0
" INDEX (RANGE SCAN)--XPKRP_PAIR_APPEARANCE (UNIQUE)" 2 1 39
" INDEX (RANGE SCAN)--XPKRP_CABLE_PAIR (UNIQUE)" 2 1 "15 SELECT STATEMENT"
" COUNT (STOPKEY)" 0 0 0
" VIEW" 24 1 18
" SORT (ORDER BY STOPKEY)" 24 1 88
" NESTED LOOPS" 23 1 88
" MERGE JOIN (CARTESIAN)" 8 1 50
" NESTED LOOPS (OUTER)" 4 1 45
" NESTED LOOPS" 3 1 31
" TABLE ACCESS (BY INDEX ROWID)--DVR_NETDSL_WKPKG (TABLE)" 2 1 21
" INDEX (RANGE SCAN)--DVR_NETDSL_WKPKG_WC_TU_IDX (INDEX)" 1 11 0
" INDEX (RANGE SCAN)--UNQ_MAP_WKPKG_JOB (INDEX (UNIQUE))" 1 2 20
" TABLE ACCESS (BY INDEX ROWID)--DSLENTITY (TABLE)" 1 1 14
" INDEX (UNIQUE SCAN)--DSLENTITY_PK (INDEX (UNIQUE))" 0 1 0
" BUFFER (SORT)" 7 110 550
" TABLE ACCESS (FULL)--STATUS (TABLE)" 4 110 550
" VIEW" 15 1 38
" UNION-ALL" 0 0 0
" NESTED LOOPS (SEMI)" 4 1 43
" TABLE ACCESS (BY INDEX ROWID)--ATISHADOW (TABLE)" 2 1 31
" INDEX (RANGE SCAN)--ATISHADOW_CA_PAIR_IDX (INDEX)" 1 1 0
" INDEX (RANGE SCAN)--DVRIX_DRSLTS_JOBPRVSM (INDEX)" 2 1 12
" NESTED LOOPS (SEMI)" 5 1 52
" NESTED LOOPS (ANTI)" 3 1 40
" TABLE ACCESS (BY INDEX ROWID)--DSLSHADOW (TABLE)" 2 1 28
" INDEX (RANGE SCAN)--DSLSHADOW_CA_PAIR_IDX (INDEX)" 1 1 0
" INDEX (RANGE SCAN)--DVRIX_ATISHADW_JOBPRVSM (INDEX)" 1 1 12
" INDEX (RANGE SCAN)--DVRIX_DRSLTS_JOBPRVSM (INDEX)" 2 1 12
" NESTED LOOPS (ANTI)" 6 1 52
" NESTED LOOPS (ANTI)" 5 1 40
" TABLE ACCESS (BY INDEX ROWID)--DSLRESULTS (TABLE)" 4 1 28
" INDEX (RANGE SCAN)--DSLRESULTS_CA_PAIR_IDX (INDEX)" 3 1 0
" INDEX (RANGE SCAN)--DVRIX_ATISHADW_JOBPRVSM (INDEX)" 1 1 12
" INDEX (RANGE SCAN)--DVRIX_DSLSHADW_JOBPRVSM (INDEX)" 1 1 12
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.
Can someone please help.
Thanks
BS
[Mod: Added CODE tags]
[Updated on: Fri, 01 June 2007 22:52] by Moderator Report message to a moderator |
|
|
|
| Re: SQL Performance question [message #242425 is a reply to message #242084] |
Fri, 01 June 2007 23:01 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| bheemsen wrote on Fri, 01 June 2007 00:55 | (all of the objects are views to remote tables):
|
Now that's something it might have been handy to know earlier. Didn't think it was important?
Judging by the fact that I don't see a REMOTE keyword in your Explain Plan, I'm guessing you ran it on the database that owns the underlying tables, not on the remote database.
When you are calling it from the remote database, it is almost certainly performing a different execution plan. Try using the DRIVING_SITE hint to force exection on the other database - see the doco for details.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 22:09:20 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
