Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, brijesh.

Asked: January 08, 2020 - 8:21 am UTC

Last updated: January 08, 2020 - 2:06 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi Tom,

We are facing some situation in Production even it is working fine in Pre-Prod(same environment as Production but quarterly refreshed).

We are running Gather STAT job every weekend in both environment(Prod and Pre-Prod). Problem is if we see last_analyzed column in table DBA_INDEXES for some particular tables in production, can see that column still have old entry even same job is working fine in Pre-Prod.

However Table STAT has been gather as latest only, facing this issue for INDEXES, not for all indexes only for few index in same table.

below is the production entry. 

INEDX                  TABLE NAME        LAST ANALYZED
IDX_FK_ODTR_STATUSID         OD_TRANSACTION 12-08-2017 21.57.32
IDX_FK_ODTR_TRANSACTIONTYPE  OD_TRANSACTION 12-08-2017 21.58.04
IDX_FK_ODTR_MARKETID         OD_TRANSACTION 12-08-2017 21.58.30
IDX_ODTR_PUBLISHINGSESSIONID OD_TRANSACTION 12-08-2017 22.00.43
IDX_ODTR_UNIQUE_TRANS_IDENT  OD_TRANSACTION 12-08-2017 22.01.14
IDX_FK_ODTR_RECORDTYPE       OD_TRANSACTION 12-08-2017 22.02.13
IDX_ODTR_POSTINGM            OD_TRANSACTION 12-08-2017 22.03.21
IDX_FK_ODTR_EXIT_DATE        OD_TRANSACTION 12-08-2017 22.03.50
IDX_FK_ODTR_RECEPTION_SESS   OD_TRANSACTION 12-08-2017 22.04.19
IDX_ODTR_PMM_CTR_ID          OD_TRANSACTION 28-08-2017 11.18.51
IDX_ODTR_EXTDAY_EXTLN_2      OD_TRANSACTION 08-11-2017 11.46.51
IDX_TR_PUBLISHING            OD_TRANSACTION 02-11-2019 02.25.34
IDX_ODTR_COMPOSED_SUMMARIES  OD_TRANSACTION 02-11-2019 02.29.56
IDX_FK_ODTR_PAYMENT_TYPE     OD_TRANSACTION 02-11-2019 02.32.20
IDX_ODT_COMPOSED3            OD_TRANSACTION 02-11-2019 02.33.18
IDX_ODT_COMPOSED2            OD_TRANSACTION 02-11-2019 02.33.51
IDX_FK_ODTR_PASSAGETYPE      OD_TRANSACTION 02-11-2019 02.34.30
IDX_ODTR_PMM_ACT_ID          OD_TRANSACTION 04-01-2020 22.03.14
IDX_ODTR_FILTER_CODE         OD_TRANSACTION 04-01-2020 22.03.33
IDX_ODTR_PMM_OPERATION_DATE  OD_TRANSACTION 04-01-2020 22.06.19
IDX_ODTR_EXIT_LANE_CMP_1     OD_TRANSACTION 04-01-2020 22.11.56
PK_OD_TRANSACTION            OD_TRANSACTION 04-01-2020 22.14.31
IDX_ODTR_LPN                 OD_TRANSACTION 04-01-2020 22.14.53
IDX_ODTR_EXIT_POINT          OD_TRANSACTION 04-01-2020 22.18.11
IDX_TR_OBUID                 OD_TRANSACTION 04-01-2020 22.21.04
IDX_ODTR_EXIT_DAY_LOCAL      OD_TRANSACTION 04-01-2020 22.23.56
IDX_EXIT_POINT_AEG           OD_TRANSACTION 04-01-2020 22.29.03
UP_OD_TRANSACTION            OD_TRANSACTION 04-01-2020 22.31.50
IDX_ODTR_EXDL_EXD            OD_TRANSACTION 04-01-2020 22.37.02
IDX_ODTR_ARCH_FLAG           OD_TRANSACTION 04-01-2020 22.40.20
IDX_EXTTRXID_SUBSTR          OD_TRANSACTION 04-01-2020 22.40.20


what would be the possible cause for this.

Thanks,
BR

and Chris said...

It's possible the stats gathering job has run out of time. So had to stop part-way through.

You can verify this by querying dba_autotask_job_history to see if the job was stopped. If this happened, the job_info will tell you why.

If it is taking too long, Nigel Bayliss has suggestions for making it faster in this article:

https://blogs.oracle.com/optimizer/how-to-gather-optimizer-statistics-fast

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 08, 2020 - 11:42 am UTC

actually that same gather STAT script is taking more time in Pre-Prod compare to Prod and working perfectly fine.
I read some article that remove STAT for index and then again gather STAT, do you think it will be useful?

can i use "DBMS_STATS.DELETE_INDEX_STATS" for that purpose?
Chris Saxon
January 08, 2020 - 2:06 pm UTC

First question:

Have you got any slow queries with "wrong" plans because these indexes haven't been analyzed recently?

If the answer is no, your action is easy. DO NOTHING!

Deleting stats means that there will be a period of time where the optimizer has no information about these indexes. Which is likely to lead to some... undesirable plans.

If gathering stats for an index enables the optimizer to find a better plan you're better off manual gathering for just that index with:

DBMS_STATS.GATHER_INDEX_STATS

But I repeat: it's only worth doing this if performance is worse than normal AND you think it's due to out-of-date stats. If performance is "good"/the same as usual, leave it as is!

A reader, January 14, 2020 - 1:17 pm UTC

Thank you very much for you clear response !!!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.