Clarification about the histograms behaviour

[manjunathk]

Hi,
Environment: 8.1.7.4 on sun Solaris 5.8
While tuning a sql query, I have been successful in terms of reducing the cpu and the LIO. However, I am not understanding the change in the execution path due to the histograms. Can some one please clarify? This is important for me because I need to recommend the change to the Production env. and without a clear understanding of how it was achieved, I am reluctant.
The query is:
SELECT t_10007_0.TableInstanceID,t_10007_0.ContextID,t_1.TableInstanceID,t_1.ContextID,
CAST( t_1.ACCOUNTNAME AS CHAR(255)) FROM LYTE_CTXAMT t_10007_0,LYTE_Account t_1 WHERE
(UPPER(t_10007_0.AccountRef) LIKE '6%') AND t_10007_0.ApplicationID = 17 AND
t_10007_0.EnterpriseKEY=81 AND t_10007_0.ContextID in (
1000001158,......a list of almost 300 values)
AND t_10007_0.EnterpriseKEY=81 AND t_10007_0.EnterpriseKEY = t_1.EnterpriseKEY and
t_10007_0.AccountRef = t_1.ACCOUNTCODE AND t_1.ENTERPRISEKEY=81 AND t_1.ApplicationID = 17;

I created a histogram on all indexed columns in contextdetail table.
The tkprof results after sql_trace for both scenes(with and without histograms) are as under:
With Histograms:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.09 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.02 0.03 0 280 4 48
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.11 0.11 0 280 4 48

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 58

Rows Row Source Operation
------- ---------------------------------------------------
48 NESTED LOOPS
49 VIEW LYTE_CTXAMT
49 SORT GROUP BY
48 NESTED LOOPS
49 HASH JOIN
215 INDEX FAST FULL SCAN (object id 74304)IX_CONTEXTDETAIL_3
48 TABLE ACCESS BY INDEX ROWID CTXAMT
49 INDEX RANGE SCAN (object id 74319)UKCTXAMT1
48 INDEX RANGE SCAN (object id 74324)IX_CTXAMT_LYTE
48 TABLE ACCESS BY INDEX ROWID LYACCOUNT
96 INDEX UNIQUE SCAN (object id 74815)UKLYACCOUNT1

With No Histograms(but regular analyze with 100% was done)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.06 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.21 0.20 0 3832 4 48
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.27 0.26 0 3832 4 48

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 58

Rows Row Source Operation
------- ---------------------------------------------------
48 NESTED LOOPS
49 VIEW LYTE_CTXAMT
49 SORT GROUP BY
48 NESTED LOOPS
49 NESTED LOOPS
216 INDEX FAST FULL SCAN (object id 74304)IX_CONTEXTDETAIL_3
263 TABLE ACCESS BY INDEX ROWID CTXAMT
263 INDEX RANGE SCAN (object id 74319)UKCTXAMT1
48 INDEX RANGE SCAN (object id 74324)IX_CTXAMT_LYTE
48 TABLE ACCESS BY INDEX ROWID LYACCOUNT
96 INDEX UNIQUE SCAN (object id 74815)UKLYACCOUNT1.
My understanding is that the operations on Contextdetail are not radically changed. 21e rows against 216 rows from Index Fast Full Scan. Is that sufficient to reduce the overall cpu and gets by more than 50%?
I may be missing the obvious here. Please help me.
Thanks in advance
Manjunath

[nagarjuna]

It's like this. You have 1000 rows in your table and one of histogram column is having values as follows

5 rows for value 'A'
995 rows for value 'B'

Since you generated histograms for this table, Oracle will have idea abt how many rows will be fetched for each of the values while creating execution plan itself. (without histograms, it is found only after executing the query.. that is after generating relatively approximate execution plan and executing the query based on this plan). Since, Oracle knows how many rows will be fetched for each value of histogrammed column, it can get better execution plan.

So, a query for value 'A' will use index for sure and for value will go for FTS.

This is simple case. Something like this happen for other complex cases.

Without histograms, Oracle would have thought that any value for the column would fetch 50% of the rows and calculates the cost based on this and may / may not go for indexes.