| Performace of the Views.Please HELP URGENT!!!!!!!!!!!! [message #374217] |
Wed, 30 May 2001 15:53  |
Sue
Messages: 49
Registered: May 2000
|
Member |
|
|
There are some views in our system that are very slow and bcos of this our sysytem has become so slow that Client is screaming on us .Could any one of you please help me .The views select the data from different tables.Please Help me Please;.
Note:it is not datawarhousing
Thanks,
Sue
|
|
|
|
| Re: Performace of the Views.Please HELP URGENT!!!!!!!!!!!! [message #374218 is a reply to message #374217] |
Wed, 30 May 2001 16:06  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Sue
1.) Make sure all your tables referenced by the views are freshly analyzed.
2.) Ensure that the underlying SQL of a slow view still runs fast in sqlplus.
3.) Avoid any unnecessary clauses like "distinct" in the views unless it's really required. Also use "union all" rather than "union" where possible in views.
4.) Rebuild indexes which have heavy update/delete/inserts. "alter index ABC rebuild tablespace XYZ;"
5.) Re-org the table too to reclaim unused space if you think this could be a table (e.g. a table with only a small number of rows compared to the peak it has held at some time in the past).
When a view has group clauses or anything else (like "distinct") the "where" caluse applied to the view can't be merged with the underlying SQL in the view. Usually Oracle tries to merge the SQL to provide an efficient statement.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
