Solved

SLOW PERFORMANCE WITH STORED PROCEDURES iN ORACLE

Posted on 2002-07-30
9
1,507 Views
Last Modified: 2008-02-01
We run an insert from Oracle sql tool that selects 200.000 rows from various  tables and inserts them in another table. This ends in 10mins. We try the same query inside a stored procedure in oracle and we run for hours.Oracle version is 8.1.5 and runs on WNT
0
Comment
Question by:slefteris
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 7188449
Usually the performance of an SQL statement in a stored procedure is at least as fast as that same statement executed as an ad-hoc SQL statement from SQL*Plus.  The key is: "the same statement".  Are you sure that the stored procedure is doing only and exactly the same SQL as the other insert process?  Can you post the text of the two statements/processes here?
0
 
LVL 47

Expert Comment

by:schwertner
ID: 7190378
Try to figure out possible contentions in yor DB. Look in trace and alert files of your instance. It is worth to read these files and also the alerts files of the Archiver, Logwriter and all other sources. It is posible that the rollback segments are too small.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 7190426
Check also v$session_event for the wait statistics.
0
 

Author Comment

by:slefteris
ID: 7190732
I am sending the procedure and same things we do from SQL Navigator connected to same database instance.Procedure takes 2.5 hours and SQL Navigator goes in 14mins.Whats wrong? There is no other session in system. thanks in advance

PROCEDURE                MOTHERS_PROD_USAGE_BY_HOSP
   (
     CHILDREN_NUM            VARCHAR,
     HOSPITAL_CODE           VARCHAR,
     CAMPAIGN_TYPE           VARCHAR,
     CUSTOMER_CODE           VARCHAR,
     PARENT_PRODUCT_CODE     VARCHAR,
     MIN_ACCOUNT_ZIP_CODE    VARCHAR,
     MAX_ACCOUNT_ZIP_CODE    VARCHAR,
     MIN_ACCOUNT_PROV_CODE   VARCHAR,
     MAX_ACCOUNT_PROV_CODE   VARCHAR,
     MIN_CONTACT_BIRTH_DATE  DATE,
     MAX_CONTACT_BIRTH_DATE  DATE
   )
AS

BEGIN
-- CREATE STATISTICS --
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_CONTACT ESTIMATE STATISTICS SAMPLE 2000 ROWS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_CONTACT_X ESTIMATE STATISTICS SAMPLE 2000 ROWS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_ADDR_ORG ESTIMATE STATISTICS SAMPLE 2000 ROWS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_ADDR_PER ESTIMATE STATISTICS SAMPLE 2000 ROWS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_CHRCTR ESTIMATE STATISTICS SAMPLE 2000 ROWS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_CON_CHRCTR ESTIMATE STATISTICS SAMPLE 2000 ROWS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_ORG_EXT ESTIMATE STATISTICS SAMPLE 2000 ROWS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_ORG_EXT_X ESTIMATE STATISTICS SAMPLE 2000 ROWS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_PER_ORG_UNIT ESTIMATE STATISTICS SAMPLE 2000 ROWS';
------------------------  

EXECUTE IMMEDIATE 'TRUNCATE TABLE SIEBEL_REPORTS.MOTHERS_PROD_USAGE_BY_HOSP_T1';
EXECUTE IMMEDIATE 'ALTER TABLE SIEBEL_REPORTS.MOTHERS_PROD_USAGE_BY_HOSP_T1 NOLOGGING';    -- Disable redo logging for table
SET TRANSACTION USE ROLLBACK SEGMENT SIEBEL_LARGE;

INSERT INTO  SIEBEL_REPORTS.MOTHERS_PROD_USAGE_BY_HOSP_T1
SELECT
      SUBSTR(T8.NAME,32,5)                                                 "PC_CAMPAIGN_TYPE",
      SUBSTR(T8.NAME,26,4)                                                 "PARENT_PRODUCT",
      SUBSTR(T8.NAME,21,4)                                                 "PARENT_VENDOR",
      SUBSTR(T8.NAME,16,4)         "PARENT_PRODUCT_LINE",
      -- Categories
      T9.LAST_UPD           "CATEGORY_LAST_UPD",
      T9.CHRCTR_ID                                                             "CATEGORY_NAME_ROW_ID",
      T8.NAME                                                                   "CATEGORY_NAME",
      T9.CHRCTR_VAL                                                       "CATEGORY_VALUE",
      -- Product
      SUBSTR(SUBSTR(T9.CHRCTR_VAL,-9),1,4)                         "ACCOUNT_CODE",
      SUBSTR(T9.CHRCTR
_VAL,-4)                                          "PRODUCT_CODE",
      -- Household Info
      T10.ROW_ID                                                             "HOUSEHOLD_ROW_ID",          
      T10.NAME                                                                   "HOUSEHOLD_NAME",              
      T24.ATTRIB_18                                                       "CHILDREN_NO",              
      T24.ATTRIB_21                                                       "ACTUAL_CHILDREN_NO",
      -- Contact
      T9.CONTACT_ID                                                       "CONTACT_ROW_ID",
      T1.MID_NAME                    "CONTACT_CODE",
      T1.FST_NAME                                                             "CONTACT_FST_NAME",
      T1.LAST_NAME                                                             "CONTACT_LAST_NAME",
      T1.BIRTH_DT                                                             "CONTACT_BIRTH_DATE",
      T2.ATTRIB_37          "CONTACT_TYPE",
      -- Manager
      T6.ROW_ID                                                        "MGR_ROW_ID",
      T6.MID_NAME                                                             "MGR_CODE",
      T6.FST_NAME                                                             "MGR_FST_NAME",
      T6.LAST_NAME                                                             "MGR_LAST_NAME",
      T7.ATTRIB_37          "MGR_TYPE",
      -- Manager Personal Address
      T23.ADDR                      "MGR_PERSONAL_STREET_ADDRESS",            
      T23.ADDR_NUM                  "MGR_PERSONAL_ADDR_NUM",                  
      T23.X_FLOOR                   "MGR_PERSONAL_FLOOR",                      
      T23.ZIPCODE                   "MGR_PERSONAL_ZIP_CODE",                  
      T23.PROVINCE                  "MGR_PERSONAL_PROVINCE_ADDR",              
      T23.CITY                      "MGR_PERSONAL_POST_OFFICE",                
      T23.STATE                     "MGR_PERSONAL_PROV_CODE",                  
      T23.COUNTY                    "MGR_PERSONAL_CITY",                      
      T23.ADDR_TYPE_CD              "MGR_PERSONAL_ADDR_TYPE",
       -- Manager Account Address
      T5.ADDR                      "MGR_ACCT_STREET_ADDRESS",                  
      T5.ADDR_NUM                  "MGR_ACCT_ADDR_NUM",                        
      T5.X_FLOOR                   "MGR_ACCT_FLOOR",                          
      T5.ZIPCODE                   "MGR_ACCT_ZIP_CODE",                        
      T5.PROVINCE                  "MGR_ACCT_PROVINCE_ADDR",                  
      T5.CITY                      "MGR_ACCT_POST_OFFICE",                    
      T5.STATE                     "MGR_ACCT_PROV_CODE",                      
      T5.COUNTY                    "MGR_ACCT_CITY",                            
      T5.ADDR_TYPE_CD              "MGR_ACCT_ADDR_TYPE",        
      -- Manager Phone Numbers
      T10.MAIN_PH_NUM                                                       "HH_PH_NUM",
      T6.CELL_PH_NUM                                                       "CELL_PH_NUM",
      T6.HOME_PH_NUM                                                       "CON_HM_NUM",
      T6.WORK_PH_NUM                                                       "WORK_PH_NUM"
FROM
       SIEBEL.S_CONTACT T1,                                                     -- Contact
       SIEBEL.S_CONTACT_X T2,                                                   -- Contact Extension
       SIEBEL.S_ADDR_ORG T5,                                                    -- Manager Account Address
       SIEBEL.S_ADDR_PER T23,                                                   -- Manager Personal Address
       SIEBEL.S_CONTACT T6,                                                     -- Manager
       SIEBEL.S_CONTACT_X T7,                                                   -- Manager Extension
       SIEBEL.S_CHRCTR T8,
       SIEBEL.S_CON_CHRCTR T9,
       SIEBEL.S_ORG_EXT T10,
       SIEBEL.S_ORG_EXT_X T24
     WHERE
      -- Category Joins
          T9.CHRCTR_ID = T8.ROW_ID                                              -- S_CON_CHRCTR and S_CHRCTR
      AND T8.NAME like 'Euaeeuo A?aioo%'                                        -- Category Name
      -- Contact Joins
      AND T9.CONTACT_ID   /*(+)*/         = T1.ROW_ID
      AND T1.ROW_ID                   = T2.PAR_ROW_ID
      AND T2.ATTRIB_37                = '?aea?'
      -- Manager Joins
      AND T1.CON_MANAGER_PER_ID /*(+)*/  = T6.ROW_ID
      AND T6.ROW_ID /*(+)*/              = T7.PAR_ROW_ID
      AND T7.ATTRIB_37                not in ('?aea?','?nuou?i Eiaaneaoi?i')
      AND T6.PR_OU_ADDR_ID            = T5.ROW_ID (+)                           -- S_CONTACT and S_ADDR_ORG
      AND T6.PR_PER_ADDR_ID           = T23.ROW_ID (+)                          -- S_CONTACT and S_ADDR_PER
      AND T10.ROW_ID                  = T6.PR_DEPT_OU_ID                        -- S_ORG_EXT and S_CONTACT
      AND T24.PAR_ROW_ID  (+)         = T10.ROW_ID                              -- S_ORG_EXT and S_ORG_EXT_X (for Household)

AND   SUBSTR(T8.NAME,32,5) = CAMPAIGN_TYPE          --PC_CAMPAIGN_TYPE      
AND   SUBSTR(T8.NAME,21,4) = CUSTOMER_CODE          --PARENT_VENDOR
AND   SUBSTR(T8.NAME,26,4) = PARENT_PRODUCT_CODE    --PARENT_PRODUCT
AND   T24.ATTRIB_21 = CHILDREN_NUM
AND   T5.ZIPCODE   >= MIN_ACCOUNT_ZIP_CODE
AND   T5.ZIPCODE   <= MAX_ACCOUNT_ZIP_CODE
AND   T5.STATE     >= MIN_ACCOUNT_PROV_CODE
AND   T5.STATE     <= MAX_ACCOUNT_PROV_CODE
and   T1.BIRTH_DT  >= MIN_CONTACT_BIRTH_DATE
AND   T1.BIRTH_DT  <= MAX_CONTACT_BIRTH_DATE
;
COMMIT;

EXECUTE IMMEDIATE 'TRUNCATE TABLE SIEBEL_REPORTS.MOTHERS_PROD_USAGE_BY_HOSP_T2';
EXECUTE IMMEDIATE 'ALTER TABLE SIEBEL_REPORTS.MOTHERS_PROD_USAGE_BY_HOSP_T2 NOLOGGING';    -- Disable redo logging for table
SET TRANSACTION USE ROLLBACK SEGMENT SIEBEL_LARGE;

INSERT INTO SIEBEL_REPORTS.MOTHERS_PROD_USAGE_BY_HOSP_T2
SELECT
      -- Contact
      T8.PER_ID                         "CONTACT_ROW_ID",
      T4.MID_NAME                       "CONTACT_CODE",
      T4.FST_NAME                       "CONTACT_FST_NAME" ,
      T4.LAST_NAME                      "CONTACT_LAST_NAME",
      T4.BIRTH_DT                       "CONTACT_BIRTH_DATE",
      T4.SEX_MF                         "CONTACT_GENDER",
      -- Manager
      T4.CON_MANAGER_PER_ID             "MGR_ROW_ID",
      T1.FST_NAME                       "MGR_FST_NAME",
      T1.LAST_NAME                      "MGR_LAST_NAME",
      -- Related Accounts
      T8.ROW_ID                         "RECORD_ROW_ID",
      T8.CREATED                        "CREATED",
      T8.LAST_UPD                       "LAST_UPD",
      -- Store Details
      T8.MAIL_STOP                      "RELATION_TYPE",
      T8.OU_ID                          "ACCOUNT_ROW_ID",
      T7.NAME                           "ACCOUNT_NAME",
      T7.LOC                            "ACCOUNT_SITE",
      T5.ATTRIB_04                      "ACCOUNT_CODE",
      T7.OU_TYPE_CD                     "ACCOUNT_TYPE"
   FROM
       SIEBEL.S_CONTACT T1,
       SIEBEL.S_CONTACT T4,
       SIEBEL.S_ORG_EXT_X T5,
       SIEBEL.S_ORG_EXT T7,
       SIEBEL.S_PER_ORG_UNIT T8
   WHERE
          T8.OU_ID = T7.ROW_ID
      AND T8.OU_ID = T5.PAR_ROW_ID (+)
      AND T8.PER_ID = T4.ROW_ID
      AND T4.CON_MANAGER_PER_ID = T1.ROW_ID --(+)
      AND (T7.OU_TYPE_CD = 'Iioieiia?i')
      AND (T8.MAIL_STOP  = 'Aaii?ecea oa')
      AND T5.ATTRIB_04   = HOSPITAL_CODE
      AND T4.BIRTH_DT   >= MIN_CONTACT_BIRTH_DATE
      AND T4.BIRTH_DT   <= MAX_CONTACT_BIRTH_DATE
;
COMMIT;

-- DROP STATISTICS --
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_CONTACT DELETE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_CONTACT_X DELETE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_ADDR_ORG DELETE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_ADDR_PER DELETE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_CHRCTR DELETE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_CON_CHRCTR DELETE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_ORG_EXT DELETE STATISTICS';  
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_ORG_EXT_X DELETE STATISTICS';  
EXECUTE IMMEDIATE 'ANALYZE TABLE SIEBEL.S_PER_ORG_UNIT DELETE STATISTICS';
------------------------  

END;
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 34

Accepted Solution

by:
Mark Geerlings earned 25 total points
ID: 7190892
One reason for the difference is the DDL commands (analyze table, alter table, truncate table, etc.).  PL\SQL by default does not support these, so you have to use the "execute immediate" syntax to process them in a PL\SQL procedure.  That adds at least some overhead to the PL\SQL process that isn't needed in a plain SQL script running in SQL*Plus or another tool.

Another possible reason for the difference is the presence or absence of table statistics (assuming that your optimizer mode is not "RULE").  Since you are creating the statistics via dynamic PL\SQL in the procedure, they are not available when the procedure is compiled, so the access path chosen by the optimizer does not have the benefit of the statistics.  I'm not sure if Oracle stores this access path with the procedure when it compiles it or not, but if it does, that could explain widely different response times.

One other note, the outer joins in the where clause can be performance killers.  Avoid them if at all possible.  Sometimes a PL\SQL function that returns a value (or null) can be used instead of an outer join and give a huge performance improvement.  But this varies by statement, number of records in the tables, version of Oracle, size of the SGA, etc.

My recommendation?  Leave DDL statements (analyze table, alter table, truncate table, etc.) out of PL\SQL procedures if at all possible.
0
 
LVL 3

Assisted Solution

by:randyd
randyd earned 25 total points
ID: 7193873
first suggestion - dont do table analyze stuff here.
do them on some other schedule - or better yet do rule based optimizer.

second suggestion - try this as a WHERE clause, and look at the execution plan (explain plan)

WHERE
     T7.ATTRIB_37 not in ('?aea?','?nuou?i Eiaaneaoi?i')
 AND T8.NAME like 'Euaeeuo A?aioo%'
 AND SUBSTR(T8.NAME,32,5) = CAMPAIGN_TYPE
 AND SUBSTR(T8.NAME,21,4) = CUSTOMER_CODE
 AND SUBSTR(T8.NAME,26,4) = PARENT_PRODUCT_CODE
 AND T5.ZIPCODE   >= MIN_ACCOUNT_ZIP_CODE
 AND T5.ZIPCODE   <= MAX_ACCOUNT_ZIP_CODE
 AND T5.STATE     >= MIN_ACCOUNT_PROV_CODE
 AND T5.STATE     <= MAX_ACCOUNT_PROV_CODE
 AND T1.BIRTH_DT  >= MIN_CONTACT_BIRTH_DATE
 AND T1.BIRTH_DT  <= MAX_CONTACT_BIRTH_DATE
 AND T2.ATTRIB_37 = '?aea?'
 AND T24.PAR_ROW_ID (+) = T10.ROW_ID
 AND T6.PR_OU_ADDR_ID = T5.ROW_ID (+)
 AND T6.PR_PER_ADDR_ID = T23.ROW_ID (+)
 AND T24.ATTRIB_21 = CHILDREN_NUM
 AND T9.CHRCTR_ID = T8.ROW_ID
 AND T10.ROW_ID = T6.PR_DEPT_OU_ID                        
 AND T1.CON_MANAGER_PER_ID = T6.ROW_ID
 AND T6.ROW_ID = T7.PAR_ROW_ID
 AND T9.CONTACT_ID = T1.ROW_ID
 AND T1.ROW_ID = T2.PAR_ROW_ID

please just try this as a sql command and see if it is better than 10 minutes and post the results...

randy
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9426239
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in one week.  I would appreciate any comments by the experts that would help me in making a recommendation.

It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
http://www.experts-exchange.com/help/closing.jsp

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER

patelgokul
EE Cleanup Volunteer
0
 
LVL 3

Expert Comment

by:randyd
ID: 9427884
i think both mark and my suggestions are valid - possible solutions.

dont care about points though.

randy
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9449128
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
split  points between markgeer and randyd
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer
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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

758 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

23 Experts available now in Live!

Get 1:1 Help Now