Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002

    Question Unanswered: SQL Query performace question


    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
    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 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)

    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.


  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2002
    Reading, UK
    1) What do you do in the procedure with the data retrieved? The reason I ask this is if your doing something like inserting into another table then elimanate the cursor as well and just do it with a straight sql statement

    2) To join the two queries you can do this. I may not have got the row_number() analytic quite right as I often get the syntax wrong .

    into x from
        --<as in the 2nd query>
        select a.*,b.*,c.*,d.*,
        row_number() over(partition by cable_num,pair_num ORDER BY d.db_dn_date DESC NULLS LAST) rn 
        from tab4 a,
        tab7 d,
        (   --<as in the 1st query without the order by clause>...
            select distinct cable_num,pair_num
            from ... 
        ) e
        where ...
        AND c.db_dca = e.Cable_Num 
        AND c.db_dpair = e.Pair_Num  
    ) where rn=1
    3) remove the hint unless you do get a speed up. Also you should ensure your tables are analyzed.

    4) If some of the tables are remote you may want to force execution to the remote db (there are hints to do this or maybe creating a pipelined function at the remote db or a view with a no_merge hint).


  4. #4
    Join Date
    May 2002
    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.


  5. #5
    Join Date
    Mar 2002
    Reading, UK
    Are you comparing the timings to run the sql (and note go to the last row as sometimes Oracle can return the first row very quickly but take much longer to get to the last row) to the timings of you cursor loop? If so it might not be comparable especially if your procedure is doing other things.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts