Advertisements

Soft parsing of SQL statements was consuming significant database time.

Every query needs to be parsed before it got executed. If some queries are quite frequently used, a good design will be reuse the parsed query. That means, it will not parse the same query again (provided that we are using bind variables), rather will reuse the existing parsed query and then execute it. In an ideal world, execute should be more than the parse. More parsing requires more CPU.

Ideally when execute is more than parse, the figure will be positive. The ratio goes down if there are more parses than the execute. Then some of the queries are parsed, but less or never executed!.

Causes could be one of the followings:

  • There might be no “Prepared Statement caching” in java layer or in jdbc connection pool.

Solution:

Enable Statement Caching in Java:

// Enable statement caching

((OracleConnection)connection).setStatementCacheSize(x);

((OracleConnection)connection).setImplicitCachingEnabled(true);
  • There might be the case, before the execution of a query, the cursor was closed.
  • There might not be enough “session cached cursors” to hold the queries to reuse.

 

Fallback if you cannot change the application(java) to use statement caching :

session_cached_cursors = X
  • There might be the case where new queries were coming to play quite frequently.
  • There might be connection interruptions, this need to be investigate from network end.

Below SQL query will help to identify, the SQL’s which is parsed but not executed or less executed:

set linesize 200;
set pagesize 1000;
col sql_text format a40;
SELECT sq.sql_text, st.executions_total, st.parse_calls_total
, round( 100*(1-( st.parse_calls_total / st.executions_total )),2) execute_to_parse
, st.executions_delta, st.parse_calls_delta
, round( 100*(1-( st.parse_calls_delta / st.executions_delta )),2) delta_ratio
FROM DBA_HIST_SQLSTAT st
, DBA_HIST_SQLTEXT sq
, DBA_HIST_SNAPSHOT s
WHERE s.snap_id = st.snap_id
AND s.begin_interval_time >= to_date('2018-10-19 01:00 pm','YYYY-MM-DD HH:MI pm')
AND s.end_interval_time <= to_date('2018-10-19 03:00 pm','YYYY-MM-DD HH:MI pm') AND st.sql_id = sq.sql_id AND st.parsing_schema_name='<schema_owner>' AND st.executions_total !=0 AND st.executions_delta !=0 ORDER BY delta_ratio;
Advertisements

How to solve High ITL Waits for given segments

The segments ( table and it’s indexes)  are encountering High ITL (Interested Transaction List) waits, it is usually because of frequent inserts against blocks with insufficient space to support the demands of the concurrency requested of them.  So when a transaction occurs it marks the blocks that the transaction is ‘interested in’ modifying by placing an entry in the block’s ITL. As shown below by default the INITRANS value for a table is one  and for an index is two.

If there are insufficient free ITL slots then new ones will be taken in the free space reserved in the block. If this runs out and too many concurrent DML transactions are competing for the same data block then we observe contention against the following wait event – “enq: TX – allocate ITL entry”.

SQL> SELECT STATISTIC_NAME, VALUE FROM V$SEGMENT_STATISTICS WHERE OBJECT_NAME = '<Table or Index>'and VALUE > 200;

STATISTIC_NAME                   VALUE
--------------------------- ----------
logical reads                 25024144
db block changes               5300608
physical reads                  815272
physical writes                 260050
physical read requests          815272
physical write requests         234212
gc cr blocks received              281
gc current blocks received      698263
ITL waits                          575
row lock waits                   11068
space used                   108721232
space allocated              268435456

The database is affecting the “COMMIT/ROLLBACK”,is enq: TX – allocate ITL entry, – AWR report:

 

Enqueue Type (Request Reason)
Requests
Succ Gets
Failed Gets
Waits
TX-Transaction (allocate ITL entry)
4
4
0
2

Segments by ITL Waits (from AWR)

Owner
Tablespace Name
Object Name
Subobject Name
Obj. Type
ITL Waits
% of Capture
TEST
TEST_DATA
TEST_INDEX1
INDEX
3
100.00

There is a small section of predefined ITL (defined by the MAXTRANS parameter) in the block header, the ITL structure grows dynamically as concurrency occurs. If there is no space to handle the concurrency then waits occur. The primary cause of ITL waits is that free slots in the ITL structure in the block are not available. The lack of slots can be due to low setting of the INITRANS & MAXTRANS, which reserves minimal ITL slots initially and place a hard limit on the number of transactions that can have locks on a block respectively and/or, the block is so packed that there is no room for the ITL to grow Setting a high value of INITRANS will try to ensure that there are enough free slots in the ITL to hold all the concurrent transactions with a goal of minimal or no dynamic extension of the ITL. However, doing so also means that there is less space in the block for actual data, potentially increasing wasted space.

Another option is to try to make the data is less packed so that ITL can grow enough to accommodate the surges in ITL. This can be done by increasing PCTFREE, increasing FREELISTS and FREELIST GROUPS parameters for a table. This will make a block hold less data and more room for the ITL to grow. As a direct result of the reduction in packing, the table may experience fewer buffer busy wait events, and an increase in performance.

SOLUTION
The main solution to this issue is to increase the ITL capability of the table or index by re-creating it and altering the INITRANS or PCTFREE parameter to be able to handle more concurrent transactions. This in turn will help to reduce “enq: TX – allocate ITL entry” wait events.

To reduce enq: TX – allocate ITL entry” wait events, We need to follow the steps below:

A Combination of increasing both INITRANS and PCTFREE

1) Set INITRANS to 50 and  pct_free to 40

alter table <table_name> PCTFREE 20  INITRANS 50;

2) Re-organize the table using move (alter table <table_name> move;)

3) Then rebuild all the indexes of the table as below

alter index <index_name>  rebuild PCTFREE 20 INITRANS 50;

SELECT TABLE_NAME,INI_TRANS FROM DBA_TABLES WHERE TABLE_NAME IN ('<table_name>');
SELECT PCT_FREE FROM DBA_TABLES WHERE TABLE_NAME IN ('<table_name>');

Note :  Move the Table
Syntax: ALTER TABLE MOVE

Advantages:
1. This operation is fast especially when compared to shrink.
2. This can be performed using simple commands.

Drawbacks:
1. Table is not available for DML during the move operation.
2. Additional space is needed.
3. Indexes need to be rebuilt manually post move operation.

Additional Note: Alter table move ONLINE can be used only for :
-> index-organized tables and for nested table storage tables that are index organized. Using ONLINE clause, DML operations on the IOTs are allowed during rebuilding of the primary key index of the table.
-> For moving partitions and sub partitions online.

 

 

My 1st UDEMY course on Step-by-Step Instructions for Creating a Physical Standby Database (RMAN Active dupication)

https://www.udemy.com/oracle-data-guard-122/

screen_1

Kindly enroll this course and give me your valuable suggestion, so I can improve the content of the course accordingly:

This course is not detailing about what is Oracle Dataguard but it help you to build a Physical Standby as quick as possible for your further practices

What are the requirements?

Basic knowledge of Database Administration
Standard laptop / desktop for practicing course assignments
Windows / Mac / Linux PC, Minimum 8 GB RAM and 100 GB HDD Storage
Virtualization software VirtualBox – Create two machines
OEL 5.9 / 6.5 ISO image file to install Linux on Virtual Box

What am I going to get from this course?

Database creation using DBCA.
Preparing the Primary Database for Standby Database Creation.
Preparing the Standby system
Step-by-Step Instructions for Creating a Physical Standby Database (RMAN Active duplication)
Post-Creation Steps.
Bonus Topic: Oracle 12CR2: Creating PDBs from the Seed.

What is the target audience?

Anyone who would like to learn Oracle Data Guard Technology
On job people, who are looking to get a guidance to create a Physical Standby Database

Oracle 12CR2: Creating PDB from the Seed

• Create a PDB using SQL*Plus

• Delete a PDB using the DBCA

• Add the created Pluggable Database net service name in the tnsnames.ora file

DBCA error while creating CDB database.

Attempt to create a database in Oracle Restart environment fails and the following error

dbca_error_1.JPG

ORA-01017: invalid username/password; logon denied
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/multisrv1/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.cdb1.db' on 'multisrv1' failed
ORA-01017: invalid username/password; logon denied
oracle.cluster.impl.crs.cops.CRSNativeResult.createException(CRSNativeResult.java:617)
oracle.cluster.impl.crs.cops.CRSNative.doStartResource(Native Method)
oracle.cluster.impl.crs.cops.CRSNative.genericStartResource(CRSNative.java:567)
oracle.cluster.impl.crs.cops.EntityOperations.startResource(EntityOperations.java:678)
oracle.cluster.impl.crs.CRSResourceImpl.start(CRSResourceImpl.java:883)
oracle.cluster.impl.crs.CRSResourceImpl.start(CRSResourceImpl.java:858)
oracle.cluster.impl.crs.CRSResourceImpl.start(CRSResourceImpl.java:846)
oracle.cluster.impl.common.SoftwareModuleImpl.start(SoftwareModuleImpl.java:525)
oracle.sysman.assistants.util.hasi.HADatabaseUtils.start(HADatabaseUtils.java:1476)
oracle.sysman.assistants.dbca.backend.PostDBCreationStep.executeImpl(PostDBCreationStep.java:1145)
oracle.sysman.assistants.util.step.BasicStep.execute(BasicStep.java:278)
oracle.sysman.assistants.util.step.Step.execute(Step.java:135)
oracle.sysman.assistants.util.step.StepContext$ModeRunner.run(StepContext.java:2941)
java.lang.Thread.run(Thread.java:745)
[Thread-291] [ 2018-05-01 10:46:38.078 GST ] [PostDBCreationStep.executeImpl:1156] Exception while Starting with HA Database Resource PRCR-1079 : Failed to start resource ora.cdb1.db
CRS-5017: The resource action "ora.cdb1.db start" encountered the following error:
ORA-01017: invalid username/password; logon denied
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/multisrv1/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.cdb1.db' on 'multisrv1' failed
ORA-01017: invalid username/password; logon denied

 

Cause:

grid user is not under dba group.

[root@multisrv1 ~]# id grid
uid=54323(grid) gid=54321(oinstall) groups=54321(oinstall),492(vboxsf),54323(asmadmin),54324(asmdba)

Solution:

Add grid user to dba group and relink the oracle binary

 
[root@multisrv1 ~]# usermod -a -G dba grid
[root@multisrv1 ~]# id grid
uid=54323(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),492(vboxsf),54323(asmadmin),54324(asmdba)

[oracle@multisrv1 bin]$ ./relink all
writing relink log to: /u01/app/oracle/product/12.2.0.1/db_1/install/relink.log
[oracle@multisrv1 bin]$

 

 

 

Upgrade the Oracle RAC Grid Infrastructure from release 12.1.0.2 to release 12.2.0.1.

Upgrade Oracle Grid Infrastructure:

Note: Oracle Grid Infrastructure 12.2 has added new features that require plenty of disk space in the CRS disk group. Therefore, you must expand the size of the CRS disk group before you can upgrade Oracle Grid to the new release.

Expand the CRS disk group in ASM.
## Shut down srv1 and srv2.
## Add a new fixed-size sharable disk to srv1. Give it the name DISK4 and set its size to 40 GB.

up21.JPG
In Oracle VirtualBox, click on srv1 >> click on Settings >> click on Storage in the right pane >> click on Controller: SATA >> click on Add Hardisk >> click on Add a New Disk >> select VDI option >> make it fixed-size >> enter the full-path filename of the disk >> set its size to 40 GB

up22.JPG
## Once the disk is created, make it sharable.
File menu item >> Virtual Media Manager >> select the created disk DISK4 >> click on the Modify button >> choose the option to make this file shareable >> press Ok >> press Close
## Link the new disk to srv2.
click on srv2 >> click on Settings >> click on Storage in the right pane >> click on Controller: SATA >> click on Add Hardisk >> click on an Existing Disk button>> navigate to the new disk file and press OK

 ## Create the directory of the new Oracle Grid home. Make grid the owner of the home directory.

Start srv1 and wait for its OS to load.
## Start Putty and login to srv1 as root user. Format the added disk.
## display all the available disks:
ls -l /dev/sd*
# format the disk:
# answer "n", "p","1", default, default, "w" when prompted
fdisk /dev/sde

## Add the partitioned disk to the ASM recognized disk list.
oracleasm listdisks
oracleasm createdisk DISK4 /dev/sde1
oracleasm listdisks

Start srv2 and wait for its OS to load.
## Start Putty and login to srv2 as root user.
## Scan the ASM disks and make sure DISK4 is seen by srv2.

oracleasm scandisks
oracleasm listdisks

## Login to the VirtualBox window of srv1 as grid.

## Start asmca utility and add DISK4 to the CRS disk group.
Right-click on the CRS disk group >> select Add Disks >> select DISK4 then click on OK button
You should see the CRS disk group size increased to nearly 50GB, as shown in the following screenshot:

up20.JPG

## Make sure you have Putty sessions connected to srv1 and srv2 as root.
## Create the directory of the new Oracle Grid home. Make grid the owner of the home directory.

##srv1
mkdir -p /u01/app/12.2.0/grid
chown grid:oinstall /u01/app/12.2.0/grid

##Create the same directory in srv2.

ssh srv2
mkdir -p /u01/app/12.2.0/grid
chown grid:oinstall /u01/app/12.2.0/grid
exit

##In the VirtualBox window of srv1, login as grid user

##Unzip the linuxx64_12201_grid_home.zip file to the new Grid home directory.

unzip linuxx64_12201_grid_home.zip -d /u01/app/12.2.0/grid

##In the terminal windows, change the current directory to the new Oracle Grid directory.

cd /u01/app/12.2.0/grid
export ORACLE_HOME=/u01/app/12.2.0/grid
./gridSetup.sh
up1

up2.JPG

up3.JPG

up4

 

Click on SSH Connectivity button

Enter the OS grid user password.

Click on Test button. If it reports that the SSH connectivity is not configured, click on Setup button.

up5.JPG

up6.JPG

up7

 

Make sure asmadmin and asmdba are selected.

up8

Make sure /u01/app/grid is selected.

up9Mark the check box “Automatically run configuration scripts

Enter the root password

up10

Set the upgrade on srv2 to Batch 2.

If you keep it in Batch 1, the system will not be available while the upgrade is going on.

up11

up12

up13.JPG

If you receive error: “cvuqdisk-1.0.10-1” being unavailable on the system, click on “Fix and Check Again” button.

The following warning can be ignored (for a production system they must be addressed):

– Memory is less than 8 GB

– resolv.conf Integrity

– (Linux)resolv.conf Integrity

Select Ignore All check box then click on Next button

Note: You could have run the Cluster Verification Utility (CVU) before running the installer. Running it from within the installer gives the same results.

up14.JPG

up15.JPG

click on Install button

up16.JPG

up17.JPG

up18.JPG

up19.JPG

In the Putty window, verify the upgrade has been successfully concluded:

/u01/app/12.2.0/grid/bin/crsctl check cluster -all
/u01/app/12.2.0/grid/bin/crsctl query crs activeversion

OGG-00446 No valid log files for current redo sequence:

ERROR: OGG-00446 No valid log files for current redo sequence 33, thread 1, error retrieving redo file name for sequence 33, archived = 0, use_alternate = 0Not able to establish initial position for begin time 2018-03-18 17:44:15.000000.

Cause:

As the redo log file was stored under ASM, the process was unable to connect to ASM leading to the error.

workaround:

Extract process to run successfully, specify a user that can connect to the ASM instance using the below in Extract Parameter file

TRANLOGOPTIONS ASMUSER {user}@{ASM_TNS_ALIAS} ASMPASSWORD {password}

Example:

GGSCI (ggsrv1.localdomain as ogg@db1) 73> view params ESRV1

Extract esrv1
USERID ogg, PASSWORD oracle
TRANLOGOPTIONS ASMUSER sys@asm ASMPASSWORD oracle
ExtTrail ./dirdat/es
Table HR.JOB_HISTORY;
Table HR.EMPLOYEES;
Table HR.JOBS;
Table HR.DEPARTMENTS;
Table HR.LOCATIONS;
Table HR.REGIONS;

To ensure that the Oracle GoldenGate Extract process can connect to an ASM instance, list the ASM instance in the listener.ora and tnsnames.ora file.

--->listener.ora
SID_LIST_LISTENER_ASM =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = ASM)
 (ORACLE_HOME = /u01/app/grid/product/12.1.0/grid)
 (SID_NAME = +ASM)
 )
)
--->tnsnames.ora
ASM =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = ggsrv1.localdomain)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SID = +ASM)
 )
 )

 

Step-by-step guide for Install, Configure and Maintain Oracle Database 12C in AWS Cloud

Creating an Account with AWS
Creating a VPC
Creating a Security Group & SSH Key Pair
Building an EC2 instance in AWS Cloud
Connecting to an EC2 Instance
Downloading Oracle Database 12c Software
Preparing EC2 instance for Oracle Database 12c installation
Changing Hostname of an EC2 Instance.
Adding Secondary IP (for oracle installation we need static IP)
Creating Private DNS Hosted Zone
Configuring SELinux and Firewall
Adding a Volume to an EC2 Instance
Taking a snapshot of an EC2 Instance
Adding a Swap Volume
Oracle Database 12c Installation

All the above details are demonstrated in this document :

oracle12c_on_aws.docx

Installing and Using Swingbench 2.5

Swingbench is a free easy-to-use stress testing utility on Oracle databases.download

Click the link above to download the full document.