Re: Flumoxed - CBO/RBO

Preston Kemp wrote:

> Hi, I could do with some pearls of wisdom on this one. Win2k server,
> 8.1.7
>
> I set up a new server for a client a few weeks ago, ran a few tests in
> our application (USoft), & it was working fine & flying along. Then over
> the weekend just gone they did the necessary to switch to it as the live
> server. This basically involved dropping the schema owner, re-creating
> using the script I used originally, importing a dump from their 'old'
> server, building the indexes, and running dbms_stats.gather etc.
>
> So as of this morning their users were on the new server, & generally
> happy with the performance gains over the old one. However, some parts
> of the application are horrendously slow. It's impossible to know which
> are good and which are bad at the moment (there's hundreds of screens),
> but one of the most used screens is taking over 3 minutes to retrieve
> all the data, whereas it should take well under a second.
>
> Unfortunately they're a couple of hundred miles away, with no remote
> access, so troubleshooting is not easy. They don't have any Oracle bods
> on site. Just out of interest, I got them to delete the stats, &
> suddenly the 3 minute screen was back to it's usual sub-one-second
> performance. Gathered the stats again, & sure enough it ran like crap.
>
> As the SQL that actually hits the database is generated by USoft,
> there's no way of using hints. So, question is, why has it suddenly
> decided to play dead when it was perfectly happy before they loaded the
> latest dump? More importantly, how can I convince it to start behaving
> again? The relevant init parameters (sort_area_size/hash_area_size)
> appear to be reasonable. We've left it using RBO for now, but I've no
> doubt the phone will be ringing tomorrow to tell me about how brilliant
> the 'problem' screens are, but now the rest of the system has gone on
> strike.
>
> I will be spending the evening Googling, but everything you read seems
> to get blown out the water by you knowledgeable people in here, so I
> don't know what to trust anymore! I'm not (necessarily) looking for
> specific things to try, more ballpark areas, if anyone's got any tips.
>

This might well be a case where an outline would be of value. Can you create an outline on the old server? Alternatively ... create a new one.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)