- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Oracle 8.1.7 Memory Leak, High CPU Utilization
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-05-2001 08:54 AM
тАО04-05-2001 08:54 AM
Oracle 8.1.7 Memory Leak, High CPU Utilization
When we begin a load test using Mercury Interactive LoadRunner, there seems to be quite a bit more RAM being utilized than before, throughout the test, this amount continues to grow and eventually begins to swap. According to Glance, all of this RAM is being used by Oracle. On top of this problem, Oracle is also taking up 100% of the CPU throughout the test, which it didn't before. Even after the test completes, the CPU continues to be maxed out for up to 30 minutes.. Our local DBA has searched metalink without success.
Has anyone else seen issues similar to this? The only other thing that changed was we changed to the Cost Based Optimizer and ran Statistics on the DB..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-05-2001 10:29 PM
тАО04-05-2001 10:29 PM
Re: Oracle 8.1.7 Memory Leak, High CPU Utilization
I found this in the documentation :
Parameter type: String
Syntax: OPTIMIZER_MODE =
{RULE | CHOOSE | FIRST_ROWS | ALL_ROWS}
Parameter class: Dynamic. Scope=ALTER SESSION.
Default value: CHOOSE
OPTIMIZER_MODE specifies the goal of optimization of SQL statements.
RULE specifies that rule-based optimization is to be used unless hints are specified in the query.
CHOOSE specifies cost-based optimization for a SQL statement if the data dictionary contains statistics for at least one table accessed in the statement. If no such statistics are present, the optimizer uses the rule-based approach.
FIRST_ROWS causes the optimizer to use cost-based optimization to choose execution plans that minimize response time. This setting overrides a nonzero setting for "OPTIMIZER_PERCENT_PARALLEL".
ALL_ROWS causes the optimizer to use cost-based optimization to choose execution plans that minimize total execution time.
That means, if you changed the optimizer mode,
you also change the load on the machine a lot.
Rgds
Alexander M. Ermes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-06-2001 01:49 AM
тАО04-06-2001 01:49 AM
Re: Oracle 8.1.7 Memory Leak, High CPU Utilization
See parameters in init___.ora
transaction_auditing = FALSE
# it could produce undesired results.
max_dump_file_size = 1024k # no unlimited
See :http://forums.itrc.hp.com/cm/QuestionAnswer/1,1150,0xfba3854994d9d4118fef0090279cd0f9,00.html
No much info, but...
Good luck.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-06-2001 04:15 AM
тАО04-06-2001 04:15 AM
Re: Oracle 8.1.7 Memory Leak, High CPU Utilization
It may be the case that many or most of the application queries are optimized for rule based optimization. Try setting OPTIMIZER_MODE back to rule and see if you have same results. May help isolate the problem.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-06-2001 06:28 AM
тАО04-06-2001 06:28 AM
Re: Oracle 8.1.7 Memory Leak, High CPU Utilization
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-06-2001 06:40 AM
тАО04-06-2001 06:40 AM
Re: Oracle 8.1.7 Memory Leak, High CPU Utilization
I figured by changing the optimizer mode, we would see some differences in the performance of the server and may cause the high cpu, but my main concern however is the gross overuse of RAM.. We want to try using the CBO (Cost Based Optimizer) as Oracle has stated that it will eventually phase out support for the Rule based optimizer..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-06-2001 07:05 AM
тАО04-06-2001 07:05 AM
Re: Oracle 8.1.7 Memory Leak, High CPU Utilization
We have the max_dump_file_size set to 2000K, I suggested the transaction_auditing to our DBA, he'll look into it and I'll keep you guys informed..
Brian,
We definitely analyzed all the tables and indexes, your right about CBO making poor decisions though, after doing a little more digging, some of our queries that were taking ~1sec are now taking ~84 seconds.. I assume it's doing some full table scans or something.. On the other hand, 20% of our queries are about 3 times faster.. Unfortunately for us, the bad outweighs the good.
We changed back to rule and deleted all statistics.. The server is running GREAT.. CPU 55%, Disk 64%, Mem 62%.. So there is something quirky about Oracle's CBO in 8.1.7.1.. On another note, with RULE, our shadow processes are ~5MB each, with COST, they start out at ~30MB, and grow up to ~70MB.. We simulate 300 users, so I think I know where all of our RAM is going.. Sounds like a problem for the folks at Oracle to fix..
Oliver,
Our LoadRunner scripts do an initialization phase where they login to the database, then loop through a bunch of permutations of database activity for about an hour, then do a disconnect, so there shouldn't be a lot of connect/disconnects.. Thanks though..
We are going to open a ticket with Oracle, as this seems to point directly to them, but if anyone has any other useful things to try, I'd love to hear from ya..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-06-2001 11:27 AM
тАО04-06-2001 11:27 AM
Re: Oracle 8.1.7 Memory Leak, High CPU Utilization
What is the application you are running against this database? I ask because we are about to do the same upgrade, 8.0.6 - 8.1.7. We are still on SAP 3.1h which our Basis people tell us does not support Cost based. We are stuck with Rule based until we upgrade SAP to 4.6. It might not be just database. There might be an application problem.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-09-2001 05:55 AM
тАО04-09-2001 05:55 AM
Re: Oracle 8.1.7 Memory Leak, High CPU Utilization
I work for a Health Industry Software company, the application we are running is one of our own products. Historically it has been a RULE based product, but we have been getting a lot of push from our clients to go COST. I agree with you however that about half of the fault is probably with our own product design.. Some tweaking of some queries would probably make a big difference.. Like I said though, when we stick with RULE, everything performs fine.. In fact, in our latest tests, with a cold database, we saw a tiny bit of a performance hit, but once the database has been primed, it outperforms 8.0.6 with RULE.. So as long as you go with that, you should be ok..