Row lock with for update

SB35 · Jul 4, 2021 1:45PM

https://community.oracle.com/tech/developers/discussion/comment/16801040#Comment_16801040

I think both of these are modifiable at system level. But if i want to test if my query behavior changes or say the dynamic statistics usage in the note section will really change, is there anyway i can turn off the real-time statistics at session level?

Solomon Yakobson · Jul 4, 2021 5:00PM

Session level:

SQL> alter session set "_optimizer_gather_stats_on_conventional_dml" = false;

Session altered.

SQL> alter session set "_optimizer_use_stats_on_conventional_dml" = false;

Session altered.

SQL>

SY.

Solomon Yakobson · Jul 4, 2021 5:26PM

For example:

SQL> EXPLAIN PLAN FOR
  2  SELECT  COUNT(*)
  3    FROM  activity qRoot
  4    WHERE qRoot.Retired = 0
  5  /

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3056008855


-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                   |     1 |     4 |   194   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                                   |     1 |     4 |            |          |
|*  2 |   INDEX STORAGE FAST FULL SCAN| ACTIVITY_N_CLOSEUSERI_8RPPDAL8NU6 |   127K|   496K|   194   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("QROOT"."RETIRED"=0)
       filter("QROOT"."RETIRED"=0)

Note
-----
   - dynamic statistics used: statistics for conventional DML

19 rows selected.

SQL> alter session set "_optimizer_gather_stats_on_conventional_dml" = false
  2  /

Session altered.

SQL> alter session set "_optimizer_use_stats_on_conventional_dml" = false
  2  /

Session altered.

SQL> EXPLAIN PLAN FOR
  2  SELECT  COUNT(*)
  3    FROM  activity qRoot
  4    WHERE qRoot.Retired = 0
  5  /

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3056008855

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                   |     1 |     4 |   194   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                                   |     1 |     4 |            |          |
|*  2 |   INDEX STORAGE FAST FULL SCAN| ACTIVITY_N_CLOSEUSERI_8RPPDAL8NU6 |   127K|   496K|   194   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("QROOT"."RETIRED"=0)
       filter("QROOT"."RETIRED"=0)

15 rows selected.

SQL>

You can also control it on statement level:

SQL> show parameter "_optimizer_gather_stats_on_conventional_dml"

NAME                                        TYPE        VALUE
------------------------------------------- ----------- ------------------------------
_optimizer_gather_stats_on_conventional_dml boolean     FALSE

SQL> show parameter "_optimizer_use_stats_on_conventional_dml"

NAME                                     TYPE        VALUE
---------------------------------------- ----------- ------------------------------
_optimizer_use_stats_on_conventional_dml boolean     FALSE

SQL> EXPLAIN PLAN FOR
  2  SELECT  --+ OPT_PARAM('_optimizer_use_stats_on_conventional_dml' 'true')
  3          COUNT(*)
  4    FROM  activity qRoot
  5    WHERE qRoot.Retired = 0
  6  /

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3056008855

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                   |     1 |     4 |   194   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                                   |     1 |     4 |            |          |
|*  2 |   INDEX STORAGE FAST FULL SCAN| ACTIVITY_N_CLOSEUSERI_8RPPDAL8NU6 |   127K|   496K|   194   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("QROOT"."RETIRED"=0)
       filter("QROOT"."RETIRED"=0)

Note
-----
   - dynamic statistics used: statistics for conventional DML

19 rows selected.

SQL> alter session set "_optimizer_gather_stats_on_conventional_dml" = true
  2  /

Session altered.

SQL> alter session set "_optimizer_use_stats_on_conventional_dml" = true
  2  /

Session altered.

SQL> show parameter "_optimizer_gather_stats_on_conventional_dml"

NAME                                        TYPE        VALUE
------------------------------------------- ----------- ------------------------------
_optimizer_gather_stats_on_conventional_dml boolean     TRUE

SQL> show parameter "_optimizer_use_stats_on_conventional_dml"

NAME                                     TYPE        VALUE
---------------------------------------- ----------- ------------------------------
_optimizer_use_stats_on_conventional_dml boolean     TRUE

SQL> EXPLAIN PLAN FOR
  2  SELECT  --+ OPT_PARAM('_optimizer_use_stats_on_conventional_dml','false')
  3          COUNT(*)
  4    FROM  activity qRoot
  5    WHERE qRoot.Retired = 0
  6  /

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3056008855

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                   |     1 |     4 |   194   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                                   |     1 |     4 |            |          |
|*  2 |   INDEX STORAGE FAST FULL SCAN| ACTIVITY_N_CLOSEUSERI_8RPPDAL8NU6 |   127K|   496K|   194   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("QROOT"."RETIRED"=0)
       filter("QROOT"."RETIRED"=0)

15 rows selected.

SQL>

SY.

Jonathan Lewis · Jul 5, 2021 9:24AM

I think @Solomon Yakobson has supplied a convincing demonstration that the dynamic sampling is a consequence of real-time sampling, and your comment about constant change of status is a hint that the way the application works may be doing enough to trigger real-time stats.

The only remaining part of the puzzle is whether Oracle simply discard the histogram information because it's looking at real-time stats - and I think I may have seen something about that somewhere on the internet, possibly from @Sayan Malakshinov or @Mohamed Houri

Strategically - i.e. long term, rather than immediate hack - the ideal approach is the function-based index with matching query. Among other things this may allow you to drop a very large index which is (a) constantly subject to updates and (b) mostly a waste of space.

If you can't change the SQL at present and can't find a good way to disable the real-time stats collection for the task then you could simply connect an sql_plan_baseline to each of the two queries forcing the use of the index you want the optimizer to pick.

Looking at these current actuals, and the details you've given about the gathered histogram, the most robust option, and the one least likely to result in performance swings it the function-based index approach - possibly with a function that hides the C and MT values and exposes all the rest, and I think I'd got for a global index rather than have Oracle probe 100 different index partitions to find a few rows.

You'll notice that the D doesn't appear in the histogram data - the histogram is a hybrid and the 3,000 D rows have been captured in the ED bucket. (The ED repeat count is 53 but the ED bucket size is 2967). This is a simple indicator of the instability introduced by the "size repeat" option. I could invent scenarios that could result in the row path appearing as a side effect of this type of behaviour.

Regards

Jonathan Lewis

Sayan Malakshinov · Jul 5, 2021 1:12PM

Statistics: On the importance of the Notes column:

Jonathan is correct: that was the post by @Mohamed Houri:

Statistics: On the importance of the Notes column

Best regards,

Sayan Malakshinov

http://orasql.org

SB35 · Jul 5, 2021 4:03PM

@Jonathan Lewis @Solomon Yakobson @Sayan Malakshinov Thank You so much.

Yes , i am also seeing by disabling the real time stats collection using hint (OPT_PARAM('_optimizer_use_stats_on_conventional_dml','false')) , the dynamic statistics note section disappears from the plan. But still , i am not seeing that "STORAGE FULL" path getting reproduced. So may be something odd happening at run time making "STORAGE FULL FIRST ROWS" changed to "STORAGE FULL" path.

And i see in dba_part_col_statistics all the NOTES section against each partition for that column STATUS set as "HYPERLOGLOG ", so its not really matching with the blog note which states , discard of histogram may be result of "HIST_FOR_INCREM_STATS HYPERLOGLOG".

My Apology if i miss the answer somewhere, Its regarding, on 19C what exact METHOD_OPT option we should use(mainly for partition table with INCREMENTAL stats ON) in place of "FOR ALL COLUMSN SIZE REPEAT" on an existing table having few columns already HISTOGARM created on them.

Say for example a partition table TAB1 having three columns col1, col2, col3 but having COl2 with height balanced histogram and col3 having frequency histogram already present on it. Should we use

1)"For all columsn size 1 for columns size auto col2 col3"

2)"For all columsn size 1 for columns size auto col2 FOR COLUMNS SIZE 254 col3"

3)"For all columsn size AUTO"

Jonathan Lewis · Jul 5, 2021 4:51PM

@Sayan Malakshinov

Sayan,

Thanks for supplying a reference - unfortunately that wasn't the one I had in mind; there was something I read about a year ago.

Regards

Jonathan Lewis

Jonathan Lewis · Jul 5, 2021 5:09PM

@Sandeep Gandhi, Consultant @SB35

One little detail - for 19c it ought to be hybrid, not height-balanced. You'd only get height balanced if you were setting an actual value for estimate_percent rather than auto_sample_size.

Generally my preference is to avoid "size auto" completely and then setting individual "safe" sizes for columns that I knew ought to have frequency or top-frequency histograms. I do not like hybrid or height-balanced histograms - hybrid are better than height balanced, but they are still subject to instability because the stats are sampled.

So my preliminary choice would be:

for all columns size 1 for columns size X col2 for columns size Y col3

Where X and Y would be set to sizes where I was confident they would be safe candidates for frequency or top-frequency histograms. Remember that from 12c the number of buckets can go up to 2047 (or maybe 2048) so if you think an old height-balanced would have been useful then aiming for a top-frequency by setting a large value for X would be good. As for the col3 - if there should only be a small number of values (like the 7 or 8 in your example) than I might put in Y = 15 to give comfortable room for values that might be invented in the future - the excess specification won't change the workload or the number of buckets finally produced.

Regards

Jonathan Lewis

Forum Stats

Quick Links

Categories

Row lock with for update

Answers

Statistics: On the importance of the Notes column:

Statistics: On the importance of the Notes column

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Row lock with for update

Answers

Statistics: On the importance of the Notes column:

Statistics: On the importance of the Notes column