--显示正在使用的参数和文本参数值
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.系统参数:
# 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'
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.系统参数:
# 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'