参数设置

--显示正在使用的参数和文本参数值
1)select * from v$parameter;
2)select * from v$parameter2;
3)select * from v$spparameter;
--当前正在使用的SGA的大小
4)select sum(bytes)/1024/1024 from v$sgastat;
--显示PGA的状态
5)select * from v$pgastat;
6)show parameter pga



1. 系统版本：
[oracle@db-server80 ~]$ uname –a
RHEL 5.2 x86_64
Linux db-server80 2.6.18-128.el5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86_64 x86_64 x86_64 GNU/Linux
ORACLE 10.2.0.4 64bit
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2. 系统资源：top
top - 10:10:38 up 16:28,  6 users,  load average: 0.08, 0.04, 0.01
Tasks: 324 total,   1 running, 323 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.1%sy,  0.0%ni, 99.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  132164032k total, 28138212k used, 104025820k free,   179748k buffers
Swap: 153583608k total,        0k used, 153583608k free,  6502948k cached
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                           
9312 oracle    15   0 12868 1260  812 R  1.0  0.0   0:00.58 top                                                                                               
6383 oracle    15   0 88200 1736  996 S  0.3  0.0   0:00.15 sshd                                                                                              
1 root      15   0 10344  680  564 S  0.0  0.0   0:03.13 init 
3. 内存文件系统：tmpfs
[oracle@db-server80 ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol01                       95G  1.3G   89G   2% /
/dev/mapper/VolGroup00-LogVol02                       95G  389M   90G   1% /home
/dev/mapper/VolGroup00-LogVol03                       48G  181M   45G   1% /tmp
/dev/mapper/VolGroup00-LogVol04                      142G  2.2G  133G   2% /usr
/dev/mapper/VolGroup00-LogVol05                       95G  6.9G   83G   8% /opt
/dev/mapper/VolGroup00-LogVol06                      1.2T  163G  965G  15% /data
/dev/sda1              99M   13M   82M  14% /boot
tmpfs                  64G     0   64G   0% /dev/shm
4.操作系统限制：ulimit –a
[oracle@db-server80 dbs]$ ulimit -a
core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 1053696
max locked memory       (kbytes, -l) unlimited
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) unlimited
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
5.系统参数：
[root@db-server80 ~]# sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 32719476736
kernel.shmall = 33554432
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
vm.nr_hugepages = 10240
6.数据库参数配置：pfile
#cmob80.__db_cache_size=27179089920
#cmob80.__java_pool_size=16777216
#cmob80.__large_pool_size=16777216
#cmob80.__shared_pool_size=2885681152
#cmob80.__streams_pool_size=0
#db_block_buffers=2310720
db_cache_size=16474836400
shared_pool_size=2885681152
large_pool_size=16777216
java_pool_size=16777216
*.audit_file_dest='/opt/oracle/admin/cmob80/adump'
*.background_dump_dest='/opt/oracle/admin/cmob80/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/data/cmob80_database/cmob80/control01.ctl','/data/cmob80_database/cmob80/control02.ctl','/data/cmob80_database/cmob80/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/cmob80/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='cmob80'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cmob80XDB)'
*.job_queue_processes=10
*.open_cursors=500
*.pga_aggregate_target=28288897024
#workarea_size_policy=manual
#sort_area_size=500000
#sort_area_retained_size=588897024
#hash_area_size=1088897024
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=2205
#*.sga_max_size=4106127360
#*.sga_target=4106127360
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/cmob80/udump'