Histogram Statistics on Function based Index hidden column.

In one of previous post, had discussed on METHOD_OPT Params for different histogram stats setup.
Current post is just an extension of previous post.

If need more information on same, please check these out METHOD OPT Param for Stats in Oracle.

During performance analysis for a SQL, identified recommendations comprise of having histogram created on a hidden columns created as part of function based index.

Lets start with field work.
All Examples are tested in Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 version.

CREATE TABLE TEST_HIDDEN_HISTOGRAM
AS
SELECT * FROM ALL_OBJECTS;

CREATE INDEX IDX_LOWER ON TEST_HIDDEN_HISTOGRAM(LOWER(OWNER));

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>NULL, TABNAME=> 'TEST_HIDDEN_HISTOGRAM', ESTIMATE_PERCENT=>NULL, CASCADE=>TRUE, METHOD_OPT=> 'FOR ALL COLUMNS SIZE 1');

COLUMN_NAME NUM_DISTINCT HIDDEN_COLUMN HISTOGRAM
------------- ------------ ------------- ---------
SYS_NC00016$ 33 YES NONE
OWNER 33 NO NONE
.....
...

With new function based index, oracle internally create a hidden/virtual columns.
Post table stats gathering, necessary stats for hidden column is also gathered as normal column.

Data for functions columns “SYS_NC00016$” is Skew and as part of performance improvement histogram was an need.

SELECT LOWER(OWNER), COUNT(*) FROM TEST_HIDDEN_HISTOGRAM
GROUP BY LOWER(OWNER) ORDER BY COUNT(*) DESC;

LOWER(OWNER) COUNT(*)
------------------------------ ----------
sys 69294
ordsys 2467
sysman 2154
system 526
...

Lets check cardinality estimates as per current table/column stats.

EXPLAIN PLAN FOR
SELECT * FROM TEST_HIDDEN_HISTOGRAM
WHERE LOWER(OWNER) = 'sys';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 4115666762
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2442 | 238K|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_HIDDEN_HISTOGRAM | 2442 | 238K|
|* 2 | INDEX RANGE SCAN | IDX_LOWER | 2442 | |
-----------------------------------------------------------------------------

Current estimations is purely on distinct values of hidden columns and not on distribution.
Cardinality estimate : 2442.

Hence for each and every input filter, it will go for “INDEX ACCESS”.
But for some values we want it to go for FULL TABLE.

With “FOR ALL HIDDEN COLUMNS SIZE” Option, we can create histogram on hidden columns.

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'DMAHTO', TABNAME=> 'TEST_HIDDEN_HISTOGRAM', ESTIMATE_PERCENT=>NULL, CASCADE=>TRUE, METHOD_OPT=> 'FOR ALL HIDDEN COLUMNS SIZE 35');

Columns Stats :
COLUMN_NAME NUM_DISTINCT HIDDEN_COLUMN HISTOGRAM
------------- ------------ ------------- ---------------
SYS_NC00016$ 33 YES FREQUENCY

Let recheck same plan now,

EXPLAIN PLAN FOR
SELECT * FROM TEST_HIDDEN_HISTOGRAM
WHERE LOWER(OWNER) = 'sys';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 4265761292
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 |
| 1 | SORT AGGREGATE | | 1 | 5 |
|* 2 | TABLE ACCESS FULL| TEST_HIDDEN_HISTOGRAM | 69294 | 338K|
--------------------------------------------------------------------

Cardinality estimate for columns improve to exact values to 69k.
With new cardinality estimates, plan has switch from index scan to full table.

With “FOR ALL HIDDEN COLUMNS SIZE”, we can gather statistics for hidden columns created as part of function based index or columns groups at column level or with histograms.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s