1.SGA
a.指定sga最大值,
一旦指定后data_buffer（oracle9i中db_cache_size）和share_pool_size就无需指定了，即：
在9i中若设置了SGA_MAX_SIZE，则在总和小于等于这个值内，可以动态的调整数据缓冲区和共享池的大小
9i中用db_cache_size来取代db_block_buffers，用db_keep_cache_size取代buffer_pool_keep,用db_recycle_cache_size取代buffer_pool_recycle；这里要注意9i中设置的是实际的缓存大小而不再是块的数量。
另外9i新增加了db_nk_cache_size，这是为了支持在同一个数据库中使用不同的块大小而设置的。
对于不同的表空间，可以定义不同的数据块的大小，而缓冲区的定义则依靠该参数的支持。
其中n可以为2、4、6、8、16等不同的值。
在这里顺便提及的一个参数就是db_block_lru_latches，
该参数在9i中已经成为了保留参数，不推荐手工设置。
alter system set sga_max_size=2014 scope=spfile;
alter system set large_pool_size=50000000 scope=spfile;
alter system set java_pool_size=80000000 scope=spfile;
----
b.指定data_buffer.(在oracle9i中sga_max_size指定后，这个无需指定)
alter system set db_cache_size=80000000 scope=spfile;
c.large_pool_size
d.java_pool_size
2.PGA
在9i里面这部分也有了很大的变化。
在独立模式下，9i已经不再主张使用原来的UGA相关的参数设置，而代之以新的参数。
假如workarea_size_policy=AUTO（缺省），则所有的会话的UGA共用一大块内存，
该内存在pga_aggregate_target设置以内分配。
评估了所有进程可能使用的最大PGA内存之后，可以在初始化参数中设置这个参数，
从而不再关心其他”*_area_size”参数。
在共享模式下：
将参数workarea_size_policy=auto，pga_aggregate_target为默认值
然后oracle会自动给每个用户进程分配所需要的sort_area_size以及hash_area_size等
要用的内存划分给SGA.
---
3.查看设定的参数值
a.show命令
show parameter 参数
如：
show parameter sga
show parameter sga_max_size
show parameter java_pool_size
show parameter share_pool_size
show parameter large_pool_size
……
b.视图方式
select * from v$parameter
select * from gv$parameter
PRE_PAGA_SGA只是在启动时将物理内存分配给SGA
但并不能保证系统在以后的运行过程不会将SGA中的
某些页置换到虚拟内存中，也就是说，
尽管设置了这个参数，还是可能出现Page In/Out。
如果需要保障SGA不被换出，就需要由另外一个参数LOCK_SGA来控制了。
alter system set pre_page_sga=true scope=spfile;
可以将全部SGA都锁定在物理内存中,对于centos这个不能锁定，否则Cannot allocate memory，切记
alter system set LOCK_SGA=TURE scope=spfile;
以另外的静态配置文件启动oracle
startup pfile= 你的pfile文件
4.查看pfile和spfile
show parameter pile
show parameter spile
都是看到的同一个文件，因为数据启动时只能启用pfile或spfile之一
5.创建spfile
CREATE SPFILE = '$ORACLE_HOME/dbs/spfiledb01.ora' FROM PFILE = '$ORACLE_HOME/dbs/pile';
创建pfile
CREATE PFILE = '$ORACLE_HOME/dbs/pfile' FROM SPFILE = '$ORACLE_HOME/dbs/spfiledb01.ora';
6.游标优化
alter system set open_cursors=600 scope=spfile;
alter system set session_cached_cursors=400 scope=spfile;
alter system set cursor_sharing=similar scope=spfile;
------
To see if you've set OPEN_CURSORS high enough,
monitor v$sesstat for the maximum opened cursors current.
If your sessions are running close to the limit, up the value of OPEN_CURSORS.
SQL> select max(a.value) as highest_open_cur, p.value as max_open_cur
2> from v$sesstat a, v$statname b, v$parameter p
3> where a.statistic# = b.statistic#
4> and b.name = 'opened cursors current'
5> and p.name= 'open_cursors'
6> group by p.value;
HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- ------------
1953 2500
After you've increased the value of OPEN_CURSORS,
keep an eye on v$sesstat to see if opened cursors current
keeps increasing for any of your sessions.
If you have an application session whose opened cursors current
always increases to catch up with OPEN_CURSORS,
then you've likely got a cursor leak in your application code:
your application is opening cursors and not closing them when it's done.
There is nothing you, as a DBA, can do to fix a cursor leak.
The application developers need to go through the code,
find the cursors that are being left open, and close them.
As a stopgap, the most you can do is raise OPEN_CURSORS very high
and schedule times when all the application sessions will be closed
and reopened (eg. by kicking the webserver).
How not to tell if you're closing all your cursors
Frustratingly for developers, the session statistic 'currently open cursors'
can include some cursors that the application has closed. When application
code calls for a cursor to be closed, Oracle actually marks the cursor as "closeable".
The cursor may not actually be closed until Oracle needs the space for another cursor.
So it's not possible to test to see if a complex application is closing all
its cursors by starting a session, running a test, and then checking to see
if currently open cursors has gone down to 1. Even if the application
is closing all its cursors properly, currently open cursors may report
that some "closeable" cursors are still open.
One way for application developers to tell if an application is closing all its cursors
is to do a single test run, on a dedicated development box,
while monitoring "opened cursors cumulative" in v$sesstat for
the session that's running the test. Then set OPEN_CURSORS to
a value a little bit higher than the peak cursors open during your test run,
start a new session, and run through multiple iterations of the same test run.
If your application still has a cursor leak, you will see the value of OPEN_CURSORS going up,
and you may hit an ORA-1000 after a reasonable number of iterations.
(Don't set OPEN_CURSORS too low or it may be used up by recursive SQL;
if your single test run opens very few cursors,
consider making your test run longer rather than setting OPEN_CURSORS unreasonably low.)
Monitoring the session cursor cache
v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.
--session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'session cursor cache count' ;
You can also see directly what is in the session cursor cache by querying v$open_cursor. v$open_cursor lists session cached cursors by SID, and includes the first few characters of the statement and the sql_id, so you can actually tell what the cursors are for.
select c.user_name, c.sid, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id -- for 9i and earlier use: c.address=sql.address
and c.sid=&sid
;
Tuning SESSION_CACHED_CURSORS
If you choose to use SESSION_CACHED_CURSORS to help out an application that is continually closing and reopening cursors, you can monitor its effectiveness via two more statistics in v$sesstat. The statistic "session cursor cache hits" reflects the number of times that a statement the session sent for parsing was found in the session cursor cache, meaning it didn't have to be reparsed and your session didn't have to search through the library cache for it. You can compare this to the statistic "parse count (total)"; subtract "session cursor cache hits" from "parse count (total)" to see the number of parses that actually occurred.
SQL> select cach.value cache_hits, prs.value all_parses,
2> prs.value-cach.value sess_cur_cache_not_used
3> from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
4> where cach.statistic# = nm1.statistic#
5> and nm1.name = 'session cursor cache hits'
6> and prs.statistic#=nm2.statistic#
7> and nm2.name= 'parse count (total)'
8> and cach.sid= &sid and prs.sid= cach.sid ;
Enter value for sid: 947
old 8: and cach.sid= &sid and prs.sid= cach.sid
new 8: and cach.sid= 947 and prs.sid= cach.sid
CACHE_HITS ALL_PARSES SESS_CUR_CACHE_NOT_USED
---------- ---------- -----------------------
106 210 104
Monitor this in concurrence with the session cursor cache count.
--session cached cursors, for a given SID, compared to max
select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s, v$parameter2 p
where a.statistic# = b.statistic# and s.sid=a.sid and a.sid=&sid
and p.name='session_cached_cursors'
and b.name = 'session cursor cache count' ;
If the session cursor cache count is maxed out, session_cursor_cache_hits is low compared to all parses, and you suspect that the application is re-submitting the same queries for parsing repeatedly, then increasing SESSION_CURSOR_CACHE_COUNT may help with latch contention and give a slight boost to performance. Note that if your application is not resubmitting the same queries for parsing repeatedly, then session_cursor_cache_hits will be low and the session cursor cache count may be maxed out, but caching cursors by session won't help at all. For example, if your application is using a lot of unsharable SQL, raising this parameter won't get you anything.
7.PGA_AGGREGATE_TARGET参数
从Oracle9i开始,Oracle引入了自动PGA管理的新特型,PGA_AGGREGATE_TARGET参数用于控制PGA的总体期望目标:
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 6 16:40:13 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit ProductionWith the Partitioning optionJServer Release 9.2.0.4.0 - Production
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 1073741824
SQL>
但是在Oracle9i中，PGA_AGGREGATE_TARGET参数仅对专用服务器模式下（Dedicated Server）的专属连接有效，
对共享服务器（Shared Server）连接无效；
从Oracle10g开始PGA_AGGREGATE_TARGET对专用服务器连接和共享服务器连接同时生效
转载自“http://hi.baidu.com/xiutuo/blog/item/db64e27e5b72233c0dd7da0a.html”