That’s Me

Gavin Soorma
  • Oracle Certified Professional
    • 7.3, 8i, 9i,10g, 11g
  • 11i Apps DBA OCP
    •
  • 10g RAC OCE
    •
  • Certified GoldenGate Implementation Specialist
    •
  • 10g OCM
    •
  • 11g OCM
    •

    Feedback

    1,650,000 hits

    Thanks A MILLION for your support!

    Please send me your valuable feedback and suggestions

    Restoring Optimizer Statistics

    In some cases we may find that gathering fresh statistics has led to the optimizer executing sub-optimal execution plans and we would like to restore the statistics as of a date when we had good performance.

    REMEMBER – we gather statistics to ensure that the optimizer chooses the optimal execution plans, but gathering statistics invalidates the parsed representation of the SQL statement and reparsing the statement post gather stats can make the optimizer choose an execution plan which is different (and less optimised) than the original plan.

    In Oracle 10g and above, we can also restore table statistics to a previous point in the past by using the DBMS_STATS.RESTORE_TABLE_STATS package.

    Note: we can also export and import the statistics using the DBMS_STATS.CREATE_STATS_TABLE to create a statistics table which we can use to export and import statistics using the DBMS_STATS.EXPORT_*_STATS and DBMS_STATS.IMPORT_*_STATS procedures.

    We can check how far back in time we can go to

    Continue reading Restoring Optimizer Statistics

    March 29th, 2011 | Tags: , , | Category: Performance Tuning | Leave a comment

    Using GoldenGate Tokens with the COLMAP clause

    We can use the @TOKEN function to extract data which is stored in what is called the user token area of the GoldenGate trail file record header.

    We can populate this Token data from information stored in the header portion of trail records using the GGHEADER option of the GETENV function or by capturing information about the GoldenGate environment obtained via the GGENVIRONMENT option of GETENV function. We can also populate the tokens with data obtained from some database queries or functions.

    To define a token, use the TOKENS option of the TABLE parameter in the Extract parameter file as shown in the example below.

    We can then use this information in the tokens to populate columns in target tables by using the @TOKEN column conversion function in the COLMAP clause in a Replicat parameter file.

    In the example below, the source table has two columns (SAL and COMM) and the target table

    Continue reading Using GoldenGate Tokens with the COLMAP clause

    March 25th, 2011 | Tags: , , | Category: GoldenGate | Leave a comment

    GoldenGate performance tuning using the RANGE function

    We can improve performance by splitting large tables into row ranges and then assign processing of those ranges of rows to two or more Extract or Replicat process groups.

    We can use the RANGE function to divide the rows of table across processing groups.

    The syntax is @RANGE ({range}, {total ranges} [, {column}] [, {column}] [, ...])

    For example here the replication workload is split into two ranges between two different Replicat processes based on the table column ‘ORDID’.

    The column name is optional. If not specified, GoldenGate uses the primary key of the table to allocate the ranges.

    (Replicat group 1 parameter file)

    MAP sh.orders, TARGET sh.orders, FILTER (@RANGE (1, 2, ORDID));

    (Replicat group 2 parameter file)

    MAP sh.orders, TARGET sh.orders, FILTER (@RANGE (2, 2, ORDID));

    While the above example shows the use of RANGE in the Replicat process groups, we can also use the RANGE function in the Extract process group.

    The GoldenGate documentation also states that it

    Continue reading GoldenGate performance tuning using the RANGE function

    March 14th, 2011 | Tags: , | Category: GoldenGate | 4 comments

    Creating a 11g Data Guard Physical Standby Database using Enterprise Manager Grid Control 10.2.0.5

    In one of my earlier posts, I had discussed the process of setting up a 11g Standby Database using the DUPLICATE FROM ACTIVE method where we do not use any RMAN backup to create the standby database, but copy the datafiles from an active running database over the network.

    Let us now have a look at some of the screenshots of creating a 11g Data Guard physical standby database using Oracle Enterprise Manager Grid Control version 10.2.0.5.

    Download ….

    March 11th, 2011 | Tags: , , | Category: High Availability, Oracle 11g release 2 | Leave a comment