Home » RDBMS Server » Performance Tuning » Tuning SQL statement in PL/SQL
| Tuning SQL statement in PL/SQL [message #200383] |
Mon, 30 October 2006 11:00  |
Akshar
Messages: 116
Registered: May 2006
|
Senior Member |
|
|
Hi Experts!
Any suggestion and guideline to improve SQL in PL/SQL
I have generate a DBMS_PROFILER results and it gave
the following results : (These are just few excerpts from
a large procedure where SQL is being used in PL/SQL).
All the performance is being killed by these SQL statements.
Any ideas which make this sql statement
faster will be a great help.
OR
Any other approaches to tackle this problem?
Thanking you in advance for your insight.
Line Occur Sec Text
---------- ---------- ---------- -------------------------------------------------------------------------------------------------------------------
1201 22080 883.014144 SELECT /*+ FIRST_ROWS */ PSP.PatientNumber, PSP.IntakeID, U.OperationCenterCode OpCenterProcessed,
1202 PSP.ServiceCode, PSP.UOMcode, PSP.StartDt, PSP.ProvID, PSP.ExpDt, NVL(PSP.Units, 0) Units,
1203 PAS.Descript, PAS.ServiceCatID, PSP.CreatedBy AuthCreatedBy, PSP.CreatedDateTime AuthCreatedDateTime,
1204 PSP.AuthorizationID, PSP.ExtracontractReasonCode, PAS.ServiceTypeCode,
1205 NVL(PSP.ProvNotToExceedRate, 0) ProvOverrideRate,
1206 prov.ShortName ProvShortName, PSP.OverrideReasonCode, PAS.ContractProdClassId
1207 bulk collect into c_MGNA_Cur1_Rec
1208 FROM tblPatServProv psp, tblProductsAndSvcs pas, tblProv prov, tblUser u, tblGlMonthlyClose GLMC
1209 WHERE GLMC.AUTHORIZATIONID >= v_StartAuthId
1210 AND GLMC.AUTHORIZATIONID < v_StopAuthId
1211 AND PSP.AuthorizationID = GLMC.AUTHORIZATIONID
1212 AND PSP.Authorizationid < v_StopAuthId
1213 AND (PSP.ExpDt >= v_FiscalStart OR PSP.ExpDt IS NULL)
1214 AND PSP.ServiceCode = PAS.ServiceCode(+)
1215 AND prov.ProvID(+) = PSP.ProvID
1216 AND U.UserId(+) = PSP.CreatedBy;
1217
1232 8491627 1431.31769 SELECT /*+ FIRST_ROWS */ NWP.KID, NWP.PlanID, PI.ShortName, C.CarName, C.CarID, CI.SvcScheduleCode
1233 INTO v_IR.PlanKID, v_IR.PlanID, v_IR.PlanShortName, v_IR.CarName, v_IR.CarID, v_IR.PlanSvcScheduleCode
1234 FROM tblPatIntakePlan pip, tblNetworkPlan nwp, tblPlan pi, tblCarrier c, tblContractInfo ci
1235 WHERE PIP.PatientNumber = c_MGNA_Cur1_Rec(i).PatientNumber
1236 AND PIP.IntakeID = c_MGNA_Cur1_Rec(i).IntakeID
1237 AND PIP.PlanLevelCd = '1'
1238 AND NWP.PlanID = PIP.PlanID
1239 AND PI.PlanID = PIP.PlanID
1240 AND C.CarID = NWP.CarID
1241 AND CI.KID = NWP.KID;
1242
1247 8482787 696.831423 SELECT /*+ FIRST_ROWS */ OperationCenterCode
1248 INTO v_IR.PlanRNC
1249 FROM tblNetworkPlanPmtClass
1250 WHERE PlanID = v_IR.PlanID --v_PlanID
1251 AND NetworkPlanPmtClsStartDt <= NVL(c_MGNA_Cur1_Rec(i).StartDt, SYSDATE)
1252 AND (EndDate > NVL(c_MGNA_Cur1_Rec(i).StartDt, SYSDATE) OR EndDate IS NULL)
1253 AND LogicalDeleteInd = 'N'
1254 AND ROWNUM = 1;
1255 EXCEPTION
1256 WHEN NO_DATA_FOUND THEN
1257 2857 .010279425 GOTO INSERT_HERE; --PatIntakePlan record is missing. LK
1258 END;
1263 8482787 488.483078 SELECT Prov.ShortName, DECODE(prov.ProvParentID, 3, 4, prov.ProvTypeCd), ProvParentID
1264 INTO v_ProvShortName, v_IR.ProviderTypeID, v_IR.ProvParentID
1265 FROM tblProv Prov
1266 WHERE Prov.ProvID = c_MGNA_Cur1_Rec(i).ProvID;
1267 EXCEPTION
1268 WHEN NO_DATA_FOUND THEN
1269 49875 .021840545 v_ProvShortName := NULL;
1270 49875 .013905571 v_IR.ProviderTypeID := NULL;
1271 49875 .01314084 v_IR.ProvParentID := NULL;
1272 0 .076864525 END;
1328 380089 708.645573 SELECT SUM(PROVAMOUNT), SUM(PLANAMOUNT)
1329 INTO v_IR.Expense, v_IR.Revenue
1330 FROM tblClaimDetail WHERE AuthorizationId = c_MGNA_Cur1_Rec(i).AuthorizationId;
1331 END IF;
1332
1369 317771 547.292323 SELECT /*+ FIRST_ROWS */ SUM(PlanAmount) INTO v_IR.Revenue
1370 FROM tblClaimDetail
1371 WHERE AuthorizationId = c_MGNA_Cur1_Rec(i).AuthorizationId;
1388 0 2.71342391 BEGIN
1389 /*Selects patient name and ICD9 code LK*/
1390 8482787 1275.72944 SELECT /*+ FIRST_ROWS */ PAT.LastName, PAT.FirstName, SUBSTR(PID.ICD9, 1, 3) || '.' || SUBSTR(PID.ICD9, 4, 2),
1391 PAT.ZIP, PAT.State
1392 INTO v_IR.LastName, v_IR.FirstName, v_IR.PriICD9, v_IR.PatientZIP, v_IR.State
1393 FROM TBLPATIENT PAT, TBLPATINTAKEDIAG PID
1394 WHERE PID.PatientNumber(+) = c_MGNA_Cur1_Rec(i).PatientNumber
1395 AND PID.IntakeID(+) = c_MGNA_Cur1_Rec(i).IntakeID
1396 AND PID.LevelCD = '0001'
1397 AND PAT.PatientNumber = c_MGNA_Cur1_Rec(i).PatientNumber
1398 AND ROWNUM = 1;
1399 EXCEPTION
1400 WHEN NO_DATA_FOUND THEN
1401 8953 .004611824 v_IR.LastName := NULL;
1402 8953 .002442715 v_IR.FirstName := NULL;
1403 8953 .002782131 v_IR.PriICD9 := NULL;
1404 8953 .002430142 v_IR.PatientZIP := NULL;
1405 0 .016387219 END;
1416 8482787 340.928193 SELECT /*+ FIRST_ROWS */ DECODE(v_IR.CovType,'FFS', 'FFS', 'CAP')
1417 INTO v_IR.CovClass
1418 FROM DUAL;
1436 BEGIN
1437 8482787 2651.03249 SELECT /*+ FIRST_ROWS */ rg.Name
1438 INTO v_IR.GroupName --v_GroupName
1439 FROM tblNetworkPlanRptGroup nprg, tblReportGroup rg
1440 WHERE nprg.PlanID = v_IR.PlanID
1441 AND nprg.ReportGroupID = rg.ReportGroupID
1442 AND rg.ReportGroupClassID = pk_monthly2.v_MGNA_AcctgRptGrpClassID
1443 AND ROWNUM = 1;
1444 EXCEPTION
1445 WHEN NO_DATA_FOUND THEN
1446 5192886 3.63765938 v_IR.GroupName := NULL;
1447 8482787 23.4337623 END;
[Updated on: Mon, 30 October 2006 11:09] Report message to a moderator |
|
|
|
| Re: Tuning SQL statement in PL/SQL [message #200406 is a reply to message #200383] |
Mon, 30 October 2006 15:46  |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
|
First, there is little one can usually do by just seeing the SQL. We would need to look at the trace statistics. Second, and more importantly, I really doubt if you are going to speed these up. You are calling each of them 8.4 million times. That's your problem. The slowest of them is executing each query at a average of 0.0003125 seconds per query, quite fast by any standard. You may need to rethink how you're doing whatever it is you're doing.
|
|
|
|
| Re: Tuning SQL statement in PL/SQL [message #200417 is a reply to message #200406] |
Mon, 30 October 2006 19:19 |
Akshar
Messages: 116
Registered: May 2006
|
Senior Member |
|
|
Thanks for your reply.
I am woking on SQL trace statistics.
Does 'BULK COLLECT INTO' will make any different?
I have to work for COLLECTION for that if performance
is feasible using COLLECTION.
-----I have checked whether the indexs are being used
in the select statement and i found many statement
are using not all the indexs created on a particular table.
Let's say this SQL Statement....
SELECT /*+ FIRST_ROWS */ PAT.LastName,
PAT.FirstName,
SUBSTR(PID.ICD9, 1, 3) || '.' || SUBSTR(PID.ICD9, 4, 2),
PAT.ZIP,
PAT.State
INTO v_IR.LastName,
v_IR.FirstName,
v_IR.PriICD9,
v_IR.PatientZIP,
v_IR.State
FROM TBLPATIENT PAT,
TBLPATINTAKEDIAG PID
WHERE PID.PatientNumber (+) = :PatientNumber
AND PID.IntakeID (+) = :IntakeID
AND PID.LevelCD = '0001'
AND PAT.PatientNumber = :PatientNumber
AND ROWNUM = 1
in table - TBLPATINTAKEDIAG
CREATE TABLE TBLPATINTAKEDIAG
(
PATIENTNUMBER NUMBER(10) NOT NULL,
INTAKEID NUMBER(10) NOT NULL,
OPERATIONCENTERCODE VARCHAR2(8 BYTE) NOT NULL,
ICD9 VARCHAR2(8 BYTE) NOT NULL,
LEVELCD VARCHAR2(4 BYTE) NOT NULL,
ONSETDATE DATE,
CREATEDBY VARCHAR2(30 BYTE) DEFAULT user,
CREATEDDATETIME DATE DEFAULT sysdate,
UPDATEDBY VARCHAR2(30 BYTE) DEFAULT user,
UPDATEDDATETIME DATE DEFAULT sysdate
)
i have four index created on the columns :
Index Name Unique? Column Name Order Position Index Owner
PLAIN PK_TBLPATINTAKEDIAG Y PATIENTNUMBER Asc 1 NETWORX_OWNER
PLAIN PK_TBLPATINTAKEDIAG Y INTAKEID Asc 2 NETWORX_OWNER
PLAIN PK_TBLPATINTAKEDIAG Y OPERATIONCENTERCODE Asc 3 NETWORX_OWNER
PLAIN PK_TBLPATINTAKEDIAG Y LEVELCD Asc 4 NETWORX_OWNER
When i checked with TOAD in the SQL tuning
DESCRIBE DETAIL OF EXPLAIN PLAN it showed
only 1 index participation i.e. PK_PATIENTNUMBER,
Question is how to make the query to use all the index
on the column which are being used in the WHERE clause?
[Updated on: Tue, 31 October 2006 08:21] Report message to a moderator |
|
|
|
|
|
| Re: Tuning SQL statement in PL/SQL [message #200644 is a reply to message #200417] |
Tue, 31 October 2006 11:50 |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
|
You can either waste you time trying to get the database to do things it cannot do or you can actually listen to what I am trying to tell you. How much faster than 0.0003125 seconds per query do you think you can get the database to work? Answer: That is as fast as it is going to go. Forget about the trace. Forget about the indexes. Forget about hints. Bulk collection will not help you within a loop. That is NOT your problem. Your problem is a loop that iterates 8.4, make that closer to 8.5 million, times. The only way you are going to get anything resembling performance is to remove the PL/SQL loop. Any other exploration is just a waste of your time.
|
|
|
|
| Re: Tuning SQL statement in PL/SQL [message #200648 is a reply to message #200644] |
Tue, 31 October 2006 12:56 |
Akshar
Messages: 116
Registered: May 2006
|
Senior Member |
|
|
Skooman listen -->>> Tuning SQL is the area where I need to improve my skill,give simple solution instead.
Thanks and Agreeing with Scottwmackey's observations and suggetions.
I am still remain with one question,
then why my procedure takes 17 hours for 21 Million Rows ?
Do you have any suggestion for that ?
[Updated on: Tue, 31 October 2006 12:59] Report message to a moderator |
|
|
|
| Re: Tuning SQL statement in PL/SQL [message #200670 is a reply to message #200648] |
Tue, 31 October 2006 19:25 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
21 Million rows takes a long time to process.
Yes, you can help it along with BULK COLLECT, but that is
not going to help much.
Your problem is that you have SQL inside a loop that executes millions of times.
The ONLY way to get an order of magnitude improvement is NOT to have SQL inside this loop.
There are 3 main techniques to avoid this problem:
- Instead of SELECTs or CURSOR LOOPs inside an outer loop, try to join these tables into the outer cursor. If the outer cursor returns all of the columns you need
then you wont need to open a cursor or perform a SELECT inside the loop.
- Load rows for INSERT, UPDATE, and DELETE into nested tables and apply them inside a FORALL loop; NOT a regular LOOP (eg: FOR / WHILE).
- Use Concurrent Cursors to process cursors with different cardinality.
Ross Leishman
|
|
|
|
| Re: Tuning SQL statement in PL/SQL [message #200708 is a reply to message #200648] |
Wed, 01 November 2006 00:54 |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
Yes, I have a suggestion. Find an expert. And I don't say that with the least bit of sarcasm. If you are processing (updating, deleting , inserting) 21 million rows on a regular basis, you are going to need somebody to design a solution that makes use of Oracle's bulk processing. As Ross and I suggested, you cannot do this in a loop. It's just not fast enough and there is no way you can make it faster. So your options, it seems to me, are a) hire an expert contractor, b) study up real hard and real fast on the best ways to process large amounts of data in Oracle, or c) post your entire problem here and hope somebody has is slow at work and can at least suggest a method. To paraphrase Tom Kyte, if there were a generic solution to all problems, Oracle would just include it in the next release. But there isn't. That's why you need somebody who understands how Oracle works and what your specific requirements are to design an optimal solution. There are at least fifteen people who post here on a regular basis who could provide you with a solution, but you have not provided them with a set of requirements. If you have restrictions that prevent you from doing so, I doubt that there is anything much that they can do for you.
[Updated on: Wed, 01 November 2006 00:56] Report message to a moderator |
|
|
|
| Re: Tuning SQL statement in PL/SQL [message #200874 is a reply to message #200708] |
Wed, 01 November 2006 14:11 |
Akshar
Messages: 116
Registered: May 2006
|
Senior Member |
|
|
Thanks to rleishman
And
Thanks to scottwmackey.
I really need to improve the performance.
I cannot hire any expert because I am being designated to finish this task.
My commitment is:
I am ready for option
b) Study up real hard and real fast on the best ways to process large amounts of data in Oracle.
c) Post your entire problem here.
Posting entire problem is really large text.
My constraint is time: only 15 days to complete.
Though i have started to work on rleishman suggested techniques to reduce or avoid this problem,really need someone who can help me as a Mentor for this project.
|
|
|
|
|
|
| Re: Tuning SQL statement in PL/SQL [message #200977 is a reply to message #200383] |
Thu, 02 November 2006 02:07 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi.
You are executing about 45,000,000 SQL statements during 17 hours.
It means that on average each statements takes 1.36 msec - looks pretty good to me.
As I see it - the solution is to reduce the number of executed statements and NOT to improve them (you must be aware that for each statement you are paying a penalty of context switch).
A small example:
your application executes
SELECT /*+ FIRST_ROWS */ DECODE(v_IR.CovType,'FFS', 'FFS', 'CAP')
INTO v_IR.CovClass FROM DUAL;
about 8.4 M times and each time it incurrs contetxt switch.
Why not to use:
IF v_IR.CovType = 'FFS' THEN
v_IR.CovClass := 'FFS';
ELSE
v_IR.CovClass := 'CAP';
END IF;
instead?
|
|
|
|
| Re: Tuning SQL statement in PL/SQL [message #201102 is a reply to message #200943] |
Thu, 02 November 2006 09:21 |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
Akshar,
Do yourself and the company for which you are working a big favor, take Frank's advice. It looks to me like you are in over your head. You will not complete this task. Let you supervisor know right now. Don't take it personally. It says nothing about your intelligence or ability. All it means is that you don't have the experience. All of us had to learn at some time. Your supervisor has given you a task that is usually reserved for the rleishmans of the world, i.e. seasoned experts who know every technique, trick, and shortcut to milk every last millisecond out of Oracle. You cannot learn that in 14 days. You just can't. For instance, my guess is that you neglected to get the first requirement: How fast does it need to run? "Make it faster" is not a proper requirement. Given your statements hear and on the other thread, you don't seem to be in a position to go back to your supervisor and, with confidence, tell him/her what can or cannot be done or if, given the requirements, a major rearchitecting of your data is required. For example, the person given this task should already understand that grouping on 17 million rows takes time (see the other thread you started). Yes CPUs and disks are fast these days, but they're not magic. Only experience will give you "feelings" of how long processes should legitimately take. You're not there yet.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Apr 29 14:14:33 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
)