| How to tune a view using 3 big tables [message #316019] |
Wed, 23 April 2008 09:03  |
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 #316336 is a reply to message #316135] |
Thu, 24 April 2008 12:13  |
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 |
rleishman
Messages: 3728
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 |
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 #318261 is a reply to message #316770] |
Tue, 06 May 2008 04:52 |
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 |
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 |
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 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
