1 2 Previous Next 27 Replies Latest reply on Jun 18, 2018 5:38 AM by mathguy

    ORA-30009: Not enough memory for CONNECT BY operation

    Quanwen Zhao

      Hi, experts

       

      Currently I wanna create a test table TEST1 for doing something else on my oracle db server 11.2.0.4.0 for Linux x86_64.

       

      At the same time I don't make sure whether creating table failed is related to the value (default 15) of the parameter arraysize of SQL*Plus.

       

      The following are my some operation steps,

       

      (1) on SQL*Plus,

       

      SYS@test> show arraysize
      arraysize 15
      SYS@test> set arraysize
      SP2-0267: arraysize option 0 out of range (1 through 5000)
      

       

      (2) on SQLcl 18.1.1,

       

      SQL> show arraysize
      arraysize 15
      SQL> set arraysize
      SQLPLUS command failed - not enough arguments
      

       

      SQL> set arraysize 5000
      SQL> 
      SQL> show arraysize
      arraysize 5000
      SQL> 
      
      
      SQL> set arraysize 5001
      SP2-0267: arraysize option 5001 out of range (1 through 5000)
      
      
      SQL> set arraysize 5000
      SQL> 
      SQL> show arraysize
      arraysize 5000
      

       

      SQL> 
      SQL> set timing on
      SQL> show timing
      timing ON
      SQL> 
      SQL> create table test1
        2  segment creation immediate
        3  nologging
        4  as
        5  select rownum as id
        6         , to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime
        7         , trunc(dbms_random.value(0, 100)) as random_id
        8         , dbms_random.string('x', 20) random_string
        9  from dual
       10  connect by level <= 100000000
       11  ;
      
      Error starting at line : 1 in command -
      create table test1
      segment creation immediate
      nologging
      as
      select rownum as id
             , to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime
             , trunc(dbms_random.value(0, 100)) as random_id
             , dbms_random.string('x', 20) random_string
      from dual
      connect by level <= 100000000
      Error report -
      ORA-30009: Not enough memory for CONNECT BY operation
      
      
      Elapsed: 00:37:11.019
      

       

      As you can see it has shown error ORA-30009 finally, could you help me for trouble-shooting it?

       

      Thanks in advance.

       

      Best Regards

      Quanwen Zhao

        • 1. Re: ORA-30009: Not enough memory for CONNECT BY operation
          John Thorton

          unwilling or incapable to use GOOGLE yourself?

           

          [oracle@localhost trace]$ oerr ora 30009

          30009, 0000, "Not enough memory for %s operation"

          // *Cause: The memory size was not sufficient to process all the levels of the

          //         hierarchy specified by the query.

          // *Action: In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to

          //          a reasonably larger value.

          //          Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a

          //          reasonably larger value.

          • 2. Re: ORA-30009: Not enough memory for CONNECT BY operation
            AndrewSayer

            What has arraysize got to do with this at all? That is just a setting to determine how many rows to fetch from a cursor each iteration..

             

            Either reduce the amount of times you are connecting the row in dual to itself - do you really need 100 million rows just to try something?

            Or rewrite your statement to use cross joins or something else to prevent needing to store so much in memory.

            • 3. Re: ORA-30009: Not enough memory for CONNECT BY operation
              Quanwen Zhao

              Either reduce the amount of times you are connecting the row in dual to itself - do you really need 100 million rows just to try something?

              Or rewrite your statement to use cross joins or something else to prevent needing to store so much in memory.

               

              Hi, Andrew

               

              Yep, I wanna use this test table to simulate production system's table size (approximately 6 gigabytes) for testing something.

               

              Last time I used this figure "10000000" with connect by but the table's size is about 600 megabytes.

               

              SQL> set timing on
              SQL> 
              SQL> create table test1
                2  segment creation immediate
                3  nologging
                4  as
                5  select rownum as id
                6         , to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime
                7         , trunc(dbms_random.value(0, 100)) as random_id
                8         , dbms_random.string('x', 20) random_string
                9  from dual
               10  connect by level <= 10000000  <<==
               11  ;
              
              
              Table TEST1 created.
              
              
              Elapsed: 00:15:58.255
              
              
              SQL> select sum(blocks),sum(bytes)/1024/1024 size_mb from user_segments
                2  where segment_name = 'TEST1'
                3  and segment_type = 'TABLE';
              
              
              SUM(BLOCKS)    SIZE_MB
              ----------- ----------
                    76800        600
              

               

              It's so far as 6 gigabytes so I change that figure to be "100000000" for retry it.

               

              Best Regards

              Quanwen Zhao

              • 4. Re: ORA-30009: Not enough memory for CONNECT BY operation
                AndrewSayer

                Quanwen Zhao wrote:

                 

                Either reduce the amount of times you are connecting the row in dual to itself - do you really need 100 million rows just to try something?

                Or rewrite your statement to use cross joins or something else to prevent needing to store so much in memory.

                 

                Hi, Andrew

                 

                Yep, I wanna use this test table to simulate production system's table size (approximately 6 gigabytes) for testing something.

                 

                Last time I used this figure "10000000" with connect by but the table's size is about 600 megabytes.

                 

                1. SQL>settimingon
                2. SQL>
                3. SQL>createtabletest1
                4. 2segmentcreationimmediate
                5. 3nologging
                6. 4as
                7. 5selectrownumasid
                8. 6,to_char(sysdate+rownum/24/3600,'yyyy-mm-ddhh24:mi:ss')asinc_datetime
                9. 7,trunc(dbms_random.value(0,100))asrandom_id
                10. 8,dbms_random.string('x',20)random_string
                11. 9fromdual
                12. 10connectbylevel<=10000000<<==
                13. 11;
                14. TableTEST1created.
                15. Elapsed:00:15:58.255
                16. SQL>selectsum(blocks),sum(bytes)/1024/1024size_mbfromuser_segments
                17. 2wheresegment_name='TEST1'
                18. 3andsegment_type='TABLE';
                19. SUM(BLOCKS)SIZE_MB
                20. ---------------------
                21. 76800600

                 

                It's so far as 6 gigabytes so I change that figure to be "100000000" for retry it.

                 

                Best Regards

                Quanwen Zhao

                Its likely you need the table to have the same row distribution, the same indexes etc if you want your tests to be meaningful.

                 

                Cant you spin up a duplicate DB from a backup of production, you would need to obfuscate data that doesn’t belong outside of production before you use it though (And if this effects your selectivities then you’ll need to change your queries accordingly)

                 

                To directly answer this, like I said before you can use a cross join to bump this up, if level<=100,000 is fine then cross join that to a generated row source of 100 rows (I can’t count your 0s properly on my phone screen so you’ll have to do the maths)

                • 5. Re: ORA-30009: Not enough memory for CONNECT BY operation
                  Quanwen Zhao

                  unwilling or incapable to use GOOGLE yourself?

                   

                  [oracle@localhost trace]$ oerr ora 30009

                  30009, 0000, "Not enough memory for %s operation"

                  // *Cause: The memory size was not sufficient to process all the levels of the

                  //         hierarchy specified by the query.

                  // *Action: In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to

                  //          a reasonably larger value.

                  //          Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a

                  //          reasonably larger value.

                   

                  Hello, John Thorton

                   

                  As you know, due to country's specific situation I haven't never logged in Google in China. BTW I've searched other posts but it hasn't any clues and answers so I come here.

                   

                  Best Regards

                  Quanwen Zhao

                  • 6. Re: ORA-30009: Not enough memory for CONNECT BY operation
                    BEDE

                    Wouldn't export and import provide better conditions to test whatever you wish to test?

                    • 7. Re: ORA-30009: Not enough memory for CONNECT BY operation
                      AndrewSayer

                      BEDE wrote:

                       

                      Wouldn't export and import provide better conditions to test whatever you wish to test?

                      Any logical duplication (like export and import) will leave the rows in any order it likes. Your indexes will then all be built from scratch - your statistics will be different and the performance of using your indexes will be different.

                      If you want to test performance then you absolutely must use physical duplication

                      • 8. Re: ORA-30009: Not enough memory for CONNECT BY operation
                        Gaz in Oz

                        Try using less memory with "connect by":

                        Generating lots of rows using connect by – safely! | Tanel Poder's Performance & Troubleshooting blog

                        For example:

                        create table test1
                        segment creation immediate
                        nologging
                        select r as id
                             , to_char(sysdate + r/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime
                             , trunc(dbms_random.value(0, 100)) as random_id
                             , dbms_random.string('x', 20) random_string
                        from  (SELECT rownum r
                               FROM  (SELECT level r FROM dual CONNECT BY level <= 1000) d1,
                                     (SELECT level r FROM dual CONNECT BY level <= 1000) d2,
                                     (SELECT level r FROM dual CONNECT BY level <= 1000) d3
                               WHERE  rownum <= 100000000
                        );
                        

                        ...generating 100,000,000 will take time.

                        • 9. Re: ORA-30009: Not enough memory for CONNECT BY operation
                          BEDE

                          Physical duplication would mean copying the database files as they are - a cold backup. But that may not be feasible. So, for some tests, export and import of some data may be used, although, as you have pointed out it will not be the same for performance. Moreover, for exact duplication, not only a cold backup would be needed, but also exactly the same kind of system (processors, memory and disks), which I pretty much doubt that many organizations have. For development and test environments pretty many use not so powerful computers as in production, and, if in test environment, on a not so powerful machine, some batch process performs satisfactory, then, in production, it should do no worse...

                          I meant export and import in order to have the same data.

                          • 10. Re: ORA-30009: Not enough memory for CONNECT BY operation
                            John_K

                            So instead of using a very narrow table where each row is going to consume a very small amount of space, why not base your query on something a bit more hefty like dba_objects?

                            • 11. Re: ORA-30009: Not enough memory for CONNECT BY operation
                              AndrewSayer

                              BEDE wrote:

                               

                              Physical duplication would mean copying the database files as they are - a cold backup. But that may not be feasible. So, for some tests, export and import of some data may be used, although, as you have pointed out it will not be the same for performance. Moreover, for exact duplication, not only a cold backup would be needed, but also exactly the same kind of system (processors, memory and disks), which I pretty much doubt that many organizations have. For development and test environments pretty many use not so powerful computers as in production, and, if in test environment, on a not so powerful machine, some batch process performs satisfactory, then, in production, it should do no worse...

                              I meant export and import in order to have the same data.

                              If export and import are good enough then there is absolutely no way its necessary to create such a big random data set from dual.

                              You don’t need a cold backup for physical duplication. Hot backups work fine and should already be available. I imagine PDB cloning can achieve the same.

                               

                              Sure, different hardware will perform differently. But so long as you use default system stats (which is the current recommendation) your queries will do the same plans (for the same bind peeking profile) and any system related time can be scaled up or down depending on your known hardware differences. Sure, it’s not quick to determine this but if you’re going to test on different hardware you are going to have to do this, otherwise whats the point of testing?

                               

                              If your company says they can’t afford to test properly then theyll have to deal with the consequences

                              • 12. Re: ORA-30009: Not enough memory for CONNECT BY operation
                                Quanwen Zhao
                                1. create table test1
                                2. segment creation immediate
                                3. nologging
                                4. select r as id
                                5.      , to_char(sysdate + r/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime
                                6.      , trunc(dbms_random.value(0, 100)) as random_id
                                7.      , dbms_random.string('x', 20) random_string
                                8. from  (SELECT rownum r
                                9.        FROM  (SELECT level r FROM dual CONNECT BY level <= 1000) d1,
                                10.              (SELECT level r FROM dual CONNECT BY level <= 1000) d2,
                                11.              (SELECT level r FROM dual CONNECT BY level <= 1000) d3
                                12.        WHERE  rownum <= 100000000
                                13. );

                                 

                                Hi, Gaz in Oz

                                 

                                According to your vice SQL code, I've created test table test1 and it has shown this error ORA-00922 below,

                                 

                                SQL> create table test1
                                  2  segment creation immediate
                                  3  nologging
                                  4  select r as id
                                  5       , to_char(sysdate + r/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime
                                  6       , trunc(dbms_random.value(0, 100)) as random_id
                                  7       , dbms_random.string('x', 20) random_string
                                  8  from  (SELECT rownum r
                                  9         FROM  (SELECT level r FROM dual CONNECT BY level <= 1000) d1,
                                 10               (SELECT level r FROM dual CONNECT BY level <= 1000) d2,
                                 11               (SELECT level r FROM dual CONNECT BY level <= 1000) d3
                                 12         WHERE  rownum <= 100000000
                                 13  );
                                
                                
                                Error starting at line : 1 in command -
                                create table test1
                                segment creation immediate
                                nologging
                                select r as id
                                     , to_char(sysdate + r/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime
                                     , trunc(dbms_random.value(0, 100)) as random_id
                                     , dbms_random.string('x', 20) random_string
                                from  (SELECT rownum r
                                       FROM  (SELECT level r FROM dual CONNECT BY level <= 1000) d1,
                                             (SELECT level r FROM dual CONNECT BY level <= 1000) d2,
                                             (SELECT level r FROM dual CONNECT BY level <= 1000) d3
                                       WHERE  rownum <= 100000000
                                )
                                Error report -
                                ORA-00922: missing or invalid option
                                

                                 

                                Best Regards

                                Quanwen Zhao

                                • 13. Re: ORA-30009: Not enough memory for CONNECT BY operation
                                  Gaz in Oz

                                  Missing "as" keyword.

                                  create table test1
                                  segment creation immediate
                                  nologging as
                                  select r as id
                                       , to_char(sysdate + r/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime
                                       , trunc(dbms_random.value(0, 100)) as random_id
                                       , dbms_random.string('x', 20) random_string
                                  from  (SELECT rownum r
                                         FROM  (SELECT level r FROM dual CONNECT BY level <= 1000) d1,
                                               (SELECT level r FROM dual CONNECT BY level <= 1000) d2,
                                               (SELECT level r FROM dual CONNECT BY level <= 1000) d3
                                         WHERE  rownum <= 100000000
                                  );
                                  
                                  • 14. Re: ORA-30009: Not enough memory for CONNECT BY operation
                                    BEDE

                                    The select itself is running in Oracle 11.2.0.4. And so does the create table as select. All you need is an "AS" after nologging (if that is exactly as you have posted).

                                     

                                    Of course the figure I used was only 10000, for I wasn't that insane to produce that many rows you need.

                                    1 2 Previous Next