Core dump when running multiple row insert from select
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Core dump when running multiple row insert from select

  1. #1
    Join Date
    Sep 2012
    Posts
    4

    Core dump when running multiple row insert from select

    We are running Solaris Intel with 11.2.0.2
    This is a new oracle setup for us.
    When running an insert from test table1 with 1 column(id), 2 rows(JT,TJ) into test table2 with 1 column(id) we get a core dump.

    Further investigation shows that if we run a single row insert from select, it runs fine.

    Could this be a buffer setting we need to adjust or ???
    At this point, we are out of ideas to try.

    What works
    Insert into table2 (id) select id from table1 where rownum<2

    What core dumps
    Insert into table2 (id) select id from table1

    We have narrowed it down via other test tables, etc... that it is not a permission issue and is only core dumping when multiple rows are being selected.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    I would look to see what triggers exist for this table. You might also look for
    invalid objects in the database, Make sure that your SGA is large enough, etc.
    But you may also want to post any trace files that you have.
    this space intentionally left blank

  3. #3
    Join Date
    Sep 2012
    Posts
    4
    No triggers as it is just 2 standard tables I created for testing.
    Not seeing any invalid objects and trace files aren't really producing anything that has any hint of being useful.
    I will check the setting size.
    Do you have a recommended size I should try?

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    With 11gR2, I have found the the shared pool needs to be at least 768MB, and db block buffers should be at least 32MB, probably more. If I had to guess I would say that you are using automatic memory management and you set the size too small. 11gR2 should have at least 1GB for memory_max_target and memory_target. 2GB would be better. I would also set them to the same value. But for the most part I specify manually sga_target, sga_max_size, db_cache_size, pga_aggregate_target, java_pool_size, and large_pool_size, leaving memory_max_target and memory_target set to 0.
    this space intentionally left blank

  5. #5
    Join Date
    Sep 2012
    Posts
    4
    Thank you for this information!
    Our block buffers were 0 and we have set them to 32mb.
    We were using auto memory, but have switched so we can play with the parameters.
    The mem_max_target and mem_target are at 5gb ea.
    I would be curious to see what we should set the other parameters to that you listed?
    I have spooled our parameters to a text file.
    If it would be helpful, I could send that to you if you wanted to take a look at them?
    Appreciate all your help as we are lost on this one.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    You should be able to just post the parameters online.
    Try this query:

    column name format a50
    column value format a30

    select name, to_char(value, '999,999,999,999') bytes from v$parameter where type=6 order by 1;

    This query will just show the memory parameters.
    this space intentionally left blank

  7. #7
    Join Date
    Sep 2012
    Posts
    4
    Thank you!
    Here are the results.

    Code:
    client_result_cache_lag                 3,000   
    client_result_cache_size                    0
    db_16k_cache_size                           0
    db_2k_cache_size                            0
    db_32k_cache_size                           0
    db_4k_cache_size                            0
    db_8k_cache_size                            0
    db_cache_size                      50,331,648   
    db_flash_cache_size                         0   
    db_keep_cache_size                          0   
    db_recovery_file_dest_size    107,374,182,400   
    db_recycle_cache_size                       0   
    java_pool_size                     50,331,648   
    large_pool_size                    50,331,648   
    memory_max_target               5,368,709,120   
    memory_target                   5,368,709,120   
    olap_page_pool_size                         0   
    pga_aggregate_target              629,145,600   
    result_cache_max_size               8,388,608   
    sga_max_size                    5,368,709,120   
    sga_target                      3,221,225,472   
    shared_pool_reserved_size          41,943,040   
    shared_pool_size                  838,860,800   
    streams_pool_size                 536,870,912
    Last edited by gandolf989; 09-20-2012 at 03:07 PM.

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    You might try turning off managed memory and also did you follow the
    before you install guide to verify that the prerequisites are all set up?
    I also edited your post to make it more readable.
    Last edited by gandolf989; 09-20-2012 at 03:08 PM.
    this space intentionally left blank

  9. #9
    Join Date
    Dec 2001
    Posts
    320
    Quote Originally Posted by notbob View Post
    We are running Solaris Intel with 11.2.0.2
    This is a new oracle setup for us.
    When running an insert from test table1 with 1 column(id), 2 rows(JT,TJ) into test table2 with 1 column(id) we get a core dump.

    Further investigation shows that if we run a single row insert from select, it runs fine.

    Could this be a buffer setting we need to adjust or ???
    At this point, we are out of ideas to try.

    What works
    Insert into table2 (id) select id from table1 where rownum<2

    What core dumps
    Insert into table2 (id) select id from table1

    We have narrowed it down via other test tables, etc... that it is not a permission issue and is only core dumping when multiple rows are being selected.
    Hi,

    Are you only seeing core dumps? No ORA-7445 or other errors in the alert log file?
    You may need to use a debugging tool like gdb to extract the call stack and look for similar bugs in MyOracleSupport.

    Hope this helps.
    Anyone can hold the helm when the sea is calm.

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