Loading ...
Sorry, an error occurred while loading the content.

Re: PeopleSoft DBA Forum Query Returning Incomplete Results

Expand Messages
  • James De Atley
    It s not.  Andy already mentioned PeopleSoft row level security. That s where I would start looking. Using PeopleSoft you must jump through this security
    Message 1 of 12 , May 10, 2012
    • 0 Attachment
      It's not.  Andy already mentioned PeopleSoft row level security.
      That's where I would start looking.
      Using PeopleSoft you must jump through this security hoop,
      where as straight SQL in toad or SqlPlus does not.
       
      __________________________________________________________
      James C. De Atley


      Mobile: (912) 253-0419
      From: "rsalido@..." <rsalido@...>
      To: psftdba@yahoogroups.com
      Sent: Thursday, May 10, 2012 1:43 PM
      Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
       
      Here it is but I dont think the SQL is the issue. The SQL runs but the results sets are different depending on the tool used to submit it.
       
      Thanks!
       
      SELECT DISTINCT A.INV_ITEM_ID, A.DESCR254_MIXED, A.RECEIVE_UOM, A.CONVERSION_RATE, ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE), A.QTY_SH_ACCPT_SUOM, D.PRICE_PO, ( D.PRICE_PO /  A.CONVERSION_RATE), ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO), C.NAME1, B.VENDOR_ID, A.ITM_ID_VNDR, A.MFG_ID, A.MFG_ITM_ID, TO_CHAR(E.PO_DT,'YYYY-MM-DD'), A.PO_ID, D.LINE_NBR, B.RECEIVER_ID, TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'), G.ACCOUNT, G.DEPTID, F.CATEGORY_ID, F.CATEGORY_CD, G.LOCATION, H.BUSINESS_UNIT, H.PO_ID, H.COMMENT_CD, H.COMMENT_TYPE, H.COMMENT1,C.SETID,C.VENDOR_ID
        FROM PS_RECV_LN_SHIP A, PS_RECV_HDR B, PS_VENDOR C, PS_PO_LINE_SHIP D, PS_PO_HDR E, PS_ITM_CAT_TBL F, PS_PO_LINE_DISTRIB G, PS_TMC_PO_COMMENTS H
        WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT
           AND A.RECEIVER_ID = B.RECEIVER_ID
           AND C.VENDOR_ID = B.VENDOR_ID
           AND B.BUSINESS_UNIT = 'TMC01'
           AND A.BUSINESS_UNIT = D.BUSINESS_UNIT
           AND A.PO_ID = D.PO_ID
           AND A.LINE_NBR = D.LINE_NBR
           AND A.SCHED_NBR = D.SCHED_NBR
           AND A.RECV_SHIP_STATUS <> 'X'
           AND D.CANCEL_STATUS <> 'X'
           AND B.RECEIPT_DT BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD')
           AND A.BUSINESS_UNIT = E.BUSINESS_UNIT
           AND A.PO_ID = E.PO_ID
           AND F.CATEGORY_ID = A.CATEGORY_ID
           AND F.EFFDT =
              (SELECT MAX(F_ED.EFFDT) FROM PS_ITM_CAT_TBL F_ED
              WHERE F.SETID = F_ED.SETID
                AND F.CATEGORY_TYPE = F_ED.CATEGORY_TYPE
                AND F.CATEGORY_CD = F_ED.CATEGORY_CD
                AND F.CATEGORY_ID = F_ED.CATEGORY_ID
                AND F_ED.EFFDT <= SYSDATE)
           AND A.BUSINESS_UNIT = G.BUSINESS_UNIT
           AND A.PO_ID = G.PO_ID
           AND D.LINE_NBR = G.LINE_NBR)
           AND E.BUSINESS_UNIT = H.BUSINESS_UNIT
           AND E.PO_ID = H.PO_ID
           AND H.COMMENT_CD IN ('RPT','CNT')
        GROUP BY  A.INV_ITEM_ID,  A.DESCR254_MIXED,  A.RECEIVE_UOM,  A.CONVERSION_RATE,  A.QTY_SH_ACCPT_SUOM,  D.PRICE_PO,  ( D.PRICE_PO /  A.CONVERSION_RATE),  ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO),  C.NAME1,  B.VENDOR_ID,  A.ITM_ID_VNDR,  A.MFG_ID,  A.MFG_ITM_ID,  TO_CHAR(E.PO_DT,'YYYY-MM-DD'),  A.PO_ID,  D.LINE_NBR,  B.RECEIVER_ID,  TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'),  G.ACCOUNT,  G.DEPTID,  F.CATEGORY_ID,  F.CATEGORY_CD,  G.LOCATION,  H.BUSINESS_UNIT,  H.PO_ID,  H.COMMENT_CD,  H.COMMENT_TYPE,  H.COMMENT1,C.SETID,C.VENDOR_ID
        ORDER BY 11, 15, 16, 17;
      Follow me on twitter: @rsalido --- On Thu, 5/10/12, Ashish Bhatt <ashish_bhatt@...> wrote:

      From: Ashish Bhatt <ashish_bhatt@...>
      Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
      To: psftdba@yahoogroups.com, rsalido@...
      Date: Thursday, May 10, 2012, 11:25 AM

       
      Please copy paste the SQL in the mail.
      Thanks in advance.
      To: psftdba@yahoogroups.com From: rsalido@... Date: Thu, 10 May 2012 10:24:14 -0700 Subject: PeopleSoft DBA Forum Query Returning Incomplete Results 
      Hi,
         We have a situation with a peopleSoft Query. If we run such query from Query (PeopleSoft's product), we get one row. But, if we grab the SQL and run it from TOAD, we get 1200+ rows. Same parameters and everything.
          Can you think of anything that could be affecting the number of rows Query is returning?
       
      Thank you very much,
      Roberto. Follow me on twitter: @rsalido
    • David Kurtz
      Is it possible that you have hit the maximum query size specified in the Application Server configuration? Note i) the default settings are
      Message 2 of 12 , May 10, 2012
      • 0 Attachment

        Is it possible that you have hit the maximum query size specified in the Application Server configuration?

         

        Note

        i)                    the default settings are different for PSAPPSRV and PSQRYSRV. 

        a.       If your users are running lots of ad-hoc queries then perhaps you should configure PSQRYSRVs

        ii)                  I am not suggesting that you should increase the maximum fetch size limit.  If your query is returning this much data I would question whether users should be allowed to run it on-line.  It might be better if the query was scheduled and was thus executed by an Application Engine process instead.

         

         

         

        [PSAPPSRV]

        ;=========================================================================

        ; Settings for PSAPPSRV

        ;=========================================================================

        ; Max Fetch Size -- max result set size in KB for a SELECT query

        ; Default is 5000KB. Use 0 for no limit.

        Max Fetch Size=5000

        [PSQRYSRV]

        ;=========================================================================

        ; Settings for PSQRYSRV

        ;=========================================================================

        ; Max Fetch Size -- max result set size in KB for a SELECT query

        ; Default is 10000KB. Use 0 for no limit.

        Max Fetch Size=10000

         

         

         

        From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of James De Atley
        Sent: 10 May 2012 18:47
        To: psftdba@yahoogroups.com
        Subject: Re: PeopleSoft DBA Forum Query Returning Incomplete Results

         




        It's not.  Andy already mentioned PeopleSoft row level security.

        That's where I would start looking.

        Using PeopleSoft you must jump through this security hoop,

        where as straight SQL in toad or SqlPlus does not.

         

        __________________________________________________________
        James C. De Atley

         

        Mobile: (912) 253-0419

        From: "rsalido@..." <rsalido@...>
        To: psftdba@yahoogroups.com
        Sent: Thursday, May 10, 2012 1:43 PM
        Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results

         

        Here it is but I dont think the SQL is the issue. The SQL runs but the results sets are different depending on the tool used to submit it.

         

        Thanks!

         

        SELECT DISTINCT A.INV_ITEM_ID, A.DESCR254_MIXED, A.RECEIVE_UOM, A.CONVERSION_RATE, ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE), A.QTY_SH_ACCPT_SUOM, D.PRICE_PO, ( D.PRICE_PO /  A.CONVERSION_RATE), ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO), C.NAME1, B.VENDOR_ID, A.ITM_ID_VNDR, A.MFG_ID, A.MFG_ITM_ID, TO_CHAR(E.PO_DT,'YYYY-MM-DD'), A.PO_ID, D.LINE_NBR, B.RECEIVER_ID, TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'), G.ACCOUNT, G.DEPTID, F.CATEGORY_ID, F.CATEGORY_CD, G.LOCATION, H.BUSINESS_UNIT, H.PO_ID, H.COMMENT_CD, H.COMMENT_TYPE, H.COMMENT1,C.SETID,C.VENDOR_ID

          FROM PS_RECV_LN_SHIP A, PS_RECV_HDR B, PS_VENDOR C, PS_PO_LINE_SHIP D, PS_PO_HDR E, PS_ITM_CAT_TBL F, PS_PO_LINE_DISTRIB G, PS_TMC_PO_COMMENTS H

          WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT

             AND A.RECEIVER_ID = B.RECEIVER_ID

             AND C.VENDOR_ID = B.VENDOR_ID

             AND B.BUSINESS_UNIT = 'TMC01'

             AND A.BUSINESS_UNIT = D.BUSINESS_UNIT

             AND A.PO_ID = D.PO_ID

             AND A.LINE_NBR = D.LINE_NBR

             AND A.SCHED_NBR = D.SCHED_NBR

             AND A.RECV_SHIP_STATUS <> 'X'

             AND D.CANCEL_STATUS <> 'X'

             AND B.RECEIPT_DT BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD')

             AND A.BUSINESS_UNIT = E.BUSINESS_UNIT

             AND A.PO_ID = E.PO_ID

             AND F.CATEGORY_ID = A.CATEGORY_ID

             AND F.EFFDT =

                (SELECT MAX(F_ED.EFFDT) FROM PS_ITM_CAT_TBL F_ED

                WHERE F.SETID = F_ED.SETID

                  AND F.CATEGORY_TYPE = F_ED.CATEGORY_TYPE

                  AND F.CATEGORY_CD = F_ED.CATEGORY_CD

                  AND F.CATEGORY_ID = F_ED.CATEGORY_ID

                  AND F_ED.EFFDT <= SYSDATE)

             AND A.BUSINESS_UNIT = G.BUSINESS_UNIT

             AND A.PO_ID = G.PO_ID

             AND D.LINE_NBR = G.LINE_NBR)

             AND E.BUSINESS_UNIT = H.BUSINESS_UNIT

             AND E.PO_ID = H.PO_ID

             AND H.COMMENT_CD IN ('RPT','CNT')

          GROUP BY  A.INV_ITEM_ID,  A.DESCR254_MIXED,  A.RECEIVE_UOM,  A.CONVERSION_RATE,  A.QTY_SH_ACCPT_SUOM,  D.PRICE_PO,  ( D.PRICE_PO /  A.CONVERSION_RATE),  ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO),  C.NAME1,  B.VENDOR_ID,  A.ITM_ID_VNDR,  A.MFG_ID,  A.MFG_ITM_ID,  TO_CHAR(E.PO_DT,'YYYY-MM-DD'),  A.PO_ID,  D.LINE_NBR,  B.RECEIVER_ID,  TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'),  G.ACCOUNT,  G.DEPTID,  F.CATEGORY_ID,  F.CATEGORY_CD,  G.LOCATION,  H.BUSINESS_UNIT,  H.PO_ID,  H.COMMENT_CD,  H.COMMENT_TYPE,  H.COMMENT1,C.SETID,C.VENDOR_ID

          ORDER BY 11, 15, 16, 17;

        Follow me on twitter: @rsalido--- On Thu, 5/10/12, Ashish Bhatt <ashish_bhatt@...> wrote:


        From: Ashish Bhatt <ashish_bhatt@...>
        Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
        To: psftdba@yahoogroups.com, rsalido@...
        Date: Thursday, May 10, 2012, 11:25 AM

         

        Please copy paste the SQL in the mail.

        Thanks in advance.

        To: psftdba@yahoogroups.comFrom: rsalido@...: Thu, 10 May 2012 10:24:14 -0700Subject: PeopleSoft DBA Forum Query Returning Incomplete Results 

        Hi,

           We have a situation with a peopleSoft Query. If we run such query from Query (PeopleSoft's product), we get one row. But, if we grab the SQL and run it from TOAD, we get 1200+ rows. Same parameters and everything.

            Can you think of anything that could be affecting the number of rows Query is returning?

         

        Thank you very much,

        Roberto.Follow me on twitter: @rsalido




      • rsalido@yahoo.com
        This is a really good thought. However, I just ran into a new issue: TOAD is returning diffrent result sets.   MOST of the time I get 1200 rows back but there
        Message 3 of 12 , May 10, 2012
        • 0 Attachment
          This is a really good thought. However, I just ran into a new issue: TOAD is returning diffrent result sets.
           
          MOST of the time I get 1200 rows back but there are instances where I get just one.
           
          So, it is not the tools, nor peoplesoft. It seems Oracle is not returning consistent datasets.
           
          As you can see from the query, this is PO information (from Production) so it is not changing that much. I mean, I would expect a variance of a few rows, but not one row one time and 1200+ the next.
           
           We are scratching our heads.

          Follow me on twitter: @rsalido

          --- On Thu, 5/10/12, David Kurtz <david.kurtz@...> wrote:

          From: David Kurtz <david.kurtz@...>
          Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
          To: psftdba@yahoogroups.com
          Date: Thursday, May 10, 2012, 11:55 AM

           

          Is it possible that you have hit the maximum query size specified in the Application Server configuration?

           

          Note

          i)                    the default settings are different for PSAPPSRV and PSQRYSRV. 

          a.       If your users are running lots of ad-hoc queries then perhaps you should configure PSQRYSRVs

          ii)                  I am not suggesting that you should increase the maximum fetch size limit.  If your query is returning this much data I would question whether users should be allowed to run it on-line.  It might be better if the query was scheduled and was thus executed by an Application Engine process instead.

           

           

           

          [PSAPPSRV]

          ;=========================================================================

          ; Settings for PSAPPSRV

          ;=========================================================================

          ; Max Fetch Size -- max result set size in KB for a SELECT query

          ; Default is 5000KB. Use 0 for no limit.

          Max Fetch Size=5000

          [PSQRYSRV]

          ;=========================================================================

          ; Settings for PSQRYSRV

          ;=========================================================================

          ; Max Fetch Size -- max result set size in KB for a SELECT query

          ; Default is 10000KB. Use 0 for no limit.

          Max Fetch Size=10000

           

           

           

          From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of James De Atley
          Sent: 10 May 2012 18:47
          To: psftdba@yahoogroups.com
          Subject: Re: PeopleSoft DBA Forum Query Returning Incomplete Results

           




          It's not.  Andy already mentioned PeopleSoft row level security.

          That's where I would start looking.

          Using PeopleSoft you must jump through this security hoop,

          where as straight SQL in toad or SqlPlus does not.

           

          __________________________________________________________
          James C. De Atley

           

          Mobile: (912) 253-0419

          From: "rsalido@..." <rsalido@...>
          To: psftdba@yahoogroups.com
          Sent: Thursday, May 10, 2012 1:43 PM
          Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results

           

           

          Thanks!

           

          SELECT DISTINCT A.INV_ITEM_ID, A.DESCR254_MIXED, A.RECEIVE_UOM, A.CONVERSION_RATE, ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE), A.QTY_SH_ACCPT_SUOM, D.PRICE_PO, ( D.PRICE_PO /  A.CONVERSION_RATE), ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO), C.NAME1, B.VENDOR_ID, A.ITM_ID_VNDR, A.MFG_ID, A.MFG_ITM_ID, TO_CHAR(E.PO_DT,'YYYY-MM-DD'), A.PO_ID, D.LINE_NBR, B.RECEIVER_ID, TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'), G.ACCOUNT, G.DEPTID, F.CATEGORY_ID, F.CATEGORY_CD, G.LOCATION, H.BUSINESS_UNIT, H.PO_ID, H.COMMENT_CD, H.COMMENT_TYPE, H.COMMENT1,C.SETID,C.VENDOR_ID

            FROM PS_RECV_LN_SHIP A, PS_RECV_HDR B, PS_VENDOR C, PS_PO_LINE_SHIP D, PS_PO_HDR E, PS_ITM_CAT_TBL F, PS_PO_LINE_DISTRIB G, PS_TMC_PO_COMMENTS H

            WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT

               AND A.RECEIVER_ID = B.RECEIVER_ID

               AND C.VENDOR_ID = B.VENDOR_ID

               AND B.BUSINESS_UNIT = 'TMC01'

               AND A.BUSINESS_UNIT = D.BUSINESS_UNIT

               AND A.PO_ID = D.PO_ID

               AND A.LINE_NBR = D.LINE_NBR

               AND A.SCHED_NBR = D.SCHED_NBR

               AND A.RECV_SHIP_STATUS <> 'X'

               AND D.CANCEL_STATUS <> 'X'

               AND B.RECEIPT_DT BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD')

               AND A.BUSINESS_UNIT = E.BUSINESS_UNIT

               AND A.PO_ID = E.PO_ID

               AND F.CATEGORY_ID = A.CATEGORY_ID

               AND F.EFFDT =

                  (SELECT MAX(F_ED.EFFDT) FROM PS_ITM_CAT_TBL F_ED

                  WHERE F.SETID = F_ED.SETID

                    AND F.CATEGORY_TYPE = F_ED.CATEGORY_TYPE

                    AND F.CATEGORY_CD = F_ED.CATEGORY_CD

                    AND F.CATEGORY_ID = F_ED.CATEGORY_ID

                    AND F_ED.EFFDT <= SYSDATE)

               AND A.BUSINESS_UNIT = G.BUSINESS_UNIT

               AND A.PO_ID = G.PO_ID

               AND D.LINE_NBR = G.LINE_NBR)

               AND E.BUSINESS_UNIT = H.BUSINESS_UNIT

               AND E.PO_ID = H.PO_ID

               AND H.COMMENT_CD IN ('RPT','CNT')

            GROUP BY  A.INV_ITEM_ID,  A.DESCR254_MIXED,  A.RECEIVE_UOM,  A.CONVERSION_RATE,  A.QTY_SH_ACCPT_SUOM,  D.PRICE_PO,  ( D.PRICE_PO /  A.CONVERSION_RATE),  ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO),  C.NAME1,  B.VENDOR_ID,  A.ITM_ID_VNDR,  A.MFG_ID,  A.MFG_ITM_ID,  TO_CHAR(E.PO_DT,'YYYY-MM-DD'),  A.PO_ID,  D.LINE_NBR,  B.RECEIVER_ID,  TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'),  G.ACCOUNT,  G.DEPTID,  F.CATEGORY_ID,  F.CATEGORY_CD,  G.LOCATION,  H.BUSINESS_UNIT,  H.PO_ID,  H.COMMENT_CD,  H.COMMENT_TYPE,  H.COMMENT1,C.SETID,C.VENDOR_ID

            ORDER BY 11, 15, 16, 17;

          Follow me on twitter: @rsalido--- On Thu, 5/10/12, Ashish Bhatt <ashish_bhatt@...> wrote:


          From: Ashish Bhatt <ashish_bhatt@...>
          Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
          To: psftdba@yahoogroups.com, rsalido@...
          Date: Thursday, May 10, 2012, 11:25 AM

           

          Please copy paste the SQL in the mail.

          Thanks in advance.

          To: psftdba@yahoogroups.comFrom: rsalido@...: Thu, 10 May 2012 10:24:14 -0700Subject: PeopleSoft DBA Forum Query Returning Incomplete Results 

          Here it is but I dont think the SQL is the issue. The SQL runs but the results sets are different depending on the tool used to submit it.

             We have a situation with a peopleSoft Query. If we run such query from Query (PeopleSoft's product), we get one row. But, if we grab the SQL and run it from TOAD, we get 1200+ rows. Same parameters and everything.

              Can you think of anything that could be affecting the number of rows Query is returning?

           

          Thank you very much,

          Roberto.Follow me on twitter: @rsalido

          Hi,




        • margery brauner
          did you generate a trace from query? Compare the sql being executed by query to that you are executing in Toad.  There was also an issue  pt 8.51 issue pre
          Message 4 of 12 , May 10, 2012
          • 0 Attachment
            did you generate a trace from query? Compare the sql being executed by query to that you are executing in Toad.  There was also an issue  pt 8.51 issue pre 8.51.11 where the sql being generated by query was faulty. It was fixed in 8.51.11.

            --- On Thu, 5/10/12, rsalido@... <rsalido@...> wrote:

            From: rsalido@... <rsalido@...>
            Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
            To: psftdba@yahoogroups.com
            Date: Thursday, May 10, 2012, 2:24 PM

             

            i)                    the default settings are different for PSAPPSRV and PSQRYSRV. 

            a.       If your users are running lots of ad-hoc queries then perhaps you should configure PSQRYSRVs

            ii)                  I am not suggesting that you should increase the maximum fetch size limit.  If your query is returning this much data I would question whether users should be allowed to run it on-line.  It might be better if the query was scheduled and was thus executed by an Application Engine process instead.

             

             

             

            [PSAPPSRV]

            ;=========================================================================

            ; Settings for PSAPPSRV

            ;=========================================================================

            ; Max Fetch Size -- max result set size in KB for a SELECT query

            ; Default is 5000KB. Use 0 for no limit.

            Max Fetch Size=5000

            [PSQRYSRV]

            ;=========================================================================

            ; Settings for PSQRYSRV

            ;=========================================================================

            ; Max Fetch Size -- max result set size in KB for a SELECT query

            ; Default is 10000KB. Use 0 for no limit.

            Max Fetch Size=10000

             

             

             

            From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of James De Atley
            Sent: 10 May 2012 18:47
            To: psftdba@yahoogroups.com
            Subject: Re: PeopleSoft DBA Forum Query Returning Incomplete Results

             




            It's not.  Andy already mentioned PeopleSoft row level security.

            That's where I would start looking.

            Using PeopleSoft you must jump through this security hoop,

            where as straight SQL in toad or SqlPlus does not.

             

            __________________________________________________________
            James C. De Atley

             

            Mobile: (912) 253-0419

            From: "rsalido@..." <rsalido@...>
            To: psftdba@yahoogroups.com
            Sent: Thursday, May 10, 2012 1:43 PM
            Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results

             

            This is a really good thought. However, I just ran into a new issue: TOAD is returning diffrent result sets.
             
            MOST of the time I get 1200 rows back but there are instances where I get just one.
             
            So, it is not the tools, nor peoplesoft. It seems Oracle is not returning consistent datasets.
             
            As you can see from the query, this is PO information (from Production) so it is not changing that much. I mean, I would expect a variance of a few rows, but not one row one time and 1200+ the next.
             
             We are scratching our heads.

            Follow me on twitter: @rsalido

            --- On Thu, 5/10/12, David Kurtz <david.kurtz@...> wrote:

            From: David Kurtz <david.kurtz@...>
            Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
            To: psftdba@yahoogroups.com
            Date: Thursday, May 10, 2012, 11:55 AM

             

            Is it possible that you have hit the maximum query size specified in the Application Server configuration?

             

            Note

             

            Thanks!

             

            SELECT DISTINCT A.INV_ITEM_ID, A.DESCR254_MIXED, A.RECEIVE_UOM, A.CONVERSION_RATE, ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE), A.QTY_SH_ACCPT_SUOM, D.PRICE_PO, ( D.PRICE_PO /  A.CONVERSION_RATE), ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO), C.NAME1, B.VENDOR_ID, A.ITM_ID_VNDR, A.MFG_ID, A.MFG_ITM_ID, TO_CHAR(E.PO_DT,'YYYY-MM-DD'), A.PO_ID, D.LINE_NBR, B.RECEIVER_ID, TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'), G.ACCOUNT, G.DEPTID, F.CATEGORY_ID, F.CATEGORY_CD, G.LOCATION, H.BUSINESS_UNIT, H.PO_ID, H.COMMENT_CD, H.COMMENT_TYPE, H.COMMENT1,C.SETID,C.VENDOR_ID

              FROM PS_RECV_LN_SHIP A, PS_RECV_HDR B, PS_VENDOR C, PS_PO_LINE_SHIP D, PS_PO_HDR E, PS_ITM_CAT_TBL F, PS_PO_LINE_DISTRIB G, PS_TMC_PO_COMMENTS H

              WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT

                 AND A.RECEIVER_ID = B.RECEIVER_ID

                 AND C.VENDOR_ID = B.VENDOR_ID

                 AND B.BUSINESS_UNIT = 'TMC01'

                 AND A.BUSINESS_UNIT = D.BUSINESS_UNIT

                 AND A.PO_ID = D.PO_ID

                 AND A.LINE_NBR = D.LINE_NBR

                 AND A.SCHED_NBR = D.SCHED_NBR

                 AND A.RECV_SHIP_STATUS <> 'X'

                 AND D.CANCEL_STATUS <> 'X'

                 AND B.RECEIPT_DT BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD')

                 AND A.BUSINESS_UNIT = E.BUSINESS_UNIT

                 AND A.PO_ID = E.PO_ID

                 AND F.CATEGORY_ID = A.CATEGORY_ID

                 AND F.EFFDT =

                    (SELECT MAX(F_ED.EFFDT) FROM PS_ITM_CAT_TBL F_ED

                    WHERE F.SETID = F_ED.SETID

                      AND F.CATEGORY_TYPE = F_ED.CATEGORY_TYPE

                      AND F.CATEGORY_CD = F_ED.CATEGORY_CD

                      AND F.CATEGORY_ID = F_ED.CATEGORY_ID

                      AND F_ED.EFFDT <= SYSDATE)

                 AND A.BUSINESS_UNIT = G.BUSINESS_UNIT

                 AND A.PO_ID = G.PO_ID

                 AND D.LINE_NBR = G.LINE_NBR)

                 AND E.BUSINESS_UNIT = H.BUSINESS_UNIT

                 AND E.PO_ID = H.PO_ID

                 AND H.COMMENT_CD IN ('RPT','CNT')

              GROUP BY  A.INV_ITEM_ID,  A.DESCR254_MIXED,  A.RECEIVE_UOM,  A.CONVERSION_RATE,  A.QTY_SH_ACCPT_SUOM,  D.PRICE_PO,  ( D.PRICE_PO /  A.CONVERSION_RATE),  ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO),  C.NAME1,  B.VENDOR_ID,  A.ITM_ID_VNDR,  A.MFG_ID,  A.MFG_ITM_ID,  TO_CHAR(E.PO_DT,'YYYY-MM-DD'),  A.PO_ID,  D.LINE_NBR,  B.RECEIVER_ID,  TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'),  G.ACCOUNT,  G.DEPTID,  F.CATEGORY_ID,  F.CATEGORY_CD,  G.LOCATION,  H.BUSINESS_UNIT,  H.PO_ID,  H.COMMENT_CD,  H.COMMENT_TYPE,  H.COMMENT1,C.SETID,C.VENDOR_ID

              ORDER BY 11, 15, 16, 17;

            Follow me on twitter: @rsalido--- On Thu, 5/10/12, Ashish Bhatt <ashish_bhatt@...> wrote:


            From: Ashish Bhatt <ashish_bhatt@...>
            Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
            To: psftdba@yahoogroups.com, rsalido@...
            Date: Thursday, May 10, 2012, 11:25 AM

             

            Please copy paste the SQL in the mail.

            Thanks in advance.

            To: psftdba@yahoogroups.comFrom: rsalido@...: Thu, 10 May 2012 10:24:14 -0700Subject: PeopleSoft DBA Forum Query Returning Incomplete Results 

            Here it is but I dont think the SQL is the issue. The SQL runs but the results sets are different depending on the tool used to submit it.

               We have a situation with a peopleSoft Query. If we run such query from Query (PeopleSoft's product), we get one row. But, if we grab the SQL and run it from TOAD, we get 1200+ rows. Same parameters and everything.

                Can you think of anything that could be affecting the number of rows Query is returning?

             

            Thank you very much,

            Roberto.Follow me on twitter: @rsalido

            Hi,




          • rsalido@yahoo.com
            Just as a heads up to the group: We think this issue might be related to an Oracle bug specific to prefetching. Depending on what Oracle version you run, this
            Message 5 of 12 , May 10, 2012
            • 0 Attachment
              Just as a heads up to the group: We think this issue might be related to an Oracle bug specific to prefetching. Depending on what Oracle version you run, this isssue might be present on your system as well.
               
              "The "table prefetch" has a dynamic component that can kick in or out depending on the load on the buffer cache making it unpredictable so selects and DMLs can produce different results depending on the load on the server."

               

               

              Table Prefetching causes intermittent Wrong Results in 9iR2,10gR1 and 10gR2 [ID 406966.1]

                              Modified 23-APR-2012     Type BULLETIN     Status PUBLISHED         

               

              In this Document

                              Purpose

                              Scope

                              Details

                              Likelihood of Occurrence

                              Possible Symptoms

                              Diagnostics

                              Workaround or Resolution

                              Patches

                              References

               

              Applies to:

              Oracle Server - Enterprise Edition - Version 9.2.0.1 to 10.2.0.4 [Release 9.2 to 10.2]

              Information in this document applies to any platform.

              Confirmed in 9.2.0.7 , 10.2.0.1, 10.2.0.2, 10.2.0.3 and 10.2.0.4

              Applies to 9.2.X, 10.1.X, 10.2.0.1, 10.2.0.2, 10.2.0.3, 10.2.0.4

               

               

              Purpose

               

              This document is informational and intended for any user.

              Scope

               

              It is possible to get intermittent wrong results from a query which has the same execution plan for each execution due to a problem with the "table prefetch" feature (enabled by default from 9i onwards).

              Details

              Likelihood of Occurrence

               

              *** There is no positive way to know if prefetch is being used or not or how much is used. ***

              *** All bitmap plans are prefetch plans and the prefetch cannot be disabled on them. ***

               

              Any statement that present the unique arrangement of a table lookup by rowid on top of joins or other buffering row sources like Sorts, Bitmap Plans and some forms of Nested Loops can have the prefetch feature enabled, so it can be at risk.

               

              Here is an example of what to look for :

               

              Table prefetching is enabled on DESTINATION TABLE (Step 1) as the "TABLE ACCESS BY INDEX ROWID" gets the results of the "NESTED LOOPS" on Step 2.

              Notice the indentation of the Nested Loop that implies is done before the TABLE ACCESS and that the index I_DEST is in the probe of that Nested Loop.

              No other step in this execution plan shows prefetching.

               

                -----------------------------------------------------------------------------------------                              

               

               

               | Id  | Operation                       | Name                  | Rows  | Bytes | Cost  |                              

               

               

               -----------------------------------------------------------------------------------------                              

               

               

               |   0 | SELECT STATEMENT                |                       |   215 | 56115 | 27080 |                              

               

               

               |   1 |  TABLE ACCESS BY INDEX ROWID    | DESTINATION           |     2 |    68 |     9 |                              

               

               

               |   2 |   NESTED LOOPS                  |                       |   215 | 56115 | 27080 |                              

               

               

               |   3 |    NESTED LOOPS                 |                       |   105 | 23835 | 26134 |                              

               

               

               |   4 |     HASH JOIN                   |                       |   609 |   102K| 25466 |                              

               

               

               |   5 |      TABLE ACCESS FULL          | ONE_SOURCE            |  1114 |   126K|    10 |                              

               

               

               |   6 |      TABLE ACCESS BY INDEX ROWID| ANOTHER_SOURCE        |   151K|  8279K| 25455 |                              

               

               

               |   7 |       INDEX SKIP SCAN           | I_ANOTHER_SOURCE      |   910K|       |  1543 |                              

               

               

               |   8 |     TABLE ACCESS BY INDEX ROWID | YET_ANOTHER_SOURCE    |     1 |    55 |     3 |                              

               

               

               |   9 |      INDEX RANGE SCAN           | I_YA_SOURCE           |     2 |       |     1 |                              

               

               

               |  10 |    INDEX RANGE SCAN             | I_DEST                |     7 |       |     1 |                              

               

               

               -----------------------------------------------------------------------------------------     

               

              This format in the plan does not guarantee that table prefetching will happen. It just means that it is enabled.

              If prefetching is used some IO reads could be done with wait event 'db file parallel reads' but the feature can turn itself on and off so even when these wait events are not present is possible for the feature to be enabled.

              Possible Symptoms

               

              The "table prefetch" has a dynamic component that can kick in or out depending on the load on the buffer cache making it unpredictable so selects and DMLs can produce different results depending on the load on the server.

              This includes selects used in INSERTS, CTAS and MVIEWs.

               

                  Sometimes returns more rows, sometimes return less even in the same session on repeated executions.

                  Sometimes reproduces when using 2 different schema users and/or sessions accessing the same tables.

                  Sometimes reproduces only immediately after the instance has been recycled or the buffer cache flushed  and goes away after "warm up" and sometimes is completely the opposite scenario.

               

              Diagnostics

               

              To diagnose apply patchset 10.2.0.4 or patch 6044413 for earlier versions.

               

               

              *** All bitmap plans are prefetch plans and the prefetch cannot be disabled on them.  The only option is by applying the patches.

               

              If the issue is not solved, then set Event 10753 first to level 1 and then to level 2.

              With this event set, Level 1 forces the prefetch to happen and may (not "should" nor "must") get the wrong results consistently. If it does not give consistently wrong results, then it might not be this bug.

              With level 2, the wrong result may (not "should" nor "must") return right results.

              Note that this has performance implications so should only be used by Support to aid diagnosis.

               

              If the above cannot be done, then use the workaround.

              Workaround or Resolution

               

              Support encourages the customer to apply the patches listed below when possible because the patches include additional diagnostic information if they do not fix the issue and prefetch is still a suspect.

               

              *** All bitmap plans are prefetch plans and the prefetch cannot be disabled on them. The only option is by applying the patches.

               

              Setting _table_lookup_prefetch_size=0 or _table_lookup_prefetch_thresh to a very high value disables the table prefetch feature too and is a quicker way to evaluate if prefetch is the culprit.

              e.g.

              ALTER SYSTEM SET "_table_lookup_prefetch_size"=0 scope=spfile;

              ALTER SYSTEM SET "_table_lookup_prefetch_thresh" = 1000000 scope=spfile;

               

              They are static parameters, in order to set them the instance must be recycled.

              Disabling the feature has a performance impact but because of the dynamic component it is not possible to predict it's scale.

              Patches

               

              Each of the following solves a different aspect of the prefetch.

              The Patches are Listed in order of likelihood of occurrence:

              Note:6044413.8 Intermittent wrong results when table prefetch occurs

              Solves most of the generic issues and is needed to diagnose further

              if a prefetch bug is still suspected. Included in 10.2.0.4.

               

              bug 8366255 Intermittent wrong results with prefetch and projection pushdown

               

               

               

               

               

               

               

               

              bug 5403855 WRONG RESULT WITH SQL_TRACE = TRUE

              Specific when SQL_TRACE/10046 is turned on.

               

              bug 5068565 WRONG RESULTS WITH PREFETCH ENABLED (DEFAULT)

              Specific to the use of pipelined table functions

               

              References

              BUG:5403855 - WRONG RESULT WITH SQL_TRACE = TRUE

              BUG:8366255 - WRONG RESULT FROM DIFFERENT SESSIONS EVEN PLANS ARE SAME

              NOTE:5893779.8 - Bug 5893779 - Intermittent wrong results when table prefetch occurs - superceded fix

              NOTE:6044413.8 - Bug 6044413 - Intermittent wrong results when table prefetch occurs

              @ BUG:4148420 - QUERY PRODUCES DIFFERENT RESULTS WHEN INSERTING TO A TABLE

              BUG:5068565 - WRONG RESULTS WITH PREFECTH ENABLED (DEFAULT)

               

              Show Related Information Related

              Products

               

                  Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

               

              Keywords

              INTERMITTENT; TABLE PREFETCHING; WRONGRES; _TABLE_LOOKUP_PREFETCH_SIZE



              Follow me on twitter: @rsalido

              --- On Thu, 5/10/12, margery brauner <mbrauner2@...> wrote:

              From: margery brauner <mbrauner2@...>
              Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
              To: psftdba@yahoogroups.com
              Date: Thursday, May 10, 2012, 1:21 PM

               
              did you generate a trace from query? Compare the sql being executed by query to that you are executing in Toad.  There was also an issue  pt 8.51 issue pre 8.51.11 where the sql being generated by query was faulty. It was fixed in 8.51.11.

              --- On Thu, 5/10/12, rsalido@... <rsalido@...> wrote:

              From: rsalido@... <rsalido@...>
              Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
              To: psftdba@yahoogroups.com
              Date: Thursday, May 10, 2012, 2:24 PM

               

              Note

              i)                    the default settings are different for PSAPPSRV and PSQRYSRV. 

              a.       If your users are running lots of ad-hoc queries then perhaps you should configure PSQRYSRVs

              ii)                  I am not suggesting that you should increase the maximum fetch size limit.  If your query is returning this much data I would question whether users should be allowed to run it on-line.  It might be better if the query was scheduled and was thus executed by an Application Engine process instead.

               

               

               

              [PSAPPSRV]

              ;=========================================================================

              ; Settings for PSAPPSRV

              ;=========================================================================

              ; Max Fetch Size -- max result set size in KB for a SELECT query

              ; Default is 5000KB. Use 0 for no limit.

              Max Fetch Size=5000

              [PSQRYSRV]

              ;=========================================================================

              ; Settings for PSQRYSRV

              ;=========================================================================

              ; Max Fetch Size -- max result set size in KB for a SELECT query

              ; Default is 10000KB. Use 0 for no limit.

              Max Fetch Size=10000

               

               

               

              From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of James De Atley
              Sent: 10 May 2012 18:47
              To: psftdba@yahoogroups.com
              Subject: Re: PeopleSoft DBA Forum Query Returning Incomplete Results

               




              It's not.  Andy already mentioned PeopleSoft row level security.

              That's where I would start looking.

              Using PeopleSoft you must jump through this security hoop,

              where as straight SQL in toad or SqlPlus does not.

               

              __________________________________________________________
              James C. De Atley

               

              Mobile: (912) 253-0419

              From: "rsalido@..." <rsalido@...>
              To: psftdba@yahoogroups.com
              Sent: Thursday, May 10, 2012 1:43 PM
              Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results

               

              This is a really good thought. However, I just ran into a new issue: TOAD is returning diffrent result sets.
               
              MOST of the time I get 1200 rows back but there are instances where I get just one.
               
              So, it is not the tools, nor peoplesoft. It seems Oracle is not returning consistent datasets.
               
              As you can see from the query, this is PO information (from Production) so it is not changing that much. I mean, I would expect a variance of a few rows, but not one row one time and 1200+ the next.
               
               We are scratching our heads.

              Follow me on twitter: @rsalido

              --- On Thu, 5/10/12, David Kurtz <david.kurtz@...> wrote:

              From: David Kurtz <david.kurtz@...>
              Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
              To: psftdba@yahoogroups.com
              Date: Thursday, May 10, 2012, 11:55 AM

               

              Is it possible that you have hit the maximum query size specified in the Application Server configuration?

               

               

              Thanks!

               

              SELECT DISTINCT A.INV_ITEM_ID, A.DESCR254_MIXED, A.RECEIVE_UOM, A.CONVERSION_RATE, ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE), A.QTY_SH_ACCPT_SUOM, D.PRICE_PO, ( D.PRICE_PO /  A.CONVERSION_RATE), ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO), C.NAME1, B.VENDOR_ID, A.ITM_ID_VNDR, A.MFG_ID, A.MFG_ITM_ID, TO_CHAR(E.PO_DT,'YYYY-MM-DD'), A.PO_ID, D.LINE_NBR, B.RECEIVER_ID, TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'), G.ACCOUN

              (Message over 64 KB, truncated)

              Here it is but I dont think the SQL is the issue. The SQL runs but the results sets are different depending on the tool used to submit it.

            • the_dragon Draco
              I remember something like this from a couple of years ago - it required an Oracle patch, if I recall correctly. Could it be some kind of strange shared pool
              Message 6 of 12 , May 11, 2012
              • 0 Attachment
                I remember something like this from a couple of years ago - it required an Oracle patch, if I recall correctly.
                 
                Could it be some kind of strange shared pool buffering?
                 
                What results do you get if you use sql*plus?
                 
                peace,
                clark 'the dragon' willis
                 

                To: psftdba@yahoogroups.com
                From: rsalido@...
                Date: Thu, 10 May 2012 11:24:07 -0700
                Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results

                 
                This is a really good thought. However, I just ran into a new issue: TOAD is returning diffrent result sets.
                 
                MOST of the time I get 1200 rows back but there are instances where I get just one.
                 
                So, it is not the tools, nor peoplesoft. It seems Oracle is not returning consistent datasets.
                 
                As you can see from the query, this is PO information (from Production) so it is not changing that much. I mean, I would expect a variance of a few rows, but not one row one time and 1200+ the next.
                 
                 We are scratching our heads.

                Follow me on twitter: @rsalido

                --- On Thu, 5/10/12, David Kurtz <david.kurtz@...> wrote:

                From: David Kurtz <david.kurtz@...>
                Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
                To: psftdba@yahoogroups.com
                Date: Thursday, May 10, 2012, 11:55 AM

                 

                Is it possible that you have hit the maximum query size specified in the Application Server configuration?

                 

                Note

                i)                    the default settings are different for PSAPPSRV and PSQRYSRV. 

                a.       If your users are running lots of ad-hoc queries then perhaps you should configure PSQRYSRVs

                ii)                  I am not suggesting that you should increase the maximum fetch size limit.  If your query is returning this much data I would question whether users should be allowed to run it on-line.  It might be better if the query was scheduled and was thus executed by an Application Engine process instead.

                 

                 

                 

                [PSAPPSRV]

                ;=========================================================================

                ; Settings for PSAPPSRV

                ;=========================================================================

                ; Max Fetch Size -- max result set size in KB for a SELECT query

                ; Default is 5000KB. Use 0 for no limit.

                Max Fetch Size=5000

                [PSQRYSRV]

                ;=========================================================================

                ; Settings for PSQRYSRV

                ;=========================================================================

                ; Max Fetch Size -- max result set size in KB for a SELECT query

                ; Default is 10000KB. Use 0 for no limit.

                Max Fetch Size=10000

                 

                 

                 

                From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of James De Atley
                Sent: 10 May 2012 18:47
                To: psftdba@yahoogroups.com
                Subject: Re: PeopleSoft DBA Forum Query Returning Incomplete Results

                 




                It's not.  Andy already mentioned PeopleSoft row level security.

                That's where I would start looking.

                Using PeopleSoft you must jump through this security hoop,

                where as straight SQL in toad or SqlPlus does not.

                 

                __________________________________________________________
                James C. De Atley

                 

                Mobile: (912) 253-0419

                From: "rsalido@..." <rsalido@...>
                To: psftdba@yahoogroups.com
                Sent: Thursday, May 10, 2012 1:43 PM
                Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results

                 

                Here it is but I dont think the SQL is the issue. The SQL runs but the results sets are different depending on the tool used to submit it.

                 

                Thanks!

                 

                SELECT DISTINCT A.INV_ITEM_ID, A.DESCR254_MIXED, A.RECEIVE_UOM, A.CONVERSION_RATE, ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE), A.QTY_SH_ACCPT_SUOM, D.PRICE_PO, ( D.PRICE_PO /  A.CONVERSION_RATE), ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO), C.NAME1, B.VENDOR_ID, A.ITM_ID_VNDR, A.MFG_ID, A.MFG_ITM_ID, TO_CHAR(E.PO_DT,'YYYY-MM-DD'), A.PO_ID, D.LINE_NBR, B.RECEIVER_ID, TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'), G.ACCOUNT, G.DEPTID, F.CATEGORY_ID, F.CATEGORY_CD, G.LOCATION, H.BUSINESS_UNIT, H.PO_ID, H.COMMENT_CD, H.COMMENT_TYPE, H.COMMENT1,C.SETID,C.VENDOR_ID

                  FROM PS_RECV_LN_SHIP A, PS_RECV_HDR B, PS_VENDOR C, PS_PO_LINE_SHIP D, PS_PO_HDR E, PS_ITM_CAT_TBL F, PS_PO_LINE_DISTRIB G, PS_TMC_PO_COMMENTS H

                  WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT

                     AND A.RECEIVER_ID = B.RECEIVER_ID

                     AND C.VENDOR_ID = B.VENDOR_ID

                     AND B.BUSINESS_UNIT = 'TMC01'

                     AND A.BUSINESS_UNIT = D.BUSINESS_UNIT

                     AND A.PO_ID = D.PO_ID

                     AND A.LINE_NBR = D.LINE_NBR

                     AND A.SCHED_NBR = D.SCHED_NBR

                     AND A.RECV_SHIP_STATUS <> 'X'

                     AND D.CANCEL_STATUS <> 'X'

                     AND B.RECEIPT_DT BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD')

                     AND A.BUSINESS_UNIT = E.BUSINESS_UNIT

                     AND A.PO_ID = E.PO_ID

                     AND F.CATEGORY_ID = A.CATEGORY_ID

                     AND F.EFFDT =

                        (SELECT MAX(F_ED.EFFDT) FROM PS_ITM_CAT_TBL F_ED

                        WHERE F.SETID = F_ED.SETID

                          AND F.CATEGORY_TYPE = F_ED.CATEGORY_TYPE

                          AND F.CATEGORY_CD = F_ED.CATEGORY_CD

                          AND F.CATEGORY_ID = F_ED.CATEGORY_ID

                          AND F_ED.EFFDT <= SYSDATE)

                     AND A.BUSINESS_UNIT = G.BUSINESS_UNIT

                     AND A.PO_ID = G.PO_ID

                     AND D.LINE_NBR = G.LINE_NBR)

                     AND E.BUSINESS_UNIT = H.BUSINESS_UNIT

                     AND E.PO_ID = H.PO_ID

                     AND H.COMMENT_CD IN ('RPT','CNT')

                  GROUP BY  A.INV_ITEM_ID,  A.DESCR254_MIXED,  A.RECEIVE_UOM,  A.CONVERSION_RATE,  A.QTY_SH_ACCPT_SUOM,  D.PRICE_PO,  ( D.PRICE_PO /  A.CONVERSION_RATE),  ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO),  C.NAME1,  B.VENDOR_ID,  A.ITM_ID_VNDR,  A.MFG_ID,  A.MFG_ITM_ID,  TO_CHAR(E.PO_DT,'YYYY-MM-DD'),  A.PO_ID,  D.LINE_NBR,  B.RECEIVER_ID,  TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'),  G.ACCOUNT,  G.DEPTID,  F.CATEGORY_ID,  F.CATEGORY_CD,  G.LOCATION,  H.BUSINESS_UNIT,  H.PO_ID,  H.COMMENT_CD,  H.COMMENT_TYPE,  H.COMMENT1,C.SETID,C.VENDOR_ID

                  ORDER BY 11, 15, 16, 17;

                Follow me on twitter: @rsalido--- On Thu, 5/10/12, Ashish Bhatt <ashish_bhatt@...> wrote:


                From: Ashish Bhatt <ashish_bhatt@...>
                Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
                To: psftdba@yahoogroups.com, rsalido@...
                Date: Thursday, May 10, 2012, 11:25 AM

                 

                Please copy paste the SQL in the mail.

                Thanks in advance.

                To: psftdba@yahoogroups.comFrom: rsalido@...: Thu, 10 May 2012 10:24:14 -0700Subject: PeopleSoft DBA Forum Query Returning Incomplete Results 

                Hi,

                   We have a situation with a peopleSoft Query. If we run such query from Query (PeopleSoft's product), we get one row. But, if we grab the SQL and run it from TOAD, we get 1200+ rows. Same parameters and everything.

                    Can you think of anything that could be affecting the number of rows Query is returning?

                 

                Thank you very much,

                Roberto.Follow me on twitter: @rsalido




              • Timothy Herring
                There is an Oracle bug that returns inconsistent data-sets related to unnest_subquery PS recommended: *._unnest_subquery=FALSE ... From: the_dragon Draco
                Message 7 of 12 , May 11, 2012
                • 0 Attachment
                  There is an Oracle bug that returns inconsistent data-sets related to unnest_subquery

                  PS recommended: *._unnest_subquery=FALSE


                  --- On Fri, 5/11/12, the_dragon Draco <ceprn@...> wrote:

                  From: the_dragon Draco <ceprn@...>
                  Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
                  To: psftdba@yahoogroups.com
                  Date: Friday, May 11, 2012, 4:47 AM

                   

                  I remember something like this from a couple of years ago - it required an Oracle patch, if I recall correctly.
                   
                  Could it be some kind of strange shared pool buffering?
                   
                  What results do you get if you use sql*plus?
                   
                  peace,
                  clark 'the dragon' willis
                   

                  To: psftdba@yahoogroups.com
                  From: rsalido@...
                  Date: Thu, 10 May 2012 11:24:07 -0700
                  Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results

                   
                  This is a really good thought. However, I just ran into a new issue: TOAD is returning diffrent result sets.
                   
                  MOST of the time I get 1200 rows back but there are instances where I get just one.
                   
                  So, it is not the tools, nor peoplesoft. It seems Oracle is not returning consistent datasets.
                   
                  As you can see from the query, this is PO information (from Production) so it is not changing that much. I mean, I would expect a variance of a few rows, but not one row one time and 1200+ the next.
                   
                   We are scratching our heads.

                  Follow me on twitter: @rsalido

                  --- On Thu, 5/10/12, David Kurtz <david.kurtz@...> wrote:

                  From: David Kurtz <david.kurtz@...>
                  Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
                  To: psftdba@yahoogroups.com
                  Date: Thursday, May 10, 2012, 11:55 AM

                   

                  Is it possible that you have hit the maximum query size specified in the Application Server configuration?

                   

                  Note

                  i)                    the default settings are different for PSAPPSRV and PSQRYSRV. 

                  a.       If your users are running lots of ad-hoc queries then perhaps you should configure PSQRYSRVs

                  ii)                  I am not suggesting that you should increase the maximum fetch size limit.  If your query is returning this much data I would question whether users should be allowed to run it on-line.  It might be better if the query was scheduled and was thus executed by an Application Engine process instead.

                   

                   

                   

                  [PSAPPSRV]

                  ;=========================================================================

                  ; Settings for PSAPPSRV

                  ;=========================================================================

                  ; Max Fetch Size -- max result set size in KB for a SELECT query

                  ; Default is 5000KB. Use 0 for no limit.

                  Max Fetch Size=5000

                  [PSQRYSRV]

                  ;=========================================================================

                  ; Settings for PSQRYSRV

                  ;=========================================================================

                  ; Max Fetch Size -- max result set size in KB for a SELECT query

                  ; Default is 10000KB. Use 0 for no limit.

                  Max Fetch Size=10000

                   

                   

                   

                  From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of James De Atley
                  Sent: 10 May 2012 18:47
                  To: psftdba@yahoogroups.com
                  Subject: Re: PeopleSoft DBA Forum Query Returning Incomplete Results

                   




                  It's not.  Andy already mentioned PeopleSoft row level security.

                  That's where I would start looking.

                  Using PeopleSoft you must jump through this security hoop,

                  where as straight SQL in toad or SqlPlus does not.

                   

                  __________________________________________________________
                  James C. De Atley

                   

                  Mobile: (912) 253-0419

                  From: "rsalido@..." <rsalido@...>
                  To: psftdba@yahoogroups.com
                  Sent: Thursday, May 10, 2012 1:43 PM
                  Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results

                   

                  Here it is but I dont think the SQL is the issue. The SQL runs but the results sets are different depending on the tool used to submit it.

                   

                  Thanks!

                   

                  SELECT DISTINCT A.INV_ITEM_ID, A.DESCR254_MIXED, A.RECEIVE_UOM, A.CONVERSION_RATE, ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE), A.QTY_SH_ACCPT_SUOM, D.PRICE_PO, ( D.PRICE_PO /  A.CONVERSION_RATE), ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO), C.NAME1, B.VENDOR_ID, A.ITM_ID_VNDR, A.MFG_ID, A.MFG_ITM_ID, TO_CHAR(E.PO_DT,'YYYY-MM-DD'), A.PO_ID, D.LINE_NBR, B.RECEIVER_ID, TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'), G.ACCOUNT, G.DEPTID, F.CATEGORY_ID, F.CATEGORY_CD, G.LOCATION, H.BUSINESS_UNIT, H.PO_ID, H.COMMENT_CD, H.COMMENT_TYPE, H.COMMENT1,C.SETID,C.VENDOR_ID

                    FROM PS_RECV_LN_SHIP A, PS_RECV_HDR B, PS_VENDOR C, PS_PO_LINE_SHIP D, PS_PO_HDR E, PS_ITM_CAT_TBL F, PS_PO_LINE_DISTRIB G, PS_TMC_PO_COMMENTS H

                    WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT

                       AND A.RECEIVER_ID = B.RECEIVER_ID

                       AND C.VENDOR_ID = B.VENDOR_ID

                       AND B.BUSINESS_UNIT = 'TMC01'

                       AND A.BUSINESS_UNIT = D.BUSINESS_UNIT

                       AND A.PO_ID = D.PO_ID

                       AND A.LINE_NBR = D.LINE_NBR

                       AND A.SCHED_NBR = D.SCHED_NBR

                       AND A.RECV_SHIP_STATUS <> 'X'

                       AND D.CANCEL_STATUS <> 'X'

                       AND B.RECEIPT_DT BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD')

                       AND A.BUSINESS_UNIT = E.BUSINESS_UNIT

                       AND A.PO_ID = E.PO_ID

                       AND F.CATEGORY_ID = A.CATEGORY_ID

                       AND F.EFFDT =

                          (SELECT MAX(F_ED.EFFDT) FROM PS_ITM_CAT_TBL F_ED

                          WHERE F.SETID = F_ED.SETID

                            AND F.CATEGORY_TYPE = F_ED.CATEGORY_TYPE

                            AND F.CATEGORY_CD = F_ED.CATEGORY_CD

                            AND F.CATEGORY_ID = F_ED.CATEGORY_ID

                            AND F_ED.EFFDT <= SYSDATE)

                       AND A.BUSINESS_UNIT = G.BUSINESS_UNIT

                       AND A.PO_ID = G.PO_ID

                       AND D.LINE_NBR = G.LINE_NBR)

                       AND E.BUSINESS_UNIT = H.BUSINESS_UNIT

                       AND E.PO_ID = H.PO_ID

                       AND H.COMMENT_CD IN ('RPT','CNT')

                    GROUP BY  A.INV_ITEM_ID,  A.DESCR254_MIXED,  A.RECEIVE_UOM,  A.CONVERSION_RATE,  A.QTY_SH_ACCPT_SUOM,  D.PRICE_PO,  ( D.PRICE_PO /  A.CONVERSION_RATE),  ( A.QTY_SH_ACCPT_SUOM /  A.CONVERSION_RATE) * ( D.PRICE_PO),  C.NAME1,  B.VENDOR_ID,  A.ITM_ID_VNDR,  A.MFG_ID,  A.MFG_ITM_ID,  TO_CHAR(E.PO_DT,'YYYY-MM-DD'),  A.PO_ID,  D.LINE_NBR,  B.RECEIVER_ID,  TO_CHAR(B.RECEIPT_DT,'YYYY-MM-DD'),  G.ACCOUNT,  G.DEPTID,  F.CATEGORY_ID,  F.CATEGORY_CD,  G.LOCATION,  H.BUSINESS_UNIT,  H.PO_ID,  H.COMMENT_CD,  H.COMMENT_TYPE,  H.COMMENT1,C.SETID,C.VENDOR_ID

                    ORDER BY 11, 15, 16, 17;

                  Follow me on twitter: @rsalido--- On Thu, 5/10/12, Ashish Bhatt <ashish_bhatt@...> wrote:


                  From: Ashish Bhatt <ashish_bhatt@...>
                  Subject: RE: PeopleSoft DBA Forum Query Returning Incomplete Results
                  To: psftdba@yahoogroups.com, rsalido@...
                  Date: Thursday, May 10, 2012, 11:25 AM

                   

                  Please copy paste the SQL in the mail.

                  Thanks in advance.

                  To: psftdba@yahoogroups.comFrom: rsalido@...: Thu, 10 May 2012 10:24:14 -0700Subject: PeopleSoft DBA Forum Query Returning Incomplete Results 

                  Hi,

                     We have a situation with a peopleSoft Query. If we run such query from Query (PeopleSoft's product), we get one row. But, if we grab the SQL and run it from TOAD, we get 1200+ rows. Same parameters and everything.

                      Can you think of anything that could be affecting the number of rows Query is returning?

                   

                  Thank you very much,

                  Roberto.Follow me on twitter: @rsalido




                • Your message has been successfully submitted and would be delivered to recipients shortly.