Home » RDBMS Server » Performance Tuning » Some basic tuning steps
Some basic tuning steps [message #234121] Mon, 30 April 2007 01:48 Go to next message
kesevan
Messages: 17
Registered: April 2007
Location: MUMABI
Junior Member

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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: merge join and hash join
Next Topic: Partitioning
Goto Forum:
  


Current Time: Sat Dec 07 00:15:22 CST 2024