Home » RDBMS Server » Performance Tuning » SQL Tuning...? (Oracle 10G)
SQL Tuning...? [message #572582] Thu, 13 December 2012 07:26 Go to next message
gblackiv
Messages: 15
Registered: August 2012
Location: Springfield VA
Junior Member
I have a select statement within a procedure that seems, to me, less than optimal.

I have never delved into performance, so I don't know if this is an issue or not... but I am running a procedure that is taking a very long time and am trying to minimize possible performance issues. The procedure below is being called many times within a larger proc and I was wondering if this might be the culprit.

Unfortunately I don't know how (or even if) this code can (or should) be modified to perform faster... I was hoping someone on this site can take a look and give me some pointers... or at least tell me if modifying it would be a waste of time.

Thanks in advance for any help...

Here's the code:
CREATE OR REPLACE PROCEDURE GBLACK.S0730_REF_CURSOR(v_INT IN NUMBER,
                                                        v_STOREROOM IN VARCHAR2, 
                                                      v_NSN_STATUS_TYPE IN VARCHAR2,
                                                      v_REFCURSOR OUT SYS_REFCURSOR)
IS
BEGIN
  
  IF v_INT = 4 THEN
  
      OPEN v_REFCURSOR FOR
       
         SELECT S.STRATIFICATION_ID
          
        FROM GBLACK.S0550_STRAT_STOREROOM_HOLDER S  
        JOIN (SELECT DISTINCT NSN_A NSN, STOREROOM 
                FROM A0100_SUB_LIST  
                WHERE NSN_B IN (SELECT DISTINCT NSN 
                                FROM GBLACK.P0525_STOREROOM_HOLDER 
                                WHERE NSN_STATUS_TYPE = v_NSN_STATUS_TYPE 
                                AND STOREROOM = v_STOREROOM 
                                AND STRATIFIED_AS_SUBSTITUTE = 'P') 
                AND STOREROOM = v_STOREROOM) A
        ON (S.STOREROOM = A.STOREROOM AND S.NSN = A.NSN)
        ORDER BY STRATIFICATION_ID;
         
  ELSE
  
    OPEN v_REFCURSOR FOR
         SELECT S.STRATIFICATION_ID 
        FROM GBLACK.S0550_STRAT_STOREROOM_HOLDER S
        JOIN (SELECT DISTINCT NSN, STOREROOM, STRATIFIED_AS_SUBSTITUTE 
              FROM GBLACK.P0525_STOREROOM_HOLDER 
              WHERE NSN_STATUS_TYPE = v_NSN_STATUS_TYPE 
              AND STOREROOM = v_STOREROOM) P 
        ON (S.STOREROOM = P.STOREROOM AND S.NSN = P.NSN)
        WHERE STRATIFIED_AS_SUBSTITUTE = 'P';
        
             
  END IF; 
          
END S0730_REF_CURSOR;
/
Re: SQL Tuning...? [message #572583 is a reply to message #572582] Thu, 13 December 2012 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

You can read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: SQL Tuning...? [message #572584 is a reply to message #572582] Thu, 13 December 2012 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have a select statement within a procedure that seems, to me, less than optimal.


What make you feel that?
At first sight they seem to me well written but I have not all the information.

Regards
Michel
Re: SQL Tuning...? [message #572586 is a reply to message #572584] Thu, 13 December 2012 07:38 Go to previous messageGo to next message
cookiemonster
Messages: 11208
Registered: September 2008
Location: Rainy Manchester
Senior Member
What you probably need to look at is why that procedure is being called multiple times.
Re: SQL Tuning...? [message #572587 is a reply to message #572584] Thu, 13 December 2012 07:40 Go to previous messageGo to next message
gblackiv
Messages: 15
Registered: August 2012
Location: Springfield VA
Junior Member
Well I was reading somehwere that you should use joins as much as possible vs. subqueries... but I didn't know how to make that happen with these select statements because they us "Select Distinct"... so that's why I wasn't sure if they could be written more efficiently... Honestly, I don't know... that's why I am asking.

I am trying to stratify data into a table (basically fill some buckets with inventory data based in another table). The issue is that I modify both tables as I push data from one to the other. One table is 500K records and the other is 130K. I haven't been able to run the whole process, but when I run a section and extrapolate, I estimate the whole process would take close to 23 hours to complete... which is way too long. I doubt the DBAs will allow it. So I am trying to look at sections of my code which I may be able to speed up. As such, I posted this.

Re: SQL Tuning...? [message #572589 is a reply to message #572587] Thu, 13 December 2012 07:53 Go to previous messageGo to next message
gblackiv
Messages: 15
Registered: August 2012
Location: Springfield VA
Junior Member
Quote:
What you probably need to look at is why that procedure is being called multiple times.


Because the tables I use are so large, I break the data down into subsets (based on the Storeroom input variable); after data manipulation these subsets are pushed into shell tables which will (when the process is finished) comprize the two final tables. This proc 4 times is run with each new storeroom based upon 4 different categories of data.

Sorry if this is too vague... it's tough to explain the entire process.

Anyway... if you feel this proc is close to optimal... I will investigate other possibilities.

Thanks,
Gary
Re: SQL Tuning...? [message #572599 is a reply to message #572589] Thu, 13 December 2012 10:41 Go to previous message
cookiemonster
Messages: 11208
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've got 130K records and 500K records and a process taking 23 hrs?
Ouch.

I don't know what your process is, or what hardware you are on, but I'd expect just about any process with those data volumes to take no more than an hour or so. Those data volumes are pretty small.
I suspect your problem is too much procedural code and lots of loops.
You want to do as much work as possible in a single sql statement.
So if you're reading data from one table and inserting it into another then in an ideal world that would be a single insert/select statement.

I'm guessing you've got lots of loops and single row insert/update/deletes.

The select at the top may be optimal (we really can't tell without additional information about your tables/system) but the fact that you're calling it many times probably isn't optimal.
You want to get all the data you need in as few select statements as possible.


One comment on the selects themselves - you never need to distinct an in sub-query.
So:
WHERE NSN_B IN (SELECT DISTINCT NSN 

Can be:
WHERE NSN_B IN (SELECT NSN 

Whether that makes any noticable difference I have no idea.
Previous Topic: Please help me on tune this query
Next Topic: How to run a query with an different plan_hash value then in GV$SQL_PLAN
Goto Forum:
  


Current Time: Fri Nov 28 17:02:30 CST 2014

Total time taken to generate the page: 0.09490 seconds