| optimization of query [message #52223] |
Tue, 09 July 2002 21:49  |
santosh
Messages: 85
Registered: October 2000
|
Member |
|
|
Hello Friends,
I got a serious problem. I am dealing with some tables with huge amount of data into them.Above 50,00,000 or so.
Now because of huge data , even simple queries like 'select count(colname) from table' takes hell lot of time about 20 minutes or so. And i need to run my procedures and functions on these tables which frequently access different data with different criterias. I am afraid if it will take more than a day time to execute my single procedure or function .
I havent done oracle dba which includes optimization . Can anybody plz suggest me what measures i should take to reduce the time for execution of queries on these tables? One point to consider here is that there is no lobs like huge data columns in the table and also there is no constraints or triggers defined yet but in future they must have to be applied on the tables
If someone suggest to do modification setting in database itself like datablock size,tablespace or so i will try it too.
Thnaking you
Santy
|
|
|
|
| Re: optimization of query [message #52229 is a reply to message #52223] |
Wed, 10 July 2002 04:15  |
sai sreenivas jeedigunta
Messages: 370
Registered: November 2001
|
Senior Member |
|
|
hi santy,
download the tuning guide from ORacle technet.
1.Check wether the indexes are rightly maintained.
2. u can change your Rule based optimizer mode to cost based optimizer by setting the Parameter in init.ora / by using the Hint as
select .. /* First_Rows(
) from
;
3. these two will itself will enhance the performance to some extent...
4. We can furthur enhance the performace by checking the Explain plan & Trace File generated by SQL TRACE Optioons output.
Good Luck..
Sai
|
|
|
|
| Re: optimization of query [message #52231 is a reply to message #52223] |
Wed, 10 July 2002 04:28 |
sai sreenivas jeedigunta
Messages: 370
Registered: November 2001
|
Senior Member |
|
|
hi santy,
download the tuning guide from ORacle technet.
1.Check wether the indexes are rightly maintained.
2. u can change your Rule based optimizer mode to cost based optimizer by setting the Parameter in init.ora / by using the Hint as
select .. /* First_Rows(
) from
;
3. these two will itself will enhance the performance to some extent...
4. We can furthur enhance the performace by checking the Explain plan & Trace File generated by SQL TRACE Optioons output.
Good Luck..
Sai
|
|
|
|
| Re: optimization of query [message #52247 is a reply to message #52223] |
Wed, 10 July 2002 20:56 |
santosh
Messages: 85
Registered: October 2000
|
Member |
|
|
Hi Friends,
I first of all thank to Mr.Mahesh and Mr.Sai for providing their kind attention towards my problem.
I tried the way by increasing the temporary segment and sort_area_size which reduced my time to some extent.
Regarding the indexes i afraid if i create them they will occupy lots of memory cause data is huge. As far as my knowledge goes indexes are maintained in index tablespace which is having less available storage capacity at present.
But does it cause extra overhead on server if we maintain many indexes based on our queries?
Which type of index is most suitable for faster query execution . I would be happy for these clarification.
Thanking you
Santy
|
|
|
|
| Add on [message #52278 is a reply to message #52223] |
Fri, 12 July 2002 02:15 |
B
Messages: 327
Registered: August 1999
|
Senior Member |
|
|
Hi,
You can also redesign your application ... I mean build partition on indexes and tables, separate them on disk. Loading frequently used tables into cache.
use hash_join enable and hash_area_size ( use only when you query 2 tables or more in HASH_JOIN mode )
Because the sort_area_size is set for all users, you can grant "alter session" privilege to the user who run the query. And this user can nonw define its own sort_area and save A LOT OF MEMORY.
|
|
|
|
| Add on [message #52280 is a reply to message #52223] |
Fri, 12 July 2002 02:17 |
B
Messages: 327
Registered: August 1999
|
Senior Member |
|
|
Hi,
You can also redesign your application ... I mean build partition on indexes and tables, separate them on disk. Loading frequently used tables into cache.
use hash_join enable and hash_area_size ( use only when you query 2 tables or more in HASH_JOIN mode )
Because the sort_area_size is set for all users, you can grant "alter session" privilege to the user who run the query. And this user can nonw define its own sort_area and save A LOT OF MEMORY.
|
|
|
|
| Add-on [message #52282 is a reply to message #52223] |
Fri, 12 July 2002 02:28 |
B
Messages: 327
Registered: August 1999
|
Senior Member |
|
|
Hi,
You can redisign your application. I mean use partition on indexes and tables ( use the correct columns for patitionning ). Separate them on disk.
Split table who contains LONG or LOB into two tables. Example you have a table like TABLE( NAME varchar, IMAGES BLOB ) and you query often NAME, split the tables into TABLE_1 ( NAME , KEY ( PRIMARY ) ) and TABLE_2 ( KEY( FOREIGN UNIQUE ), IMAGES ). Use sql_trace and tkprof to analyze the request ,or explain plan, or autotrace.
If you join frequently some tables you can use hash_join=enable and hash_area_size in init.ora.
In init.ora sort_area_size is defined for all users. So you can grant "alter session" to one user ( who runs the query ) and then he can define his own sort_area_size and save a lot of memory.
There are a lot of methods like data caching ... but there is no easy way to say change this parameter or not because it's specific to your application / system and way to use them.
( sorry for my poor english ... thanks..)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
