| Oracle 8/9/10 performance problem [message #65817] |
Tue, 04 January 2005 06:10  |
Glyn Dobson
Messages: 1
Registered: January 2005
|
Junior Member |
|
|
I am having very big problems trying to test a piece of software that heavily relies on an Oracle Database. The last version of the software was working OK, but now it is very slow and, at sometimes, completely unresponsive. It seems that the database it just taking a very long time replying to adds/deletes/query's.
Is there any way I can somehow put the database into 'debug' mode where it will create a log file of what it is doing so I can see why its taking so long?
I am very new to Oracle, so i'm sorry if my question is very simple - but nobody at work seems to be able to help. We have Oracle 8i, 9i and 10g - so advice on any of these databases would be very much appreciated.
|
|
|
|
| Re: Oracle 8/9/10 performance problem [message #65820 is a reply to message #65817] |
Tue, 04 January 2005 20:27  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Better take a look at the documentation; tuning is not something you do on a rainy wednesday afternoon.
In short there are a couple of things you can do:
- Enable session tracing: Start a session in your application. Find out the sid and serial for your session and use dbms_system.set_sql_trace_in_session for that session to true. Now all statements executed from this session are written to a trace-file, along with the time needed to parse, execute and fetch. Also the needed logical and fysical I/O are stored.
You can find this file in the user-dump directory. To find out where that is give the following command in sqlplus: show parameter user_dump_dest.
Close your session. Go to the directory (on the db-server) indicated by the user_dump_dest and look for your trace-file. This tracefile can be formatted using tkprof. This is a standard Oracle tool:
"tkprof <<z>tracefile> <<z>outputfile> sys=no sort=prsela,fchela exeela"
This will sort the file and put the statements that took the longest time on top.
- Secondly, you can use Statspack. This will monitor your entire database instead of just 1 session. More info will be stored than by tracing. You can find a short explanation of statspack here
Documentation on tuning found here and here
Good luck. If you have any further questions, don't hesitate to ask here.
hth
|
|
|
|
| Re: Oracle 8/9/10 performance problem [message #65822 is a reply to message #65817] |
Tue, 04 January 2005 22:24 |
Shimon Tourgeman
Messages: 2
Registered: December 2004
|
Junior Member |
|
|
Hi,
sounds like your database and app needs some tuning.
Oracle support many tools and options to see exactly what is happening while your appliation is running.
You shold be more specific: what DB version, what OS, box spex (CPUs, RAM), storage devices, network architecture.
In general, I would check:
1. SGA memoray allocations.
2. SGA buffer cache hit ratios - small values indicates problems in SGA allocations.
3. optimizer mode
4. collect optimizer statistics on tables and indexes.
5. tune problematic SQL statements or critical statements.
and many more.
Its hard to say more before seeing your actual database performance indicators.
I suggets you use Oracle Enterprise Manager --> Performance Manager tool to spot specific problems, SQL's, buffer allocations, etc.
It should be installed already if you did the default installation of your database server.
You just log in in "Stand Alone" mode, connect to your database as 'system', and click the "Diagnostic Pack" icons in the left part of the screen, and then choose the "Performance Manager" tool.
Or just ask your DBA to tune the database - the easist way for you...
Good Luck
Shimon Tourgeman, DBA.
Israel.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
