How to tune a view using 3 big tables

[goodmans]

Hi Oracle Gurus.

I have a performance related problem here. I have 3 big tables. (15 million, 14 million, 8 million)records.

I got a view joining these three tables and we have indexes on the columns which are used for join. And the tables have been analyzed using old "ANALYZE TABLE STATEMENT" ( Sample Size = 25%,25%,100%)

I can see few things are missing
a) View is not using any hints like parallel or pushing indexes if they are not being used due to cordinality range.
b) New DBMS package's gather STATS function is not used to analyze these tables.

But the tables are partitioned and join columns are indexes.

What else can be added to make this view perform better. Right now its giving only 64 records read throughput per second. And the data transfer is taking morethan 18 hrs.

I know 1 thing might cause some proble that like network problems, other than it can you advise any tips to make it fast Please.

Thanks in advance.

Goodman

 

[Thargy]

The first port of call must be to gather statistics. If you don't do that the CBO has no chance of coming up with a good plan.

Run a timing test and record the times.
Gather stats and re-run the test.
Any difference?

Regards

T

 

[DrD123]

Hi Goodman,

Taking T's suggestion a little further, use on the tables:

  "dbms_stats.gather_index_stats" & "dbms_stats.gather_table_stats"

and/or use, on the whole schema:

"dbms_stats.gather_schema_stats"

Good Luck
DrD

 

[goodmans]

Thanks people.

Query started performing better now.
I have done the following things.

I have gathered stats on table
Rebuilded the indexes
Added parallel hints in the query.

Thank you very much for the help.

Goodmans

 

[tekdudedude]

Hitting this at a lower level...

If you have more than 10gb of data Big File Tablespaces (BFTS) can be effective. Also, make sure you are using Locally Managed Tablespaces (LMTS). On this topic, check for ancient and outdated Oracle practices on the creation of DB, TS and Tables.

If Windows you may wish to routinely defragment your disk(s).

You said you are using partitions. I hope you know using partitions is NOT like setting FASTER=TRUE in all cases. You may wish to test using local indexes on your partitions rather than a global one.

How efficient is your PGA? Check this too.

Good luck,

TD