Very slow insertion of record in Oracle database.

Very slow insertion of record in Oracle database

Hi readers I am sharing this knowledge to all you to make you understand how to resolve issue related to very slow insertion of record in a database.

Platform:
OS: Windows Server 2003 32 bits.
Oracle Version: 10.2.0.1 Enterprise Edition.

Issue Observed:

Its an issue related to slow insertion of record and slow movement of messages in IBM MQ server from MQ local queue only connected to an oracle database server. As per observation found, that during morning insertion of messages are fine, but as the time passes and peak how started, the number of messages gets started increasing and movement of messages / insertion of record getting very slow.

One important think also observed that the table where the data gets inserted, in this table lots of DML operation related to (insertion and deletion) are going on in a high level.

Analysis for Issue:

1) Found fragmentation in table.

2) Checked Initran values for these tables and its indexes set to 1.
3) Observed one query that is used for fetching the records from these tables having Full table scan.
4) As per application team information it has been found that only 750 approx messages are being inserted
to table during peak hour when volume of messages goes beyond 50,000 in one hour, and there is a huge queue of messages started in local queue of MQ.

Solution.

1) Set the Initran value for these table to 50.
2) Create one index for the query.
     Please see the explain plan for the same query before and after index creation,and number of execution for the same query is too high.

Observation after Changes.

1) More that 120 queries Full table scan has been resolved by creating one index.
2) As per application team information now 12000 approx messages are being inserted in a hour.
3) And we have achieved to transferred more than 90000 message in a day without any issue.
4) Application now doses't observed any pile up or MQ messages queue or stack from local queue of MQ.

Statistics of record inserted to the table without any slowness or pile up in MQ local queue.

Conclusion:

Finally we found that in morning as there is very less number of record present in table, so query was fetcing the record without delay, now during peak hour number of count increases in table and also number of record to be inserted to table also increasing simultaneously. So that is the reason that after creating the index and after increasing the Iinitran value the issue gets resolved.

Oracle Data guard in Oracle Standard Edition license product.

An important documentation on Oracle data sync on Standard edition.

Oracle Data guard in Oracle Standard Edition license.

Prepared by: Tapas Kumar Karmakar & Suman Das.

Designation: Oracle DBA.

Base Version: Ora_SE_DG_V01.d

Applied to: Oracle Server - Standard Edition - Version 8.1.5.0 and later

Metalink ID: 333749.1

This document is prepared with the guidance taken from Oracle Metalink ID: 333749.1 and Suman Das (Oracle DBA @ Nelito Syatem) . As per this Metalink ID, when any organization took “Oracle license for Standard edition” for using Production with DR (Disaster Recovery) setup, No one can use the keyword “STANDBY” in DR setup, this keyword is subject to license violation.

So in order to overcome this license violation we can follow the following steps, for creating DR database with archive log apply from primary database.

Steps to be followed.

Note:  (Assuming DR setup is ready with same replica of datafile, redolog file as of primary set up)

        1)        Create a backup control file from Primary.

           SQL> alter database backup controlfile to '<name>.ctl';

        2)        Transfer the backup controlfile from DC to DR, and keep it in same location as it in pfile location on DR database.

        3)        Start the DR database.

a)        Create spfile from pfile=’……..’;

b)        Startup nomount

c)        Alter database mount;

        4)        Transfer the archive log created in DC to DR.

        5)        Apply the archive log to DR using command.

           SQL> recover database using backup controlfile until cancel;

              After all the set up has been done, some important note needs to be kept in mind.

        1)        Add tempfiles to temp tablespace.

This is because recovery using backup controlfile command automatically removed tempfile records from controlfile

            SQL> alter tablespace temp add tempfile '<name>' size..

This is because recovery using backup controlfile command automatically removed tempfile records from controlfile

        2)        If you create a new tablespace in primary you may get the following errors while applying the corresponding logs in DR server.

            ORA-00283: recovery session canceled due to errors

            ORA-01244: unnamed datafile(s) added to controlfile by media recovery

            ORA-01110: data file 7: 'D:\oradatd\DR\tes1.dbf'

         3)        At this stage you need to run a command like this to create an empty datafile in standby.

a)        SQL>select name from v$datafile where name like '%UNNAMED%';

Output will come with “UNNAMED00007” where 7 represent datafile number.

                b)     SQL> alter database create datafile 'D:\ app\oracle\OraHome1\UNNAMED00007'

                              as ' D:\oradatd\DR\test01.dbf'; Where “D:\oradatd\DR\” is location for datafiles in standby.

Now you can restart the recovery process.

If anybody wants to open the DR database for reporting purpose then steps to be followed.

1)     Stop recover of database if pending.

      Sql> Alter database recover cancel;

           Sql> Alter database open read only;

2)     Alter reporting purpose works gets completed.

           Sql> Shu immediate;

           Sql> Startup nomount;

           Transfer archive logs from DC to DR.

           Sql> recover database using backup controlfile until cancel;

Transfer the archive log files from DC to DR.

COMMAND

  xcopy F$\TEST\ARC \\DR_IP\F$\TEST\ARC  /E /D /C /Y

  OR

  robocopy F$\TEST\ARC \\DR_IP\F$\TEST\ARC  /MIR /MON:1 /R:2000 /LOG+:file_name.txt

For checking archive log applied.

Sql> select max(fhrba_Seq) from x$kcvfh;

Conclusion.

Tested in training environment, passed all the statement without any error.

Required Oracle SE 8.1.5.0 and Later, windows xcopy or robocopy utility of OS. So before configuring the DR setup in SE edition everybody must follow this in order to follow Oracle license policy for DR setup created with oracle standard edition.

Regards.

Tapas Kumar Karmakar.

Oracle DBA.