RE: Stats on partitioned table

[Bala, Prakash]

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-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of rpr
Sent: Monday, November 24, 2003 12:29
To: Multiple recipients of list ORACLE-L
Subject: Re: Stats on partitioned table

Hi,

  1) Did you look at the explain plans in both places?

2) What the difference in size of data?

3) How many times did you run the test?

4) Did you try giving the *RULE* hint and see the timing?

"Bala, Prakash" <[EMAIL PROTECTED]> 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 Pop-Up Blocker - Get it now