Advice on a weird query plan
Date: Wed, 16 Apr 2008 09:15:31 -0700 (PDT)
Message-ID: <8e132d52-45fb-4d8f-8ded-de9583399902@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? Received on Wed Apr 16 2008 - 11:15:31 CDT