Performance Related Issue

[vishal sood]

Hi all

I have joined a new company as DBA. They have given me the very first task i.e. performance tuning.

The database speed is very slow and some times when they prees save button the systems gets hanged(goes into working mode), no responce received back, after 30-40 min. it came out of working mode.

I did following to increase the speed.

1. Checked the Gethitratios and found ok.
2. Indexed Rbuilted.
3. Seperated the tablespaces of tables & indexes.
4. Set the parameter db_block_checking to false.
5. The Parameter DB_block_checksum is true.
6. The datafiles, & redolog files are on same disk. I want to split datafiles & redolog files on different disks but due to space problem/and companies constraints not able to do so.
7. Coalsced the Tablespace.
8. Deallocated unused space of indexes & tables.
8. The log file size is 30 MB.

Kindly suggest what other steps I should take to increase the performance.

[vishal sood]

one more thing to add in above

we are using SUN OS with Oracle 9i.

[hrishy]

Hi Vishal

Congrajulations on your new job.You need to trap the sql statement the save button is eexcuting..run it from sql plus and have a look at the explain plan ..sql tuning is what you need in most cases..is the entire database slow ? or only a few screens ?

regards
Hrishy

[vishal sood]

Otherwise the database speed is quite ok. But when user press save button on any screen then the performance goes down.

The compnay is having 20 branches all over in different cities and connected through citrix server.

I had a talk with system administrator and network administrator regarding this issue. The Network speed and cpu settings may effect the performance.

Any other suggestions to increase the performance.?

[hrishy]

Hi Vishal

What sql is being excuted when the users presses the save button..You need to take that sql and trace the sql performance from sqlplus..thats the way i do it..Have a look at the geenrated plan ..

I suspect the problem is with the SQL ..

regards
Hrishy

[hacketta1]

I would suggest that the quicket way of tracking down the problems is to ask a user to get to a point where they're ready to press the save button. Next, identify their session using the following query:

SELECT s.status, s.osuser, s.username, s.sid,s.serial#,
p.spid, s.terminal
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.username = '&username';

Then, still in SQL*Plus:

alter system set timed_statistics = true;
alter system set max_dump_file_size = unlimited;
oradebug setospid ;
oradebug unlimit;
oradebug event 10046 trace name context forever, level 12;

Ask the user to hit the save button, and then when the save finally completes turn of the trace:

oradebug event 10046 trace name context off;

You can use Trace Analyzer to format the 10046 trace file; download from MetaLink


here . In the resulting output from trace analyzer you should quickly be able to tell what waits are causing the delays your users are experiencing.

HTH

Austin

[hacketta1]

Just relised my mistake above:

oradebug setospid;

Should read:

ordebug setospid the_spid_returned_by_the_query

[pando]

how about some statspack snaps?