| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: _index_join_enabled in 9i
The parameter makes available an access method as Jonathan described, and it
*is* available in 8i, just defaults to false. And of course it isn't to be
confused with "join indexes", the type of index spanning multiple tables,
introduced in 9i.
Here's an 8i example, note the "VIEW OF 'index$_join$_001'" step. Code_Detail is a table with the T1 and T2 columns each having a BMI on them:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL> alter session set "_index_join_enabled" = true;
Session altered.
SQL> set autotrace trace explain
SQL> select t1, t2 from code_detail;
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=299600 Bytes=2097200)
1 0 VIEW OF 'index$_join$_001' (Cost=17 Card=299600 Bytes=2097200)
2 1 HASH JOIN 3 2 BITMAP CONVERSION (TO ROWIDS) 4 3 BITMAP INDEX (FULL SCAN) OF 'CD_BMI_T1' 5 2 BITMAP CONVERSION (TO ROWIDS) 6 5 BITMAP INDEX (FULL SCAN) OF 'CD_BMI_T2'
The default was false on 8.1.7.0.0. On my copy of 9.2.0.1, the default is true. So this is one of those changes of default parameter values between versions that can impact you when upgrading from 8i to 9i.
But it does raise an interesting question. Why did support recommend to April that it be set to FALSE? What have people's experience been with it? I came across an 8.1.7.3/4 64 bit DB on Solaris some time ago where the people had _index_join_enabled set to true. I can see where it would be a desirable access path for some queries, but in this case they ran into all kinds of issues, seemed to go a little bonkers with it's usage, and decided to turn it off (for immediate relief, it took care of a lot of "problem" queries). Would *loved* to have had more time to dig into all the relevant factors playing a role but never got the time to revisit the issue and investigate further. For example my sample query above stinks performance wise with the access method (disk access on temp segments due to the HJ). But not surprisingly and for obvious reasons if I bump up the hash_area_size enough, it smokes right along. Bring h_a_s back down and the disk lights up and poor performance again. In the real world case where they turned it off, h_a_s was already set pretty high, but never got the opportunity to explore the issue and other relevant items further, and considering it's an undocumented parameter, and the default in 8.1.7 was false, felt it was probably prudent to leave it at false.
FWIW, on my test box, and using Kyte's harness, and with the h_a_s set way up there, here's the difference in my simple sample query. Run1 is with _index_join_enabled = true, Run2 is with it set to false. So it can be an effective option for some queries under certain situations:
SQL> exec runstats_pkg.rs_stop(500)
Run1 ran in 2734 hsecs
Run2 ran in 6020 hsecs
run 1 ran in 45.42% of the time
Name Run1 Run2 Diff LATCH.library cache 1,429 2,036 607 STAT...prefetched blocks 0 1,221 1,221 STAT...free buffer requested 1 1,432 1,431 STAT...physical reads 9 1,440 1,431 LATCH.cache buffers lru chain 2 3,003 3,001 STAT...consistent gets 105 21,434 21,329 STAT...session logical reads 117 21,449 21,332 STAT...no work - consistent re 63 21,420 21,357 STAT...buffer is not pinned co 71 21,430 21,359 STAT...table scan blocks gotte 0 21,405 21,405 STAT...session uga memory max 27,084 0 -27,084 STAT...session pga memory 34,048 0 -34,048 STAT...session pga memory max 34,048 0 -34,048 LATCH.cache buffers chains 298 44,687 44,389 STAT...table scan rows gotten 0 299,600 299,600 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct
But repeating the test with h_a_s set back down, well (Run1 with true, Run2 with false):
SQL> exec runstats_pkg.rs_stop(500)
Run1 ran in 141452 hsecs
Run2 ran in 4619 hsecs
run 1 ran in 3062.39% of the time
Name Run1 Run2 Diff LATCH.row cache objects 886 304 -582 STAT...recursive calls 268 1,044 776 STAT...prefetched blocks 0 1,221 1,221 STAT...free buffer requested 2 1,432 1,430 STAT...physical writes 1,773 9 -1,764 STAT...physical writes non che 1,773 9 -1,764 STAT...physical writes direct 1,773 9 -1,764 LATCH.redo writing 1,843 61 -1,782 LATCH.checkpoint queue latch 2,090 60 -2,030 LATCH.cache buffers lru chain 291 2,965 2,674 LATCH.enqueue hash chains 2,870 110 -2,760 LATCH.session allocation 4,444 1,296 -3,148 LATCH.messages 3,889 132 -3,757 LATCH.shared pool 5,676 970 -4,706 STAT...CPU used by this sessio 6,299 174 -6,125 STAT...CPU used when call star 6,299 174 -6,125 LATCH.enqueues 7,962 302 -7,660 LATCH.session idle bit 48,823 40,296 -8,527 STAT...session uga memory 12,712 -8,476 -21,188 STAT...table scan blocks gotte 0 21,405 21,405 STAT...no work - consistent re 59 21,496 21,437 STAT...buffer is not pinned co 68 21,540 21,472 STAT...consistent gets 102 21,631 21,529 STAT...session logical reads 114 21,653 21,539 LATCH.library cache 32,982 2,600 -30,382 LATCH.cache buffers chains 5,365 44,716 39,351 STAT...table scan rows gotten 0 299,600 299,600 STAT...physical reads 389,169 1,440 -387,729 STAT...physical reads direct 389,169 9 -389,160 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct
Regards,
Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> Connor McDonald
> Sent: Thursday, December 04, 2003 4:59 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: _index_join_enabled in 9i
>
>
> My understanding is that the parm simply reverts you
> to pre-9 behaviour where index joins were not there,
> so there should be no major problems.
>
> hth
> connor
>
> --- April Wells <AWells_at_csedge.com> wrote: >
> > We have discovered an... opportunity... and Oracle's
> > suggestion is to set
> > _index_join_enabled = false
> >
> > Anyone seen any detrimental effects of setting this
> > parameter to false in
> > 9.2.0.#?
> >
> > April Wells
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: elkinsl_at_flash.net Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Dec 04 2003 - 09:34:26 CST
 |
 |