Oracle-L: RE: Stats on partitioned table

From: Bala, Prakash <prakash.bala_at_hp.com>
Date: Mon, 24 Nov 2003 13:09:26 -0800
Message-ID: <F001.005D7A3B.20031124130926@fatcity.com>

The 'explain plan' was obviously different because of the presence of statistics. The amount of data in both tables were the same.

After doing a lot of tests and reading, found a better way to get the results faster. Instead of 'analyze' , am currently using 'dbms_stats.gather_table_stats' with the option "method_opt=>'for all indexed columns size 2'"

Thanks for your input!

-----Original Message-----
rpr
Sent: Monday, November 24, 2003 12:29
To: Multiple recipients of list ORACLE-L

Hi,

Did you look at the explain plans in both places?
What the difference in size of data?
How many times did you run the test?
Did you try giving the *RULE* hint and see the timing?

"Bala, Prakash" <prakash.bala_at_hp.com> wrote:

Hello,

Data-warehouse application using Oracle 8.1.7.4 on HP-UX 11i

Since our ETL process is running for a long time, have been given the task of partitioning some of huge tables and check the results. So picked the top 5 sessions (in terms of running time) and got the sql used by Informatica.

Test results were excellent: 48 minutes against the original table vs 15 seconds on the partitioned table to select about 4000 rows from a 5 million row table. The test was done using a separate schema on the same production database.

However, after implementing the same in the production schema, the same query is taking about 100 seconds.

Automatic_parallel_tuning is set to TRUE. Parallelism is set to 5 at the table level.

The only diference that I can see is that the table in production schema is analyzed (using analyze table ... estimate statistics 20 percent) whereas my test table was not analyzed.

Would appeciate any input.

TIA Prakash

Do you Yahoo!?
Free <http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/> Pop-Up Blocker - Get it now

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Bala, Prakash
INET: prakash.bala_at_hp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Nov 24 2003 - 15:09:26 CST