- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: need more hardware?
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-18-2003 10:31 AM
тАО06-18-2003 10:31 AM
need more hardware?
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-18-2003 10:37 AM
тАО06-18-2003 10:37 AM
Re: need more hardware?
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-18-2003 10:39 AM
тАО06-18-2003 10:39 AM
Re: need more hardware?
'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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-18-2003 10:43 AM
тАО06-18-2003 10:43 AM
Re: need more hardware?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-18-2003 10:43 AM
тАО06-18-2003 10:43 AM
Re: need more hardware?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-18-2003 10:50 AM
тАО06-18-2003 10:50 AM
Re: need more hardware?
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-18-2003 10:54 AM
тАО06-18-2003 10:54 AM
Re: need more hardware?
Scripts attached:
SEP
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-18-2003 11:14 AM
тАО06-18-2003 11:14 AM
Re: need more hardware?
The process that pegs the CPU is coming from:
oracleTSAV
TSAV is the Oracle SID
Here are the files for the commands Steven suggested.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-18-2003 11:26 AM
тАО06-18-2003 11:26 AM
Re: need more hardware?
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-18-2003 12:12 PM
тАО06-18-2003 12:12 PM
Re: need more hardware?
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?