Re: Advice on a weird query plan

"Pat" <pat.casey_at_service-now.com> schreef in bericht news: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 Received on Wed Apr 16 2008 - 12:28:28 CDT