Jump to content

KLC

Members
  • Content count

    4
  • Joined

  • Last visited

Community Reputation

0 Neutral

About KLC

  • Rank
    Newbie
  1. does ord reset for a new id? so if the next row in your data set were ID = 2, or would ord be 8?
  2. Thank you, glad to be here The table is small, 1.7 million records. The CTAS from a straight select of the external staging table completes fast, so disk IO is not an issue here I think. The issue is creating that intermediary table, using the script i uploaded originally, that one was the one that was never completing. The only thing being done there was a regex_substr on a CLOB, and a to_char from that CLOB. I've had issues with this server and clobs before, specifically to_char conversion from clob. I found it amazing that after playing with the memory settings, the CTAS went from never finishing, to actually doing something. I'm convinced there's a configuration problem in this server, but I don't know where to start.
  3. I decided to run the CTAS command again, and leave it running with some metrics, looks like it did complete, after 1 hour, 8 minutes. Definitely an improvement over never, but still pretty slow. Looking forward to some advice from the experts, thanks!
  4. I wanted to start out by saying that I, by no means, consider myself a DBA, but I've been put into a situation where more often than not, I have to fix my own problems...I guess that's how everyone gets started? That being said, the Burleson website has been a great tool for me through my years of learning. I'm having an issue with one of our oracle servers, a simple CTAS script refuses to complete. Everything I could find points to a memory issue, I've been able to replicate the issue on two different servers, both servers have similar specs hardware wise, and both are running a similar DB configuration, in terms of data and config. Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production (Prod) Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production (Test) I left the query running 8 hours, and SQL Developer would eventually hang, thinking it was still processing, but EM showed the job as failed. I made a few changes, and again left it running over night, same result, the query would never finish in SQL Developer, but it had actually failed and not finish running. At one point, while trying to figure out the issue, I also received ORA-12805: parallel query server died unexpectedly, I decided to remove all parallel hints from the CTAS and from the source table in the select. However, the issues persisted After reviewing the articles below, I decided to look into potential memory issues on the Oracle side: https://lessonsfromoracle.wordpress.com/2014/02/23/parallel-query-server-died-unexpectedly-but-why/ http://www.dba-oracle.com/oracle11g/oracle_11g_memory_target_parameter.htm Starting out on the 11g server, I noticed that db_cache_size was 0, sga_target was 0. memory_max_target was 6560M and memory_target were 6560M, the server has 8GB of memory. In trying to fix the db_cache_size, I issues the following commands: ALTER SYSTEM SET MEMORY_TARGET = 0; ALTER SYSTEM SET db_cache_size = 2G; ALTER SYSTEM SET SGA_TARGET = 0; ALTER SYSTEM SET MEMORY_TARGET = 6560M; I left the query running, and went to lunch, half an hour later when I returned, to my surprise, the query had completed, I don't exactly how long it took to complete, but I wasn't gone long. I was able to finish the archiving task and proceeded to try and replicate the fix on the 12c server....to no avail. I then went back to the 11g server, reran the CTAS script, and of course...now it refuses to complete again. I've pretty much given up here, I played a little bit more with the memory settings, but I'm completely lost at this point. Hoping for an assist from the group, I'm attaching some details to this post, and I'll be willing to provide anything else you guys ask for! ADDITIONAL INFO.txt
×