RE: Need some 10053 Guidance to help me solve a puzzler (with V$BH information and block info)

From: <Christopher.Taylor2_at_parallon.net>
Date: Fri, 12 Oct 2012 08:40:06 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88562532632_at_NADCWPMSGCMS10.hca.corpad.net>

I wanted to share some V$BH observations this morning as I tested this query with and without the INDEX hint.

Checking V$BH before running the query with the index hint:

---

OBJECT_NAME                               SIZE_MB
-----------                               ---------
CONCUITY.MAPY_CALC_SVC_PERF1              3.2890625	

CONCUITY.MON_ACCOUNT_PAYER_CALC_SERVICE 27.3828125

OWNER       OBJECT_NAME                       OBJECT_TYPE    NUM_BLOCKS
--------    -------------------               -----------    ----------
CONCUITY    MAPY_CALC_SVC_PERF1               INDEX          417	
CONCUITY    MON_ACCOUNT_PAYER_CALC_SERVICE    TABLE          3874		

Now I run the query with the INDEX hint (after purging shared pool of any cursors containing "gather_plan_statistics"):

---

197876 rows selected.

Elapsed: 00:02:55.06

V$BH after the query with the INDEX hint:

---

OBJECT_NAME                                  SIZE_MB
-----------                                  ----------
CONCUITY.MAPY_CALC_SVC_PERF1                 53.328125	
CONCUITY.MON_ACCOUNT_PAYER_CALC_SERVICE      245.6484375	

OWNER       OBJECT_NAME                         OBJECT_TYPE     NUM_BLOCKS
--------    ------------------                  -----------     -----------
CONCUITY    MAPY_CALC_SVC_PERF1                 INDEX           6826	
CONCUITY	MON_ACCOUNT_PAYER_CALC_SERVICE	TABLE           31443

V$BH after the query WITHOUT the INDEX hint:

---

OWNER       OBJECT_NAME                         OBJECT_TYPE     NUM_BLOCKS
CONCUITY    MON_ACCOUNT_PAYER_CALC_SERVICE      TABLE           94172	

Total Blocks For Segments:

---

OWNER       SEGMENT_NAME                        COUNT Blocks
--------    -------------------                 -------------
CONCUITY    MAPY_CALC_SVC_PERF1                 73728	
CONCUITY    MON_ACCOUNT_PAYER_CALC_SERVICE      769952
	

Double check me. For _this_ query:

---

1.) Somewhere between 0-417 blocks of index MAPY_CALC_SVC_PERF1 are cached
2.) Somewhere between 5992 and 6409 blocks of index MAPY_CALC_SVC_PERF1 are read into cache if none of the original 417 that were cached are used by this query.
3.) Somewhere between 0-3874 blocks of table MON_ACCOUNT_PAYER_CALC_SERVICE are cached
4.) Somewhere between 23695 and 27569 blocks of table MON_ACCOUNT_PAYER_CALC_SERVICE are read into cache if none of the original 3874 can be used for this query.
5.) Reading the needed table blocks after scanning the index is faster than reading the whole table - though we don't necessarily know why

What else can we say about this so far specific to this query?

---

a.) Table data _may_ be colocated?
b.) reading the index blocks and then getting the needed table blocks requires LESS total blocks than reading the whole table

Thoughts?

Chris

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 12 2012 - 15:40:06 CEST