Tuesday, August 31, 2021

Oracle rename Asm diskgroup with DB files


There are basically we have two phases of using renamedg command, phase 1 generates a configuration file to be used by phase 2. In phase 2 actual renaming of diskgroup is done.


renamedg
[phase={ one|two |both } ] dgname=diskgroup
newdgname=newdiskgroup [config=configfile]
[ asm_diskstring=discoverystring, discoverystring ... ]
[ clean={true|false} ] [ check={true|false} ]
[ confirm={true|false}] [ verbose={ true|false} ]
[ keep_voting_files={true|false}]


1) Check Current configuration for database attached to diskgroup 

srvctl config database -d <DB_NAME>
select file_name from  v$datafile ; 


2) Stop/dismount diskgroup 

srvctl stop database -d <DB_NAME>
srvctl status diskgroup -g reco
srvctl stop diskgroup -g recoc1   


3) Rename diskgroup 

renamedg phase=both dgname=RECOC1 newdgname=RECO verbose=true keep_voting_files=true 

If the above command fails, searching for disks, then we need to include the diskstring and then you need to use

renamedg phase=both dgname=<OLD_DG_NAME> newdgname=<NEW_DG_NAME> verbose=true asm_diskstring='<discoverystring1>','<discoverystring2>


SQL> alter diskgroup RECO mount restricted;
SQL> alter diskgroup RECO rename disks all ;

srvctl start diskgroup -g reco   
srvctl status diskgroup -g reco


4) Modify spfile and password file location for database to point to new asm diskgroup 

srvctl modify database -d <db_name> -p <spfile_path_with_new_diskgroup> -a "<diskgroup_list>"


5)  Rename datafile location  in mount stage 

 select 'alter database rename file '''||name||''' to '' +NEWDG'||substr(name,instr(name,'/',1,1))||''';' from V$DATAFILE;

select 'alter database rename file '''||member||''' to '' +NEWDG'||substr(member,instr(member,'/',1,1))||''';' from V$logfile;


V$RECOVER_FILE view can be used to check for any issues with datafiles not being available.
If everything is fine, you can open the database. You would have noticed that I have not done anything for Temp files. Checking alert log, we see that it is renamed automatically


6)   Drop and recreate block change tracking file 

7) Change  Archive location / Fra Location / Snapshot Controlfile location  that is attached to this diskgroup 


References : 

How To Rename A Diskgroup On ASM 11gR2? (Doc ID 948040.1)
How to Change Database to Use a New Diskgroup After Diskgroup Rename (Doc ID 1359625.1)

Tuesday, August 17, 2021

Oracle -- Database Resident Connection Pooling (DRCP)

 DRCP (11g) is especially designed to help architectures such as PHP with the Apache
server, that can’t take advantage of middle-tier connection pooling because they used
multiprocess single-threaded application servers. DRCP enables applications such as these
to easily scale up to server connections in the tens of thousands.


DRCP is controlled by the following configuration parameters:

INACTIVITY_TIMEOUT maximum idle time for a pooled server before it is terminated.

MAX_LIFETIME_SESSION time to live TTL duration for a pooled session.

MAX_USE_SESSION maximum number of times a connection can be taken and released to the
pool.

MAX_SIZE and MIN_SZIE the maximum and minimum number of pooled servers in the connections
pool.

INCRSIZE pool would increment by this number of pooled server when pooled server
are unavailable at application request time.

MAX_THINK_TIME maximum time of inactivity by the client after getting a server from the
pool. If the client does not issue a database call after grabbing a server
from the pool, the client will be forced to relinquish control of the pooled
server and will get an error. The freed up server may or may not be
returned to the pool.

SESSION_CACHED_CURSORS turn on SESSION_CACHED_CURSORS for all connections in the pool. This is an existing initialization parameter



/* Enabling and Disabling DRCP */

conn sys as sysdba
-- the ramins open after DB restart
exec dbms_connection_pool.start_pool();
select connection_pool, status, maxsize from dba_cpool_info;
exec dbms_connection_pool.stop_pool();

-- specify using DRCP
-- in EZCONNECT method (.Net 11g)
myhost.comany.com:1521/mydb.company.com:POOLED

-- tnsnames
mydb = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=myhost.company.com)
(SERVER=POOLED))) 
Page 241 Oracle DBA Code Examples
/* Configuring DRCP */
begin
 DBMS_CONNECTION_POOL.ALTER_PARAM( PARAM_NAME =>'INACTIVITY_TIMEOUT',
PARAM_VALUE=>'3600');
end;
/
-- restore parameter values to their defaults
exec dbms_connection_pool.restore_defaults()
/* Monitor DRCP */
SELECT
STATUS,MINSIZE,MAXSIZE,INCRSIZE,SESSION_CACHED_CURSORS,INACTIVITY_TIMEOUT
FROM DBA_CPOOL_INFO;
SELECT NUM_OPEN_SERVERS, NUM_BUSY_SERVERS, NUM_REQUESTS, NUM_HITS
 NUM_MISSES, NUM_WAITS, NUM_PURGED, HISTORIC_MAX
FROM V$CPOOL_STATS;

-- class-level stats
Select * From V$CPOOL_CC_STATS

Sunday, August 8, 2021

Oracle -- Open Cursor & Session Cached Cursors

 

This article mainly covers  importance of SESSION_CACHED_CURSORS on performance and  open cursors  .   Apart from SESSION_CACHED_CURSORS ,   other parameters that control   open cursors in database are cursor_sharing and  how developers close cursor in pl/sql block  .


OPEN_CURSORS 
specifies the maximum number of open cursors (handles to private SQL areas) a
session can have at once. You can use this parameter to prevent a session from opening an excessive
number of cursors. This parameter also constrains the size of the PL/SQL cursor cache, which PL/SQL
uses to avoid having to reparse as statements are re-executed by a user. 

SESSION_CACHED_CURSORS 
lets you specify the number of session cursors to cache. Repeated
parse calls of the same SQL statement cause the session cursor for that statement to be moved into the
session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen
the 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 will not help at all. For example, if your application is using a lot of unsharable SQL, raising this parameter will not help


When a SQL statement is issued, the server process, after checking its syntax and semantics, searches the library cache for an existing cursor for the SQL statement. If a cursor does not already exist, a new cursor is created (hard parse), else existing cursor is used (soft parse). Whereas hard parsing is a resource intensive operation, soft parse,  although less expensive, also incurs some cost, as the server process has to search the library cache for previously parsed SQL, which requires the use of the library cache and shared pool latches. Latches can often become points of contention for busy OLTP systems, thereby affecting response time and scalability.

To minimize the impact on performance, session cursors of repeatedly issued statements can be stored in the session cursor cache to reduce the cost of or even eliminate soft parse. This is called Session Cursor Caching. When session cursor caching is enabled, Oracle caches the cursor of a reentrant SQL statement in the session memory (PGA / UGA). As a result, the session cursor cache now contains a pointer into the library cache where the cursor existed when it was closed. Since presence of a cursor in session cursor cache guarantees the correctness of the corresponding SQL’s syntax and semantics, these checks are bypassed when the same SQL is resubmitted. Subsequently, instead of searching for the cursor in library cache, the server process follows the pointer in the session memory and uses the cursor after having confirmed its presence and validity.

Hence, if a closed cursor is found in the session cursor cache, it is registered as a ‘session cached cursor hit’ and also as a ‘soft parse’, since a visit to the shared SQL area must be made to  confirm its presence and validity


Handling Cursor from Pl/SQL side Using Precompiler Option RELEASE_CURSOR=YES 

Well this is  totally different  topic altogether hence not sharing too much information on this. However  just to give  direction to developer on cursor handling can be done at coding side , 



Find out the session that is causing the error by using the following SQL statement:

select sid ,  count(*)  from v$open_cursor  group by sid order by 2  ;
select sql_id ,  count(*)  from v$open_cursor where sid=193  group by sql_id  ;

col hwm_open_cur format 99,999
col max_open_cur format 99,999
select 
   max(a.value) as hwm_open_cur, 
   p.value      as max_open_cur
from 
   v$sesstat a, 
   v$statname b, 
   v$parameter p
where 
   a.statistic# = b.statistic# 
and 
   b.name = 'opened cursors current'
and 
   p.name= 'open_cursors'
group by p.value;



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 = 'opened cursors current' and s.username is not null;

select  sid ,sql_text, count(*) as "OPEN CURSORS", USER_NAME from v$open_cursor where sid in ($SID);

SELECT  max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE  a.statistic# = b.statistic#  and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;


-- cursor open for  each session and its address 
SELECT C.SID AS "OraSID",
 C.ADDRESS || ':' || C.HASH_VALUE AS "SQL Address",
 COUNT(C.SADDR) AS "Cursor Copies"
 FROM V$OPEN_CURSOR C
GROUP BY C.SID, C.ADDRESS || ':' || C.HASH_VALUE
HAVING COUNT(C.SADDR) > 2
ORDER BY 3 DESC


--  open cursor value and  its usage 
SELECT
 'session_cached_cursors' parameter,
 LPAD(value, 5) value,
 DECODE(value, 0, ' n/a', to_char(100 * used / value, '990') ||
'%') 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, '990') || '%'
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'
 )


-- Cursor details for particular session 

SELECT b.SID, UPPER(a.NAME), b.VALUE
 FROM v$statname a, v$sesstat b, v$session c
 WHERE a.statistic# = b.statistic#
 AND c.SID = b.SID
 AND LOWER(a.NAME) LIKE '%' || LOWER('CURSOR')||'%' 
 AND b.SID=20
UNION
SELECT SID, 'v$open_cursor opened cursor', COUNT(*)
FROM v$open_cursor
WHERE SID=&sid
GROUP BY SID
ORDER BY SID
/

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


-- CHECKING CACHE CURSOR FOR SESSIONS 

select s.username, s.sid, s.serial#, 'CACHED_CURSORS'
Statistic,
       a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and s.username='HAFEEZ' 
and b.name in ('session cursor cache count')
union
select s.username, s.sid, s.serial#, 'CURSORS_HITS',
       a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and s.username='HAFEEZ' 
and b.name in ( 'session cursor cache hits')
union
select s.username, s.sid, s.serial#, 'PARSES',
       a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and s.username='HAFEEZ' 
and b.name in ( 'parse count (total)')
union
select s.username, s.sid, s.serial#, 'HARD PARSES',
       a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and s.username='HAFEEZ' 
and b.name in ( 'parse count (hard)') ;

-- CHECKING DETAIL OF PARTICULAR SQL ID 

SELECT
  CHILD_NUMBER CN,
  NAME,
  VALUE,
  ISDEFAULT DEF
FROM
  V$SQL_OPTIMIZER_ENV
WHERE
  SQL_ID='5ngzsfstg8tmy'
  AND CHILD_NUMBER in (0,2,12)
ORDER BY
  NAME,
  CHILD_NUMBER;


SELECT
  CHILD_NUMBER CN,
  PARSING_SCHEMA_NAME,
  OPTIMIZER_ENV_HASH_VALUE OPTIMIZER_ENV,
  INVALIDATIONS,
  PARSE_CALLS,
  IS_OBSOLETE,
  FIRST_LOAD_TIME,
  TO_CHAR(LAST_ACTIVE_TIME,'YYYY-MM-DD/HH24:MI:SS') LAST_ACTIVE_TIME
FROM
  V$SQL
WHERE
  SQL_ID='5ngzsfstg8tmy';



It will provide highest count of open cursor that opened by a session currently. 
========================================================== 


col open_cur_configured form 9999999999
col max_open_cur form a15
select 
max(a.value) as highest_open_cur,
p.value as max_open_cur
from v$sesstat a, 
v$statname b, 
v$parameter p
where a.statistic# = b.statistic# 
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;


Cursor usage per user:
======================

select 
sum(a.value) total_cur, 
max(a.value) max_cur, 
s.username
from v$sesstat a, 
v$statname b,
v$session s 
where a.statistic# = b.statistic# 
and s.sid=a.sid 
and b.name = 'opened cursors current' 
group by s.username
order by 2 desc;


Cursor usage per session:
=========================

select 
s.sid,
s.serial#,
s.username,
a.value cur_count
from 
v$sesstat a,
v$statname b,
v$session s
where a.statistic# = b.statistic# 
and s.sid = a.sid
and b.name = 'opened cursors current' 
order by 4 desc ;

historical open cursor usage:
============================= 

select to_char(b.begin_interval_time,'DD-MON-YY HH24:MI') begin_time,a.instance_number,a.value Open_cursor from dba_hist_sysstat a, dba_hist_snapshot b 
where a.stat_name='opened cursors current' 
and to_char(b.begin_interval_time,'DD-MON-YY HH24:MI') like '%14-OCT-19%'
and a.snap_id=b.snap_id 
and a.instance_number=b.instance_number 
order by 2,1;



To get the % useage of all available open cursor 
======================================================

SELECT 'session_cached_cursors' parameter, LPAD(value, 5) value, 
DECODE(value, 0, ' n/a', to_char(100 * used / value, '990') || '%' ) 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, '990') || '%' 
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' ) 







Cursor leak in Oracle :

It is important to be able to diagnose which cursor is being 'leaked' (not closed) to identify what part of the application is responsible for managing the cursor,

  Lists each cursor, which has been opened by the session more than once in descending order.
   SELECT COUNT(*), address
  2  FROM v$open_cursor
  3  WHERE sid = 135
  4  GROUP BY address HAVING COUNT(address) > 1 ORDER BY COUNT(*);



Enable  Traving to diagnose ORA-1000   :
ALTER SYSTEM SET EVENTS '1000 trace name errorstack level 3';



Bugs Related to Cursors :

1) BUG 30518349 - ORA-01000 SELECT DOCID FROM "DR#IX_TS0481$U" , "DR#IX_TS0481$K"...

2) Bug 23608322 - CURSOR LEAK WITH DBMS_SQL.RETURN_RESULT IN JDBC THIN DRIVER 12.1.0.2 was opened for this issue.



References   : 

https://docs.oracle.com/cd/E40329_01/admin.1112/e27149/cursor.htm#OMADM5352
https://docs.oracle.com/cd/B10501_01/appdev.920/a97269/pc_10opt.htm
https://docs.oracle.com/database/121/TGSQL/tgsql_cursor.htm#TGSQL848