Some basic tuning steps [message #234121] |
Mon, 30 April 2007 01:48 |
|
Dear Sir / Madam,
i need to find out the following :
1) for a patricular TABLE / TABLESPACE.....i need to check how much size is used, how much is free
and ..whether they reach the maximum size ? so that we can increase the size of tabelspace..
IS THERE ANY SCRIPT TO FIND OUT THIS ?
2) How to check the index fragmentation ?
IS THERE ANY SCRIPT TO FIND OUT THIS ?
WHAT IS THE SOLUTION TO SORT OUT THIS INDEX FRAGMENTAION ?
3) what is the frequency of analyzing the tables and indexes ?
ie. every week it has to be analyzed ? or on daily basis it has to be analyzed ?
Kindly guide me ....
what is the method to anlayze the tables and indexes ?
IS THERE ANY SCRIPT AVAILABLE, IF YES, PLEASE PROVIDE ME.
4) IN MY PRODUCTION DATABASE, VERY PATRICULARLY ONE PROCEDURE IS RUNNING VERY SLOW...
what would be the reason behind ?
shall i use the below ...?
sql> execute dbms_shared_pool.keep('<procedure name >', 'p');
whether this will solve the problem ? if not, please provide the alternate to improve
the efficiency of one patricular PROCEDURE / FUNCTION/ TRIGGER....
5) IN MY DEVELOPMENT DATABASE, THERE ARE SOME SQL STATEMENTS RUNNING VERY SLOW.
i want to know how to write the sql statement efficiently ?
is there any pre-defined rules to make one sql efficient ?
Memory concerned , which area or parameters can be set to good value to improve the query
response time ?
as of now, one query takes just 7 seconds in test database.. but it taks 55 seconds
in development database.....
please guide me.
thank you
kesavan.
|
|
|
Re: Some basic tuning steps [message #234125 is a reply to message #234121] |
Mon, 30 April 2007 02:01 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
1/ dba_segments, dba_free_space, dba_data_files
2/ What is index fragmentation?
3/ Variable
4/ Many reasons, almost infinite
5/ Read, test, read, test, read, test, read, test, read, test, read, test
Regards
Michel
|
|
|
Re: Some basic tuning steps [message #234212 is a reply to message #234121] |
Mon, 30 April 2007 08:19 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
Just to add,
for all questions, search this forum/google/docset.
These are very generic quesitions (as already kindly pointed by Michel) which are asked and answered numerous times.
|
|
|