Home » RDBMS Server » Performance Tuning » How to tune a view using 3 big tables
How to tune a view using 3 big tables [message #316019] Wed, 23 April 2008 09:03 Go to next message
goodmans
Messages: 6
Registered: April 2008
Junior Member
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 cardinality 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 more than 18 hrs.

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

Thanks in advance.

Goodman
Re: How to tune a view using 3 big tables [message #316023 is a reply to message #316019] Wed, 23 April 2008 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
Which of the suggestions in URL have you followed & what were the results?
How to tune a view using 3 big tables [message #316024 is a reply to message #316019] Wed, 23 April 2008 09:06 Go to previous messageGo to next message
goodmans
Messages: 6
Registered: April 2008
Junior Member
Is that all I get from you people. I guess I better read all oracle books instead of coming to this website lol.

Cheers

[Updated on: Wed, 23 April 2008 10:34]

Report message to a moderator

Re: How to tune a view using 3 big tables [message #316135 is a reply to message #316019] Wed, 23 April 2008 19:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I was thinking of helping. Was going to suggest you offer more info like:

describes of the tables
indexes on these tables
view text
sql queries run against the view
query execution plan of said queries

And then I and others can look at it.

But with a bad attitude like you got, I won't suggest this.

Kevin
Re: How to tune a view using 3 big tables [message #316138 is a reply to message #316019] Wed, 23 April 2008 22:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Is that all I get from you people.
With free advice, you get what you paid for it.

If you don't like the timeliness, completeness or correctness of any response, you are entitled to a full 100% refund.
Please submit your claim to /dev/null.
Re: How to tune a view using 3 big tables [message #316336 is a reply to message #316135] Thu, 24 April 2008 12:13 Go to previous messageGo to next message
goodmans
Messages: 6
Registered: April 2008
Junior Member
Sorry mate, somebody gave the link to go to oracle complete help. So I am surprized and got tired of this pressure at my work and had to talk like that. I am extreamly sorry for what have i done. I am really sorry mate. Please forgive me.

Thanks
Goodmans
Re: How to tune a view using 3 big tables [message #316606 is a reply to message #316135] Sat, 26 April 2008 07:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Kevin Meade wrote on Thu, 24 April 2008 10:41
I was thinking of helping. Was going to suggest you offer more info like:

describes of the tables
indexes on these tables
view text
sql queries run against the view
query execution plan of said queries

And then I and others can look at it.

But with a bad attitude like you got, I won't suggest this.

Kevin

So, you're still working on this, right?
Re: How to tune a view using 3 big tables [message #316648 is a reply to message #316019] Sat, 26 April 2008 21:56 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Three things to look at:

1) How is the view being used? Are you doing a SELECT * FROM VIEW (view should make no difference) or are you doing SELECT ... FROM VIEW JOIN SOMETHING ELSE WHERE ... (many, many predicates). In the latter case, things are much more complicated.

2) Are you 9i or 10g? Hints work differently in the different versions.

3) There is no undocumented init.ora parameter of the form "_RUN_VIEWS=fast".
Re: How to tune a view using 3 big tables [message #316770 is a reply to message #316019] Mon, 28 April 2008 05:04 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

Can you post the view in question? Also post indexes on each table with columns (in sequence).

Regards,

MSMallya
Re: How to tune a view using 3 big tables [message #318261 is a reply to message #316770] Tue, 06 May 2008 04:52 Go to previous messageGo to next message
goodmans
Messages: 6
Registered: April 2008
Junior Member
Here i am giving the oracle view text, We are working on 10g now.
View,
create or replace view v1
select /*+ parallel(a,6) parallel(b,6) */
a.col1, a.col2, b.col2, b.col3
from a_table a, b_table b
where
a.key=b.key and
a.country=b.country

Querying the view:
select v1.col1, v1.col2, v1.col3 from v1
where v1.col1=10

Table is partitioned on column "country"
We got index on composite key (key, country) on both tables
Tables are analyzed, indexes are analyzed
Size of tables is around 10million each

Query is performing better after introducing parallel hints, but still slow.
Is there any thing else I can add to improve the performance more.

[Updated on: Tue, 06 May 2008 05:07]

Report message to a moderator

Re: How to tune a view using 3 big tables [message #318266 is a reply to message #318261] Tue, 06 May 2008 05:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What Explain Plan do you get for this query?

How many rows in a_table or b_table match the COL1 = 10 restriction?

The fact that the Parallel hint are making a difference suggests to me that you've got at least one full table scan going on in there.
Re: How to tune a view using 3 big tables [message #318315 is a reply to message #318266] Tue, 06 May 2008 08:27 Go to previous messageGo to next message
goodmans
Messages: 6
Registered: April 2008
Junior Member
col1=10 is a load id

It contains LOAD ID.
We have normal index on it. Every day will have a incremented load id.

The cordinality varies. One day might get 1 million the other day 500 thousands. In the bulk we got 10 millions for the first load.

Unfortunately i dont have rights to explain plan on this table because i got only read permission.

But when i tried to push the sql to use indexes by INDEX Hint the query is more slow than normal run.

Thanks

[Updated on: Tue, 06 May 2008 08:34]

Report message to a moderator

Re: How to tune a view using 3 big tables [message #318316 is a reply to message #318315] Tue, 06 May 2008 08:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you can't get us an Explain Plan, then we can't tell you what your query is doing behind the scenes, and we can only make educated guesses about why it might be running slowly.

My guess is that you are retrieving enough rows for the CBO to reckon that it's quicker to do a FTS rather than to read all the records individually.

Possibly Hash Clusters might be able to help you a bit.
Partitioning by Load_Id would be a good idea too.
Re: How to tune a view using 3 big tables [message #318348 is a reply to message #318316] Tue, 06 May 2008 09:33 Go to previous message
goodmans
Messages: 6
Registered: April 2008
Junior Member
Thanks for all your suggessions.

I will try to get the explain plan for the query ASAP.

Thanks
Previous Topic: Perfomance Tuning
Next Topic: Oracle Compression Question
Goto Forum:
  


Current Time: Thu Dec 08 04:13:47 CST 2016

Total time taken to generate the page: 0.08556 seconds