9 Replies Latest reply: Jul 1, 2013 12:39 AM by Mihael RSS

    export Statistics

    985871
      Hi Friends ,

      i am using 11.2.0.3.0 version db on Linux x86 64-bit .

      i need your help on how to export the database statistics from Production to staging .

      Regards ,
      DBApps
        • 1. Re: export Statistics
          Mihael
          You can use DBMS_STATS.EXPORT_DATABASE_STATS and DBMS_STATS.IMPORT_DATABASE_STATS. Also you can use DataPump export with "include=table_statistics,index_statistics"
          • 2. Re: export Statistics
            985871
            we have a staging environment . We are refreshing certain tables from live to staging using golden gate . But we dont referesh all the columns of a table but only the ones that we need .
            The issue is certain queries run on live smoothly but the same queries run slow on staging . Can you please help .
            • 3. Re: export Statistics
              Mihael
              we have a staging environment . We are refreshing certain tables from live to staging using golden gate . But we dont referesh all the columns of a table but only the ones that we need .
              The issue is certain queries run on live smoothly but the same queries run slow on staging . Can you please help .
              Try to gather statistics on staging DB.
              • 4. Re: export Statistics
                DBApps
                Hi Mihael ,

                Thanks for your support . Yes after running the gather stats the performance of the query has now improved . Now my query is since i already have the auto gather stats feature enabled in 11G , is it still required to manually run the gather stats .

                Regards ,
                DBAPps
                • 5. Re: export Statistics
                  Mihael
                  Now my query is since i already have the auto gather stats feature enabled in 11G , is it still required to manually run the gather stats .
                  "auto optimizer stats collection" runs in window group ORA$AT_WGRP_OS that consists of the following windows:
                  WINDOW_NAME      RESOURCE_PLAN            REPEAT_INTERVAL                                                 DURATION
                  ---------------- ------------------------ ----------------------------------------------------- ------------------
                  MONDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +0 04:00:00.000000
                  TUESDAY_WINDOW   DEFAULT_MAINTENANCE_PLAN freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +0 04:00:00.000000
                  WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +0 04:00:00.000000
                  THURSDAY_WINDOW  DEFAULT_MAINTENANCE_PLAN freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +0 04:00:00.000000
                  FRIDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +0 04:00:00.000000
                  SATURDAY_WINDOW  DEFAULT_MAINTENANCE_PLAN freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0  +0 20:00:00.000000
                  SUNDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0  +0 20:00:00.000000
                  You can modify schedule of all or some windows (but keep in mind that they are also used by other autotask clients), or add additional windows with required schedule. Of course you can use your own scheduler job with dbms_stats in addition or instead of this auto task.

                  Edited by: Mihael on Jun 7, 2013 7:53 AM
                  • 6. Re: export Statistics
                    985871

                    Mihael ,

                    The query is still running slow . Both the explain plans on live and staging are different . It is doing FTS on staging but not on live . Running Gather stats also didnt help .

                     

                    Regards ,

                    DBApps

                    • 7. Re: export Statistics
                      Mihael

                      Check "LAST_ANALYZED" columns for objects involved in query. Post here execution plan on production and staging.

                      • 8. Re: export Statistics
                        985871

                        Mihael ,

                         

                        below is the query

                        SELECT

                              itm.order_id,

                              ord.order_num,

                              itm.x_prod_desc customer_orderline_ref,

                              bac.ou_num billing_account_no,

                              itm.bill_accnt_id billing_account_id,

                              bac.NAME billing_account_name,

                              itm.req_ship_dt customer_required_dt,

                              itm.x_cdd contractual_delivery_dt,

                              itm.must_dlvr_by_dt customer_promised_dt,

                              prod.part_num product_scode,

                              prod.NAME product_name,

                              itm.prod_id,

                              itm.qty_req orderline_qty,

                              itm.status_cd orderline_status,

                              itm.x_order_item_substatus orderline_sub_status,

                              itm.x_bes_status orderline_bes_status,

                              NULL kci_event,

                              NULL kci_dt,    

                              itm.completed_dt completion_dt,

                              itm.x_cancelled_dt cancellation_dt,

                              null appointment_dt,

                              itm.service_num service_id,

                              itm.row_id source_system_key,

                              DECODE (ordx.attrib_08, 'Y', 2, 1) sourcesystem_id,

                              NULL engg_note,

                              NULL engg_note_dt,

                              NULL engg_note_updated_by    

                            FROM s_order_item itm,

                              s_order ord,

                              s_order_x ordx,

                              s_prod_int prod,

                              s_org_ext bac,    

                              s_bu bu,

                              (SELECT start_execution_time,

                                last_execution_time

                              FROM odi_last_execution_details

                              WHERE scenario = 'LOAD_ONE_SIEBEL_TO_WCDS_DS_ORDERLINE_PKG'

                              ) odi_exec

                            WHERE ord.bu_id       = bu.row_id

                            AND itm.order_id      = ord.row_id

                            AND ord.row_id        = ordx.row_id

                            AND itm.prod_id       = prod.row_id

                            AND itm.bill_accnt_id = bac.par_row_id

                            AND bu.NAME           = 'BT Wholesale Markets'    

                            AND itm.root_order_item_id   = itm.row_id

                            AND ord.active_flg           = 'Y'

                            AND ((itm.process_timestamp >= odi_exec.start_execution_time

                            AND itm.process_timestamp    < last_execution_time)

                            OR (ord.process_timestamp   >= odi_exec.start_execution_time

                            AND ord.process_timestamp    < last_execution_time)

                            OR (ordx.process_timestamp  >= odi_exec.start_execution_time

                            AND ordx.process_timestamp   < last_execution_time)

                            OR (prod.process_timestamp  >= odi_exec.start_execution_time

                            AND prod.process_timestamp   < last_execution_time)

                            OR (bac.process_timestamp   >= odi_exec.start_execution_time

                            AND bac.process_timestamp    < last_execution_time)    

                            OR (bu.process_timestamp >= odi_exec.start_execution_time

                            AND bu.process_timestamp  < last_execution_time) ) 

                         

                         

                        ------------------------------------------------------------------------------------------------------------------------
                        | Id  | Operation                         | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                        ------------------------------------------------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT                  |                            |  2570K|  1024M|       |  1099K  (1)| 03:39:55 |
                        |*  1 |  HASH JOIN                        |                            |  2570K|  1024M|       |  1099K  (1)| 03:39:55 |
                        |   2 |   TABLE ACCESS BY INDEX ROWID     | S_BU               |     1 |    41 |       |     2   (0)| 00:00:01 |
                        |*  3 |    INDEX RANGE SCAN               | S_BU_NAME                  |     1 |       |       |     1   (0)| 00:00:01 |
                        |*  4 |   HASH JOIN                     |                            |    21M |  7724M|   189M|  1099K  (1)| 03:39:53 |
                        |   5 |    TABLE ACCESS FULL              | S_CONTACT  |  4133K|   141M|       | 11791   (1)| 00:02:22 |
                        |*  6 |    HASH JOIN                      |                            |    21M|  6987M|   318M|   719K  (1)| 02:23:51 |
                        |   7 |     TABLE ACCESS FULL             | S_ORDER_X      |  9273K|   212M|       | 22646   (1)| 00:04:32 |
                        |*  8 |     HASH JOIN                     |                            |    21M|  6495M|  1174M|   346K  (1)| 01:09:13 |
                        |*  9 |      TABLE ACCESS FULL            | S_ORDER   |  7245K |  1091M|       | 51607   (2)| 00:10:20 |
                        |* 10 |      HASH JOIN                    |                            |    11M |  1752M|   263M|   142K  (1)| 00:28:29 |
                        |  11 |       TABLE ACCESS FULL           | S_ORG_EXT      |  3897K|   219M|       | 16784   (1)| 00:03:22 |
                        |  12 |       MERGE JOIN CARTESIAN        |                            |    11M|  1115M|       | 50350   (1)| 00:10:05 |
                        |  13 |        TABLE ACCESS BY INDEX ROWID| ODI_LAST_EXECUTION_DETAILS |     3 |   165 |       |     2   (0)| 00:00:01 |
                        |* 14 |         INDEX RANGE SCAN          | ODI_LAST_EXE_DET_SCENARIO  |     3 |       |       |     1   (0)| 00:00:01 |
                        |  15 |        BUFFER SORT                |                            |  3897K|   167M|       | 50348   (1)| 00:10:05 |
                        |  16 |         TABLE ACCESS FULL         | S_ORG_EXT   |  3897K|   167M|       | 16783   (1)| 00:03:22 |
                        ------------------------------------------------------------------------------------------------------------------------
                        
                        • 9. Re: export Statistics
                          Mihael

                          But what plan is on production DB ?