Oracle 8i performance question - a cry for help!

[Pyrophos]

Originally posted by cjolley
Unfortunately, fixing an app that doesn't use bind variables can involve something very close to a re-write.
Binds are something that need to be planed for in the design stage.
chuck

Depending on the application, language and the number of SQL-statements, it might not be as big problem as it sounds (been there done that...). But it is mechanical, repetitive and dull work and thus suspect to errors. Hope they have a good QA.

The easiest approach is to find the most common SQL-statements and refactor those... helps a lot, and doesn't take more than 10-15 minutes per statement at most.

Or upgrade to 9i and hope that everything works...

Anyway, applications, whose internals are not in your control, are bit nasty in that usually the only options of tuning them is to tune the hardware / OS / DBMS... and the biggest gains of tuning usually come from application tuning.

[cjolley]

Originally posted by Pyrophos
Beginning from Oracle 9i or 9i SE (can't remember which) there is option in the SQL-engine, which can automatically convert literals to bind variables thus helping with some of the parse problems.

That's what CURSOR_SHARING=FORCE starting with 8.1.6 does
(or for 9i you can also use "SIMILAR")

A problem is that this can remove some information that the optimizer can use to speed up queries.

For example:

select *
  from t
where status = 1;

The optimizer might use an index on status if the statistics say it would benifit.

but if the engine rewrites the query as:

select *
  from t
where status = :system_bind_variable_1;

Statistics won't do the optimizer much good in predicting the cost of using the index on status if only two rows have a status of 1 and 50,000,000 have a status of 2.
Then the re-write might cause the query to run slower!
That's why it is better to pick your own bind variables.
Though it would solve the parse problem for that query.

On your second point.
It might or might not be the case that it is that easy.
Depends on how their dynamic sql is generated.
Chuck

[RedRed]

I have changed the cursor_sharing to FORCE (Oracle 8i - it was EXACT) - I havent heard of any problems in DEV....... I understand that the CBO might 'get it wrong' after the FORCE is enabled, however, I think that the gains on the PARSE to EXEC ratio improvements (If the optimisation is sucessful) should more than ofset this - at this time anyway.....

The core search screens were written for Oracle 7, and migrated, I have suggested that they optimise these forst (our search client screen is exactly the same, irrespective of the application are...) When these are sorted, I think (fairly sure) that this screen acounts for about 5% of all the SQL generated by users..... The Caseload management sections I have requested next - as these are known to be 'slow' at remote bases.... (But I recon that is at least partly because the data returned to the client is 'too much' at the initial drill down.....) I have acquired a tool to check this....

[Pyrophos]

Originally posted by cjolley
A problem is that this can remove some information that the optimizer can use to speed up queries.

AFAIK, the reason why cursor sharing feature has not been so "hot" is that there are about gazillion SQL-queries, which trick the rule-based optimizer to use indexes or ignore indexes with constant variables. But it seems that with Oracle10 they are finally getting rid of the rule-based optimizer, so they can now enable the feature. Have to wonder, though, how they are going to handle data-dictionary queries...

On your second point.
It might or might not be the case that it is that easy.
Depends on how their dynamic sql is generated.

Certainly. But most of the dynamic SQL I have seen is often "SELECT * FROM customer WHERE customer_id = " + variable.

Then again, I have seen some reporting tools and few Oracle Forms-applications which do REALLY weird dynamic SQL...

[cjolley]

Sorry, been out of town a few days...

Red,
Be sure that they gather statistics for the CBO.
We do a full compute on all columns once a week, but that might not be best for your system.

We had vurtualy no problems going from rule to cost (7.3-8i)because our problematic sql was already hinted.
However once we started gathering histograms on our data we were able to get rid of the hints too.

Pyrophos,
Nobody but Oracle it's self (APPS ) has used the RULE optimizer for years
Good question about the data-dictionary queries.

chuck