Detailed description of some Oracle initialization parameters

1. SGA

A. specify the maximum SGA value,
Once specified, data_buffer (db_cache_size in Oracle9i) and pai_pool_size do not need to be specified. That is:
If sga_max_size is set in 9i, the size of the data buffer and shared pool can be dynamically adjusted if the sum is less than or equal to this value.
In 9i, db_cache_size is used to replace db_block_buffers, db_keep_cache_size is used to replace buffer_pool_keep, and cached is used to replace buffer_pool_recycle.
In addition, 9i adds db_nk_cache_size, which is set to support using different block sizes in the same database.
For different tablespaces, the size of different data blocks can be defined, while the definition of the buffer zone depends on the support of this parameter.
Where N can be 2, 4, 6, 8, 16 and other different values.
Db_block_lru_latches,
This parameter has become a reserved parameter in 9i. It is not recommended to set it manually.

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. Specify data_buffer. (This parameter is not required after sga_max_size is specified in Oracle9i)

Alter system set db_cache_size = 80000000 scope = spfile;

C. large_pool_size

D. java_pool_size

2. PGA

In 9i, this Part has also changed a lot.

In standalone mode, 9i no longer claims to use the original UGA-related parameter settings, instead of new parameters.
If workarea_size_policy = auto (default), The UGA of all sessions share a large block of memory,
Pga_aggregate_target exists in this file.
After evaluating the maximum possible PGA memory used by all processes, you can set this parameter in the initialization parameters,
Therefore, you do not need to care about other "* _ area_size" parameters.

In Shared Mode:
Set workarea_size_policy = auto, pga_aggregate_target to the default value.
Then Oracle automatically assigns the required sort_area_size and hash_area_size to each user process.
The memory to be used is divided into SGA.

 

---
3. view the set parameter values
A. show command
Show parameter Parameters
For example:
Show parameter SGA
Show parameter sga_max_size
Show parameter java_pool_size
Show parameter pai_pool_size
Show parameter large_pool_size
......
B. View Mode
Select * from V $ Parameter
Select * From GV $ Parameter

Pre_paga_sga only allocates physical memory to SGA at startup.
However, it cannot be guaranteed that the system will not
Some pages are replaced with virtual memory, that is,
Although this parameter is set, page in/out may still appear.
If you need to ensure that SGA is not swapped out, you need to control it by another parameter lock_sga.

Alter system set pre_page_sga = true scope = spfile;

All SGA instances can be locked in the physical memory. centos instances cannot be locked. Otherwise, cannot allocate memory. Remember
Alter system set lock_sga = ture scope = spfile;

Start Oracle with another static configuration file
Startup pfile = Your pfile File

4. View pfile and spfile

Show parameter Pile
Show parameter spile
Because only pfile or spfile can be enabled when data is started.

5. Create a spfile
Create spfile = '$ ORACLE_HOME/dbs/spfiledb01.ora' from pfile = '$ ORACLE_HOME/dbs/pile ';
Create pfile
Create pfile = '$ ORACLE_HOME/dbs/pfile' from spfile = '$ ORACLE_HOME/dbs/spfiledb01.ora ';

6. cursor Optimization

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 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 callfor 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
The session that's running the test. Then set open_cursors
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 should des the first few characters of the statement and the SQL _id, so you can actually tell what the cursors are.

Select C. user_name, C. Sid, SQL. SQL _text
From v $ open_cursor C, V $ 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 functionality tiveness 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 Parameter

Starting from Oracle9i, Oracle introduced the new feature of automatic PGA management. The pga_aggregate_target parameter is used to control the overall expected goal of PGA:

$ sqlplus "/As sysdba"
SQL * Plus: Release 9.2.0.4.0-production on Thu APR 6 16:40:13 2006
copyright (c) 1982,200 2, Oracle Corporation. all rights reserved.
connected: 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
Upgrade ----------- upgrade
pga_aggregate_target big integer 1073741824
SQL>

However, in Oracle9i, The pga_aggregate_target parameter is only valid for dedicated connections of the dedicated server in dedicated server mode,
The connection to the shared server is invalid;
Pga_aggregate_target takes effect for both dedicated server connections and shared server connections starting from Oracle10g

Reposted from "http://hi.baidu.com/xiutuo/blog/item/db64e27e5b72233c0dd7da0a.html"