9 Replies Latest reply: Jul 8, 2013 2:27 PM by Hussein Sawwan-Oracle RSS

    Table Gather stats in R12

    873768

      Hi:

       

      I ran Gather schema stats on our 12.1.3, 11.1.0.7 system with all and 10% last night. It took about 2.5 hrs. I checked just now and saw 38% tables were updated. There are some tables have dates as earlier as 2004. May I ask why? And also there are 0.5 % tables have no dates (blank) under last_analyzed column. These happen to all schemas. Thank you for answering my questions in advince.

        • 1. Re: Table Gather stats in R12
          Hussein Sawwan-Oracle

          Are you saying the request completed successfully and only 38% of the tables were analyzed?

           

          What are the tables which have dates earlier as 2004? Are these seeded tables or custom ones?

           

          What are the tables that have no dates under last_analyzed column?

           

          Did you chose to gather schema stats for ALL schemas?

           

          Thanks,

          Hussein

          • 2. Re: Table Gather stats in R12
            873768

            Hussein:

            They completed successfully. Yes, only 38% were analyzed. Yes, I gather schama for All and 10%.

            No dates like:
            AR, APPS, SYS, AZ, BIC, BIL, BIS, BOM

             

            Before yesterday like:

            ASM, CTXSYS, DBSNMP, OLAPSYS. SYSTEM, PO, MDSYS, ORDSYS...

            • 3. Re: Table Gather stats in R12
              Hussein Sawwan-Oracle

              Please run Gather Schema Statistics concurrent program for one of those schemas only (let's say "AR") and see if the column gets populated then -- I assume "AR" is a licensed module/product.

               

              Thanks,

              Hussein

              • 4. Re: Table Gather stats in R12
                873768

                I will do that. Thank you, Hussien. May I ask you what the 10% mean when I select All with 10% for the CR?

                • 5. Re: Table Gather stats in R12
                  Hussein Sawwan-Oracle

                  It's the percentage of rows to estimate.

                   

                  Concurrent Processing - How To Gather Statistics On Oracle Applications Release 11i and/or Release 12 - Concurrent Process,Temp Tables, Manually [ID 419728.1]

                  Definition of Parameters Used in Gather Schema Statistics Program [ID 556466.1]

                   

                  Thanks,

                  Hussein

                  • 6. Re: Table Gather stats in R12
                    873768

                    I ran AR only with 10%, still some tables have blank dates. The following is sample:

                    ARHZ_ORG_PROFILE_VAL_GT
                    ARHZ_PARTY_SCORE_DTLS_GT
                    ARHZ_PARTY_SITE_VAL_GT
                    ARHZ_PARTY_VAL_GT
                    ARHZ_PURGE_GT
                    ARHZ_RELATIONSHIP_VAL_GT
                    ARHZ_TEMP_REL_GT
                    ARHZ_USER_ORG_GT
                    AROCM_CREDIT_REVIEW_GT
                    ARRA_AR_AMOUNTS_GT
                    ARRA_AR_GT
                    ARRA_CUSTOMER_TRX_LINES_GT
                    ARRA_INTERFACE_REQID_GT
                    ARARI_REG_VERIFICATIONS_GT
                    ARAR_AE_ALLOC_REC_GT
                    ARAR_BASE_DIST_AMTS_GT
                    ARAR_CASH_RECOS_PS_GT
                    ARAR_CASH_RECPT_RULE_PARAM_GT
                    ARAR_CMGT_CURR_USAGE_GT
                    ARAR_CM_SUM_TRX_DAYS_GT
                    ARAR_CREATE_RECEIPTS_GT
                    ARAR_CRH_APP_GT
                    ARAR_CRH_GT
                    ARAR_CUST_SEARCH_GT_ALL
                    ARAR_DETECT_GT
                    ARAR_GL_RECON_GT
                    ARAR_GTA_AR_DIFFERENCE_TEMP
                    ARAR_GTA_CONSOL_INVS_TEMP
                    ARAR_GTA_CONSOL_TEMP
                    ARAR_GTA_DIFFERENCE_TEMP
                    ARAR_GTA_IMPORT_REP_TEMP
                    ARAR_GTA_MISSING_ARTRX_TEMP
                    ARAR_GTA_TRANSFER_TEMP
                    ARAR_GTA_UNMATCHED_TEMP
                    ARAR_INV_API_DIST_GT
                    ARAR_INV_API_HEADERS_GT
                    • 7. Re: Table Gather stats in R12
                      Hussein Sawwan-Oracle

                      Since the above is a sample, are rest of the tables TEMP/GT ones? If yes, then I believe you can ignore this for temporary/global tables.

                       

                      If the statistics of the main AR tables are up to date, then you should be OK.

                       

                      Thanks,

                      Hussein

                      • 8. Re: Table Gather stats in R12
                        873768

                        Hussein:

                        Most of them are temp and gt. Here are some others:

                        ARAR_MRC_CASH_HEADER
                        ARAR_MRC_CASH_LINES
                        ARAR_MRC_INQ_TRANS_BALANCES
                        SYSSTREAMS$_COMPONENT_LINK_IN
                        SYSSTREAMS$_COMPONENT_PROP_IN
                        SYSSTREAMS$_COMPONENT_STAT_IN
                        SYSSTREAMS$_COMPONENT_STAT_OUT
                        SYSSTREAMS$_LOCAL_ACTIONS_IN
                        SYSSTREAMS$_LOCAL_FINDINGS_IN
                        SYSSTREAMS$_LOCAL_RECS_IN
                        SYSSTREAMS$_PATH_BOTTLENECK_OUT
                        SYSSTREAMS$_PATH_STAT_OUT
                        APPSSUGGESTIONSLIST
                        SYSSYS$SERVICE_METRICS_TAB
                        CSRSYS_IOT_OVER_576202
                        SYSSYS_MFBA_NCHANGE
                        SYS

                        SYS_MFBA_NROW

                        • 9. Re: Table Gather stats in R12
                          Hussein Sawwan-Oracle

                          Since most of them (if not all) are global and temp tables then this is an expected behavior.

                           

                          Thanks,

                          Hussein