5 Replies Latest reply on Apr 22, 2019 10:16 AM by fac586

    ORA-10260: limit size (1024000) of the PGA heap set by event 10261 exceeded.

    3004152

      ORA-10260: limit size (1024000) of the PGA heap set by event 10261 exceeded

      When i am trying to run a SQL query for my report generation, it is returning ORA-10260: limit size (1024000) of the PGA heap set by event 10261 exceeded.

      How can i overcome this issue.

        • 1. Re: ORA-10260: limit size (1024000) of the PGA heap set by event 10261 exceeded.
          Mahmoud_Rabie

          Hi

           

          Could you put your Query here. I think it needs to be optimized.

           

          Regards

          Mahmoud

          • 2. Re: ORA-10260: limit size (1024000) of the PGA heap set by event 10261 exceeded.
            3004152

            Hi,

             

            Here is the SQL query i am using:

            with clicktable1(clickname1,prev_rt,prev_RT_date) as

            (select unique_identifier,total_rt_ms,test_date

            from table1

            where (unique_identifier,test_date) in

            (select unique_identifier,max(test_date)

            from table1

            where total_rt_ms > 0 and test_date <:reportdate and environment not like 'PSR%'

            group by unique_identifier

            )

            ),

            clicktable(clickname,min_rt_ms,min_date) as

            (select UNIQUE_IDENTIFIER, TOTAL_RT_MS,TEST_DATE

            FROM table1

            where (unique_identifier,total_rt_ms) in

            (select unique_identifier,min(total_rt_ms)

            from table1

            WHERE TOTAL_RT_MS > 0 and (test_date+209 > :reportdate) and (test_date < :reportdate) and environment not like 'PSR%'

               GROUP BY UNIQUE_IDENTIFIER

            )

            )

               SELECT functional_area,c.click_name,min_date, TOTAL_RT_MS,MIN_RT_MS, (TOTAL_RT_MS-MIN_RT_MS) as DIFFERENCE,prev_rt,bug_number, triage_comments

            FROM table1 a, clicktable b, table2 c,clicktable1 d

            where  b.clickname=a.unique_identifier and d.clickname1 = a.unique_identifier and c.unique_identifier=b.clickname and environment not like 'PSR%'  and IS_ACTIVE='Y'

            and TEST_DATE = :REPORTDATE

            and total_rt_ms >b.min_rt_ms+:difference

            order by functional_area,difference desc

            • 3. Re: ORA-10260: limit size (1024000) of the PGA heap set by event 10261 exceeded.
              fac586

              3004152 wrote:

              Please update your forum profile with a recognisable username instead of "3004152".

              ORA-10260: limit size (1024000) of the PGA heap set by event 10261 exceeded

              When i am trying to run a SQL query for my report generation, it is returning ORA-10260: limit size (1024000) of the PGA heap set by event 10261 exceeded.

              How can i overcome this issue.

              Start by providing all of the information that may be relevant in isolating the problem and identifying potential solutions:

               

              • When you ask a question, always include the information detailed in these guidelines so we can provide version-, environment-, and application-specific advice.
              • Provide background information about the problem domain, application, and data involved to give the question some context. An understanding of the bigger picture helps us to identify the most appropriate solution.
              • Explain what you mean by "report": where and when is this query being executed?
              • Post a complete debug trace of the page request (as text, not a screenshot).
              • Post properly formatted code:

               

              with clicktable1 (clickname1, prev_rt, prev_RT_date) as (
                    select
                        unique_identifier
                      , total_rt_ms
                      , test_date
                    from
                        table1
                    where
                        (unique_identifier, test_date) in (select
                                                              unique_identifier
                                                            , max(test_date)
                                                          from
                                                              table1
                                                          where
                                                              total_rt_ms > 0
                                                          and test_date < :reportdate
                                                          and environment not like 'PSR%'
                                                          group by
                                                              unique_identifier))
                , clicktable (clickname, min_rt_ms, min_date) as (
                      select
                          unique_identifier
                        , total_rt_ms
                        , test_date
                      from
                          table1
                      where
                          (unique_identifier,total_rt_ms) in (select
                                                                  unique_identifier
                                                                , min(total_rt_ms)
                                                              from
                                                                  table1
                                                              where
                                                                  total_rt_ms > 0
                                                              and (test_date + 209 > :reportdate)
                                                              and (test_date < :reportdate)
                                                              and environment not like 'PSR%'
                                                              group by
                                                                  unique_identifier))
              select
                  functional_area
                , c.click_name
                , min_date
                , total_rt_ms
                , min_rt_ms
                , (total_rt_ms - min_rt_ms) difference
                , prev_rt
                , bug_number
                , triage_comments
              from
                  table1 a
                , clicktable b
                , table2 c
                , clicktable1 d
              where
                  b.clickname = a.unique_identifier
              and d.clickname1 = a.unique_identifier
              and c.unique_identifier = b.clickname
              and environment not like 'PSR%'
              and is_active = 'Y'
              and test_date = :reportdate
              and total_rt_ms > b.min_rt_ms + :difference
              order by
                  functional_area
                , difference desc
              

               

              Are table1 and table2 tables or views?

               

              What data type is test_date? Is it indexed?

               

              Does the exception occur when the query is executed in SQL*Plus or SQL Developer, or only in APEX?

              • 4. Re: ORA-10260: limit size (1024000) of the PGA heap set by event 10261 exceeded.
                3004152

                table1 and table 2 are tables and not views.

                I get this error when i run the query in sqlplus and sqldeveloper also.

                test_date is date datatype and it is not indexed. My issue is i did not get this error, when i run the same query on table3 and table4, which have similar structure as table1 and table2.

                • 5. Re: ORA-10260: limit size (1024000) of the PGA heap set by event 10261 exceeded.
                  fac586

                  3004152 wrote:

                   

                  table1 and table 2 are tables and not views.

                  I get this error when i run the query in sqlplus and sqldeveloper also.

                  test_date is date datatype and it is not indexed. My issue is i did not get this error, when i run the same query on table3 and table4, which have similar structure as table1 and table2.

                  How many rows in each table? How many rows do you expect to be returned by each step in the query?

                   

                  How similar is the structure? If it's identical why are there separate tables?

                   

                  Why aren't you providing the real table names?

                   

                  This problem isn't going to be resolved magically. We need all of the information requested above before beginning to think about a solution.