We have performance problem in Database. SQL queries taking too much time. I found that some users are created under SYSTEM tablespace. Table Data and Index are in the same drive. I don't know where I have to tune the database either in DB Buffer or Shared memory etc., Can anyone help me please?
The normal sequence of tuning the database is
D = Design
A = Application
M = Memory
I = IO
C = Contention
O = OS
Now since your application is already deployed so there is no chance of design tuning.
Under the current situation which u r in, what I feel is that you should consider reducing the IO and Contention first.
Try isolating the SYSTEM tablespace. Alter user SYSTEM and change its default tablespace to TOOLS and temporary tablespace to TEMP.
Create separate Tablespaces for different objects and place them there. You should follow the OFA in this respect. You can separate the indexes already created by
alter index rebuild
Go thru the OFA Layout plan and try implementing that.
Once you have done that, go for application tuning i.e SQL, procedures etc.
If you need even more performance enhancement then go for Memory (SGA) and OS.
Click Here to Expand Forum to Full Width