Jump to content

potential memory configuration issues, frustrated and lost

Recommended Posts

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 - 64bit Production (Prod)
Oracle Database 11g Enterprise Edition Release - 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:


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 db_cache_size = 2G;

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!




Share this post

Link to post
Share on other sites

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!

Share this post

Link to post
Share on other sites

Hi, and welcome to the forum!

Just curious, how big was this table?

A CTAS always does a full table scan, but not setting db_cache_size defaults to only 64K!

Always remember, Disk I/O is in milliseconds, the most expensive operation in any database!

Hope this helps!

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now