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 Go to next message
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 Go to previous messageGo to next message
scottwmackey
Messages: 505
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 Go to previous messageGo to next message
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 #200638 is a reply to message #200417] Tue, 31 October 2006 10:58 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Remark at "the sideline" (is that a correct expression?? Razz )

Why did you put the first_rows hint in? It looks as if you would like to get all rows, so it seems better to let the optimizer know that, instead of "fool it".

Regards,
Sabine
Re: Tuning SQL statement in PL/SQL [message #200644 is a reply to message #200417] Tue, 31 October 2006 11:50 Go to previous messageGo to next message
scottwmackey
Messages: 505
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
scottwmackey
Messages: 505
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 Go to previous messageGo to next message
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 #200943 is a reply to message #200874] Thu, 02 November 2006 00:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Inform your manager that you lack the knowledge to complete this task now instead of waiting 14 days.
Re: Tuning SQL statement in PL/SQL [message #200977 is a reply to message #200383] Thu, 02 November 2006 02:07 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
scottwmackey
Messages: 505
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.
Re: Tuning SQL statement in PL/SQL [message #201486 is a reply to message #201102] Sat, 04 November 2006 15:20 Go to previous message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Guys,

No more panic, i have handed over that project back.

Thanks for all of your support and suggestions.

Previous Topic: Will Deleting rows from Table improve Performance?
Next Topic: v$librarycache
Goto Forum:
  


Current Time: Fri Dec 02 16:19:47 CST 2016

Total time taken to generate the page: 0.30368 seconds