This discussion is archived
9 Replies Latest reply: Jun 30, 2013 10:39 PM by Mihael RSS

export Statistics

985871 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

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

  • 8. Re: export Statistics
    985871 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    But what plan is on production DB ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points