Sql tuning? any Issue?

[bensmail]

Hi,

I develop some SQL queries for a client in a development environment.
I should optimize the queries before to roll out, by run it in traceonly mode in production environment.
For each query, I write 10 comparables queries and I'm waiting for results. How can I choose between differents explain plan?



DB: Oracle 9i
OS: Solaris 10
DB Size: 200 Tera Bytes

[hrishy]

Hi

You need to test your sql against production data to be sure that your sql's dont have a performance problem.

By looking at the plan even seasoned professionals cannot tell which plan is better

regards
Hrishy

[PAVB]

200TB?... is that a Data Warehouse?

I understand you are tracing the queries in production so...

1- Look at Explain Plan just to sort out pretty bad stuff lke...
--- Detect FTS on very large tables
--- Detect expected star-transformations that are not happening.

2- Look at Wait Events, that's the whole truth.

[bensmail]

Yes it's a big datawarehouse.
I'm not allowed to run the query on production database but I can do explain plan only on it without runing.
How can I collect wait event or trace file for such a query?
How can I decide with plan is the best for the query?

Bensmail

[malay_biswal]

The not much you can do except picking up the lowest cost SQL.

[gandolf989]

The not much you can do except picking up the lowest cost SQL.

The SQL cost numbers are not comparable across two queries, even two versions of the same query. All you can do is make sure you have the right schema and partitioning set up on the database. You want to design a schema so that it will be easy to write good SQL. If it is me I would verify that all foreign keys have a foreign key constraint and an index on the foreign key column. If that isn't the case you might get locking problems when you do updates or inserts.

If your data warehouse is really 200 Terabytes, then you need a test system with all the same data, even if it runs on lesser hardware. You should also look into compressing some of the larger tables, especially tables that don't get updated very often.

[malay_biswal]

e SQL cost numbers are not comparable across two queries, even two versions of the same query

That's absolutely correct, but you got 10 sql all give same result(that's my assumption). If that's correct it's always better to pick lower cost number SQL. However low cost does not guarantee execution time will also less.

[PAVB]

I'm not allowed to run the query on production database...

Do you have any sort of UAT environment? Can you replicate in UAT a subset of whatever you are hittting with your query?

Experience says your query would be hitting "some" partitions of large tables and "some" small tables. May be you can refresh those objects from Prod into UAT... that would help a little and let you have a feeling about what to expect on Prod.

[BV1963]

Extract statistics from Prod and import it into database where you can test. It will give you an idea about what oracle is going to do in production

[bensmail]

Hi all,

how can I explain the plan of my different queries without run them on the production database?
I need only execution plan.
Lower cost don't mean less execution time, so I understand.

How can I select from the differents execution plan scenarios the right one.
I'm not allowed to run the query on production environment.

Regards
Bensmail