Amigos - how to you tune this query? this is not pl/sql but dba job..
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Amigos - how to you tune this query? this is not pl/sql but dba job..

  1. #1
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510

    Amigos - how to you tune this query? this is not pl/sql but dba job..

    THIS IS WITH QUERY WITH SUBQUERY FACTORING.. ANY ADVICE APPRECIATED...


    Code:
    WITH Page_Info AS
      ( SELECT :page_number AS page_number , :page_limit AS page_limit 
          FROM Dual ) , 
         Selected_Products AS
      ( SELECT productCode,    productCode_Find__model   AS modelID ,
               productCode_Find__configCode              AS configCode
          FROM AppleYield_ProductCode_Find_V
         WHERE ProductCode NOT IN (:blackList)
           AND ProductCode     IN (:productCode)) ,
        ProductTask_Lines_W AS
      ( SELECT /*+ FULL(pt_MV) PARALLEL(pt_MV, 3) */
               pt_MV.startedTimestamp ,  pt_MV.buildLineID ,
               MIN(sp.productCode) AS productCode
          FROM INDIGO.productTask_MV pt_MV -- use old MV table for better performance, and to
                                           -- get today's options refreshed every hour
          JOIN Selected_Products sp
            ON sp.modelID          = pt_MV.modelID
         WHERE 1                = 1
           AND pt_MV.buildLineID IS NOT NULL
           AND pt_MV.startedTimestamp BETWEEN to_date(:startDate,'MM/DD/YYYY') AND 
                                              to_date(:endDate,'MM/DD/YYYY')
         GROUP BY pt_MV.startedTimestamp, pt_MV.buildLineID ),
        LINE_NAME AS
      ( SELECT *
          FROM ( SELECT DISTINCT l.name AS Line_ID, l.lineType
                   FROM ProductTask_Lines_W ptl_V
                   JOIN INDIGO.buildLine bl
                     ON bl.buildLineID = ptl_V.buildLineID
                   JOIN INDIGO.line l
                     ON l.lineID = bl.siteLineID )
                  WHERE 1=1
                  ORDER BY upper(Line_ID)),
         LINE_NAME_T AS
      ( SELECT line_id,  Row_Number() over(order by UPPER(line_id)) AS rownumber,
               COUNT(*) over () AS total
          FROM LINE_NAME )
    SELECT line_id, total
      FROM LINE_NAME_T, Page_Info
     WHERE rownumber   > (page_number-1) * page_limit
       AND NOT rownumber > page_number * page_limit;
    Last edited by gandolf989; 04-02-2014 at 03:27 PM.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,458
    1) Format your code using the "code" tags
    2) Post the explain plan
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    Quote Originally Posted by LKBrwn_DBA View Post
    1) Format your code using the "code" tags
    2) Post the explain plan

    This is the best formatting I could do.. can we focus on tuning..there is explain plan right now..

    with
    Page_Info as (
    Select age_number as page_number , age_limit as page_limit
    From Dual
    )
    , Selected_Products as ( select productCode , productCode_Find__model as modelID
    , productCode_Find__configCode as configCode
    from AppleYield_ProductCode_Find_V
    where ProductCode not in (:blackList)
    and ProductCode in (roductCode)
    )
    , ProductTask_Lines_W as (
    select /*+ FULL(pt_MV) PARALLEL(pt_MV, 3) */
    pt_MV.startedTimestamp , pt_MV.buildLineID, min(sp.productCode) as productCode
    from
    INDIGO.productTask_MV pt_MV -- use old MV table for better performance, and to
    -- get today's options refreshed every hour
    join Selected_Products sp on sp.modelID = pt_MV.modelID
    where 1=1
    and pt_MV.buildLineID is not null
    and pt_MV.startedTimestamp between to_date(:startDate,'MM/DD/YYYY')
    and to_date(:endDate,'MM/DD/YYYY')
    group by pt_MV.startedTimestamp , pt_MV.buildLineID
    ), LINE_NAME as (select * from
    ( select distinct l.name as Line_ID, l.lineType
    from
    ProductTask_Lines_W ptl_V
    join INDIGO.buildLine bl on bl.buildLineID = ptl_V.buildLineID
    join INDIGO.line l on l.lineID = bl.siteLineID
    )
    where 1=1
    order by upper(Line_ID)
    ), LINE_NAME_T as ( select line_id,
    Row_Number() over(order by UPPER(line_id)) as rownumber,
    Count(*) over () as total
    from LINE_NAME
    )
    select line_id, total
    from LINE_NAME_T , Page_Info
    where
    rownumber > (page_number-1) * page_limit
    and not rownumber > page_number * page_limit
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  4. #4
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    I formatted it and forum removed all the formatting...
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    I added the formatting for you. When you are adding a post you can select choose "go advanced" and use the hash # tag
    to tag your code. You should probably post the explain plan and show the indexes on the table including the primary key.
    this space intentionally left blank

Posting Permissions

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



Click Here to Expand Forum to Full Width