RE: Question on Stats for Indexes Created in Parallel

  • From: "Deas, Scott" <Scott.Deas@xxxxxxx>
  • To: Stefan Koehler <contact@xxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 1 Dec 2015 17:59:33 +0000

Stefan,

I'll play around with it a bit more, but just an FYI, I have been
dropping/re-creating the table both using CTAS and manual definition and
getting the same results (but it could be me). No chance for maintenance jobs
to run when I'm doing this.

I have opened an SR with Oracle, so if they come back with anything related to
the NDV values, I'll update this thread.

Thanks!
Scott

-----Original Message-----
From: Stefan Koehler [mailto:contact@xxxxxxxx]
Sent: Tuesday, December 01, 2015 12:56 PM
To: Deas, Scott <Scott.Deas@xxxxxxx>; oracle-l@xxxxxxxxxxxxx
Subject: RE: Question on Stats for Indexes Created in Parallel

Hi Scott,

DB Version 11.2.0.4 - NDV is wrong when index is built in parallel,
correct when built serially. Stale_stats is set to NO (this is not
what I saw in my earlier tests, maybe a user error.....)

I am pretty sure that the STALE_STATS effect is related to what i described
previously (view definition). Just check the LAST_ANALYZED column of the table
statistics. I assume that the statistics (for the table) were gathered in the
default Oracle maintenance window.

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK

"Deas, Scott" <Scott.Deas@xxxxxxx> hat am 1. Dezember 2015 um 17:23
geschrieben:


Hi Stefan,

I guessed the same regarding the cause, but thought there may be something
I'm missing.

My concern about setting the stats is that for this low-cardinality
column, I know what the distinct values are, but I would assume that
any of our bitmap indexes will have bad cardinality estimates as a result of
this issue (and I won't know the NDV for each). Re-gathering stats is not a
deal-breaker, but it seems like it shouldn't have to be done.

I ran a few more tests in our environments:

DB Version 10.2.0.4 - NDV is correct, regardless of whether the index
is built in parallel or not. Stale_stats is set to NO DB Version
11.2.0.4 - NDV is wrong when index is built in parallel, correct when
built serially. Stale_stats is set to NO (this is not what I saw in my
earlier tests, maybe a user error.....) DB Version 12.1.0.2.0 - NDV is
wrong when index is built in parallel, correct when built serially.
Stale_stats is set to NO

Sounds like I need to open a ticket with Oracle.

Thanks,
Scott

Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 12-2015