Reset Oracle Parameter Value

select name,value,isdefault,isses_modifiable,issys_modifiable,
isinstance_modifiable,isdeprecated,
from v$parameter;

alter system reset some_param scope=both sid='*' ;


alter system reset some_param scope=spfile sid='*' ;

alter system reset some_param scope=memory sid='*' ;


scope =BOTH/SPFILE/MEMORY



For string parameters, setting to an empty string will restore the default.

ALTER SYSTEM SET parameter = '' scope=SPfile;


For any parameter the RESET option will restore the default.
ALTER SYSTEM RESET parameter scope=SPfile sid='*' ;

note
When resetting a parameter, you must specify sid=mySid or sid='*' even for non-RAC instances.


ALTER SYSTEM RESET memory_target scope=SPfile sid='*' ;


donot do that with memory parameter, other oracle will not startup then restart oracle services

memory_target will bet set to zero.

then reset memory_target again

SESSION_CACHED_CURSORS

alter system set SESSION_CACHED_CURSORS=500 scope=spfile;


If an application repeatedly issues parse calls on the same set of SQL statements, the reopening of the session cursors can affect system performance. Session cursors can be stored in a

session cursor cache. This feature can be particularly useful for applications designed using Oracle Forms because switching from one form to another closes all session cursors associated with

the first form.
The Oracle Server uses the shared SQL area to determine whether more than three parse requests have been issued on a given statement. If so, the Oracle Server assumes the session cursor

associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session will then find the

cursor in the session cursor cache.
To enable caching of session cursors, use SESSION_CACHED_CURSORS to specify the maximum number of session cursors kept in the cache. A Least Recently Used (LRU) algorithm

removes entries in the session cursor cache to make room for new entries when required.
To determine whether the session cursor cache is sufficiently large for your instance, examine the session statistic "session cursor cache hits" in the V$SESSTAT view. This statistic counts the

number of times a parse call found a cursor in the session cursor cache. If this statistic is a relatively low percentage of the total parse call count for the session, you should consider setting

SESSION_CACHED_CURSORS to a larger value.

if SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find

them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves

the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache,

thus avoiding even a soft parse. (Technically, a parse can't be completely avoided; a "softer" soft parse is done that's faster and requires less CPU.)
In the session cursor cache, Oracle manages the cached cursors using a LRU list. Once more than SESSION_CACHED_CURSORS closed cursors are cached, Oracle starts dropping cached

cursors off the LRU end of the list whenever it needs to make room to cache a new cursor.


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.



if this parameter is set to a high value, the amount of fragmentation in the shared pool may be increased. The primary cause of library cache latch contention is fragmentation of the shared pool, a

common symptom of which is the ORA-04031 error.


select 'session_cached_cursors' parameter, lpad(value, 5) value,
   decode(value, 0, ' n/a', to_char(100 * used / value, '9990') || '%') usage
   from ( select max(s.value) used from V$STATNAME n, V$SESSTAT s
  where n.name = 'session cursor cache count' and s.statistic# = n.statistic# ),
   ( select value from V$PARAMETER where name = 'session_cached_cursors' )
  union all
   select 'open_cursors', lpad(value, 5), to_char(100 * used / value, '9990') || '%'
  from ( select max(sum(s.value)) used from V$STATNAME n, V$SESSTAT s
   where n.name in ('opened cursors current', 'session cursor cache count') and s.statistic# = n.statistic# group by s.sid ),
   ( Select Value From V$parameter Where Name = 'open_cursors' );




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';


Above query gave a result that CUR_CACHED=MAX_CACHED . 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 will help with latch contention and can give boost to performance.



Evaluate cached cursors for sessions as compared to parsing

select a.sid, a.value parse_cnt, 
       (select x.value
        from   v$sesstat x, v$statname y
        where  x.sid = a.sid
        and    y.statistic# = x.statistic#
        and    y.name = 'session cursor cache hits') cache_cnt
from   v$sesstat a, v$statname b
where  b.statistic# = a.statistic#
and    b.name = 'parse count (total)'
and    value > 0;

The CACHE_CNT ('session cursor cache hits') of a session should be compared to the PARSE_CNT ('parse count (total)'), if the difference is high, consider increasing the

SESSION_CACHED_CURSORS parameter.


------------------------------------------

If you shoot this query against your DB you’ll see what kind of cursor caching is your system doing.

The sum of all the tree values must be 100%:

select
    to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
    to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
      to_char(100 * hard / calls, '999990.00') || '%' hard_parses
    from
     ( select value calls from v$sysstat where name = 'parse count (total)' ),
    ( select value hard  from v$sysstat where name = 'parse count (hard)' ),
   ( select value sess  from v$sysstat where name = 'session cursor cache hits' );


If the value of the first column is bellow 80% then you must raise the parameter SESSION_CACHED_CURSORS.

----------------------------------------

steps for execution a SELECT query in Oracle:
1) Parse
  1.1) Syntatical check - Oracle checks whether this is a valid SQL.
  1.2) Semantical check - Oracle checks the SQL in terms of data structures, privileges, etc.
  1.3) Shared Pool check - Oracle checks whether the statement is already parsed and optimized. If yes, goes directly to step (4) - Oracle will do "soft parsing". If not, goes through step (2) and (3)

- Oracle will do "hard parsing".
2) Optimize - the CBO generates different execution plans and chooses the one with the lowest cost;
3) Generate source - a raw-source generator prepares the code to be executed;
4) Execute;
5) Output the result.

Item 1 to 4 form part of the parsing of the query, while that Section 5 is the execution itself. When you run a query, always performed at least steps 1 and 2. After executing these steps, Oracle

transforms the query into a hash and sends it to the Shared Pool Library Cache in sought if there is any query with the same hash value (if any session and used it at some point). If there is, it

compares the query text with the one found in the Library Cache to validate if they are exactly the same (this extra step is performed because it may be several consultations with the same hash

value) in case that they are, it proceeds to execute the query. This is what we call a Soft Parse . If the query does not exist, Oracle performs the steps 3 and 4. This is known as a Hard Parse . The

Hard Parse is very costly for Oracle Server since it involves making a number of latches (loqueos) in the SGA and CPU-intensive. As we know, every query run involves using a cursor (a cursor is a

memory space allocated to the execution of our query). The ideal is that our application opens the cursor to be used, run the sentences x times and then close them. Many applications do not

usually run as Forms cursors thus implying that we can not reuse the cursors and always have to reopen the longer run. To reduce this problem, we can use the initialization parameter
SESSION_CACHED_CURSORS that will allow us make a "Softer" Soft Parse . If the parameter setemos 100, 100 Oracle open cursors remain for us to reuse and avoid us having to open each

time. This memory space for the handling of cursors is maintained with a LRU list. Oracle recommend that the parameter setee in the first instance in 50 and be monitoring it to see if it should

increase its value. This parameter must setearse considering the value of OPEN_CURSORS.




soft parse's reduces the scalability of an system and a SQL statement should be more likely parsed once and executed many times.

soft parse will always consume less resources and less time-memory latches which is a good thing and more over many dba's expect the session connecting to database issue sql statements that

are already present in shared pool, so can you please explain why many soft parses would reduce the scalability of the system.

CURSOR_SHARING

CURSOR_SHARING

The default value of parameter Cursor_sharing is Exact. Who changed it?

CURSOR_SHARING is a parameter which decides whether a SQL send from user is a candidate for fresh parsing or will use an existing plan.

CURSOR_SHARING (Default value: Exact): Share the plan only if text of SQL matches exactly with the text of SQL lying in shared pool.

Note: When cursor_sharing =EXACT it uses the index and is 10times faster than cursor_sharing= SIMILAR

Oracle Hidden Parameter

SELECT

n.ksppinm AS "name",

v.ksppstvl AS "value",

n.ksppdesc AS "description"

FROM

x$ksppi n,

x$ksppsv v

WHERE 1 = 1

AND n.indx = v.indx

ORDER BY

1;

Oracle 11g PLSQL Native Compilation

Machine code is sometimes called native code when referring to platform-dependent parts of language features or libraries.

Change parameter value,

SQL>alter system set plsql_code_type=native scope=spfile;

when use PLSQL_CODE_TYPE='NATIVE', arithmetic operations are done directly in the hardware which provides significantly better performance.

To compile a PL/SQL package to native code without setting plsql_code parameter,

ALTER PACKAGE COMPILE PLSQL_CODE_TYPE=NATIVE;

To compile a PL/SQL procedure to native code without setting plsql_code parameter,

ALTER PROCEDURE COMPILE PLSQL_CODE_TYPE = NATIVE;

Procedure to convert the entire database and recompile all PL/SQL modules into native mode

1) Shut down database

2) Edit spfile.ora and set PLSQL_CODE_TYPE =native and plsql_optimise_level=2

3) connect sys/password as sysdba

startup upgrade

4) @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql (which updates the execution mode of all PL/SQL modules to native) (You can use the TRUE command line parameter with the dbmsupgnv.sql script to exclude package specs from recompilation to NATIVE, saving time in the conversion process.)

5) shutdown immediate

startup

@$ORACLE_HOME/rdbms/admin/utlrp.sql (to recompile all invalid objects)

LOG_ARCHIVE_MAX_PROCESSES

Oracle LOG_ARCHIVE_MAX_PROCESSES parameter specifies the number of active ARCH processes (ARC0-ARCn) initially invoked by Oracle. The value of LOG_ARCHIVE_MAX_PROCESSES parameter is evaluated at instance startup if LOG_ARCHIVE_START initialization parameter is set to true.


LOG_ARCHIVE_START=true

LOG_ARCHIVE_MAX_PROCESSES


no of log_archive_max_processes depends on active log group
select * from V$log;

If you anticipate a heavy workload for archiving, such as during bulk loading of data, you can increase the maximum number of archiver processes with the LOG_ARCHIVE_MAX_PROCESSES initialization parameter. The ALTER SYSTEM statement can change the value of this parameter dynamically to increase or decrease the number of ARCn processes.


Sometimes it happens that we face application slow down due to heavy network traffic on the application and we tend to increase the LOG_ARCHIVE_MAX_PROCESSES parameter however this is not always the best solution. Whenever you face application slow down due to network traffic then instead of increasing the LOG _ARCHIVE_MAX_PROCESSES parameter one should first try to increase bandwidth or decrease network roundtrips or exchanged bytes.



----------------