Bizarre Cardinality

10.2.0.2 EE.

For several days last week we were seeing performance problems reported to us by our users. They would say that a report that used to take 1-2 minutes was going on 15 minutes and not finishing. Examination of this query in OEM would show that it was using 100% of a single CPU doing nested loops over thousands of rows. The plan reported cardinalities of 1 for every table in the query.

The same query parsed outside the procedure would show hash joins instead of nested loops and realistic cardinality numbers 6k or 12k, but certainly not 1. These queries would run in the original mark of 1-2 minutes.

The Precipitating event was a switchover. To move load from a production server, we switched over one of the instances on that box to the standby. Initially we noticed that not all of the parameters on the "new" prod were the same as the old. They included PGA_AGGREGATE_TARGET and SGA_TARGET and SGA_MAX_SIZE.
Those were fixed and these problems occurred after that.

Any clue what would cause plans to get a 1 for cardinality over dblinks or from within packages? And yet not get ones when parsed from Toad or SQLPlus?

Thanks,
Mark Brady

>>> This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2

--
http://www.freelists.org/webpage/oracle-l