Oracle 8.1.7 Memory Leak, High CPU Utilization

[David Snider]

We recently upgraded to Oracle 8.1.7.1 from 8.0.6, installed the March 2001 Support Plus Bundle, and Hardware Enablement/Critical Patches bundle on a Development Benchmark N4000 8-way 550Mhz running HP-UX 11.0 64-bit.

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..

[Alexander M. Ermes]

Hi David.
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

[Carlos Fernandez Riera]

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.

[Brian Bientz]

The use of the cost based optimizer could be causing problems. We have definitely seen cases were the cost base optimizer chooses poorly. When you analyzed your tables, did you analyze all tables and all indexes?

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.

[Oliver Tian]

Please make sure that your WinRunner test does not make a lot of Connect/Disconect calls with Database. There is a known bug only with Oracle 8.1.7, which causes the memory leak. Oracle is still working on the fix for the memory leak for Oracle 8.1.7 64-bit in HP-UX 11.00 environment.

[David Snider]

Thanks Alexander,
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..

[David Snider]

Carlos,
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..

[Dave Wherry]

David,
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.

[David Snider]

Hi Dave Wherry,
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..