Tag Archives: Enable or disable

Database Resident Connection Pooling (DRCP) in Oracle

Database Resident Connection Pooling (DRCP) in Oracle

DRCP is introduced in 11g version of Oracle. It is used for sharing connection to achieve scalability in multi process and multi threaded environment.
DRCP Pools are like dedicated it’s used to shared the connection between multiple application process from different hosts.

Note: Database Resident Connection Pooling is consuming less memory as compared to other dedicated or Shared connection.

POOLED SERVER
Database server processes and sessions combination is known as a pooled server.

CONNECTION BROKER
A connection broker manages the pooled server in database instance. Client are connected and authenticated to the broker. The background process called is Connection Broker process(CMON).

Steps follow by Client for Connection
1. Client request for connection
2. Broker authenticated and pick the pooled server and hand-off client to that pooled server.
3. The client directly communicates with the pooled server for all its database activity.
4. The pooled server is handed back to the broker when the client releases it.

Configure the DRCP

1. Start or Enabled the Pool by connecting with sysdba
Following command start the broker and register with database listener. It must be started before client started request.

execute dbms_connection_pool.start_pool;

2. Check DRCP is started or configured:

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL                STATUS     MAXSIZE
------------------------------ ---------- ----------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE   20

SQL> exec dbms_connection_pool.start_pool;
PL/SQL procedure successfully completed.

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL                STATUS   MAXSIZE
------------------------------ -------- -------
SYS_DEFAULT_CONNECTION_POOL    ACTIVE   20

3. Disable or Stop the DRCP

execute dbms_connection_pool.stop_pool();

4. For using the Pool you need to make some connection changes at tns entry level or application level
Add POOLED keyword in tns entry and application connection string

host1.oracle.com:1521/orcl:POOLED
OR
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=host1.oracle.com)
(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)
(SERVER=POOLED)))

5.Configure and ALTER DRCP with package DBMS_CONNECTION_POOL.
This is step needed if you want to change the default setting before configuring the DRCP pool.
Following example already set with default value:

execute dbms_connection_pool.configure_pool(
pool_name => 'SYS_DEFAULT_CONNECTION_POOL',
minsize => 4,
maxsize => 40,
incrsize => 2,
session_cached_cursors => 20,
inactivity_timeout => 300,
max_think_time => 600,
max_use_session => 500000,
max_lifetime_session => 86400);

execute dbms_connection_pool.alter_param(
pool_name => 'DRPC_POOL',
param_name => 'MAX_THINK_TIME',
param_value => '1200');


Parameter meaning as:
POOL_NAME: Name of the pool. Default is SYS_DEFAULT_CONNECTION_POOL
minsize: minimum number of pooled server in pool. (default 4)
maxsize: maximum number of pooled server in pool. (default 40)
incrsize: increased number of pooled server is not available if pool is not max limit( default 2)
session_cached_cursors: SESSION_CACHED_CURSORS for all connections in the pool(default 20)
inactivity_timeout: time to remain an idle server in the pool. If a server remains idle upto time limit, it is killed. (default 300 seconds)
max_think_time: Maximum time of inactivity the PHP script is allowed after connecting. (default 120 seconds)
max_use_session: Maximum number of times a server can be taken and released to the pool before it is flagged for restarting. (default 500000)
max_lifetime_session: Time to live for a pooled server before it is restarted. (default 86400 seconds)
num_cbrok: The number of connection brokers that are created to handle connection (default 1)
maxconn_cbrok: The maximum number of connections that each connection broker can handle.(default 40000)

6. Monitor the DRCP pooling from following views:

select connection_pool, status, maxsize from dba_cpool_info;

select num_requests, num_hits, num_misses, num_waits from v$cpool_stats;

select cclass_name, num_requests, num_hits, num_misses from v$cpool_cc_stats;

Advertisements

Force Full Database Caching Mode in Oracle

Force Full Database Caching Mode in Oracle

Force full database caching mode means that place full database is buffer cache. If you have sufficent space in buffer cache then you are able to use the feature in Oracle
This feature is present from Oracle Database 12c Release 1 (12.1.0.2). Caching the full database might increase the performance of the Queries.Database must have compatibility 12.0.o or higher.

Check the database is in full Caching Mode

SELECT FORCE_FULL_DB_CACHING FROM V$DATABASE;

Yes - means database is in full caching mode

Steps to configure the Full DB CACHING

1. Check that you have enough space in buffer cache

SELECT NAME, BYTES FROM V$SGAINFO WHERE NAME='Buffer Cache Size';

SELECT COMPONENT, CURRENT_SIZE FROM V$SGA_DYNAMIC_COMPONENTS WHERE COMPONENT LIKE 'DEFAULT buffer cache';

Note: Estimate buffer cache size in case of AMM Configured
SGA_TARGET is USED then SGA_TARGET is X value then buffer cache estimate is 60% of X
MEMORY_TARGET is USED then MEMORY_TARGET is x then SGA is 60% of X and buffer cache is 60% of SGA.

Example:
1. SGA is Set.
SGA_TARGET is 100 GB then buffer cache estimate size is 60 GB.
2. MEMORY is Set.
MEMORY_TARGET is 100 GB then SGA is 60 GB and Buffer cache is 36 GB.

Enabling Force Full Database Caching Mode

1. Open database is in mount state
STARTUP MOUNT
2. Enable the force full database cache
ALTER DATABASE FORCE FULL DATABASE CACHING;
3. Open the database
ALTER DATABASE OPEN;

Disable the Force full database caching

1. Open database is in mount state
STARTUP MOUNT
2. Disable the force full database caching
ALTER DATABASE NO FORCE FULL DATABASE CACHING;
3. Open the database:
ALTER DATABASE OPEN;

Managing Temporary Undo in Oracle 12c

Managing Temporary Undo in Oracle 12.1

Temporary table used same process to used the default undo tablespace like a permanent table in Oracle Database and generate redo logs for operation which little bit effects on performance.

From 12c, You can also create a separate temporary undo for temporary tables in Oracle Database and not generate redo logs.

Benefits:
–Temporary undo reduce the undo data in permanent undo tablepsace which helps undo to maintain realistic data as undo retention parameter.
–It effects on performance because less redo is generated.

Note: You can enable temporary undo for a specific session or system level.

Enable and Disable the Temporary undo

--Enable temporary undo for a session
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

--Disable temporary undo for a session
ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;

--Enable temporary undo for the system
ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;

--Disable temporary undo for the system
ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;

Check the status of Undo Space Transaction

-- monitoring and tuning the undo space
select * from V$undostat;

-- monitoring and tuning the temporary undo space
select * from V$TEMPUNDOSTAT;

Database Smart Flash Cache in Oracle

Database Smart Flash Cache in Oracle

Database Smart Flash Cache is the new feature in Oracle 12c. It will help to increase the database buffer cache size using Solid State Device. It will greatly improve the performance of Oracle database by reducing disk I/O.

Note:
1. It only supported on Oracle Linux or Solaries Operating System.
2. Database smart flash cache is configured as 2 time to 10 times of Database buffer cache size or SGA size

Configure the Database Smart Flash Cache

-- Set the parameter in the SPFILE
Alter system set db_flash_Cache_File = /dev/sda scope=spfile;
Alter system set db_flash_cache_size = 32G scope=spfile;

-- Restart the database
shutdown immediate
startup

Configure to Setup multiple paths parameters

DB_FLASH_CACHE_FILE = /dev/sda, /dev/sdb, /dev/sdc
DB_FLASH_CACHE_SIZE = 32G, 32G, 64G

Flush the Database Smart flash cache

ALTER SYSTEM FLUSH FLASH_CACHE;

For disable it you can size it to zero
Note: it is not a dynamic parameter

Alter system set db_flash_Cache_File = /dev/sda scope=spfile;
Alter system set db_flash_cache_size = 0G scope=spfile;

Shutdown immediate
Startup

Check the Usage of Database Smart Flash Cache

Select * from V$FLASHFILESTAT

Managing Lost Write Protection with Shadow Tablespaces

Managing Lost Write Protection with Shadow Tablespaces

Lost Write of data block is occurred when an I/O subsystem acknowledges the completion of the block write even when the write did not occurred.An undetected lost write may result in corruption of data.It is used to prevent data corruption of Sensitive data. It is not important to use it to track all data.
Shadow lost write protection can protect against lost writes for tablespaces or data files.

Shadow lost write protection can minimize data loss and the time required to repair a database.You can enable it at tablespace or datafiles level.

Note: This feature present in Oracle 18c

For configured it, We need to follow the following steps:
Note: For enable Shadow Lost Writer Protection the compatibility level must be 18.0.0 or higher

1. Creating Shadow Tablespaces for Shadow Lost Write Protection

CREATE BIGFILE TABLESPACE Shadowtablespace DATAFILE 'C:\oracle\oradata\shadow_tablespace01.dbf' SIZE 10M LOST WRITE PROTECTION;

2. Enable the Shadow Lost Write Protection for a Database

-- Fon Non CDB or CDB Root
ALTER DATABASE ENABLE LOST WRITE PROTECTION;

-- FOR PDB
ALTER PLUGGABLE DATABASE ENABLE LOST WRITE PROTECTION;

3. Enable for Databata files or Tablespace as you needed
Note: Database Lost Write must be enabled before tablespace or Datafile

-- At tablespace level:
ALTER TABLESPACE users ENABLE LOST WRITE PROTECTION;

-- At CDB root or NON CDB level
ALTER DATABASE DATAFILE 'C:\oracle\oradata\users01.dbf' ENABLE LOST WRITE PROTECTION;

-- At Pluggable database
ALTER PLUGGABLE DATABASE DATAFILE 'C:\oracle\oradata\users01.dbf' ENABLE LOST WRITE PROTECTION;

4. Disabling Shadow Lost Write Protection
Note: When shadow lost write protection is disabled then tracking data is persevered in the tablespace,

--FOR CDB or NON CDB
ALTER DATABASE DISABLE LOST WRITE PROTECTION;

--FOR PDB
ALTER PLUGGABLE DATABASE DISABLE LOST WRITE PROTECTION;

5. Remove the Shadow lost write protection
Note: Remove shadow lost write protection for a data file or a tablespace, then its tracking data is deleted.

-- at tablespace level
ALTER TABLESPACE users REMOVE LOST WRITE PROTECTION;

--Data File Used by a Non-CDB
ALTER DATABASE DATAFILE 'C:\oracle\oradata\users01.dbf' SUSPEND LOST WRITE PROTECTION;

--FOR PDB datafiles
ALTER PLUGGABLE DATABASE DATAFILE 'C:\oracle\oradata\users01.dbf' SUSPEND LOST WRITE PROTECTION;

6. Drop tablespace of Shadow lost write protection

DROP TABLESPACE Shadowtablespace INCLUDING CONTENTS

Manage SQL Profile in Oracle Database

Manage SQL Profile in Oracle Database

SQL Profile is used by Optimizer for use more efficient execution plan for the SQL Query to make in accepted State. It is found in DBA_SQL_PROFILES view. It used to choose better plan by optimizer if the SQL profile plan is having low cost then optimizer use it.

Check the SQL Profile in the Database.

COLUMN category FORMAT a10
COLUMN sql_text FORMAT a20
SELECT NAME,type, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;

Check the SQL Profile internal hint information

SELECT
a.name
,b.comp_data
FROM dba_sql_profiles a
,dbmshsxp_sql_profile_attr b
WHERE a.name = b.profile_name;

Create the SQL Profiles
Two ways:
1. Creating the SQL Profiles while running the manual SQL tuning task
For manually creating with SQL Tuning task follows the link:
SQL Tuning Advisory

2. Automatic SQL Tuning job runs on a daily basis (in Oracle Database 11g or higher).
Check SQL profiles that have automatically been created having the value “AUTO” in the TYPE column of the DBA_SQL_PROFILES views

--Check
select name, type, status, sql_text from dba_sql_profiles;

-- Enable
exec DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');

-- Disable
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE');

Disable the SQL Profiles

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_789tfag56hjli0004',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/

Drop the SQL Profile

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_9824ryfg6f7d78653');

Drop all profiles in a database, using PL/SQL block:

declare
cursor c1 is select name from dba_sql_profiles;
begin
for r1 in c1 loop
dbms_sqltune.drop_sql_profile(r1.name);
end loop;
end;
/