Re: Advice on a weird query plan

On Apr 16, 10:28 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Pat" <pat.ca..._at_service-now.com> schreef in berichtnews:8e132d52-45fb-4d8f-8ded-de9583399902_at_m71g2000hse.googlegroups.com...
>
>
>
> > Oracle 10.2.0.4
> > Running in case Insensative mode
> > ALTER SESSION SET NLS_COMP=LINGUISTIC;
> > ALTER SESSION SET NLS_SORT=BINARY_CI;
>
> > I have a table, sysevent, with about 4.5 million rows in it.
> > I have a column "state" with a ridiculously squewed data pattern:
> > SQL> select "state", count("state") from sysevent group by "state";
>
> > state COUNT("STATE")
> > ---------------------------------------- --------------
> > processed 4492467
> > ready 63
>
> > Basically, data gets inserted into this table by an application with a
> > state of "ready" then changed, programatically, to processed when it's
> > done grinding over the data. So at any given point in time, about
> > 99.9999% of the table has a state of "processed".
>
> > I have an nls index on state:
>
> > create index sysevent_state on sysevent(NLSSORT("state",
> > 'nls_sort=''BINARY_CI'''));
>
> > I'd reasonably expect a query against state="processed" to use a full
> > table scan.
> > I'd reasonably expect a query against state="ready" to use an index
> > range scan.
>
> > I've got current stats on that table, but I double checked for
> > purposes of this test:
>
> > SQL> analyze table sysevent compute statistics for columns "state"
> > size 10;
>
> > Table analyzed.
>
> > SQL> select endpoint_number, endpoint_value, endpoint_actual_value
> > from user_tab_histograms where table_name = 'sysevent' and column_name
> > = 'state';
>
> > ENDPOINT_NUMBER ENDPOINT_VALUE
> > --------------- --------------
> > ENDPOINT_ACTUAL_VALUE
> > ------------------------------------------------------------------------------------------------------------------------------------
> > 4494114 5.8386E+35
> > processed
>
> > 4494216 5.9398E+35
> > ready
>
> > So now I do two queries:
>
> > SQL> explain plan for select * from sysevent where "state" = 'ready';
>
> > Explained.
>
> > SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> > PLAN_TABLE_OUTPUT
> > ------------------------------------------------------------------------------------------------------------------------------------
> > Plan hash value: 122562926
>
> > ------------------------------------------------------------------------------
> > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> > |
> > ------------------------------------------------------------------------------
> > | 0 | SELECT STATEMENT | | 100 | 24700 | 26 (89)| 00:00:01
> > |
> > |* 1 | TABLE ACCESS FULL| SYSEVENT | 100 | 24700 | 26 (89)|
> > 00:00:01 |
> > ------------------------------------------------------------------------------
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
>
> > PLAN_TABLE_OUTPUT
> > ------------------------------------------------------------------------------------------------------------------------------------
>
> > 1 -
> > filter(NLSSORT("state",'nls_sort=''BINARY_CI''')=HEXTORAW('726561
> > 647900') )
>
> > 14 rows selected.
>
> > SQL> explain plan for select * from sysevent where "state" =
> > 'processed';
>
> > Explained.
>
> > SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
> > PLAN_TABLE_OUTPUT
> > ------------------------------------------------------------------------------------------------------------------------------------
> > Plan hash value: 122562926
>
> > ------------------------------------------------------------------------------
> > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> > |
> > ------------------------------------------------------------------------------
> > | 0 | SELECT STATEMENT | | 100 | 24700 | 26 (89)| 00:00:01
> > |
> > |* 1 | TABLE ACCESS FULL| SYSEVENT | 100 | 24700 | 26 (89)|
> > 00:00:01 |
> > ------------------------------------------------------------------------------
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
>
> > PLAN_TABLE_OUTPUT
> > ------------------------------------------------------------------------------------------------------------------------------------
>
> > 1 -
> > filter(NLSSORT("state",'nls_sort=''BINARY_CI''')=HEXTORAW('70726F
> > 63657373656400') )
>
> > 14 rows selected.
>
> > I cannot, for the life of me, figure out why we're table scanning in
> > the case of state='ready'. It's a highly, highly selective query and
> > the histogram bears that out. It's not bind variable peeking b/c I'm
> > using literals in this case.
>
> > Does anyone have any idea what I'm doing wrong?
>
> Before proceeding, check if anyone has (again) altered your database
> settings like
> optimizer_index_caching, optimizer_index_cost_adj etc please.....
>
> Furthermore, if you have two values (processed and ready) why the
> nls_sort=binary_ci and not just a plain index?
> I don't think the ORDER of these is relevant in any sort method.
>
> Shakespeare

optimizer_index_caching = 0
optimizer_index_cost_adjust = 100
(I believe these are system defaults which I reverted too last night)

I think the NLS index is necessary (as opposed to a binary index) because the session is running in NLS_COMP=LINGUISTIC, isn't it? So he'd actually match 'ReAdy' to 'ready' in this mode, which he can do with a linguisitic index but he can't do with a binary index (short of a full index scan and a transform on the keys). Or am I just confused (wouldn't be the first time).

As to why the whole session is running in NLS_COMP instead of just those queries that need it, the only answer is "that's the way the app is written". Not really something I can control at this point; if database efficiency were what matters, we should probably be using an int as a separator instead of a varchar2 anyway :(. Received on Wed Apr 16 2008 - 13:30:53 CDT