need more hardware?

[Tim Krego_2]

Problem: Oracle 8.1.6 package consumes 100% CPU and times out.

System
------
K460
180MHz CPU
2GB RAM
AutoRAID 12H
Oracle 8.1.6.0
WebDB 2.2

1. Is a single 180Mhz CPU a bottleneck? Seems to me RAM and Disk i/o are bigger needs for a database.

2. Everything points to bad SQL as first thing to check when having Oralce performance issues.

Development database was running on K460 with 2GB and (2) 180MHz CPU's. When moved to production system with 2GB and (1) 180MHz CPU we get a CPU spike at 100% when running a Oracle package. The web browser will time out after a few minutes.

My experience tells me the CPU is adequate for the database, or it has been, and that the package is the problem. The good part is that the problem can be duplicated so it isn't intermittent. The bad part is that I can see the CPU spike, but don't know how to drill down to find the problem.

We have used Explain_Plan to look at the query, but that is of no help.

What other performance tools do people use to debug Oracle issues?

[Patrick Wallek]

How many users are trying to access this in production? This set up does sound pretty small to me. I think I'd want at least 4 CPUs and 4 GB of RAM to start with for an app like this.

A good tool to use for diagnosing problems like this is Glance. If you don't have it, you can install a 90-day (I think) trial version from you latest applications CD-ROM set, I believe from CD #1. Glance will allow you to see which process or processes are using all the CPU.

Good luck.

[James R. Ferguson]

Hi Tim:

'glance' usually proves to be invaluable. If you don't have it licensed, load a trial version from the Application CDROM. There is built-in help on every screen's metrics. Toggle help with 'h'. To see screens available, type "?".

Regards!

...JRF...

[Michael Steele_2]

You can determine if Oracle is a problem by monitoring activity of DBWR process from top. The DBWR refreshes the SGA, or, Oracle instance buffer, only when there is too little free space in the database buffers.

For each instance there is an SGA which can be defined within the INIT.ORA file.

To correct this, increase the SGA. If you can't, add more RAM so you can. There is also the UGA or User Global Area which is apart of either the large pool or the shared pool. Look at v$sesstat and v$statname for these metrics and increase the large pool or shared pool to obtain better user performance.

From HP, use 'sar -b' in conjunction with sizing kernel parameters 'dbc_min' and 'dbc_max.' You want 100% hits within the cache.

For HW bottlenecks use:

sar -u 5 5
sar -d 5 5
vmstat 5 5
sar -v 5 5
swapinfo -tam

[Paul Sperry]

Did you tune the kernel as reconmemded by oracel?

2-2 Oracle8i Installation Guide
Table 2???1 summarizes the requirements for installing Oracle8i on your HP-UX
system. If your system fails to satisfy any listed requirement, perform the tasks
listed on page 2-4 as necessary to configure your system to meet these requirements.
Table 2???1 UNIX System Configuration Summary
System Factor Requirement for Oracle8i
UNIX Kernel Parameters:
Shared Memory
Use the System Administrator???s Menu (SAM) to configure the
HP-UX kernel with the minimum recommended values.
SHMMAX 1 GB
This setting does not affect how much
shared memory is needed or used by
Oracle8i or the operating system. It is
used only to indicate the maximum
allowable size. This setting also does not
impact operating system kernel resources.
SHMMIN 1
SHMMNI 100
SHMSEG 10
Semaphores SEMMNI 70
Set the number of semaphore set
identifiers in the system. SEMMNI
determines the number of semaphore sets
which can be created at any one time.
The PROCESSES parameter can be found
in each initsid.ora file, located in the
$ORACLE_HOME/dbs directory. The
default value of PROCESSES for the
preconfigured database created by Oracle
Database Configuration Assistant is 50.
SEMMNS 200
Set the number of semaphores in the
system to 200. The default value of
SEMMNS is 128, which is, in most cases,
too low for Oracle.
Understanding Setup Tasks
Threads MAX_THREAD_PROC 256 or more
Set the maximum number of threads per
process to 256 or more. The default value
of MAX_THREAD_PROC is 64, which is,
in most cases, too low for Oracle.
Note: If any of the kernel parameters above are less than your current values, continue to
use the current value. These are the requirements for Oracle8i only. If you have other
programs which use shared memory and semaphores, you will need to adjust the values
accordingly. Take into account that a system reboot is necessary for kernel changes to take
effect when planning for current and future database requirements.
Table 2???1 UNIX System Configuration Summary
System Factor Requirement for Oracle8i

[A. Clay Stephenson]

The best performance analysis tool is the one between your ears.

Use Glance and learn to use it well. It will quickly allow you to zero in on the bottlenecks. Post some of the Glance output along with kmtune output and maybe we can see something. Because this is oracle, always make sure that noone has set timeslice to 1.

My rule of thumb is that if a 2x performance boost will make things "ok" then I'll look at the OS but if it's beyond that (and very seldom does tuning improve things by more than 20% unless your box/disks were absolutely terribly set up) then it's time to dig into the SQL.


One of the most important SQL tuning metrics is "how fast do things get bad". If you plot the time required for an operation vs the number of rows, you can begin to see what's wrong. If as I suspect, you have a curve rather than a linear function then plot the logarithm of time vs. number of rows. The slope of this line will indicate the "degree" of the equation. If 2 then you have an n^^2 problem; if 4 then an n^^4 problem. Problems of this nature generally point to very poor joins. If you have a problem that is degrading an any exponential rate then no amount of OS tuning, 'slicing and dicing disks', or waving magic wands is going to fix it. You then have to look at the SQL code.

[Steven E. Protter]

It is time to collect some performance data. I am running a D380 in production quite smoothly with two CPU's. Neither CPU is working all that hard.

Scripts attached:

SEP

[Tim Krego_2]

Thanks everyone.

The process that pegs the CPU is coming from:

oracleTSAV

TSAV is the Oracle SID

Here are the files for the commands Steven suggested.

[Tim Krego_2]

I have glance installed. I can see the oracleTSAV process CPU spike. How do I find out what the process is doing?

I think the SQL in the Oracle package needs to be looked at. The problem is our Oracle programmer is using explain plan and nothing else. He uses TOAD to develop and I have access to the DBA module. I don't know how to dig through the pl/sql code to show what each little bit is doing.

The real issue: (big picture)

We are a small company. The budget was just enough for a off-lease K460/AutoRAID and Oracle software. We have no Oracle or HP support. I'm a MCSE pushed into HP admin and Oracle DBA. The boss isn't technically inclined and wonders why there isn't a quick fix.

I'm still enjoying the challenge, but wonder if I should quit my job and just pave roads. :)

Thanks again.

[Tim Krego_2]

I have two Oracle instances on the machine.

TSAP and TSAV

TSAV is the instance that pegs the CPU.

I found the following lines in initTSAP.ora file that are not in the initTSAV.ora file. I'm going to add these lines and bounce the database.


missing from initTSAV.ora
-------------------------
db_file_multiblock_read_count = 32
db_block_lru_latches = 4
db_writer_processes = 2
disk_asynch_io = false
parallel_max_servers = 8
rollback_segments = (rbs1, rbs2, rbs3, rbs4, rbs5, rbs6)
open_cursors = 1000
pre_page_sga = true
job_queue_processes=5
job_queue_interval=20


None of those parameters were in the initTSAV.ora file. Do any of them look critical? If they didn't exist would Oracle have a default?

[Tim Krego_2]

kernel parameters

dbc_max_pct = 50 <-- going to change to 5
dbc_min_pct = 5 <-- going to change to 2
timeslice = 10 <-- correct value

[Brian Crabtree]

How to get the sql statement.

I would suggest running this from sqlplus:

select paddr from v$process where spid = &SPID;
- put in paddr in next statement
select sid,serial#,sql_address from v$process where paddr = &PADDR;
- put in sql address in next statement
select sql_text from v$sqltext where address =
&sql_address;

This should show you the sql statement. I would suggest putting the "SID" from the second statement into the following:

select * from v$session_event where sid = &SID;

- or

select * From v$session_wait where sid = &SID;

This will show you what it is waiting on, and what it is running. I would recommend checking your analyzed times for the objects you are running this on too, as that can cause a bad query plan and force full table scans as well.

Thanks,

Brian