Re: query tuning

Bdesatnik wrote:
>
> I am trying to tune a query containing a 3 table join and am having some
> difficulty. Some background on the query and tables. Database version
> 7.3.2.3. Table 1 has approximately 30,000 rows, table 2 has 4,000,000 rows,
> table 3 has 7,000,000 rows (this is the order in the query). The two large
> tables are actually partition views, consisting of approx. 14 partitions
> each. We are also utilizing parallel query as well as hash join features.
> The appropriate init.ora parameters are set for each of these features.
> With no hints, the explain plan shows that a hash join is first being
> performed on the 7,000,000 row table and a second hash join between the
> 30,000 row table and the 4,000,000 row table. It seems to me that the
> better plan would be to hash the 30,000 row and 4,000,000 row tables and
> then hash that result with the 7,000,000 row table. I have added various
> use_hash hints and cannot seem to get this resulting plan. Using nested
> loops join takes approximately 2 minutes - using the above hash join takes
> over 8 minutes.
>
> I've seen lots of examples of the use_hash hint on 2 table joins, but have
> not seen any with more than 2 tables. Does anyone have any experience with
> this? I'm not real clear on which tables get included in the use_hash hint.
> Any assistance would be greatly appreciated. Thanks in advance.

You may be doing some of these already, but just in case.

Add a "ORDERED" to the hint to force the database to join the tables in the order specified in your FROM clause. Note that you should determine the order of the tables, not just based on the TOTAL number of rows in the tables, but based on the number of rows RESULTING from each table on the basis of your WHERE clause.

Remember that a HASH join adds a lot of overhead. So, use it sparingly depending on other processes running at the same time, etc.

A "USE_MERGE" would be better in place of a "USE_NL" where the number of rows involved is very high. A NL will loop thru each and every row.

Make sure that all the tables involved are being joined to each other, else you are doing a cartesian join, which will be a killer with such large tables.

Last, but not the least, play around with your WHERE clause to try and add fields that may help filter out more rows.

A combination of all the above with help from Explain Plan and sql_trace should help you out. Refer to "Server Tuning" manual for more tuning help. Good luck,

-Ratan Received on Thu Sep 25 1997 - 00:00:00 CDT