Monitor Oracle Resource Consumption in UNIX (文档 ID 148466.1)
来源:互联网 发布:linux cp出某个文件 编辑:程序博客网 时间:2024/06/07 11:58
For more up to date unix os commands, review following note: Note:293561.1 Unix Commands on Different OS's ==============================================================================================================PURPOSE======= This article is intended to provide DBAs an overview of the resourcesconsumed by Oracle, and the tools commonly used to monitor the resourceconsumption. SCOPE & APPLICATION=================== Database performance is bounded by the system resources. Sometimes,poor database performance can be caused by faulty configuration of theinstance and database. Sometimes, it can be caused by abnormal resourceconsumption by an Oracle transaction, user, or process. It is essentialfor DBAs to proactively monitor the resource consumption, and take anycorrective actions before potential serious impacts. Memory, CPU, and I/O are the three most common resources consumed byOracle. We will discuss these resources, and list some of the commonlyused tools that monitor them. These resources can be monitored at bothOracle Server and Operating System levels. DBAs should acquire any detailedinformation about the OS tools from the System Administrators or OS vendors. OVERVIEW======== Oracle Instance--------------- An Oracle Instance is a set of System Global Area and background processes.It is started during startup nomount. The characteristics of an instanceare: - Its name is defined by environmental variable ORACLE_SID. - It is started based on the configurations defined in init<SID>.ora file. - It has its own set of SGA and background processes. - It can only belong to one database at one time. - Multiple instances can access the same database in OPS configuration. There can be multiple oracle instances from the same $ORACLE_HOME. Theyare only limited by the OS resources, such as disk, memory, kernel parameters,etc. Each time an instance is started, the OS is being asked to give somekey resources according to the parameters specified in init<SID>.orafor that instance. Each Oracle instance has two main areas of memory structures,System Global Area (SGA) and Program Global Area (PGA) for background processes. SYSTEM RESOURCES================ Memory------ There are several memory structures used by Oracle, SGA, PGA, UGA, andsort area. 1) Shared Memory & Semaphores Oracle uses shared memory for efficiency. The OS does not haveto load up the same address page(s) into the memory each time a processneeds to reference it. Instead the process can just reference the one memorylocation, read/write to it, and then leave. Therefore, the data is notmoved from one process's memory address space to another. In order to controlmemory integrity so that only one process is accessing that memory address,semaphores and latches are used. Semaphores have only two values, set or unset. When a process goes toreference a memory location, it first checks to see if the semaphore allocatedfor that memory location is set. If so, it waits until that semaphore locationis free. Once available, it wakes up first and 'set' the semaphore so to not allowother processes to interrupt or corrupt the current processes read/write. The number of semaphores allocated is directly dependent on the PROCESSES parameter in the init.ora file. Latches are OS dependent atomic test which serialize the access of processes to data structures In SGA. The size of the SGA is made up by shared pool, database buffer cache,redo log buffer, large pool, and java pool. A commonly used formula tocalculate the size of the SGA is: ((db_block_buffers * block size) + (shared_pool_size +large_pool_size + java_pool_size + log_buffers) + 1MB See more detailed information in Note:1008866.6 How to determine SGA Size (7.x, 8.0, 8i, 9i, 10g). For optimal database performance, SGA should fit into real memory, andavoid swapping. The size of the SGA is also bounded by OS kernel parameters,such as SHMMAX in Unix. To learn more about shared memory, see Note:1011658.6Shared memory requirement on Unix. Since the SGA can be accessed by all processes that have been allocated,this is used to allow multiprocessing and access between processes withinthe SGA. A few of the common semaphores and shared memory segments that Oracleuses are: - SEMMNI => Max number of semaphores sets/identifiers - SEMMNS => Max number of semaphores in system - SHMMNI => Number of shared segments identifiers to be pre-allocated - SHMMAX => Max shared segment size For more information, see Note:15566.1Unix Semaphores and Shared Memory Explained. Also see the corresponding Oracle Installation Guide for your release of Oracle, that indicate minimum values for semaphore and shared memory. 2) Private Memory PGA is a memory region containing data and control informationfor a single server or background process. The size of PGA depends on thedatabase configuration, and what the process does. In a dedicated serverconfiguration, where one server process interacts with one user process,PGA contains stack space and UGA. UGA is made up by user session data,cursor state, and sort area. In a Shared Server configuration,where one shared server is shared by multiple user processes, UGA is partof the Shared Pool or Large Pool if it is configured. The PGA exists as: - Nonshared memory area to which a process can write - One PGA is allocated for each server or background process - Once the user has connected, a user can never run out of PGA space.Otherwise the connection will not happen and may result in an ora-4030 error. Oracle parameters that affect the sizes of PGA are: - open_links - db_files - sort_area_retained_size - sort_area_size - hash_area_size To calculate the current PGA size: SESSION PGA MEMORY => Statistic containing the current PGA size for a session SESSION PGA MEMORY MAX => Statistic containing the peak PGA size for a session PGA = dedicated server processes - UGA = Client machine process The size of the stack space in each PGA created on behalf of Oraclebackground processes (such as DBWR and LGWR), is affected by some additionalparameters. CPU--- CPU consumption varies with the activities of the processes. Duringthe peak workload, the DBAs and the SAs may see CPU utilization go up to90%. In general the DBAs should balance the workload to avoid overloadingthe system. I/O--- The amount of I/O occurrence also depends on the activities of the processes. While memory I/O may be necessary, disk I/O should be minimized. To avoiddisk I/O contention, files should be distributed across devices. Some ofthe background processes can be I/O intensive, such as DBWn, LGWn, ARCn,and CKPT. In some situations, DBAs may find multiple DBWn/ARCn, or DBWR/ARC0/LWGR I/O slaves beneficial. MONITORING AT THE SERVER LEVEL============================== V$ dynamic performance views capture the cumulative statistics of resourceconsumptions, at both system or session levels. STATSPACK and Utlbstat/utlestatreports summarize the statistics from these v$ views for a defined periodof time for the database and tkprof utility reports statistics at querylevel. Init<SID>.ora parameter TIMED_STATISTICS should set to be true. The descriptions of V$ views are in Oracle 7/8/8i/9i Server Reference manual. To learn more about analyzing statspack or utlbstat/utlestat reports,see Note:94224.1 StatsPack FAQ and Systemwide Tuning using UTLESTAT Reports in Oracle7/8 To learn more about interpreting tkprof output, see Note:32951.1 Tkprof Interpretation. Examples-------- The following examples shows the output from all different tools availablein Oracle to get this information: - STATSPACK and bstat/estat reports Statistic Total Per Transact Per Logon Per Second ---------------------------- ------------------ -------------- ----------------- ------------------ CPU used by this session 2278466 222.59 1974.41 203.6 CPU used when call started 2239857 218.82 1940.95 200.15 CR blocks created 2938 .29 2.55 .26 session pga memory 7138227 34521 991 191 session pga memory max 40327524 745091 98305 756 session uga memory 326143 96312 57843 654 session uga memory max 2483564 185735 12386 700 - tkprof .prf output file OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------------ --------- --------- ------------ ---------- ----------- ------------ ---------- Parse 3 0.04 0.05 0 0 0 0 Execute 4 0.00 0.05 0 0 0 0 Fetch 2 0.00 0.06 2 17 68 1 ------------ --------- --------- ------------ ---------- ----------- ------------ ---------- total 9 0.04 0.16 2 17 68 1 - Scripts output REM Monitor memory usage SQL> select sid, name, value 2 from v$statname n, v$sesstat s 3 where n.statistic# = s.statistic# 4 and n.name like '%memory%' 5 order by sid; SID NAME VALUE --------- --------------------------------- ---------- 1 session uga memory 18252 1 session uga memory max 18252 1 session pga memory max 59568 1 sorts (memory) 0 1 session pga memory 59568 . . REM monitor CPU usage SQL> select sid, name, value 2 from v$statname n, v$sesstat s 3 where n.statistic# = s.statistic# 4 and n.name like '%cpu%' 5 order by sid; SID NAME VALUE --------- ------------------------------ ---------- 1 recursive cpu usage 0 1 parse time cpu 0 1 OS Wait-cpu (latency) time 0 . . REM monitor I/O SQL> select file#, phyrds, phywrts 2 from v$filestat; FILE# PHYRDS PHYWRTS ------------ -------------- ---------------- 1 3239 141 2 80 246 3 9 163 4 6 3 5 5 3 . . MONITORING AT THE OPERATING SYSTEM LEVEL========================================= OS Semaphores------------- Each Oracle instance needs to have a set amount of semaphores. The totalamount of semaphores required is derived from the 'processes' parameterinside that Oracle instance init.ora file. As more instances and/or databasesare added the OS kernel parameter SEMMNS will need to be adjusted accordingly. Ulimits-------Refer to the install guide for the basic kernel parameters that need to be set. When increasing the amount of instances & databases on this Unix server, one will need to increase the kernel parameters accordingly. To check the Unix oracle user id limits: % ulimit -Sa (This is the output you may expect to see) time(seconds) unlimited file(blocks) unlimited data(kbytes) unlimited stack(kbytes) unlimited memory(kbytes) unlimited coredump(blocks) 2097151 nofiles(descriptors) unlimited Monitoring Tools ------------------Commonly used tools are vmstat, iostat, pstat, size, ipcs, ps, sar, and other OS specific tools. View the manual page to obtain the usage and column descriptions. Here are a few specific Unix supplied tools to help monitor different OS activities: 1)Memory To see how much memory is currently being used, vmstat (virtual memory statistics) focuses mainly on CPU and memory. OS Command ~~~~~~~~~~~~~~~~~~~ Sun Solaris: vmstat HP: vmstat -n IBM: /bin/vmstat Compaq: /sbin/hwmgr Linux: xosview 1)I/Os You should regulary monitor disk IO statistics by using utilities such as "sar -d" or "iostat". Average service times of 50ms or lessare reason for concern if it continues over a long time. One of the goals should be minimizing disk I/O by balancing the load on the disks. To watch over disk space usage, at the Unix prompt issue the command: df -k Look at where the datafiles and ORACLE_HOME are mounted. Pay attention to how much space is available. General rule of thumb is to never allow these mount points get to 90% full (i.e. 10% available). 2)CPU utilization OS Command ~~~~~~~~~~ ~~~~~~~~~ Sun Solaris: sar -u IBM: ps av (or) iostat 3 20 Digital: /usr/sbin/pset_info Linux: xosview In addition of monitoring the CPU usage you should monitor the runqueue to determine if processes are waiting for an available processor. You can use "sar -q" to monitor the runqueue. 3)CPU usage by processor To see how much CPU time is being used by each processor on a multiprocessor machine: OS Command ~~~~~~~~~~ ~~~~~~~~~ Sun Solaris: /usr/bin/mpstat HP: /usr/sbin/sar -M 5 5 IBM: vmstat -> Under cpu, if "us" is a very high number you have a cpu intensive process 4)Number of CPU's To see the number of CPUs there are in the machine, and their status: OS Command ~~~~~~~~~~ ~~~~~~~~~ Sun Solaris: /usr/sbin/mpstat HP: /usr/sbin/sar -M 2 2 IBM: /usr/sbin/bindprocessor -q Digital: /usr/sbin/psrinfo -v Linux: xosview 5)Swap space To see the amount of swap space is on the machine and the usage: OS Command ~~~~~~~~~~ ~~~~~~~~~ Sun Solaris: /etc/swap -l and /etc/swap -s HP: /etc/swapinfo -m ( must be root Unix id) IBM: lsps -a Digital: /usr/sbin/swapon -s Linux: free -t 6)Shared memory To see the current usage of shared memory & semaphores: "ipcs -b"
REFERENCES
NOTE:1008866.6 - How to determine SGA Size (7.x, 8.x, 9.x, 10g)NOTE:1011658.6 - Shared Memory Requirements On Unix
NOTE:15566.1 - TECH: Unix Semaphores and Shared Memory Explained
NOTE:32951.1 - TKProf Interpretation (9i and below)
NOTE:94224.1 - FAQ- Statspack Complete Reference
- Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Personal Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
- Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Standard Edition > Generic RDBMS > Database Level Performance Issues (not SQL Tuning)
- Oracle Database Products > Oracle Database Suite > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
I/O;PGA;SEMAPHORE;TROUBLESHOOT
ORA-4030
0 0
- Monitor Oracle Resource Consumption in UNIX (文档 ID 148466.1)
- Monitor Oracle Resource Consumption in UNIX (Doc ID 148466.1)
- How to Delete From or Add Resource to OCR in Oracle Clusterware (文档 ID 1069369.1)
- oracle and unix system monitor
- Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes (文档 ID 459694.1)
- 91 View the Exhibit and examine the resource consumption details for the current plan in use by the
- Cluster Health Monitor (CHM) FAQ (文档 ID 1328466.1)
- LTOM - The On-Board Monitor User Guide (文档 ID 352363.1)
- High "Resmgr:Cpu Quantum" Wait Events In 11g Even When Resource Manager Is Disabled (文档 ID 949033.1)
- Linux/Unix shell 监控Oracle实例(monitor instance)
- Linux/Unix shell 监控Oracle监听器(monitor listener)
- system resource monitor
- Tracing Sessions in Oracle Using the DBMS_SUPPORT Package (文档 ID 62160.1)
- How to Modify Private Network Information in Oracle Clusterware (文档 ID 283684.1)
- How to Modify Public Network Information including VIP in Oracle Clusterware (文档 ID 276434.1)
- 10g Manual Database Creation in Oracle (Single Instance and RAC) (文档 ID 240052.1)
- What is Split Brain in Oracle Clusterware and Real Application Cluster (文档 ID 1425586.1)
- role of social media in consumption
- memcached简介及java使用方法
- 解释在维基百科
- C#2005中使用控件DataGridView实现对数据库增删改查操作
- Linux 多核编程
- node.js基础主要参考阿里巴巴国际站前端技术部推出的文档
- Monitor Oracle Resource Consumption in UNIX (文档 ID 148466.1)
- 解决extjs4.0 grid不能编辑表格问题
- java 理论与实践:正确使用Volatile
- __attribute__ ((format (printf, 2, 3))); 疑惑
- tnsnames.ora 是什么
- Thanks for Andreas and his TexturePacker license
- shiro
- Android 关于字符串的使用String.valueOf(y).substring(0, 5)
- 一个类似Python和Ruby的新型脚本语言(Orange Script)