ORACLE部分初始化參數詳解

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”