Re: Help with suddenly long running query, 7.3.4.3/AIX

From: hhenjum <hhenjum_at_pclink.com>
Date: Thu, 4 Mar 1999 01:06:32 -0600
Message-ID: <7blca3$1ola@enews2.newsguy.com>

I believe the portion that is killing your query is the line in the tkprof output
where the rows column = 49024673. Also your query is fetching over 6million blocks
in order to get 27 rows.

I do not have a lot of experience tuning SQL, but we have been having some of
these same types of problems. I might try analyzing the indexes using ANALYZE INDEX name VALIDATE STRUCTURE. Also, you may want to try various hints (FIRST_ROWS, ALL_ROWS, RULE, maybe some join hints like HASH (table_name), MERGE (table_name) ) and then compare the tkprof output.

Also, double check the all_tables column num_rows for the PSTREESELECT06 table,
and make sure that it is not much smaller than the actual number of rows in the table.
There may have been a bunch of rows added since it was last analyzed?

Good Luck, the cost based optimizer is a challenge sometimes!

Heath Received on Thu Mar 04 1999 - 01:06:32 CST