SQL Tuning beyond the Execution Plan

Recently my client asked me to help one development team to find out why one query performed badly for the past few weeks.

The query is a simple update SQL statement with the sqlid of 1am0d1r44m7sx.

UPDATE MYDB_STAGING
SET IS_PROCESSED = 'R',
NM_MODIFIED ='MYDB_VERSIONING'
WHERE DT_COB =:B1
AND IS_LATEST =0
AND IS_PROCESSED = 'N'

Check out the AWR plan and found out two plans associated with this SQL.

SQL> @dplan_awr
Enter value for sql_id: 1am0d1r44m7sx
Enter value for plan_hash_value:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1am0d1r44m7sx
--------------------
UPDATE MYDB_STAGING SET IS_PROCESSED = 'R', NM_MODIFIED
='MYDB_VERSIONING' WHERE DT_COB=:B1 AND IS_LATEST=0 AND IS_PROCESSED =
'N'

Plan hash value: 1281386787
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                    |                         |       |       |  2398 (100)|          |       |       |
|   1 |  UPDATE                             | MYDB_STAGING            |       |       |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE            |                         |    26 |  1742 |  2398   (1)| 00:00:29 |   KEY |   KEY |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| MYDB_STAGING            |    26 |  1742 |  2398   (1)| 00:00:29 |   KEY |   KEY |
|   4 |     INDEX RANGE SCAN                | ACTIVE_MKT_DATA_STG_IDX |   104K|       |  2398   (1)| 00:00:29 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - :B1 (DATE): 09/12/2014 00:00:00

SQL_ID 1am0d1r44m7sx
--------------------
UPDATE MYDB_STAGING SET IS_PROCESSED = 'R', NM_MODIFIED
='MYDB_VERSIONING' WHERE DT_COB=:B1 AND IS_LATEST=0 AND IS_PROCESSED =
'N'

Plan hash value: 1961169265
------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT        |                  |       |       |    25 (100)|          |       |       |
|   1 |  UPDATE                 | MYDB_STAGING     |       |       |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|                  |  1392 | 22272 |    25   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL    | MYDB_STAGING     |  1392 | 22272 |    25   (0)| 00:00:01 |   KEY |   KEY |
------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - :B1 (DATE): 03/13/2015 00:00:00

Next I checked out some basic information about the table.

SQL> @dba_tables
Enter value for owner: MDDBO
Enter value for table_name: MYDB_STAGING

OWNER   TABLE_NAME    STATUS LAST_ANALYZED  NUM_ROWS   BLOCKS    DEGREE PAR
------- ------------- ------ -------------- ---------- --------- ------ ---
MDDBO   MYDB_STAGING  VALID  13-JUN-15      117989022  11234944       1 YES

The table is a partition table and table size is not big, about 120 million rows. Each partition contains 3~7 million rows. According to the user, each execution usually updates 2~3 million rows. Run the following query to get the partition key for the table. It’s DT_COB column.

SQL> select column_name, column_position from dba_part_key_columns
where owner='MDDBO' and name='MYDB_STAGING';  

COLUMN_NAME     COLUMN_POSITION
--------------- ---------------
DT_COB                        1

Based on the above information, the correct access path needs to use single partition full scan, in other words, should use plan 1961169265 instead of plan 1281386787. As I saw many cases of query plan flipped in the past, at first, I thought this could be another example of typical plan changes. Run the query to verify my assumption:

SQL> @awr_plan_change
Enter value for sql_id: 1am0d1r44m7sx
Enter value for instance_number:

   SNAP_ID   NODE BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME          AVG_LIO          AVG_PIO
---------- ------ ------------------------------ ------------- --------------- ------------ ------------ ---------------- ----------------
     97353      2 06-MAY-15 07.23.37.548 PM      1am0d1r44m7sx      1961169265            1      118.175      3,531,575.0        270,839.0
     97377      7 07-MAY-15 07.00.24.645 PM      1am0d1r44m7sx                            1    2,660.042      3,820,002.0        341,582.0
     97401      3 08-MAY-15 07.00.04.204 PM      1am0d1r44m7sx                            0    3,573.805      7,998,068.0        980,442.0
     97473      1 11-MAY-15 07.00.12.432 PM      1am0d1r44m7sx                            1      576.099      3,639,026.0        286,946.0
     97497      5 12-MAY-15 07.00.08.174 PM      1am0d1r44m7sx                            1    1,729.939      4,261,057.0        340,114.0
     97521      1 13-MAY-15 07.00.02.560 PM      1am0d1r44m7sx                            1    1,898.494      4,266,891.0        364,785.0
     97545      1 14-MAY-15 07.00.03.598 PM      1am0d1r44m7sx                            1      718.034      3,439,714.0        270,765.0
     97569      1 15-MAY-15 07.00.00.695 PM      1am0d1r44m7sx                            1      829.346      3,731,965.0        284,365.0
     97641      6 18-MAY-15 07.00.04.875 PM      1am0d1r44m7sx                            1      888.942      4,903,243.0        399,901.0
     97665      5 19-MAY-15 07.00.05.411 PM      1am0d1r44m7sx                            1      581.278      3,515,619.0        268,340.0
     97689      6 20-MAY-15 07.00.02.358 PM      1am0d1r44m7sx                            1    1,053.530      3,957,242.0        314,391.0
     97713      8 21-MAY-15 07.00.17.748 PM      1am0d1r44m7sx                            1    3,103.021      5,442,853.0        558,322.0
     97738      1 22-MAY-15 07.00.02.100 PM      1am0d1r44m7sx                            1      723.603      3,640,845.0        285,694.0
     97810      1 25-MAY-15 07.00.01.961 PM      1am0d1r44m7sx                            1      366.762      2,275,088.0        173,936.0
     97834      2 26-MAY-15 07.00.04.461 PM      1am0d1r44m7sx                            0    3,611.451      2,160,988.0        252,119.0
     97835      2 26-MAY-15 08.00.14.555 PM      1am0d1r44m7sx                            0    3,543.604     12,876,728.0      2,320,402.0
     97858      6 27-MAY-15 07.00.09.103 PM      1am0d1r44m7sx                            0    3,589.634      9,592,036.0      1,268,348.0
     97882      8 28-MAY-15 07.00.10.821 PM      1am0d1r44m7sx                            0    3,580.830      6,042,271.0        689,048.0
     97883      8 28-MAY-15 08.00.03.331 PM      1am0d1r44m7sx                            1    1,584.714     17,450,422.0      1,564,930.0
     97906      2 29-MAY-15 07.00.02.462 PM      1am0d1r44m7sx                            0    3,577.977      3,861,047.0        528,738.0
     97978      4 01-JUN-15 07.00.01.451 PM      1am0d1r44m7sx                            0    3,578.352      8,051,298.0        817,057.0
     97979      4 01-JUN-15 08.00.05.179 PM      1am0d1r44m7sx                            1      165.220      2,695,013.0        274,682.0
     98002      3 02-JUN-15 07.00.05.820 PM      1am0d1r44m7sx                            0    3,515.592      6,794,546.0        726,537.0
     98026      7 03-JUN-15 07.00.01.053 PM      1am0d1r44m7sx                            0    3,581.938     11,215,112.0      1,790,826.0
     98027      7 03-JUN-15 08.00.13.693 PM      1am0d1r44m7sx                            1    2,573.582     14,189,836.0      2,530,507.0
     98050      1 04-JUN-15 07.00.04.722 PM      1am0d1r44m7sx                            0    3,572.691      5,255,766.0        736,876.0
     98051      1 04-JUN-15 08.00.06.604 PM      1am0d1r44m7sx                            1    1,801.395     10,407,772.0      2,162,490.0
     98074      7 05-JUN-15 07.00.05.143 PM      1am0d1r44m7sx                            0    3,566.342      3,585,895.0        405,328.0
     98075      7 05-JUN-15 08.00.00.392 PM      1am0d1r44m7sx                            0    3,591.525     30,546,458.0      3,151,443.0
     98076      7 05-JUN-15 09.00.07.352 PM      1am0d1r44m7sx                            1    1,492.171     19,251,729.0      1,558,493.0
     98146      3 08-JUN-15 07.00.01.496 PM      1am0d1r44m7sx                            0    3,530.138      9,209,187.0        652,218.0
     98170      7 09-JUN-15 07.00.02.540 PM      1am0d1r44m7sx                            0    3,538.753      5,187,425.0        481,987.0
     98171      7 09-JUN-15 08.00.08.762 PM      1am0d1r44m7sx                            1      996.519      9,047,047.0        802,864.0
     98194      6 10-JUN-15 07.00.07.800 PM      1am0d1r44m7sx                            0    3,577.875      5,266,386.0        408,865.0
     98195      6 10-JUN-15 08.00.04.068 PM      1am0d1r44m7sx                            0    3,572.545      9,247,723.0      2,239,318.0
     98196      6 10-JUN-15 09.00.02.624 PM      1am0d1r44m7sx                            0    3,493.045     11,131,196.0      4,195,213.0
     98197      6 10-JUN-15 10.00.16.671 PM      1am0d1r44m7sx                            0    3,662.746     18,616,721.0      4,365,932.0
     98198      6 10-JUN-15 11.00.13.227 PM      1am0d1r44m7sx                            0    3,610.344     21,346,638.0      5,733,209.0
     98199      6 11-JUN-15 12.00.08.600 AM      1am0d1r44m7sx                            0    3,648.539      8,701,577.0      2,714,068.0
     98200      6 11-JUN-15 01.00.11.149 AM      1am0d1r44m7sx                            0    3,519.821     17,616,343.0      4,880,834.0
     98201      6 11-JUN-15 02.00.03.849 AM      1am0d1r44m7sx                            0    3,634.058     18,767,326.0      4,584,485.0
     98202      6 11-JUN-15 03.00.03.191 AM      1am0d1r44m7sx                            0    3,596.024     14,784,489.0      4,191,209.0
     98203      6 11-JUN-15 04.00.06.450 AM      1am0d1r44m7sx                            0    3,573.926     17,111,846.0      5,648,559.0
     98204      6 11-JUN-15 05.00.10.709 AM      1am0d1r44m7sx                            0    3,649.037     16,125,390.0      4,559,875.0
     98205      6 11-JUN-15 06.00.02.222 AM      1am0d1r44m7sx                            0    3,627.405     23,724,739.0      5,026,544.0
     98206      6 11-JUN-15 07.00.03.859 AM      1am0d1r44m7sx                            0    3,584.408     22,462,507.0      5,036,174.0
     98207      6 11-JUN-15 08.00.02.770 AM      1am0d1r44m7sx                            0    3,499.634     21,113,112.0      5,622,234.0
     98208      6 11-JUN-15 09.00.07.852 AM      1am0d1r44m7sx                            0    3,622.151     19,325,484.0      8,609,591.0
48 rows selected.

Checked out the plan and it was indeed doing the full scan on one single partition. Interestingly, there was no plan change and the plan was indeed using hash value 1961169265. The query result shows the majority of execution time was below 3,000 seconds before May 25th. After May 25th, the query consistently took more than a few hours, in the worst cases, days without finishing. Both logical IO and physical IO after May 25th are 4~8 times higher than normal period before May 25th.

Client confirmed there is no data volume change during this period. So the sudden data volume jump is ruled out. Thing became interesting here.

My next suspicion is whether there are other DML activities against the same table. Client said yes and there is always insert and delete activities against this table. But the same DML activities also happened before May 25th, why the performance is different right now. Fair statement. Quickly wrote a query sql_text_info.sql and used it to search all SQLs against this table.

 
SQL> @sql_text_info
Enter value for sql_text: %MYDB_STAGING%

interval time    INST SQL_ID         HASH_VALUE      EXECS      ETIME   AVG_ETIME           LIO        AVG_LIO SQL_TEXT
---------------- ---- ------------- ----------- ---------- ---------- ----------- ------------- -------------- ------------------------------------
. . . .
jun-02-15 01:00     5 1j601hg74p2qh                  1,657   95.29188        .058     14,762616        8,909.2 INSERT INTO MDDBO.MYDB_STAGING(S
jun-01-15 21:00     2 1j601hg74p2qh                    988 110.640511        .112     15,292134       15,477.9 INSERT INTO MDDBO.MYDB_STAGING(S
jun-01-15 21:00     2 bgvcuszpxp1x9                    988 110.996902        .112     15,304010       15,489.9 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
jun-01-15 20:00     4 buwna1hmvy2a0  2175852021          1 153.140902     153.141      1,825674    1,825,674.0 UPDATE MYDB_STAGING SET IS_LATES
jun-01-15 20:00     4 1am0d1r44m7sx  1961169265          1 165.219891     165.220      2,695013    2,695,013.0 UPDATE MYDB_STAGING SET IS_PROCE
jun-01-15 19:00     5 bgvcuszpxp1x9           0     11,308 186.595527        .017      1,845134          163.2 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
jun-01-15 19:00     5 1j601hg74p2qh                 11,309 183.916696        .016      1,709844          151.2 INSERT INTO MDDBO.MYDB_STAGING(S
jun-01-15 19:00     2 1j601hg74p2qh                  9,494 210.490704        .022      8,176275          861.2 INSERT INTO MDDBO.MYDB_STAGING(S
jun-01-15 19:00     2 bgvcuszpxp1x9                  9,491 212.032152        .022      8,289925          873.5 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
jun-01-15 16:00     8 1j601hg74p2qh                  2,843 123.610869        .043     20,252536        7,123.6 INSERT INTO MDDBO.MYDB_STAGING(S
jun-01-15 16:00     1 1j601hg74p2qh                  5,990 211.389932        .035     31,781333        5,305.7 INSERT INTO MDDBO.MYDB_STAGING(S
jun-01-15 16:00     8 bgvcuszpxp1x9                  2,838   123.9323        .044     20,155222        7,101.9 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
jun-01-15 16:00     1 bgvcuszpxp1x9                  5,981 212.434201        .036     31,765722        5,311.1 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
. . . .
may-25-15 22:00     7 1j601hg74p2qh                  1,498 105.318346        .070     17,110206       11,422.0 INSERT INTO MDDBO.MYDB_STAGING(S
may-25-15 22:00     8 1j601hg74p2qh                    500  51.938971        .104      8,436275       16,872.6 INSERT INTO MDDBO.MYDB_STAGING(S
may-25-15 22:00     8 bgvcuszpxp1x9                    500  52.122208        .104      8,442285       16,884.6 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-25-15 22:00     4 1j601hg74p2qh                  1,000  96.772358        .097     16,339154       16,339.2 INSERT INTO MDDBO.MYDB_STAGING(S
may-25-15 22:00     4 bgvcuszpxp1x9                    994  96.550291        .097     16,243633       16,341.7 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-25-15 22:00     6 bgvcuszpxp1x9                    505  52.105969        .103      8,401898       16,637.4 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-25-15 22:00     6 1j601hg74p2qh                    505  51.917207        .103      8,395818       16,625.4 INSERT INTO MDDBO.MYDB_STAGING(S
may-25-15 19:00     1 1am0d1r44m7sx  1961169265          1 366.762256     366.762      2,275088    2,275,088.0 UPDATE MYDB_STAGING SET IS_PROCE
may-25-15 13:00     6 bgvcuszpxp1x9           0     33,406  1444.6719        .043    256,909395        7,690.5 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-25-15 13:00     6 1j601hg74p2qh                 33,476 1439.05292        .043    258,297516        7,715.9 INSERT INTO MDDBO.MYDB_STAGING(S
may-25-15 13:00     3 bgvcuszpxp1x9                 48,792 2208.19674        .045    387,805213        7,948.1 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-25-15 13:00     3 1j601hg74p2qh                 48,917 2205.34858        .045    389,636787        7,965.3 INSERT INTO MDDBO.MYDB_STAGING(S
may-25-15 13:00     7 1j601hg74p2qh                 36,201 1801.23233        .050    321,210434        8,873.0 INSERT INTO MDDBO.MYDB_STAGING(S
may-25-15 13:00     7 bgvcuszpxp1x9                 36,155 1807.59592        .050    320,840251        8,874.0 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-25-15 13:00     2 1j601hg74p2qh                 37,718 1763.97237        .047    288,696415        7,654.1 INSERT INTO MDDBO.MYDB_STAGING(S
may-25-15 13:00     2 bgvcuszpxp1x9                 37,675 1770.14259        .047    288,827077        7,666.3 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-25-15 13:00     4 1j601hg74p2qh                 32,156 1490.11108        .046    260,436584        8,099.2 INSERT INTO MDDBO.MYDB_STAGING(S
. . .  .
may-15-15 21:00     6 bgvcuszpxp1x9                 11,282 312.503981        .028     46,699981        4,139.3 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-15-15 21:00     1 1j601hg74p2qh                 26,218 849.230019        .032    131,299020        5,008.0 INSERT INTO MDDBO.MYDB_STAGING(S
may-15-15 21:00     1 bgvcuszpxp1x9                 26,131 857.433845        .033    131,200097        5,020.9 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-15-15 21:00     2 bgvcuszpxp1x9                 14,772 386.170741        .026     58,074157        3,931.4 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-15-15 21:00     4 bgvcuszpxp1x9                 11,474 350.669289        .031     55,183416        4,809.4 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-15-15 21:00     4 1j601hg74p2qh                 11,489 348.176292        .030     55,437597        4,825.3 INSERT INTO MDDBO.MYDB_STAGING(S
may-15-15 21:00     2 1j601hg74p2qh                 14,815 383.870156        .026     58,137060        3,924.2 INSERT INTO MDDBO.MYDB_STAGING(S
may-15-15 21:00     3 bgvcuszpxp1x9                 17,502 512.293434        .029     79,491976        4,541.9 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-15-15 21:00     3 1j601hg74p2qh                 17,517  506.51756        .029     79,329693        4,528.7 INSERT INTO MDDBO.MYDB_STAGING(S
may-15-15 21:00     8 bgvcuszpxp1x9                  8,593 273.929099        .032     44,014364        5,122.1 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-15-15 21:00     8 1j601hg74p2qh                  8,601 271.977545        .032     44,284047        5,148.7 INSERT INTO MDDBO.MYDB_STAGING(S
may-15-15 19:00     1 1am0d1r44m7sx  1961169265          1 829.346177     829.346      3,731965    3,731,965.0 UPDATE MYDB_STAGING SET IS_PROCE
may-15-15 17:00     3 bgvcuszpxp1x9           0     13,998 154.832559        .011     12,076269          862.7 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-15-15 17:00     3 1j601hg74p2qh                  5,999  86.716744        .014      5,305216          884.4 INSERT INTO MDDBO.MYDB_STAGING(S
may-15-15 14:00     8 bgvcuszpxp1x9                  7,772 208.118833        .027     34,702132        4,465.0 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-15-15 14:00     8 1j601hg74p2qh                  7,804 207.315633        .027     35,442987        4,541.6 INSERT INTO MDDBO.MYDB_STAGING(S
may-15-15 14:00     2 bgvcuszpxp1x9                  6,948 231.212222        .033     36,165505        5,205.2 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-15-15 14:00     2 1j601hg74p2qh                  6,968 229.081633        .033     35,870516        5,147.9 INSERT INTO MDDBO.MYDB_STAGING(S
may-15-15 14:00     4 1j601hg74p2qh                  8,067 207.975898        .026     35,897394        4,449.9 INSERT INTO MDDBO.MYDB_STAGING(S
may-15-15 14:00     4 bgvcuszpxp1x9                  8,038 209.069809        .026     35,470064        4,412.8 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-15-15 14:00     5 bgvcuszpxp1x9                  5,010 146.873536        .029     23,838187        4,758.1 DECLARE V_NM_SUPPLY   VARCHAR2 (400)
may-15-15 14:00     5 1j601hg74p2qh                  5,025 146.944746        .029     23,908322        4,757.9 INSERT INTO MDDBO.MYDB_STAGING(S
may-15-15 14:00     6 bgvcuszpxp1x9                  7,462 217.030479        .029     35,107121        4,704.8 DECLARE V_NM_SUPPLY   VARCHAR2 (400)

As I expected, since May 25th, it did have some insert activities during the same time period between 7pm and 8pm. So I recommended them to kill the current update query, just rerun it during the time when low or no insert activity. Unsurprisingly, the query completed within the same timeframe it usually completed. Case closed. To find out the scripts used in this blog, check them out on Scripts.

For the past two months, I have some other interesting activities.
1) Both Randy and I helped our client, one of the largest banks in the world, to successfully migrate their Oracle database to Exadata platform. It was the largest oracle database migration in the bank history and involved many development teams and operation teams. It was one of the most challenge project I have ever done. Hardly slept for the first week. Almost totally exhausted after the system was stabilized. Glad the database is performing well on Exadata right now.
2) I attended our E4 event early June. I have been to every E4 event since the event was launched three years ago. The event becomes bigger and bigger, and this year is the first time under Accenture brand. It is a great event for meeting so many top database experts and chatting with friends and colleagues.
3) Finally, I joined Accenture team in New York and run JP Morgan Chase Corporate Challenge event at Central Park with other 20,000 runners on June 4th. It was fun 5K run and I really enjoy the run. If I am still around New York same time next year, I am definitely going to participate the event again.

Here are some photos from the activities.
Celebration after the Success of the Migration
IMG_1101

The First Day at E4 2015
IMG_1091

My Run at JP Morgan Chase Corporate Challenge
JPMC_CC_2015_WeidongZhou

Fixed Table STATS Issue after Gathering Fixed Object STATS

Recently we were working on another 130+TB database with 5200+ datafiles. After the database was upgraded from 11.2.0.3 to 11.2.0.4 on a full rack X4 Exadata, I did some verifications and noticed my query (using dba_free_space and dba_data_files) to find out tablespace usage took forever and never completed. I have run the same query on other 100+ TB databases and it could take some time, but never run into the situation that it has never completed.

I pulled out the execution plan for the query and found out the majority of was on fixed table X$KTFBUE. The plan was doing full table scan. At the time I killed the query, the actual row processed was 10 million rows while the estimate total number of rows was 100,000 rows. By the way, 100,000 rows is the default value for X$KTFBUE when no stats is available. Obviously it is related to STATS issue. Then I run the query below to find out whether X$KTFBUE has STATS or not.

select * from dba_tab_statistics where  table_name='X$KTFBUE';

It indeed did not have stats. Maybe we forgot to collect the fixed object stats. Run the queries below and find out we had stats in the majority of fixed tables.

SELECT * FROM DBA_TAB_STATISTICS 
WHERE OBJECT_TYPE='FIXED TABLE'; 

SELECT * FROM DBA_TAB_STATISTICS 
WHERE OBJECT_TYPE='FIXED TABLE' 
and last_analyzed is null;

Maybe we had issue during gathering fixed objects. So I rerun the stats collection shown below for fixed objects.

SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
Elapsed: 00:05:19.42

It completed successfully in 5 minutes. Checked out X$KTFBUE, still no STATS. This became interesting to me. With a fixed table having at least 10+ million rows and seem to be one of important fixed table to get tablespace sizing information, why is it excluded from fixed object stats collection?

With this question in mind, I did some research. There is a document discussing why STATS is missing for certain fixed tables: Why do some Fixed Tables not have Optimizer Statistics (Doc ID 1355608.1). Here is the answer in the document.

Why do some fixed tables not have statistics even after running: DBMS_STATS.GATHER_FIXED_OBJECTS_STATS?

Some fixed tables are left without stats because the development team has flagged the code to avoid statistics gathering for some fixed tables. This is because they know they will perform better without stats or because it is not supported to gather the statistics upon them.
This was confirmed in : Bug 12844116 fixed tables statistics

The document applies to version 10.2.0.4 to 11.2.0.2. Obviously it is covered version 11.2.0.4 in my case.

Another document also discussed the similar behavior: Query Against DBA_FREE_SPACE is Slow After Applying 11.2.0.4 (Doc ID 1904677.1). The document mentions two major symptoms that simialr to my issue:
1. Slow query from dba_free_space after applying 11.2.0.4
2. The query is doing a Full Tabble Scan (FTS) against X$KTFBUE.

Some fixed tables(X$) have no CBO statistics against them and use defaults; it may be too expensive to gather stats or the tables are too volatile such that any statistics are immediately outdated.

The sys.x$ktfbue table is one such object. These statistical inaccuracies can cause suboptimal plans in some cases.

The document mentioned gathering stats on x$ktfbue table. So I went ahead collecting stats as follows:

EXEC DBMS_STATS.gather_table_stats('SYS', 'X$KTFBUE');

WaitFixedTableStats
As it took only 5 minutes to collect fixed object stats, I thought a couple of minutes or maxmium 10 minutes should complete the stats gathering for X$KTFBUE table. I was so wrong, one hour, two hours passed and stats job was still going. Maybe the gather_table_stats was stuck somewhere. Check out OEM SQL Monitor, I can see the number of rows processed still going up slowly. I was just like the above squirrel patiently waiting for the finish. Finally, after 7.5 hours it completed successfully. The table has about 99 million rows, about 1000 times more than default value of 100,000 rows. Now I understand why this fixed table is excluded from GATHER_FIXED_OBJECTS_STATS. It is just too expensive to collect stats. Majority of time it should not cause an issue. This is the first Exadata database I notices having fixed object issue after running GATHER_FIXED_OBJECTS_STATS.

During the time I waited for the stats gathering completion, I found another Oracle Document: Query against DBA_EXTENTS slow after upgrade to 11.2.0.3 (Doc ID 1453425.1). This applies to database version 11.2.0.3 and later. Here are some part of the note:

SYMPTOMS
Queries on DBA_EXTENTS are much slower due to the presence of a merge join cartesian.
This can also influence the performance of script used to collect upgrade/migrate diagnostics as per instructions in:
Document 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql).
Running script on version 11.2.0.1 prior to upgrade, there was no performance issue.
The following SQL query from dbupgdiag.sql may run slowly (~2 hours) after applying patchset 11.2.0.3.
Prompt ===========================================
Prompt Tablespace and the owner of the aud$ table
Prompt ===========================================
select owner,tablespace_name from dba_extents where segment_name=’AUD$’ group by
owner,tablespace_name;

CHANGES
An execution plan regression occurs if there are a large number of records in the X$KTFBUE table. Gathering of dictionary or fixed object stats does not improve performance as this table is intentionally omitted from those packages.

CAUSE
The Cost Based Optimizer uses a cardinality estimate of 100,000 rows for this table, and the 11.2.0.3 execution plan is doing a full table scan. If you have a large number of extents, this query can take more than 1 hour to complete. There are a number of unpublished bugs open on slow perfomance for queries on DBA_EXTENTS.
For example, following bugs:
Bug:13542477 ON 11.2.0.3 QUERY DBA_EXTENTS IS SO SLOW
Bug:14221159 JOIN CARDINALITY INCORRECT CALCULATED AFTER FIX 11814428

SOLUTION
Following are possible workarounds to improve the performance
Gathering stats on the X$KTFBUE table using the following command:
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SYS’, ‘X$KTFBUE’);
OR
alter session set “_optimizer_cartesian_enabled” = false;
OR
alter session set “_smm_auto_cost_enabled” = false;
OR
Disable fix for unpublished Bug Bug 11814428: POOR CARDINALITY ESTIMATE FROM JOIN WITH A UNIONALL VIEW:
alter session set “_FIX_CONTROL” = “11814428:0″;

This document reminded me the similar issue we run into last year when we built the same database for POC purpose. At the time when we tried to upgrade the POC database from 11.2.0.3 to 11.2.0.4. The dbupgdiag.sql script run forever and never finished. Opened SR with Oracle and Oracle Support recommended to comment out the following line in dbupgdiag.sql script as a workaround solution.
select owner,tablespace_name from dba_extents where segment_name=’AUD$’ group by owner,tablespace_name;

Finally after the stats collection completed, run the same query again, the result comes back within 15 seconds. Checked out the execution plan, I can see it does not use full table scan of the X$KTFBUE table, but use an index from this table. Issue solved and life is good.

fixedTableStatsIssue

Additional note:
1. So what exactly X$KTFBUE table is used for? X$KTFBUE is a dynamically generated map of used extents in locally managed tablespace.

The followings are the definitions for the columns in x$ktfbue.

ADDR : N/A
INDX : N/A
INST_ID : N/A
KTFBUESEGTSN : ts# containing this segment
KTFBUESEGFNO : Relative number of the file containing the segment header
KTFBUESEGBNO : segment header block number
KTFBUEEXTNO : Extent number
KTFBUEFNO : Relative number of the file containing the extent
KTFBUEBNO : Starting block number of the extent
KTFBUEBLKS : Size of the extent in ORACLE blocks
KTFBUECTM : commit_jtime,Commit Time of the undo in the extent expressed as Julian date
KTFBUESTT :commit_wtime,Commit Time of the undo in the extent expressed as wall clock time
KTFBUESTA : Transaction Status of the undo in the extent;1, ‘ACTIVE’, 2, ‘EXPIRED’, 3, ‘UNEXPIRED’, 0 for non‐undo

2. If looked at the execution plan, even we fixed the stats issue for X$KTFBUE table, we might still run into issue related to recyclebin. The plan shows nested loop involving recyclebin. Here is the DBA_FREE_SPACE looks like.

SQL> set long 90000;
SQL> set pages 999;
SQL> select dbms_metadata.get_ddl('VIEW','DBA_FREE_SPACE') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','DBA_FREE_SPACE')
--------------------------------------------------------------------------------
  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_FREE_SPACE" ("TABLESPACE_NAME", "FILE_
ID", "BLOCK_ID", "BYTES", "BLOCKS", "RELATIVE_FNO") AS
  select ts.name, fi.file#, f.block#,
       f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0
union all
select
       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select
       ts.name, fi.file#, u.ktfbuebno,
       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
  and rb.ts# = fi.ts#
  and u.ktfbuefno = fi.relfile#
  and u.ktfbuesegtsn = rb.ts#
  and u.ktfbuesegfno = rb.file#
  and u.ktfbuesegbno = rb.block#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select ts.name, fi.file#, u.block#,
       u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
  and u.ts# = fi.ts#
  and u.segfile# = fi.relfile#
  and u.ts# = rb.ts#
  and u.segfile# = rb.file#
  and u.segblock# = rb.block#
  and ts.bitmapped = 0

Right now our database was just built and recyclebin was empty. If there are a lot of rows in the recyclebin, I will not be surprised the performance could be bad again. If it happens, one possible solution is to purge the recyclebin.

3. Another interesting part is that the total number of rows in X$KTFBUE table was about 99 million rows while the total number of extents in the database was also around 99 million rows. So reducing the total number of extent in the database seem a good way to reduce this stats issue. Pay attention to the INITIAL and NEXT in the storage clause. Setting too small will have a large number of extents and create headache in querying fixed tables.

New archivelog Files are Deleted before Applied During the Restore of Standby Database

Following one of blogs last year about restoring/recovering archivelog files to a standby database manually, I run into another interesting issue. At that time, I didn’t have time to write a blog about the issue. Right now I finally can write down what I found last year.

After a few days’ restore of archivelog files, our recover process began running into issue. Our process was to restore all archivelog files first for a certain date, then perform the recovery for the same date. So the recovery should run through until the last recover point identified by my previous blog . Interesting, the recovery stop in the middle of process and complaint about archivelog files not found. At first, I was thinking maybe I run the restore for the wrong sequence range. Then performed the restore of archive logfile again. The recovery process passed that sequence, but quickly stop at another archivelog. At this moment, I know something was not right. Of course, it is not related to wine shown below.

file_missing

Could be archivelog file deleted from RECO due to the FRA space constraint? But it did not make sense for the deletion of archive log even before the archivelogs were applied. I checked the alert logfile and found the something interesting. The alert log shows log seqence 615578 thread 5 was deleted at 09:34.

Sun Sep 07 09:34:50 2014
Deleted Oracle managed file +RECO/userdb/archivelog/2014_09_07/thread_5_seq_615578.22013.857628717
Sun Sep 07 09:34:50 2014
Deleted Oracle managed file +RECO/userdb/archivelog/2014_09_07/thread_5_seq_615577.22019.857628719
Deleted Oracle managed file +RECO/userdb/archivelog/2014_09_07/thread_5_seq_615568.22021.857628661

The recover log shows we just recover to 614809 thread 5 at 20:19. So for the log sequence 615578, 615577, and 615568 for thread 5 were deleted long before they can be applied.

ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT …
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log +RECO/userdb/archivelog/2014_09_07/thread_6_seq_491327.21626.857651973
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT …
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log +RECO/userdb/archivelog/2014_09_07/thread_5_seq_614809.9496.857655203
Sun Sep 07 20:19:51 2014
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT …
ALTER DATABASE RECOVER CONTINUE DEFAULT

After did some research, we found an Oracle note (Doc ID: Bug 17370174 : NEW ARCHIVELOG FILES ARE DELETED FIRST FROM THE FRA). Here is part of the description from the note:

DETAILED PROBLEM DESCRIPTION
============================
The algorithm used by the FRA to delete the files is deleting the more recent archivelog files created when there are old archivelogs in the FRA eligible to delete first.

TECHNICAL IMPACT
================
The backup of the archivelog files is failing because the archivelog file was deleted.

WORKAROUND INFORMATION
======================
The workaround recommended is add another condition to the archivelog policy in order to prevent that the archivelog files are deleted if there is not backup taken.

The Solutions
Instead of messing around our standard archivelog policy in RMAN, we identified the following approaches to get around the issue.

Approach 1:
Increase db_recovery_file_dest_size parameter from 10000G to 12800G. This approach can resolve the issue immediately for the current date. But will run into the same issue a few days later when FRA is reaching to full again.

Approach 2:
Manually remove completed applied archive logfiles using the following command:
delete archivelog all completed before ‘sysdate – n';

Approach 3:
Add DELETE ARCHIVELOG MAXSIZE in the recover command.

RMAN > recover database until sequence 343848 thread 2 delete archivelog maxsize 1000g;

So the above command will recover to a latest consistent state and while recovery as when 1TB of archives are applied, the applied archives will bedeleted thus making space in Flash recovery area.

DELETE ARCHIVELOG causes RMAN to delete restored log files after they have been applied to the datafiles, to save disk space.

MAXSIZE 1000g limits space occupied by restored logs at any given moment to 1000GB

The other feasible options to deletion policy for archivelogs can be the followings:
Configure deletion policy to none. Archivelogs don’t get deleted unless we manually delete them.
RMAN > configure archivelog deletion policy to none;

Configure deletion policy to backed up 1 times to disk: Archivelogs will not get deleted unless they are backed up.
RMAN > configure archivelog deletion policy to backed up 1 times to disk;

Moving SQL Profile during Exadata Migration

My last post discusses the steps to move stats to Exadata environment. During the same cutover window, we actually did another task: move certain SQL Profiles from UAT environment to PROD environment. Just like photo below, let’s do the moving in one run.
moving_stuff

For SQL Profile, I usually use it as the last approach to resolve production performance issue. I always recommend my clients to change application code to tune the poor performed queries first. Only at the time no code change is allowed and production database is suffering from bad queries significantly, SQL Profiles will be used. For a large legacy database suffering from poor performance in the past, it’s not surprise to see many SQL Profiles in the legacy database. If there are only 10 or 20 SQL Profiles, it can be manageable. But there is no way to maintain hundreds, even thousands SQL Profiles in the database, especially during migration from 11.2.0.3 Non-Exadata to 11.2.0.4 Exadata environment. So after we restore the legacy database into an UAT environment on Exadata, we disabled all SQL Profiles in the database. During the testing, if query performance was not good in Exadata environment, we implemented new SQL Profile or reused the old one if existing one performed better. By using strategy, we cut down the SQL Profiles from 256 before migration to 9 during the migration. During the cutover window, we only need to move these 9 SQL Profiles from UAT to PROD environment.

The followings are the sample steps for moving SQL Profiles. It uses the same db_my_stats schema created in the previous post. Here is the summary of the steps.
1. Copy the enabled SQL Profile to a staging table under the same db_my_stats schema created in the last post.
2. Export the staging table.
3. Copy the export data pump files to PROD database on Exadata.
4. Disable all SQL Profile in PROD database.
5. Import the enabled SQL Profiles in PROD database.

Step 2 and 3 are discussed in my last post and I am not going to repeat them here. Only step 4 and 5 are performed during cutover window. Here are the sample scripts to illustrate the steps:

Copy the enabled SQL Profile to a staging table
Generate necessary sql scripts.
vi gen_exp_imp_sqlprofile.sql

set echo off
set feedback off
set lines 150
set pages 0

prompt ---------------- Generate SQL Profile Export and Import -----------------

prompt
prompt -- generate SQL to export SQL profile
prompt
spool EXPORT_TO_SQLPROFILE_TABLE.SQL
select 'spool EXPORT_TO_SQLPROFILE_TABLE.log' from dual;
select
  'EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF ( ' ||
  'staging_table_name => ''PROFILE_STG'', profile_name=>''' || name || '''); ' content
  from dba_sql_profiles
 where status = 'ENABLED'
order by name;
select 'spool off' from dual;

prompt
prompt -- generate SQL to get total rows in the SQL Profile table
prompt
spool GET_EXPORT_SQL_PROFILE_ROW_COUNT.SQL
select 'spool GET_EXPORT_SQL_PROFILE_ROW_COUNT.log' from dual;
select 'prompt SQL profiles qualifed for export ' from dual;
select 'SELECT name, status FROM dba_sql_profiles where status = ''ENABLED'' order by name;' from dual;

select 'prompt SQL profiles exported ' from dual;
select 'SELECT count(*) FROM DB_MY_STATS.PROFILE_STG;' from dual;
select 'select obj_name, obj_type, sql_handle  status from dbmir.PROFILE_STG order by object_name;' from dual;
select 'spool off' from dual;

prompt
prompt -- generate SQL to import SQL profile
prompt
spool IMPORT_TO_SQLPROFILE_TABLE.SQL
select 'spool IMPORT_TO_SQLPROFILE_TABLE.log' from dual;
select
  'EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE, ' ||
  'staging_table_name => ''PROFILE_STG'', profile_name=>''' || name || '''); ' content
  from dba_sql_profiles
 where status = 'ENABLED'
order by name;
select 'spool off' from dual;

spool off

Run the gen script.
@gen_exp_imp_sqlprofile

Export SQL Profile
sqlplus / as sysdba
connect db_my_stats/db_my_stats

Create SQL profile staging table
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => ‘PROFILE_STG’, schema_name=>’DB_MY_STATS’);

Run the script to export SQL Profile to the staging table and get the row count for the exported profiles.
@EXPORT_TO_SQLPROFILE_TABLE.SQL
@GET_EXPORT_SQL_PROFILE_ROW_COUNT.SQL

At this moment, the export is done. Then follows the export and import step from last post to populate the staging table PROFILE_STG in the target database.

Disable all SQL Profiles
vi disable_all_sqlprofile.sql

BEGIN
for r in
( select name, category, status, sql_text, force_matching
    from dba_sql_profiles
   order by category, name)
  loop
     DBMS_SQLTUNE.ALTER_SQL_PROFILE (name => r.name, attribute_name => 'STATUS', value =>  'DISABLED');
  end loop;
END;
/

Run the script.
@disable_all_sqlprofile.sql

Import SQL Profile
@IMPORT_TO_SQLPROFILE_TABLE.SQL

Verify the the enabled SQL Profile
select name, category, type, status from dba_sql_profiles
where status = ‘ENABLED';

Ok, we are done with the moving of SQL Profile here.

Moving STATS for Large Database during Exadata Migration

I have done many Exadata migrations for the past two and half years. For the majority of migrations, my clients have enough downtime to complete the statistic gathering during the cutover window. However, this strategy is not going to work for a large database. Recently we migrated a 110+ TB database to Exadata. There were many chanllenges involved in this migration, like STATS gathering, tablespace reaching 32TB limit and 1022 datafile limit, upgrade from 11.2.0.3 to 11.2.0.4 during the cutover window and critial query tuning with only a few weeks before cutover. Anyway, there were many moving parts during the migration. In this blog, I am focusing only on STATS strategy during the cutover window.

This database had issue in gathering stats in the past and run for days without completion in the legacy environment. As the stats gathering had significant impact on the database performance, the client had to turn off the stats gathering and lock the stats. So many tables’ stats were at least two years old. With the database moving from non-Exadata 11.2.0.3 environment to Exadata 11.2.0.4 environment, we need to have the new stats available before the database can be released to business users. The question was how we were going to do it? Just like the drawing below, we need to find the fastest window to get our work done.

fast_stats

Both UAT and PROD environments are using X4 full rack Exadata. Even with eight database servers, the full stats gathering with 384 parallism could still take a few days. Even incremental stats gathering could take 10+ hours. It is definitely not going to work during cutover window with limited downtime available.

For this migration, we used Data Guard and our physical standby on Exadata was exact physical copy of the legacy primary database. We also built the UAT database with RMAN restore and last build of the UAT was just two weeks away from the cutover date. So we use the following strategy to work around the stats gathering problem during cutover window.
1.  Gather stats on UAT database. It doesn’t matter whether it takes one day or two days. As long as it is outside the cutover window, we were fine with that.
2. Copy the STATS in UAT database to STATS staging tables.
3. Export the staging tables.
4. Copy the export data pump files to PROD database on Exadata
5. Import the stats to PROD DB.

Step 1 to 4 do not have downtime requirement and can be done ahead of time. Only the last step needs to be performed during the cutover window. In this blog, I am not going to discuss Step 1 and Step 4, and only on exporting and importing the stats. I used our lab to demonstrate the steps and scripts involved.

Export Stats from Source Database

cd /dbfs/work/wzhou/test_stat

sqlplus / as sysdba
create user db_my_stats identified by db_my_stats default tablespace users temporary tablespace temp; 
grant connect, resource, dba to db_my_stats;  
create directory DB_MY_STATS_DIR as '/dbfs/work/wzhou/test_stat'; 
grant read, write on directory DB_MY_STATS_DIR to system;

Generate necessary sql scripts.
vi gen_exp_imp_stats.sql

-- Generate all the necessary scripts for export and import schema stats
--set time on
--set timing on
set echo off
set feedback off
set lines 150
set pages 0

prompt
prompt generate SQL to create schema stat table
prompt
spool CREATE_SCHEMA_STATS_TABLE.SQL
select 'set timing on' from dual;
select 'spool CREATE_SCHEMA_STATS_TABLE.log' from dual;
select 'prompt '||owner||chr(10)||
  'exec DBMS_STATS.CREATE_STAT_TABLE(''DB_MY_STATS'', ''STATS_' || owner || ''');' content
  from dba_segments
 where owner not in ( 'DB_MY_STATS', 'ANONYMOUS','APPQOSSYS','DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSTEM','WMSYS','XDB','XS$NULL', 'ORDSYS','ORDDATA')
group by owner
order by owner;
select 'spool off' from dual;
spool off

prompt
prompt -- generate SQL to export schema stat to stat table
prompt
spool EXPORT_TO_SCHEMA_STATS_TABLE.SQL
select 'set timing on' from dual;
select 'spool EXPORT_TO_SCHEMA_STATS_TABLE.log' from dual;
select 'prompt '||owner||chr(10)||
  'exec DBMS_STATS.EXPORT_SCHEMA_STATS( ' ||
  'ownname => ''' || owner || ''', ' ||
  'stattab => ''STATS_' || owner || ''', ' ||
  'statid => ''' || owner || ''', ' ||
  'statown => ''DB_MY_STATS'');' content
  from dba_segments
 where owner not in ( 'DB_MY_STATS', 'ANONYMOUS','APPQOSSYS','DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSTEM','WMSYS','XDB','XS$NULL', 'ORDSYS','ORDDATA')
group by owner
order by owner;
select 'spool off' from dual;
spool off


prompt
prompt -- generate SQL to import schema stat to stat table
prompt
spool IMPORT_TO_SCHEMA_STATS_TABLE.SQL
select 'set timing on' from dual;
select 'spool IMPORT_TO_SCHEMA_STATS_TABLE.log' from dual;
select 'prompt '|| owner || chr(10) ||
  --'--exec dbms_stats.delete_schema_stats (ownname => '''||owner||''');' || chr(10)||
  'exec DBMS_STATS.IMPORT_SCHEMA_STATS( ' ||
  'ownname => ''' || owner || ''', ' ||
  'stattab => ''STATS_' || owner || ''', ' ||
  'statid => ''' || owner || ''', ' ||
  'statown => ''DB_MY_STATS'');' content
  from dba_segments
 where owner not in ( 'DB_MY_STATS', 'ANONYMOUS','APPQOSSYS','DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSTEM','WMSYS','XDB','XS$NULL', 'ORDSYS','ORDDATA')
group by owner
order by owner;
select 'spool off' from dual;

Run the gen script.
@gen_exp_imp_stats

Create stats staging table.
@CREATE_SCHEMA_STATS_TABLE.SQL

Export schema stats to the stats staging table
@EXPORT_TO_SCHEMA_STATS_TABLE.SQL

Prepare the export script
vi run_exp_stats
expdp parfile=exp_schema_stats.par

vi exp_schema_stats.par
USERID=db_my_stats/db_my_stats
PARALLEL=4
DIRECTORY=DB_MY_STATS_DIR
DUMPFILE=stats_%U.dmp
LOGFILE=stats_exp.log
METRICS=Y
FILESIZE=4G
SCHEMAS=DB_MY_STATS
JOB_NAME=stats_exp

Run the script to export the schema
run_exp_stats

Ok, at this moment, the stats export is done. Copy the datapump file to target system.

Import Stats to Target Database
On target database, do the similar step as above.

create user db_my_stats identified by db_my_stats default tablespace users temporary tablespace temp; 
grant connect, resource, dba to db_my_stats;  
create directory DB_MY_STATS_DIR as '/dbfs/work/wzhou/test_stat'; 
grant read, write on directory DB_MY_STATS_DIR to system;

Prepare the import script.
vi run_imp_stats
impdp parfile=imp_schema_stats.par

vi imp_schema_stats.par
USERID=db_my_stats/db_my_stats
PARALLEL=4
DIRECTORY=DB_MY_STATS_DIR
DUMPFILE=stats_%U.dmp
LOGFILE=stats_imp.log
METRICS=Y
TABLE_EXISTS_ACTION=REPLACE
SCHEMAS=DB_MY_STATS
JOB_NAME=stats_imp

Run the script to import the schema
run_imp_stats

Import the stats from stats staging tables.
@IMPORT_TO_SCHEMA_STATS_TABLE.SQL

Ok, we’re done with the stats import.

The above strategy worked pretty well during the cutover window. The export of STATS took about half hour and the import of STATS took slight less time than the export. We are happy the STATS work does not take significant amount of time for this 110+ TB databases during the cutover.