Thursday, October 10, 2013

Can Oracle GoldenGate DDL replication support interval partition?

We have a fast-growing table that requires keeping only two months' worth of data. We plan to take advantage of the interval partition feature in Oracle 11g, in which Oracle automatically creates an interval partition as data for that partition is inserted. Therefore, we will not worry about data load failure situation due to the possibility of forgetting to add new partitions by DBA.

In our environment, this table will also be in Oracle GoldenGate replication configuration.   So I try to do tests to confirm whether the GG DDL replication supports the following two operations:

1. Create interval partition

2. Drop partition

I used a test GG environment, in which  a two-way GG replication with DDL replication enabled  is set up between two databases: WESTDB and EASTDB; and the tables are in different schemas called west and east respectively.

The following are the testing steps:

1. create a table named interval_tab in the source db
 denis@WESTDB>> CREATE TABLE west.interval_tab
   2  ( prod_id        NUMBER(6)
   3   , cust_id        NUMBER
   4   , time_id        DATE
   5  )
   6  PARTITION BY RANGE (time_id)
   7  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
   8   ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2013', 'MM-DD-YYYY')),
   9     PARTITION p1 VALUES LESS THAN (TO_DATE('2-1-2013', 'MM-DD-YYYY')),
  10     PARTITION p2 VALUES LESS THAN (TO_DATE('3-1-2013', 'MM-DD-YYYY')),
  11     PARTITION p3 VALUES LESS THAN (TO_DATE('4-1-2013', 'MM-DD-YYYY')) );

 Table created.

Verify it is replicated in target:
 denis@EASTDB>> desc east.interval_tab;
  Name                                                                          Null?    Type
  ----------------------------------------------------------------------------- -------- ---------------------
  PROD_ID                                                                                NUMBER(6)
  CUST_ID                                                                                NUMBER
  TIME_ID                                                                                DATE

2. create indexes and contraint At source:
 denis@WESTDB>> create unique index west.interval_tab_pk on west.interval_tab(prod_id, time_id) local;

 Index created.

 denis@WESTDB>> create index west.interval_tab_ix1 on west.interval_tab(cust_id) local;

 Index created.

 denis@WESTDB>> alter table west.interval_tab add constraint interval_tab_pk primary key (prod_id, time_id) using index;

 Table altered.

Verify index and constraints creation are replicated at target:
 denis@EASTDB>> @tabix
 Enter value for tabowner: east
 Enter value for tabname: interval_tab

 TABLE_NAME           INDEX_NAME           COLUMN_NAME             COL_POS UNIQUENES
 -------------------- -------------------- -------------------- ---------- ---------
 INTERVAL_TAB         INTERVAL_TAB_IX1     CUST_ID                       1 NONUNIQUE
 INTERVAL_TAB         INTERVAL_TAB_PK      PROD_ID                       1 UNIQUE
 INTERVAL_TAB                              TIME_ID                       2 UNIQUE


 denis@EASTDB>> select owner,table_name,  constraint_name from dba_constraints where table_name='INTERVAL_TAB';

 OWNER           TABLE_NAME           CONSTRAINT_NAME
 --------------- -------------------- --------------------
 EAST            INTERVAL_TAB         INTERVAL_TAB_PK

3. Insert data at source that will cause new interval partition created automatically

Before the insert, check the current partitions:
 denis@WESTDB>> select table_owner, table_name, partition_name, high_value from dba_tab_partitions where table_name='INTERVAL_TAB';

 TABLE_OWNER      TABLE_NAME           PARTITION_NAME       HIGH_VALUE
 ---------------- -------------------- -------------------- ---------------------------------------------
 WEST             INTERVAL_TAB         P0                   TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
                  HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST             INTERVAL_TAB         P1                   TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
                  HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST             INTERVAL_TAB         P2                   TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
                  HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST             INTERVAL_TAB         P3                   TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
                                                                         HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
Perform the following insert at source and check the partitions:

insert into interval_tab values (1004,1, sysdate);
 denis@WESTDB>> insert into west.interval_tab values (1004,1, sysdate);

 1 row created.

 denis@WESTDB>> commit;

 Commit complete.

 denis@WESTDB>> select table_owner, table_name, partition_name, high_value from dba_tab_partitions where table_name='INTERVAL_TAB';

 TABLE_OWNER                    TABLE_NAME           PARTITION_NAME       HIGH_VALUE
 ------------------------------ -------------------- -------------------- ---------------------------------------------
 WEST                           INTERVAL_TAB         P0                   TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST                           INTERVAL_TAB         P1                   TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST                           INTERVAL_TAB         P2                   TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST                           INTERVAL_TAB         P3                   TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST                           INTERVAL_TAB         SYS_P81              TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


 5 rows selected.

4. Check partitions at target database
 denis@EASTDB>> select table_owner, table_name, partition_name, high_value from dba_tab_partitions where table_name='INTERVAL_TAB';

 TABLE_OWNER            TABLE_NAME           PARTITION_NAME       HIGH_VALUE
 ---------------------- -------------------- -------------------- ---------------------------------------------
 EAST                   INTERVAL_TAB         P0                   TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
          HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                   INTERVAL_TAB         P1                   TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
          HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                   INTERVAL_TAB         P2                   TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
          HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                   INTERVAL_TAB         P3                   TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
          HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                   INTERVAL_TAB         SYS_P123             TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


 5 rows selected.


 denis@EASTDB>> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='INTERVAL_TAB_IX1';

 INDEX_OWNER                    INDEX_NAME           PARTITION_NAME
 ------------------------------ -------------------- --------------------
 EAST                           INTERVAL_TAB_IX1     P0
 EAST                                                P1
 EAST                                                P2
 EAST                                                P3
 EAST                                                SYS_P123

 5 rows selected.

5. Drop a partition at source alter table west.interval_table drop partition P0;
 denis@WESTDB>> alter table west.interval_tab drop partition P0;

 Table altered.


 Verified, it is replicable


 denis@EASTDB>> /

 TABLE_OWNER                    TABLE_NAME           PARTITION_NAME       HIGH_VALUE
 ------------------------------ -------------------- -------------------- ---------------------------------------------
 EAST                           INTERVAL_TAB         P1                   TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                           INTERVAL_TAB         P2                   TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                           INTERVAL_TAB         P3                   TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                           INTERVAL_TAB         SYS_P123             TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


 4 rows selected.


In conclusion, the tests confirmed that GG DDL replication support the interval partition automatic creation and drop partition operations. It is worth noting that when we use DBMS_METADATA package to get the definitions of interval partitioned table and its associated local indexes, we won't see the interval partitions that are created automatically. To verify the existance of the partitions, we shall use dba_tab_partitions and dba_ind_partitions views.

P.S.

Note from Oracle docs:

You cannot explicitly add a partition to an interval-partitioned table unless you first lock the partition, which triggers the creation of the partition. The database automatically creates a partition for an interval when data for that interval is inserted. In general, you only need to explicitly create interval partitions for a partition exchange load scenario.

update Apr 9, 2014 -

I may have misunderstood the relationship of DDL replication and interval partition creation.  DDL replication may have no relationship with interval partition creation at all. Even without DDL replication enabled. Interval partition will be created due to the data insertion DML statement. 

Wednesday, September 11, 2013

Avoid Merge Join Cartesian in a SQL Tunning Exercise

Encountered a query that caused CPU utilization high. In a 15 min AWR, this query executes 78 times with total Buffer Gets 949M, which contributes 78.8% of the total. I filled in some bind variable values by checking v$sql_bind_capture view. And I executed the sql from sqlplus with gather_plan_statistics hint. Below is the execution plan witn E-Rows and A-Rows info :
Plan hash value: 4161037915

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|

-------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------ 
|   1 |  SORT ORDER BY                             |                        |      1 |      1 |    113K|00:02:27.54 |    4242K|  12873 |   8580 |    76M|  3013K|   67M (0)|         | 
|   2 |   HASH UNIQUE                              |                        |      1 |      1 |    113K|00:02:23.74 |    4242K|  12873 |   8580 |    72M|  7323K| 9538K (1)|   73728 | 
|*  3 |    FILTER                                  |                        |      1 |        |    114K|00:03:25.41 |    4242K|   4293 |      0 |       |       |          |         | 
|   4 |     NESTED LOOPS                           |                        |      1 |      1 |    114K|00:00:15.77 |    4239K|   4291 |      0 |       |       |          |         | 
|   5 |      MERGE JOIN CARTESIAN                  |                        |      1 |      1 |    199K|00:00:01.71 |    6001 |    123 |      0 |       |       |          |         | 
|   6 |       MERGE JOIN CARTESIAN                 |                        |      1 |      1 |   7112 |00:00:00.44 |    5998 |    123 |      0 |       |       |          |         | 
|*  7 |        HASH JOIN OUTER                     |                        |      1 |      1 |    889 |00:00:00.15 |    5995 |    123 |      0 |   985K|   927K| 1229K (0)|         |
|   8 |         NESTED LOOPS                       |                        |      1 |      1 |    889 |00:00:00.11 |    5979 |    123 |      0 |       |       |          |         | 
|   9 |          NESTED LOOPS                      |                        |      1 |      1 |    889 |00:00:00.10 |    5088 |    123 |      0 |       |       |          |         | 
|* 10 |           HASH JOIN OUTER                  |                        |      1 |      1 |    889 |00:00:00.09 |    4197 |    123 |      0 |   928K|   928K| 1265K (0)|         | 
|  11 |            NESTED LOOPS                    |                        |      1 |      1 |    865 |00:00:00.10 |    4193 |    123 |      0 |       |       |          |         |
|  12 |             NESTED LOOPS                   |                        |      1 |      1 |   1839 |00:00:00.07 |     504 |    123 |      0 |       |       |          |         | 
|  13 |              MERGE JOIN CARTESIAN          |                        |      1 |      1 |      1 |00:00:00.01 |      12 |      0 |      0 |       |       |          |         | 
|  14 |               NESTED LOOPS                 |                        |      1 |      1 |      1 |00:00:00.01 |       8 |      0 |      0 |       |       |          |         | 
|  15 |                NESTED LOOPS                |                        |      1 |      1 |      1 |00:00:00.01 |       5 |      0 |      0 |       |       |          |         | 
|  16 |                 TABLE ACCESS BY INDEX ROWID| OZBJZFDS               |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         | 
|* 17 |                  INDEX UNIQUE SCAN         | PK_OZBJZFDS            |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|  18 |                 TABLE ACCESS BY INDEX ROWID| OZBJZFD_CATEGORY       |      1 |     26 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|* 19 |                  INDEX UNIQUE SCAN         | PK_OZBJZFD_CATEGORY    |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |      0 |       |       |          |         | 
|* 20 |                TABLE ACCESS BY INDEX ROWID | OZBJZFD_MARKETS        |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         | 
|* 21 |                 INDEX RANGE SCAN           | PK_OZBJZFD_MARKETS     |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|  22 |               BUFFER SORT                  |                        |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |      0 |  2048 |  2048 | 2048  (0)|         | 
|* 23 |                TABLE ACCESS BY INDEX ROWID | MARKETS                |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         | 
|* 24 |                 INDEX RANGE SCAN           | PK_MARKETS             |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|* 25 |              TABLE ACCESS BY INDEX ROWID   | OZBJZFD_OQNCVDSS       |      1 |      1 |   1839 |00:00:00.07 |     492 |    123 |      0 |       |       |          |         | 
|* 26 |               INDEX RANGE SCAN             | PK_OZBJZFD_OQNCVDSS    |      1 |      1 |   1894 |00:00:00.01 |      16 |     13 |      0 |       |       |          |         |
|* 27 |             TABLE ACCESS BY INDEX ROWID    | OQNCVDSS               |   1839 |      1 |    865 |00:00:00.03 |    3689 |      0 |      0 |       |       |          |         |
|* 28 |              INDEX UNIQUE SCAN             | PK_OQNCVDSS            |   1839 |      1 |   1839 |00:00:00.01 |    1841 |      0 |      0 |       |       |          |         | 
|  29 |            VIEW                            |                        |      1 |     29 |     29 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         | 
|  30 |             SORT UNIQUE                    |                        |      1 |     29 |     29 |00:00:00.01 |       4 |      0 |      0 |  4096 |  4096 | 4096  (0)|         | 
|  31 |              UNION-ALL                     |                        |      1 |        |     29 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         | 
|  32 |               INDEX FULL SCAN              | PK_SOURCE_TARGET_RULES |      1 |     20 |     20 |00:00:00.01 |       1 |      0 |      0 |       |       |          |         | 
|  33 |               TABLE ACCESS FULL            | CARRYOVER_ISOC_MAPPING |      1 |      9 |      9 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         |
|  34 |           TABLE ACCESS BY INDEX ROWID      | SPEED_CODES            |    889 |      1 |    889 |00:00:00.01 |     891 |      0 |      0 |       |       |          |         |
|* 35 |            INDEX UNIQUE SCAN               | PK_SPEED_CODES         |    889 |      1 |    889 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         | 
|  36 |          TABLE ACCESS BY INDEX ROWID       | OQNCVDS_TYPES          |    889 |      1 |    889 |00:00:00.01 |     891 |      0 |      0 |       |       |          |         | 
|* 37 |           INDEX UNIQUE SCAN                | PK_OQNCVDS_TYPES       |    889 |      1 |    889 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         |
|  38 |         INDEX FAST FULL SCAN               | PK_OFFER_PROD          |      1 |   1255 |   1255 |00:00:00.01 |      16 |      0 |      0 |       |       |          |         | 
|  39 |        BUFFER SORT                         |                        |    889 |      8 |   7112 |00:00:00.01 |       3 |      0 |      0 |  2048 |  2048 | 2048  (0)|         | 
|  40 |         TABLE ACCESS FULL                  | BILLING_FREQ           |      1 |      8 |      8 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         | 
|  41 |       BUFFER SORT                          |                        |   7112 |     28 |    199K|00:00:00.20 |       3 |      0 |      0 |  2048 |  2048 | 2048  (0)|         | 
|  42 |        TABLE ACCESS FULL                   | UNIT_TYPES             |      1 |     28 |     28 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         | 
|* 43 |      TABLE ACCESS BY INDEX ROWID           | UHCDN_RATES            |    199K|      1 |    114K|00:02:16.61 |    4233K|   4168 |      0 |       |       |          |         | 
|* 44 |       INDEX RANGE SCAN                     | PK_UHCDN_RATES         |    199K|     36 |   4879K|00:01:48.28 |     727K|    911 |      0 |       |       |          |         | 
|* 45 |     INDEX RANGE SCAN                       | PK_OZBJZFD_OQNCVDSS    |    976 |      1 |    933 |00:00:00.02 |    2928 |      2 |      0 |       |       |          |         | 
|* 46 |      INDEX UNIQUE SCAN                     | PK_OQNCVDSS            |      9 |      1 |      4 |00:00:00.01 |      18 |      0 |      0 |       |       |          |         |

-------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------

The AUTOTRACE statistics is as follows:
Statistics
----------------------------------------------------------
         72  recursive calls
          0  db block gets
    4242325  consistent gets
      13434  physical reads
          0  redo size
    9673175  bytes sent via SQL*Net to client
      13494  bytes received via SQL*Net from client
       1141  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
     113976  rows processed

By comparing the E-rows and A-rows from the execution, it is easy to identify that the problem starts from the operation id 25 and 26, where E-rows=1 and A-rows=1839 and 1894.
|* 25 |              TABLE ACCESS BY INDEX ROWID   | OZBJZFD_OQNCVDSS       |      1 |      1 |   1839 |00:00:00.07 |        ... 
|* 26 |               INDEX RANGE SCAN             | PK_OZBJZFD_OQNCVDSS    |      1 |      1 |   1894 |00:00:00.01 |        ...                   
With E-rows=1, Oracle CBO decides to use "MERGE JOIN CARTESIAN". Notice at the end, CBO estimate only 1 row whereas actual number of rows is 113K. So the key to tune this query is to avoid the Cartesian join at operation id 5 and 6. I modified the query by adding the following hints and of course make sure the tables order is correct in the FROM clause:

/*+ ordered use_hash(E), use_hash(F) */

Here is the execution plan  of the modified query:
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                        |     1 |  2841 |    74   (9)| 00:00:01 |
|   1 |  SORT ORDER BY                             |                        |     1 |  2841 |    74   (9)| 00:00:01 |
|   2 |   HASH UNIQUE                              |                        |     1 |  2841 |    73   (7)| 00:00:01 |
|*  3 |    FILTER                                  |                        |       |       |         |     |
|*  4 |     HASH JOIN OUTER                        |                        |     1 |  2841 |    72   (6)| 00:00:01 |
|   5 |      NESTED LOOPS                          |                        |     1 |  2835 |    68   (5)| 00:00:01 |
|   6 |       NESTED LOOPS                         |                        |     1 |  2828 |    67   (5)| 00:00:01 |
|*  7 |        HASH JOIN                           |                        |     1 |  2813 |    66   (5)| 00:00:01 |
|   8 |         NESTED LOOPS                       |                        |     1 |  2801 |    64   (5)| 00:00:01 |
|   9 |          NESTED LOOPS                      |                        |     1 |  2781 |    63   (5)| 00:00:01 |
|* 10 |           HASH JOIN OUTER                  |                        |     1 |  2682 |    17  (18)| 00:00:01 |
|  11 |            NESTED LOOPS                    |                        |     1 |   578 |    11   (0)| 00:00:01 |
|  12 |             NESTED LOOPS                   |                        |     1 |   326 |    10   (0)| 00:00:01 |
|  13 |              MERGE JOIN CARTESIAN          |                        |     1 |   263 |     7   (0)| 00:00:01 |
|  14 |               NESTED LOOPS                 |                        |     1 |   153 |     5   (0)| 00:00:01 |
|  15 |                NESTED LOOPS                |                        |     1 |   125 |     3   (0)| 00:00:01 |
|  16 |                 TABLE ACCESS BY INDEX ROWID| OZBJZFDS               |     1 |   120 |     2   (0)| 00:00:01 |
|* 17 |                  INDEX UNIQUE SCAN         | PK_OZBJZFDS            |     1 |       |     1   (0)| 00:00:01 |
|  18 |                 TABLE ACCESS BY INDEX ROWID| OZBJZFD_CATEGORY       |     1 |     5 |     1   (0)| 00:00:01 |
|* 19 |                  INDEX UNIQUE SCAN         | PK_OZBJZFD_CATEGORY    |     1 |       |     0   (0)| 00:00:01 |
|* 20 |                TABLE ACCESS BY INDEX ROWID | OZBJZFD_MARKETS        |     1 |    28 |     2   (0)| 00:00:01 |
|* 21 |                 INDEX RANGE SCAN           | PK_OZBJZFD_MARKETS     |     1 |       |     1   (0)| 00:00:01 |
|  22 |               BUFFER SORT                  |                        |     1 |   110 |     5   (0)| 00:00:01 |
|* 23 |                TABLE ACCESS BY INDEX ROWID | MARKETS                |     1 |   110 |     2   (0)| 00:00:01 |
|* 24 |                 INDEX RANGE SCAN           | PK_MARKETS             |     1 |       |     1   (0)| 00:00:01 |
|* 25 |              TABLE ACCESS BY INDEX ROWID   | OZBJZFD_OQNCVDSS       |     1 |    63 |     3   (0)| 00:00:01 |
|* 26 |               INDEX RANGE SCAN             | PK_OZBJZFD_OQNCVDSS    |     1 |       |     2   (0)| 00:00:01 |
|* 27 |             TABLE ACCESS BY INDEX ROWID    | OQNCVDSS               |     1 |   252 |     1   (0)| 00:00:01 |
|* 28 |              INDEX UNIQUE SCAN             | PK_OQNCVDSS            |     1 |       |     0   (0)| 00:00:01 |
|  29 |            VIEW                            |                        |    29 | 61016 |     5  (40)| 00:00:01 |
|  30 |             SORT UNIQUE                    |                        |    29 |  1138 |     5  (80)| 00:00:01 |
|  31 |              UNION-ALL                     |                        |       |       |         |     |
|  32 |               INDEX FULL SCAN              | PK_SOURCE_TARGET_RULES |    20 |   760 |     1   (0)| 00:00:01 |
|  33 |               TABLE ACCESS FULL            | CARRYOVER_ISOC_MAPPING |     9 |   378 |     2   (0)| 00:00:01 |
|* 34 |           TABLE ACCESS BY INDEX ROWID      | UHCDN_RATES            |    21 |  2079 |    46   (0)| 00:00:01 |
|* 35 |            INDEX RANGE SCAN                | PK_UHCDN_RATES         |    72 |       |     2   (0)| 00:00:01 |
|  36 |          TABLE ACCESS BY INDEX ROWID       | OQNCVDS_TYPES          |     1 |    20 |     1   (0)| 00:00:01 |
|* 37 |           INDEX UNIQUE SCAN                | PK_OQNCVDS_TYPES       |     1 |       |     0   (0)| 00:00:01 |
|  38 |         TABLE ACCESS FULL                  | BILLING_FREQ           |     8 |    96 |     2   (0)| 00:00:01 |
|  39 |        TABLE ACCESS BY INDEX ROWID         | UNIT_TYPES             |     1 |    15 |     1   (0)| 00:00:01 |
|* 40 |         INDEX UNIQUE SCAN                  | PK_UNIT_TYPES          |     1 |       |     0   (0)| 00:00:01 |
|  41 |       TABLE ACCESS BY INDEX ROWID          | SPEED_CODES            |     1 |     7 |     1   (0)| 00:00:01 |
|* 42 |        INDEX UNIQUE SCAN                   | PK_SPEED_CODES         |     1 |       |     0   (0)| 00:00:01 |
|  43 |      INDEX FAST FULL SCAN                  | PK_OFFER_PROD          |  1255 |  7530 |     3   (0)| 00:00:01 |
|* 44 |     INDEX RANGE SCAN                       | PK_OZBJZFD_OQNCVDSS    |     1 |    12 |     3   (0)| 00:00:01 |
|* 45 |      INDEX UNIQUE SCAN                     | PK_OQNCVDSS            |     1 |     6 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

AUTOTRACE statistics of the modified query is also shown below:
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     479315  consistent gets
       2097  physical reads
          0  redo size
    9673175  bytes sent via SQL*Net to client
      13535  bytes received via SQL*Net from client
       1141  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
     113976  rows processed

It can be seen that after tunning the "consistent gets" drop to 479,315 from 4,242,325. In the  production database, I created a SQL Profile to enforce the better plan.

Monday, September 09, 2013

My Experience of Data Pump Export and Import for Database Migration

Recently I have carried out a test of using Data Pump Export and Import utilities to migrate a 17 TB database from Solaris to Linux platform, also from single-instance, conventional file system to a two-node RAC on ASM. During the exercise I have learned and become more familiar with quite a few interesting features or options of Data Pump Export and Import utilites and encountered a few difficulties as well.

The first obstacle I met was that I was unable to do Data Pump Export to the NFS storage. We have the NFS storage that is shared between the Solaris and Linux server. At first I tried to perform Data Pump Export to the NFS storage directly. Unfortunately, the job was just stuck there for long time. Having researched this issue for a while and tried the options mentioned in a MOS note (See References [1]) with the help of a SA, I just could not get this worked out. Finally we decided to export to the local file system first then move the dump files to the NFS. I was able to move about 150 GB dump file per hour. Fortunately, the Data Pump Import from NFS storage did not pose any problems.

Table mode Data Pump Export and Import were used in the test. Large tables were typically exported or imported individually and smaller tables were grouped together into several batches. For each large table or a group of smaller tables, I prepared the corresponding parameter files with parallel option if appropriate. In the Import job, I always excluded the index, constraint and statistics. I used SQLFILE option of the impdp command to extract the DDL commands related to the tables, including create table, index and constraint statements etc. Those DDLs were executed after the Import with parallel option for index creation and "ENABLE NOVALIDATE" for constraints typically. Therefore the database migration actually consisted of multiple tables migration jobs. For each tables migration, I performed and recorded the timing of the following steps: export, move dump file to NFS, import, DDLs for index etc. Those tables migration jobs were overlapped as two or three of them were executed at the same time and could be in different steps.

Shortly after the onset of the test I found out that Data Pump Export was extremely slow on a big table with lob column. The  export job was unable to run in parallel regardless of the parallel setting. The segment sizes of this big table are 237 GB and 382 GB for table and lob respectively. The table uses a sequence-based PK column called "EMAIL_SEQ_ID". To speed up the export, I started 23 export jobs at the same time, each job exported a particular range of rows based on the PK. The key is to use the QUERY option for the expdp command. The QUERY option contains a WHERE clause that specifies the PK range. Finally I was able to export this table in 30 hours, move the 420 GB dump files in 2 hours, import in about 15 hours and execute DDL for indexes etc in 2 hours. It is worth noting that although we can perform the lob table export job in parallel by such an approach, we cannot do the same for import. The import job will hold a TM lock for the table, so only one import job can run at a time. I used this approach for several big lob tables. Appendix showed example Shell script to prepare expdp or impdp parfile. By the way, later I found that unable to export in parallel for lob table is a known issue in the Oracle community. (see Reference [3]). A blog post suggested using rowid based approach to separate the rows of lob tables. (see Reference [4])

Occasionally I made mistakes and needed to terminate an export or import job. I found it very convenient to use KILL_JOB command in the interactive mode. First, I need to know the job name either from log file or from dba_datapump_jobs view. Then I can enter the interactive mode using attach option:

expdp / attach=[job_name]

Then I issued "KILL_JOB" command. Note when using KILL_JOB to the expdb, all dump files will be wiped out.

During the test, I have also learned that Data Pump Import can be done through db link. When specifying a source database link for the NETWORK_LINK option of impdp command, import job will retrieve data from the db link, and write the data directly to the target database There are no dump files involved.

I have mentioned previously SQLFILE option can be used to extract DDLs. But this option only available for impdp. Suppose I have a table in a database and I want to extract DDLs related to this table including indexes, constraints, object grants, comments, triggers etc associated with it, how should I do? Previously I always use DBMS_METADATA package for such tasks. If we create a db link to the database itself, we can use impdp with NETWORK_LINK and SQLFILE options to accomplish it very neatly.

Using export/import to migrate the database has one advantage over Transportable Tablespace approach at least: it allows the re-organization of tables and indexes. i.e. it is easy to move tables and indexes to different tablespaces if desired.

I completed the 17 TB database migration with the size of about 12 TB in the target database in about a month. The test was interrupted by other tasks with higher priority. I did total 17 tables migrations to complete the whole database migration. Ordered by end-to-end time (including export, move to NFS, import, DDL steps), they were refered to as as batch 1 to 17 here:

batch   time (hours) 
-----   ----------- 
1        50
2        30
3        25
4        24
5        21
6        16
7        15
8        14
9        11
10       10
11       10
12       9.5
13       5.5
14       5
15       3.5
16       3
17       3
------------



Based on above timing, if I perform the migration task as continuely as possible, I may be able to compete it in 5-7 days. The ultimate goal is to do a zero-down time migration. We plan to use oracle Golden Gate to caputure the changes at source during the database migration. So my next task will be investiagating whether this is possible in practise in our environment.

References:
[1] 781349.1 - ORA-27054: NFS file system where the file is created or resides is not mounted with correct options [ID 781349.1])
[2] Sample Export and Import parameter files:
 -- expdp.par  ----


 DIRECTORY=dpump_dir3
 DUMPFILE=dpump_dir3:email%U_q2.dmp
 TABLES=TTQ_GMPX.TTQ_EMAIL_EVENT_DETAILS
 LOGFILE=dpump_dir3:TTQ_EMAIL_EVENT_DETAILS_q2.log
 #PARALLEL=20
 FILESIZE=5G
 JOB_NAME=exp_email_q2
 QUERY="WHERE email_seq_id >=2*200000000   and email_seq_id < 3*200000000"


  -- impdp.par ----

 DIRECTORY=dpump_dir
 DUMPFILE=dpump_dir:email%U_q2.dmp
 TABLES=TTQ_GMPX.TTQ_EMAIL_EVENT_DETAILS
 TABLE_EXISTS_ACTION=APPEND
 LOGFILE=dpump_dir:TTQ_EMAIL_EVENT_DETAILS_q2_imp.log
 PARALLEL=2
 JOB_NAME=imp_email_q2
 EXCLUDE=index,constraint,statistics
 #EXCLUDE=statistics
 transform=storage:n,oid:n
 CLUSTER=NO

[3] Bug 5599947 - Export Data Pump is slow when table has a LOB column - Defect: Bug:5599947 "DATAPUMP EXPORT VERY SLOW"

[4] http://jensenmo.blogspot.com/2012/10/optimising-data-pump-export-and-import.html

[5] Some other references:
Master Note for Data Pump [ID 1264715.1]
Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp) [ID 453895.1]
Parallel Capabilities of Oracle Data Pump [ID 365459.1]


Appendix - sample shell script to generate expdp/impdp par file for big lob table

#!/bin/ksh
i=21
while [[ $i -le 43 ]];do
 
  ((j=i+1))
  PARFILE="EMAIL_CONTENT_q$i.par"
  echo "DIRECTORY=dpump_dir3" > $PARFILE 
  echo "DUMPFILE=dpump_dir3:ecntnt_q${i}_%U.dmp" >> $PARFILE 
  echo "TABLES=TTQ_GMPX.EMAIL_CONTENT" >> $PARFILE
  echo "LOGFILE=dpump_dir3:EMAIL_CONTENT_q$i.log" >> $PARFILE 
  echo "FILESIZE=10G" >> $PARFILE
  echo "JOB_NAME=exp_ecntnt_q$i" >> $PARFILE
  echo "QUERY=\"WHERE email_seq_id > $i * 2000000  and email_seq_id <= $j * 2000000\"">> $PARFILE
 
 
  echo "i=$i"
  echo "j=$j"
  ((i=i+1))
done

--- sample script to generate impdp par file for big lob table -----------------

i=6
while [[ $i -le 43 ]];do
 
  ((j=i+1))
  PARFILE="EMAIL_CONTENT_imp_q$i.par"
  echo "DIRECTORY=dpump_dir" > $PARFILE 
  echo "DUMPFILE=ecntnt_q${i}_%U.dmp" >> $PARFILE 
  echo "TABLES=TTQ_GMPX.EMAIL_CONTENT" >> $PARFILE
  echo "TABLE_EXISTS_ACTION=APPEND" >> $PARFILE
  echo "LOGFILE=EMAIL_CONTENT_q${i}_imp.log" >> $PARFILE 
  echo "JOB_NAME=imp_ecntnt_q${i}" >> $PARFILE
  echo "#EXCLUDE=index,constraint,statistics" >> $PARFILE
  echo "EXCLUDE=statistics" >> $PARFILE 
  echo "transform=storage:n,oid:n" >> $PARFILE
  echo "CLUSTER=NO" >> $PARFILE
 
 
  echo "i=$i"
  echo "j=$j"
  ((i=i+1))
done


Friday, September 06, 2013

Find just one set of columns that uniquely identify a row

If a table has PK, we need to show the list of PK columns. If the table does not have PK, we need to see if we can find one set of columns that can uniquely identify the row. One of my colleagues wrote a PL/SQL script to look at dba_constraints to do that. But I have learned that tables could have unique indexes but don't have unique constraints; on the other hand, if we create a unique constraint, we will have a unique index. For the following test tables t1,t2,t3,t4,t5:


create table t1(c1 number, c2 number, c3 number);
alter table t1 add constraint t1_pk primary key (c1, c2);

create table t2(c1 number, c2 number, c3 number);
alter table t2 add constraint t2_uk unique (c1, c2);


create table t3(c1 number, c2 number, c3 number);
create unique index t3_ix on t3(c1,c2)

create table t4(c1 number, c2 number, c3 number);
create unique index t4_ix1 on t4(c1,c2);
create unique index t4_ix2 on t4(c1,c3);

create table t5(c1 number, c2 number, c3 number);


The original script will output something like:

T5 does not have PK or any unique cols
T4 does not have PK or any unique cols
T3 does not have PK or any unique cols
T2 does not have PK but has unique cols
T2 does not have PK but has unique cols such as C1,C2
T1 has PK
T1 PK cols : C1,C2

You can see that the statements about T3 and T4 are not correct, as they have unique indexes. Below is my modified script to look at dba_indexes in stead of dba_constraints.



-- List PK columns or unique columns
-- find PK columns and if no PK, find one of the unique columns
---


set serveroutput on

declare
cntpk number;
cntuk number;
initial number;
pk varchar2(1000);
uk varchar2(1000);

-- cursor tab_cur is  select upper(source_owner) source_owner,upper(source_object_name) source_object_name from dbscrub.config_table;

cursor tab_cur is  select upper(owner) source_owner,upper(table_name) source_object_name from dba_tables 
 where owner='Vxxxx' and table_name in ('T1','T2','T3','T4', 'T5');

begin

  for tab_rec in tab_cur
  loop

  pk :='';
  uk :='';
  initial := 1;

  select count(1) into cntpk from dba_constraints where owner=tab_rec.source_owner and table_name=tab_rec.source_object_name and constraint_type = 'P';
  -- select count(1) into cntuk from dba_constraints where owner=tab_rec.source_owner and table_name=tab_rec.source_object_name and constraint_type = 'U';
  select count(1) into cntuk from dba_indexes where owner=tab_rec.source_owner and table_name=tab_rec.source_object_name and uniqueness = 'UNIQUE';

  if cntpk != 0  then

    -- update dbscrub.config_table set has_pk_uk='YES' where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;
    dbms_output.put_line ( tab_rec.source_object_name  || ' has PK' );

    for i in (SELECT b.table_name, b.column_name, b.position
 FROM dba_constraints a, dba_cons_columns b
       WHERE     a.owner = b.owner
      AND a.constraint_name = b.constraint_name
      AND constraint_type = 'P'
       AND b.owner = tab_rec.source_owner
       AND b.table_name = tab_rec.source_object_name
       ORDER BY b.position)

    loop
    if initial=1 then
      pk:=i.column_name;
      initial:=initial+1;
    else
      pk:=pk||','||i.column_name;
    end if;
    end loop;

    -- update dbscrub.config_table set pk_uk_columns=pk where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;
    dbms_output.put_line ( tab_rec.source_object_name  || ' PK cols : ' || pk );

  elsif cntpk = 0 and cntuk != 0 then

   --  update dbscrub.config_table set has_pk_uk='YES' where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;
    dbms_output.put_line ( tab_rec.source_object_name  || ' does not have PK but has unique cols' );

    /*  -- seems not correct
  for i in (SELECT b.table_name, b.column_name, b.position
      FROM dba_constraints a, dba_cons_columns b
     WHERE     a.owner = b.owner
    AND a.constraint_name = b.constraint_name
    AND constraint_type = 'U'
     AND b.owner = tab_rec.source_owner
     AND b.table_name = tab_rec.source_object_name
       AND rownum = 1
     ORDER BY b.position)
   */
   -- replaced by :
   /*
   for i in ( 
   SELECT b.table_name, b.column_name, b.position
      FROM ( select * from  dba_constraints a1
              where a1.owner=tab_rec.source_owner
               and  a1.table_name=tab_rec.source_object_name
               and  a1.constraint_type='U'
               and  rownum=1
           ) a,
           dba_cons_columns b
     WHERE     a.owner = b.owner
     AND       a.constraint_name = b.constraint_name
     ORDER BY b.position )
    */

   -- check dba_indexes and dba_ind_columns instead

  for i in (
  SELECT b.table_name, b.column_name, b.column_position
      FROM ( select * from  dba_indexes a1
              where a1.owner=tab_rec.source_owner
               and  a1.table_name=tab_rec.source_object_name
               and  a1.UNIQUENESS='UNIQUE'
               and  rownum=1
           ) a,
           dba_ind_columns b
     WHERE     a.owner = b.index_owner
     AND       a.index_name = b.index_name
  ORDER BY b.column_position )
  
  loop
  if initial=1 then
  uk:=i.column_name;
  initial:=initial+1;
  else
  uk:=uk||','||i.column_name;
  end if;
  end loop;

  --  update dbscrub.config_table set pk_uk_columns=uk where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;
    dbms_output.put_line ( tab_rec.source_object_name  || ' do not have PK but has unique cols such as ' || uk );

  else
   

   --    update dbscrub.config_table set has_pk_uk='NO' where source_owner=tab_rec.source_owner and source_object_name=tab_rec.source_object_name;

    dbms_output.put_line ( tab_rec.source_object_name  || ' does not have PK or any unique cols');

  end if;
  end loop;
  commit;
end;
/



The modified script's output looks like:

T5 does not have PK or any unique cols
T4 does not have PK but has unique cols
T4 does not have PK but has unique cols such as C1,C2
T3 does not have PK but has unique cols
T3 does not have PK but has unique cols such as C1,C2
T2 does not have PK but has unique cols
T2 does not have PK but has unique cols such as C1,C2
T1 has PK
T1 PK cols : C1,C2

BTW, the need for this script comes from our tasks of configuring Oracle GoldenGate.

Thursday, September 05, 2013

My Oracle Database Troubleshooting Scripts






Whenever I troubleshoot Oracle production database performance issues or just need to do a health check, I will start with a script called event.sql, then I will most likely execute several other scripts from the SQL* Plus command line interface. In the above picture, I grouped my often-used scripts into four categories: Wait Events, Sessions, SQLs and Workload. After executing  event.sql, the second script I probably will use is among those green ones; and the third probably from those among blue ones. The methodology  I adopted is rooted in wait event analysis. All the scripts mentioned are in this zip file. Those scripts are intended to be used for pinpointing or narrowing down the problem area in the first 5 or 10 minnutes of troubleshooting production database issues, which often have a sense of urgency and require the solutions to stablize the system in short time.

In the following  I will give short descriptions of the purpose of each script. Notice in a previous post I already explained about a few scripts.

  • Wait Events

event.sql  - first script to execute usually; giving the count of each wait event; a quick way to show if there are  any abnormalities; in a typical OLTP type database, we shall see 'db file sequential read' as the most counted event after idle events. Tanel Poder shared the thoughts about first round session troubleshooting here.

eventashg.sql - show top 5 wait events for a given interval from gv$active_session_history; kind of like AWR top 5 wait events section; RAC -aware makes it probably the first script I will use to check RAC database health.

sw.sql - from Tanel Poder;  given SID, show current wait event of a session.

snapper.sql - from Tanel Poder; very famous, check this link! In the SQL Server world, there is something similar.  (snapper_dflt.sql in the zip file is  a wrapper for convenience by me).


  • Workload
logsw.sql - Display the number of log switch in every hour in a tabular format. Very useful to understand the workload distribution. From Jeff Hunter.

sysmetric.sql - Display some system metrics from gv$system_metric_history such as Redo Generated Per Sec, Host CPU Utilization (%) and User Transaction Per Sec etc in the past 60 minutes. RAC-aware makes it the 1st or 2nd script I use to check RAC database.

aas_ash.sql and aas_awr.sql - Display average active sessions from ASH view and AWR, respectively. AAS is an indicator for workload or performance changes.

  • Sessions
sesevt.sql - Given wait event name, show the sessions basic information.

qlocks.sql - Display blockers and waiters based on v$lock view.

longsql.sql - Display long running SQLs; A quick way to find candidate "bad" sqls in the database.

longops.sql - Display long operations from v$session_longops.

pxses.sql - Display  parallel execution server sessions.

snapper.sql - Yes again! It is really about sessions statistics and wait events.

sessid.sql  - Given session SID, display the session related information.

ses*.sql - All those are querying v$session given some inputs such as: machine, server process id, OS user,  database user and module etc.

sess_kill_batch.sql - Generate kill database sessions commands.

sess_kill_os.sql  - Generate 'kill -9' command for killing server processes at OS level.

  • SQLs
 xplan.sql - Given sql_id, show the execution plan from cursor through dbms_xplan.display_cursor().

sqlhistory.sql - by Tim Gorman; query the "history" of a specified SQLstatement, using its "SQL ID" across all database instances in a database, using the AWR repository. Show execution statistics per execution plan.

tabix.sql - List of the indexes of a table and show on which columns and in which order the indexes are.Very usefull when tune a SQL.

tabcols.sql - Display table column CBO statistics. Very useful when doing SQL tunning. (from: http://www.roughsea.com ).

bindvar.sql - When tunning a SQL, I often need to find representative bind values from this script.

get_ddl.sql - based on dbms_metadata package to obtain definitions of objects. When tuning a SQL, sometimes we want to know the underline table structure and index definitions

Below are the screenshots of  the output of several scripts:





Thursday, August 29, 2013

Practise Migration Through Transportable Tablespace from non-ASM to ASM

References:

http://relcon.typepad.com/relatively_speaking/2009/10/migrating-an-oracle-database-from-solaris-to-linux.html

http://www.oracle.com/technetwork/database/features/availability/maa-wp-11g-platformmigrationtts-129269.pdf


Source database OS is Solaris and using OS file system. Target database is in on Linux and ASM.

Steps:

1. Check that platforms are compatible.

Run this query on the database you want to migrate from and it will list the platforms you can migrate to:

select * from V$TRANSPORTABLE_PLATFORM;


check the value of ENDIAN_FORMAT, if you are migrating from one format to another you are going to need to convert your database files, so for example going from Solaris to Linux you will need to convert.
PLATFORM_ID PLATFORM_NAME                        ENDIAN_FORMAT
----------- ------------------------------------ --------------
          6 AIX-Based Systems (64-bit)           Big
         16 Apple Mac OS                         Big
         21 Apple Mac OS (x86-64)                Little
         19 HP IA Open VMS                       Little
         15 HP Open VMS                          Little
          5 HP Tru64 UNIX                        Little
          3 HP-UX (64-bit)                       Big
          4 HP-UX IA (64-bit)                    Big
         18 IBM Power Based Linux                Big
          9 IBM zSeries Based Linux              Big
         10 Linux IA (32-bit)                    Little
         11 Linux IA (64-bit)                    Little
         13 Linux x86 64-bit                     Little
          7 Microsoft Windows IA (32-bit)        Little
          8 Microsoft Windows IA (64-bit)        Little
         12 Microsoft Windows x86 64-bit         Little
         17 Solaris Operating System (x86)       Little
         20 Solaris Operating System (x86-64)    Little
          1 Solaris[tm] OE (32-bit)              Big
          2 Solaris[tm] OE (64-bit)              Big
   

2. You can only migrate tablespaces to a database which uses the same character set and national charcter set, so check if you are migrating to an existing database or make sure to use the same values if you are building a new database.
 srcdb> SELECT * FROM nls_database_parameters order by 1;

 PARAMETER                      VALUE
 ------------------------------ ----------------------------------------
 NLS_CALENDAR                   GREGORIAN
 NLS_CHARACTERSET               WE8ISO8859P1
 NLS_COMP                       BINARY
 NLS_CSMIG_SCHEMA_VERSION       2
 NLS_CURRENCY                   $
 NLS_DATE_FORMAT                DD-MON-RR
 NLS_DATE_LANGUAGE              AMERICAN
 NLS_DUAL_CURRENCY              $
 NLS_ISO_CURRENCY               AMERICA
 NLS_LANGUAGE                   AMERICAN
 NLS_LENGTH_SEMANTICS           BYTE
 NLS_NCHAR_CHARACTERSET         AL16UTF16
 NLS_NCHAR_CONV_EXCP            FALSE
 NLS_NUMERIC_CHARACTERS         .,
 NLS_RDBMS_VERSION              11.2.0.3.0
 NLS_SORT                       BINARY
 NLS_TERRITORY                  AMERICA
 NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
 NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
 NLS_TIME_FORMAT                HH.MI.SSXFF AM
 NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR

 21 rows selected.



 tgtdb> SELECT * FROM nls_database_parameters order by 1;
  
 PARAMETER                      VALUE
 ------------------------------ ----------------------------------------
 NLS_CALENDAR                   GREGORIAN
 NLS_CHARACTERSET               WE8MSWIN1252
 NLS_COMP                       BINARY
 NLS_CURRENCY                   $
 NLS_DATE_FORMAT                DD-MON-RR
 NLS_DATE_LANGUAGE              AMERICAN
 NLS_DUAL_CURRENCY              $
 NLS_ISO_CURRENCY               AMERICA
 NLS_LANGUAGE                   AMERICAN
 NLS_LENGTH_SEMANTICS           BYTE
 NLS_NCHAR_CHARACTERSET         AL16UTF16
 NLS_NCHAR_CONV_EXCP            FALSE
 NLS_NUMERIC_CHARACTERS         .,
 NLS_RDBMS_VERSION              11.2.0.3.0
 NLS_SORT                       BINARY
 NLS_TERRITORY                  AMERICA
 NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
 NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
 NLS_TIME_FORMAT                HH.MI.SSXFF AM
 NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
  
 20 rows selected.

WE8MSWIN1252 is a superset of WE8ISO8859P1:
Difference between WE8MSWIN1252 and WE8ISO8859P1 characterset (Doc ID 341676.1)

Some problems repored from WE8ISO8859P1 to WE8MSWIN1252: https://forums.oracle.com/message/9885182#9885182


3. Assuming you are good to go you need to determine which tablespaces hold the data you want to transport and then double check that the list is self-contained, execute this PL/SQL procedure to check the tablespaces you plan to migrate:
 sys@srcdb> execute sys.dbms_tts.transport_set_check('tbs1_data,tbs1_indx',true);
  
 PL/SQL procedure successfully completed.
  
 sys@srcdb> select * from sys.transport_set_violations;
  
 no rows selected

    
   Note: these violations must be resolved before the tablespaces can be transported. 

4. The tablespaces need to be in READ ONLY mode in order to successfully run a transport tablespace export:

alter tablespace tbs1_data read only;

alter tablespace tbs1_indx read only;


5. Export the metadata.
 
$ exp userid=\'/ as sysdba\'  file=tbs_exp.dmp log=tba_exp.log transport_tablespace=y tablespaces=tbs1_data,tbs1_indx 
 
Export: Release 11.2.0.3.0 - Production on Thu Aug 29 14:42:18 2013
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, Oracle Database Vault and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TBS1_DATA ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                             EC
EXP-00091: Exporting questionable statistics.
For tablespace TBS1_INDX ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully with warnings.


6. copy the metadata export, datafiles and any schema exports to the new host

cp tbs1_data_01.dbf /oradbbackup02_NFS/landingpad/dump
cp tbs1_indx_01.dbf /oradbbackup02_NFS/landingpad/dump
cp tbs_exp.dmp /oradbbackup02_NFS/landingpad/dump

Note: /oradbbackup02_NFS is NFS mount at source, and at target the mount is /oradbbackup02

7. convert the datafiles to new platform format using RMAN, in this example we will put the converted datafiles into an ASM instance:
rman  target / 

convert datafile 
  '/oradbbackup02/landingpad/dump/tbs1_data_01.dbf', 
  '/oradbbackup02/landingpad/dump/tbs1_indx_01.dbf'
  from platform 'Solaris[tm] OE (64-bit)' 
  format '+DATA_05' 
  parallelism 4;


Starting conversion at target at 29-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=118 instance=tgtdb2 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=124 instance=tgtdb2 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=137 instance=tgtdb2 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=151 instance=tgtdb2 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/oradbbackup02/landingpad/dump/tbs1_data_01.dbf
channel ORA_DISK_2: starting datafile conversion
input file name=/oradbbackup02/landingpad/dump/tbs1_indx_01.dbf
converted datafile=+DATA_05/tgtdb/datafile/tbs1_data.316.824728129
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:04
converted datafile=+DATA_05/tgtdb/datafile/tbs1_indx.317.824728131
channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:04
Finished conversion at target at 29-AUG-13



8. plug the tablespace into the target database, check the ASM instance to get the names of the converted datafiles:



  -- imp.par
file=tbs_exp.dmp
log=tts.log
transport_tablespace=Y
datafiles= '+DATA_05/tgtdb/datafile/tbs1_data.316.824728129'
,'+DATA_05/tgtdb/datafile/tbs1_indx.317.824728131'


$ imp userid=\'/ as sysdba\' parfile=imp.par


 
Import: Release 11.2.0.3.0 - Production on Thu Aug 29 12:59:02 2013
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
 
Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing OPS$ORACLE's objects into OPS$ORACLE
. . importing table                           "EC"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

9. set the imported tablespaces to read write:

alter tablespace tbs1_data read write;
alter tablespace tbs1_indx read write;

Wednesday, August 28, 2013

Get the row count from index fast full scan

This post is to show using parallel_index hint to enable an index fast full scan access path to obtain the row count of a large table with primary key faster. BO_ID is the primary key column of BO_NBQQJOH table.

The following summarizes the results:

parallel full table scan 14m11s 8.8M consistent gets

parallel index fast full scan 3m49s 2.8M consistent gets


SQL> select /*+ parallel (a 6) */ count(bo_id) from ttq_dqg.bo_nbqqjoh_v a;

COUNT(BO_ID)
------------
   917384760

1 row selected.

Elapsed: 00:14:11.13

Execution Plan
----------------------------------------------------------
Plan hash value: 1529574786

------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |     1 |     7 |   341K  (1)| 01:08:17 |        |      |            |
|   1 |  SORT AGGREGATE        |            |     1 |     7 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |            |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000   |     1 |     7 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |            |     1 |     7 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |            |   912M|  6088M|   341K  (1)| 01:08:17 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| BO_NBQQJOH |   912M|  6088M|   341K  (1)| 01:08:17 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        158  recursive calls
          0  db block gets
    8846234  consistent gets
    8403973  physical reads
          0  redo size
        218  bytes sent via SQL*Net to client
        252  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          1  rows processed




SQL> select /*+ parallel_index ( a, BO_NBQQJOH_NEW_PK, 6) */ count(bo_id) from ttq_dqg.bo_nbqqjoh_v a;

COUNT(BO_ID)
------------
   917391460

1 row selected.

Elapsed: 00:03:49.60

Execution Plan
----------------------------------------------------------
Plan hash value: 1771473710

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name              | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                   |     1 |     7 |   487K  (3)| 01:37:33 |        |      |            |
|   1 |  SORT AGGREGATE           |                   |     1 |     7 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |                   |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000          |     1 |     7 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |                   |     1 |     7 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR    |                   |   912M|  6088M|   487K  (3)| 01:37:33 |  Q1,00 | PCWC |            |
|   6 |       INDEX FAST FULL SCAN| BO_NBQQJOH_NEW_PK |   912M|  6088M|   487K  (3)| 01:37:33 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         30  recursive calls
          0  db block gets
    2854834  consistent gets
    2199288  physical reads
          0  redo size
        237  bytes sent via SQL*Net to client
        252  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>



Friday, July 26, 2013

Find rows that have duplicate values in a column

This post is to describe my test on finding rows that have duplicate values in a column.  The purpose is to understand which method is more efficient: analytic function approach or subquery approach. First of all, a test table called "big_table" was created with 10,000,000 rows and an ID column with number data type. The value of ID is from 1 to 10,000,000. Then I created 10 duplicate rows by the following insert statement:
SQL> insert into big_table select * from big_table where rownum<=10;
I also created an index on the ID column.
SQL> create index big_table_ix on big_table(id) nologging;

Index created.

Elapsed: 00:01:34.60

The analytic function approach used the following SQL statement to find those rows:
        SELECT rid
            FROM (SELECT ROWID rid,
                   ROW_NUMBER () OVER (PARTITION BY id ORDER BY ROWID) rn
                   FROM big_table)
          WHERE rn <>1;

The execution plan of above SQL is as follows:
-----------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |    10M|   238M|       | 90333   (2)| 00:18:04 |
|*  1 |  VIEW               |           |    10M|   238M|       | 90333   (2)| 00:18:04 |
|   2 |   WINDOW SORT       |           |    10M|   171M|   268M| 90333   (2)| 00:18:04 |
|   3 |    TABLE ACCESS FULL| BIG_TABLE |    10M|   171M|       | 32467   (1)| 00:06:30 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"<>1)

The subquery approach used the following SQL statement:
       select rowid
       from big_table  a
       where a.rowid >
         ( select min (rowid)
             from big_table b
             where b.id = a.id) ;

And the execution plan looks like:
------------------------------------------------------------------------------------------------
| Id  | Operation               | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |              |   495K|    17M|       | 77095   (3)| 00:15:26 |
|*  1 |  FILTER                 |              |       |       |       |            |          |
|   2 |   HASH GROUP BY         |              |   495K|    17M|   425M| 77095   (3)| 00:15:26 |
|*  3 |    HASH JOIN            |              |    10M|   346M|   286M| 39374   (2)| 00:07:53 |
|   4 |     INDEX FAST FULL SCAN| BIG_TABLE_IX |    10M|   171M|       |  5303   (3)| 00:01:04 |
|   5 |     INDEX FAST FULL SCAN| BIG_TABLE_IX |    10M|   171M|       |  5303   (3)| 00:01:04 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWID>MIN(ROWID))
   3 - access("B"."ID"="A"."ID")


If there is no index on the ID colume, the execution plan of the subquery approach looks like:
------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |   495K|    17M|       |   131K  (2)| 00:26:18 |
|*  1 |  FILTER              |           |       |       |       |            |          |
|   2 |   HASH GROUP BY      |           |   495K|    17M|   425M|   131K  (2)| 00:26:18 |
|*  3 |    HASH JOIN         |           |    10M|   346M|   286M| 93703   (2)| 00:18:45 |
|   4 |     TABLE ACCESS FULL| BIG_TABLE |    10M|   171M|       | 32467   (1)| 00:06:30 |
|   5 |     TABLE ACCESS FULL| BIG_TABLE |    10M|   171M|       | 32467   (1)| 00:06:30 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWID>MIN(ROWID))
   3 - access("B"."ID"="A"."ID")


I used the AUTOTRACE tool to obtain the Consistent gets and SET TIMING ON before executing the SQLs. I measured three times for each approach. Below are the results:
aproach      consistent get   exeuction time
-------------------------------------------
analytic     146613           1m50s
             146607           1m48s
             146545           1m36s
---------------------------------------------
subquery      47293           2m25s
              47228           2m18s
              47309           2m23s 
---------------------------------------------
subquery     293172           2m27s
(no index)   293146           2m30s
             293172           2m31s
-------------------------------------------
It can be seen that compared to the subquery approach (with index) analytic function approah is faster by ~ 40 s although it requires more logical I/O. The subquery approach (without index) required double I/O compared to the analytic function approach as it needs to scan the big_table twice as shown in the execution plan. The execution time is not determined by I/O, these query is CPU bound. SQL_TRACE/TKPROF can show that clearly. Below is a summary of data from TKPROF report:
approach     cpu       elapsed      query      
-------------------------------------------
analytic    109.57      111.35      146631
subquery    172.68      173.99      47314
-------------------------------------------
Obviously, CPU time is predominant. That explains the very small difference in term of execution time between subquery with and without index approach. It suggests that creating the index on ther id column brings little benefit to find the duplicate values in the column. However, there could be a case that we already know the duplicate values in the column and what we want to do is to remove those extra rows with the duplicate values. This situation acutaully happened recently in our production enviroment. We did a 1.5M data load into a production table. Before the data load we dropped all the indexes of this table. But we made a mistake, we loaded 50K rows twice. As a result, when we tried to create unique index on the pK column we failed. I used the analytic function approach to delete the extra rows, it took 50 min. The delete statement is similar to the following
        delete from big_table 
        where rowid in (  SELECT rid
            FROM (SELECT ROWID rid,
                   ROW_NUMBER () OVER (PARTITION BY id ORDER BY ROWID) rn
                   FROM big_table)
          WHERE rn <>1
         );

The above approach is not the most efficient one in this case. Becasue we already know the 50K duplicate PK values, the better approach is to create a non-unique index on the PK column, construct a driver table (ids) containing the 50K values, using the following SQL:
    delete from big_table a
 where 
    a.id in ( select id from ids)
 and a.rowid > 
   (select min(rowid) 
       from big_table b where b.id = a.id);


This approach took about 20 min including the time to create the index and the driver table.

Monday, July 15, 2013

Redo Reduction by Using Global Tempoary Table

There are about 1M rows in the BIG_TABLE. The goal is to load data (transformed in some way in real situation) from BIG_TABLE to MY_BIG_TABLE.The following two procedures were compared:

  • Procedure test_pro



create or replace procedure test_pro IS
 CURSOR BIG_TABLE_CUR
  IS
    SELECT id ,owner ,object_name ,object_type ,created ,status from big_table;
 
  TYPE BIG_TABLE_TYPE
  IS TABLE OF BIG_TABLE_CUR%ROWTYPE INDEX BY PLS_INTEGER;
  BIG_TABLE_REC BIG_TABLE_TYPE;
 
BEGIN
 
  OPEN BIG_TABLE_CUR;
  LOOP
    FETCH BIG_TABLE_CUR BULK COLLECT
    INTO BIG_TABLE_REC LIMIT 2000;
    EXIT WHEN BIG_TABLE_REC.COUNT = 0;
    FORALL i IN 1 .. BIG_TABLE_REC.count 
    INSERT INTO my_big_table VALUES BIG_TABLE_REC (i);
    COMMIT;
  END LOOP;
  CLOSE BIG_TABLE_CUR;
END;

  • Procedure test_pro2, here my_big_table_gtt is a global tempoary table

create or replace procedure test_pro2 IS
 CURSOR BIG_TABLE_CUR
  IS
    SELECT 
        id ,owner ,object_name ,object_type ,created ,status from big_table;
 
  TYPE BIG_TABLE_TYPE
  IS TABLE OF BIG_TABLE_CUR%ROWTYPE INDEX BY PLS_INTEGER;
  BIG_TABLE_REC BIG_TABLE_TYPE;
 
BEGIN
 
  OPEN BIG_TABLE_CUR;
  LOOP
    FETCH BIG_TABLE_CUR BULK COLLECT
    INTO BIG_TABLE_REC LIMIT 2000;
    EXIT WHEN BIG_TABLE_REC.COUNT = 0;
    FORALL i IN 1 .. BIG_TABLE_REC.count 
    INSERT  INTO my_big_table_gtt VALUES BIG_TABLE_REC (i);
    COMMIT;
  END LOOP;
  CLOSE BIG_TABLE_CUR;
  execute immediate 'insert /*+ append */ into my_big_table select * from my_big_table_gtt';
  commit;
END;

I measured the redo by v$mystat in the test, the result is showed as follows:

test_pro         redo  71M    run time:  4.57 s   
test_pro2        redo   6M     run time: 4.59 s

The test database  is  in noarchivelog mode.

Monday, June 17, 2013

GoldenGate: Steps of Resynchronizing a Table

In the target, Replicat process was abended due to the following error:

2013-06-17 10:17:30  ERROR   OGG-01163  Oracle GoldenGate Delivery for Oracle, rtdnfrd.prm:  Bad column length (3) specified for column TAX_AUTHORITY_TYPE in table WAX_TDN.V_TAX_DETAILS, maximum allowable length is 1.

I checked TAX_AUTHORITY_TYPE colume, it is char(1) in both source and target databases, so not sure how this error comes up. I have to comment it out in the Replicat parameter file in order to re-start the process. As a result, the table is out-of-sync. This actually gives me a chance to pratice steps of resynchronizing a table in GoldenGate for the first time.

I took the following steps:

1. Comment out the table in the Replicat parameter file
Already did.


2. Stop Replicat and start it again so that it continues for unaffected tables
Already did.

3. Record the time stamp on the source system
2013-06-17 13:49

4 Start a copy of the source data for the affected tables
 
Note: before making the copy, try to resolve any long-running transactions

5. Import the copy to the target table

6. Create a new Replicat group for the out-of-sync table, using Begin to start at the source time stamp that you recorded earlier and using the existing train for ExtTrain

Add Replicat  rvtd, ExtTrail ./dirdat/rb, Begin 2013-06-17 13:49:00

7. Create the new parameter file so that it includes 
  HandleCollisions

replicat rvtd
SETENV(ORACLE_HOME="/apps/opt/oracle/product/11.2.0/db_1")
SETENV(ORACLE_SID = "tdnprdfd1")
SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
-- DBOPTIONS SUPPRESSTRIGGERS
userid
gg_owner@tdnprdfd1, password AADAAAAAAAAAAAHAJIKGFGKGRJTBJBCIZGUERJNHBFPBLCAEOBUADFWASJMJCDWEICWGBEGHOIRESCPA, encryptkey securekey1
discardfile ./dirrpt/RTDNFRD.dsc, Append, megabytes 1
handlecollisions
assumetargetdefs
MAP WAX_TDN.V_TAX_DETAILS,             TARGET WAX_TDN.V_TAX_DETAILS;


8. Start the new Replicat

9. View the new Replicat's lag until it shows "At EOF, no more records to process"
GGSCI> Send replicat rvtd, GetLag

10. Turn off HandleCollisions in the new replicate with
GGSCI> send replicat rvtd NoHandleCollisions

11. Edit the parameter file to comment out or remove HandleCollisions if you ever bounce the process later
(The next steps merge the table back with the others so that only one Replicat group is needed

12. Stop Extract at source

13. View both Replicats's lag until you see "EOF" again

GGSCI> Send replicat rtdnfrd, GetLag
GGSCI> Send replicat rvtd, GetLag

14 stop both Replicats

15. Uncomment the resynced table in the original Replicat parameter file

16. Start Extract

17. Start the original Replicat:
GGSC> start rtdnfrd

18. Delete the new Replicat that you created:
GGSCI> delete replicat rvtd

All done!

GoldenGate Replicat Process Abending due to Tablespace Full and Discard File Exceeding Max Bytes

We have a cron job set up to monitor errors in the GoldenGate ggserr.log. This monrining, in a target database, we recieved:

< 2013-06-17 02:58:17  ERROR   OGG-01172  Oracle GoldenGate Delivery for Oracle, rvasip.prm:  Discard file (./dirrpt/RVASIP.dsc) exceeded max bytes (1000000).
< 2013-06-17 02:58:17  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rvasip.prm:  PROCESS ABENDING.


It appeared that Replicat process abended due to  Discard file exceeded max bytes.
Discard file is used by GoldenGate to log records it cannot proccess. The maximum size of the discard file can be specified by MAXBYTES or MEGABYSTS options,
the defaults are 1000000 or 1MB.  If the specified size is exceeded, the process will abend.

Further troubleshooting showed the reason for the discard file filled up was due to a tablespace filled up in this case. In the RVASIP.dsc files we can found:

OCI Error ORA-01653: unable to extend table PPOWNER.VZ_JNR_FEED_TRX_LOG by 8192 in tablespace PPOWNER_DATA_1 (status = 1653). INSERT INTO "PPOWNER"."VZ_JNR_FEED_TRX_LOG" ("ID","JOURNAL_ID"
,"TRX_EXT_ID","BILLED","DB_MODIFICATION_DATE","DB_CREATION_DATE","SUB_TRX_ID","TRX_ID","CHG_ATTR","ACCESS_TYPE") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9)
Aborting transaction on ./dirdat/rt beginning at seqno 21275 rba 3795007
                         error at seqno 21275 rba 5169654


To fix the problem, I renamed RAVSIP.dsc, changed the max bytes of discard file to be 10MB in the parameter file:

discardfile ./dirrpt/RVASIP.dsc, Append,megabytes 10

Then I stopped and started the Replicat process. I have verified that those discarded dmls recorded in the discard file have been applied after Replicat process re-started. no manual intervene is ndeed.

Monday, June 03, 2013

Tuning a Hierarchical Query



Encountered an expensive production sql today, basically it is in the following structure:
select BM.*, V.*  
FROM 
   BMXYZ BM,
   VXYZ V,
   BBXYZ BB
WHERE 
  V.BO_ID=BM.BO_ID
 AND BM.VOL_PARENT_BO_ID IN(SELECT B.VOL_PARENT_BO_ID
                            FROM BMXYZ B
                        START WITH BB.BO_ID=B.VOL_PARENT_BO_ID
                CONNECT BY PRIOR B.BO_ID = B.VOL_PARENT_BO_ID )
AND BB.USER_ID='xyzuvw'
AND V.CONTENT_VENDOR_ID='3000000'
;

At the first glance, it seems there are no join conditions involving BB. Finally I was able to understand what the sql tries to do :

(1) obtain a set of BO_ID's from table BB
(2) for each BO_ID in the set, find all child rows of it from the table BM
(3) finally row source from (2) join table V.

After rewriting it as follows, the query run much faster with only hundreds gets:
select BM.*, V.*  
FROM 
   BMXYZ BM,
   VXYZ V
WHERE 
  V.BO_ID=BM.BO_ID
 AND BM.VOL_PARENT_BO_ID IN(SELECT B.VOL_PARENT_BO_ID
                            FROM BMXYZ B, (select bo_id from BBXYZ  where user_id='xyzuvw') BB
                        START WITH BB.BO_ID=B.VOL_PARENT_BO_ID
                CONNECT BY PRIOR B.BO_ID = B.VOL_PARENT_BO_ID )
AND V.CONTENT_VENDOR_ID='3000000'
;

Friday, May 10, 2013

A Case of Changing or Creating OSDBA and OSOPER Groups

Goal

Created an Unix user (e.g. tier1 ) in the Oracle database server that can perform some database administration tasks but cannot be used to view or modify any application data.


Current typical Unix/Liux Environment:

- OS user: "oracle" is the Oracle software owner and its primary group is "dba" (i.e. Oracle database files have ownership oracle:dba )

- Assuming OSDBA group is "dba" (i.e. every os user that belongs to "dba" group can login as sysdba and view any data)

Suggested changes


- add a Unix group "oper" if not exist
# /usr/sbin/groupadd -g 503 oper

- add a Unix group "dbax" if not exist
# /usr/sbin/groupadd -g 504 dbax

- Change OSDBA group to "dbax" and define OSOPER group in $ORACLE_HOME/rdbms/lib/config.c, edit and ensure the following:
#define SS_DBA_GRP "dbax"
#define SS_OPER_GRP "oper"
shutdown instance and relink:
  cd $ORACLE_HOME/rdbms/lib/
   mv config.o config.o.orig
    make -f ins_rdbms.mk ioracle

- assign "dbax" as secondary unix group to "oracle"
# /usr/sbin/usermod -g dba -G dbax oracle

- create tier1 user with priamry group as "dba" and secondry group as "oper" # /usr/sbin/useradd -g dba -G oper tier1

Test cases


Test case 1 - "oracle" user can login as sysdba as it belongs to OSDBA group:dbax

[oracle@localhost ~]$ id
uid=500(oracle) gid=54322(dba) groups=504(dbax),54322(dba)
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 9 13:37:51 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> shutdown 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup 
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             390073016 bytes
Database Buffers           58720256 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.
SQL>

Test case 2 - "tier1" user can login as sysoper but not as sysdba

[tier1@localhost ~]$ id
uid=502(tier1) gid=54322(dba) groups=54322(dba),54324(oper)
[tier1@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 9 13:50:41 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges


SQL> conn / as sysoper
Connected to an idle instance.
SQL> startup 
ORACLE instance started.
Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Test case 3 - "tier1" and "oracle" belong to same primary OS group "dba", so "tier1" can operate on various Oracle files based on group privillege:

[tier1@localhost 2013_05_09]$ id
uid=502(tier1) gid=54322(dba) groups=54322(dba),54324(oper)
[tier1@localhost 2013_05_09]$ ls -lh o1_mf_1_401_8rr62tq0_.arc
-rw-rw---- 1 oracle dba 7.2M May  9 14:42 o1_mf_1_401_8rr62tq0_.arc
[tier1@localhost 2013_05_09]$ mv o1_mf_1_401_8rr62tq0_.arc /tmp
[tier1@localhost 2013_05_09]$ ls -lh o1_mf_1_401_8rr62tq0_.arc
ls: o1_mf_1_401_8rr62tq0_.arc: No such file or directory


Thursday, March 28, 2013

Problems of Temporary Tablespace Usage after a Recent Release

After a recent release, we’ve encountered two problems in a production database:  (1) Temporary tablespace usage high; (2) Instance shutdown when temporary tablespace space used up.
 
Problem 2 turns out to be an Oracle feature:

 

Root cause of crash appears to be "Bug" 7691270 - Crash the DB in case of write errors (rather than just offline files) [ID 7691270.8]

Datafile Write Errors (Oracle Database 11gR1 to 11gR2 Change):

Starting with the 11.2.0.2 patchset for Oracle Database 11g Release 2, a write error to any data file will cause the database to perform a shutdown abort of the instance.

In prior releases, I/O errors to datafiles not in the system tablespace would offline the respective datafiles when the database is in archivelog mode. This behavior is not always desirable. Some customers would prefer that the instance crash due to a datafile write error. A new hidden parameter called _datafile_write_errors_crash_instance has been introduced to control whether the instance should crash on a write error or if the datafiles should be taken offline on a write error.

If _datafile_write_errors_crash_instance = TRUE (default) then any write to a datafile which fails due to an IO error causes an instance crash.
If _datafile_write_errors_crash_instance = FALSE then the behavior reverts to the previous behavior (before this fix) such that a write error to a datafile offlines the file (provided the DB is in archivelog mode and the file is not in SYSTEM tablespace in which case the instance is aborted)

Best Practices: Monitor temporary tablespace usage with alerts, tune SQL to use less temporary space.

 
The increasing usage of temporary space after the release was due to the increasing usage of temporary lobs, which reside in the temporary tablespace. For example, there were 30+ queries using to_clob function in the release. Oracle won’t release temp lob space in the Weblogic connection unless we recycle the Weblogic app servers. There are several related MOS notes about this behavior:
 

  • How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (Doc ID 802897.1)
  • How to Release Temporary LOB Segments without Closing the JDBC Connection (Doc ID 1384829.1)
  • Bug 5723140 - Temp LOB space not released after commit (Doc ID 5723140.8)
  • Temporary LOB space not released after commit: ora-1652 being hit (Doc ID 750209.1)


 

In the above notes, the fix to the temp lob space releasing problem is to set 60025 event. Below is a simple test to demonstrate this event works:







In Session 1, execute the following statement

select to_clob(text) from dba_source where rownum &lt;100;



In session 2 monitoring session 1 temp space usage with:

SELECT   S.sid || ',' || S.serial# sid_serial,
         S.username,
         S.osuser,
         P.spid,
         S.module,
         S.machine,
         S.program,
         SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
         T.tablespace,
         T.segtype,
         COUNT(*) sort_ops
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
and    s.sid=2927   --- this is the sid of session 1
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
         S.machine, S.program, TBS.block_size, T.tablespace,T.segtype 
ORDER BY mb_used 
/


You will see even after session 1 finish and idle there, in session 2 you will have 1MB temp space allocated to it


Second test:

In session 1, before run that query do:
alter session set events '60025 trace name context forever';


Then in session 2, you will see after session 1 becomes idle, no temp space allocated.






We did not set this event in our production to fix the issue, instead, dev team change the code and avoid use temporary lob. To demonstrate difference of the temp space usage before and after release and after the fix, I queried dba_hist_active_sess_history view.  From 11g there is a field called TEMP_SPACE_ALLOCATED in this view. So it is possible to calculate the average temp space allocated per active session during a certain period of time:

 





===== Look at all active session between 16:00 ~ 16:59 in past 30 days in dba_hist_active_sess_history
select stime, size_m/c avg_mb_sess from
(
  select to_char(sample_time, 'YYYY-MM-DD HH24') stime,count(*) c, round(sum(TEMP_SPACE_ALLOCATED)/1024/1024) size_m
  from dba_hist_active_sess_history
  where to_char(sample_time, 'HH24') = 16 and sample_time > sysdate-30
  group by to_char(sample_time, 'YYYY-MM-DD HH24')
)
order by 1

STIME         AVG_MB_SESS
------------- -----------
2013-02-26 16   .00135459
2013-02-27 16  .002029264
2013-02-28 16  .000963298
2013-03-01 16  .000670166
2013-03-02 16  .001373417
2013-03-03 16   .00049838
2013-03-04 16  .000991616
2013-03-05 16  1.49810976
2013-03-06 16  1.12322684
2013-03-07 16  .000634719
2013-03-08 16  .001408054
2013-03-09 16  .081960706
2013-03-10 16  .000473485
2013-03-11 16  1.21929313
2013-03-12 16  .001113121
2013-03-13 16  .001084893
2013-03-14 16  .000864518
2013-03-15 16  1.70157371
2013-03-16 16  .114989041
2013-03-17 16  231.198656
2013-03-18 16  254.193626
2013-03-19 16  96.2858973
2013-03-20 16   93.731513
2013-03-21 16  135.284032
2013-03-22 16    1.462451
2013-03-23 16  .000423101
2013-03-24 16  .000462321
2013-03-25 16  .000395163
2013-03-26 16  16.1351924
2013-03-27 16  1.84635633



The release was happened on Saturday night of Mar 16 and Dev team fixed code on Mar 21 night completely. It can be seen, from Mar 17 till Mar 21, the average temp space allocated per active sessions were much higher compared to those before release and after the fix. During Mar 17 and Mar 21, what we did was that once the total temp space reached 150 GB, we recycled the app servers  in a controlled manner to release the temp space.