Optimizing Oracle Optimizer Statistics - Excution Plan is not being generated as expected
Posted on 2007-08-03
Some weeks ago, a built a new oracle server box and imported data from another server on it. Last week, I put the server on production. Since I put this new box on production, some users reported me performance issues on acessing data on some tables, while other tables are running really fast...
So, I decided to see the executions plans of queries that was reporting performance issues and discovered that these queries wasn't being the executions plans generated as expected... By example:
There is a table that have about 10.000.000 of rows and have the folowing DDL:
CREATE TABLE winthor2.pcmov
(dtmov DATE NOT NULL,
codprod NUMBER(6,0) NOT NULL,
numnota NUMBER(10,0) NOT NULL,
codoper VARCHAR2(2) NOT NULL,
codfilial VARCHAR2(2) NOT NULL,
status VARCHAR2(2) NOT NULL,
codcli NUMBER(6,0) NOT NULL,
codusur NUMBER(4,0) NOT NULL,
The table have some Indexes, including the index below:
CREATE INDEX winthor2.pcmov_index1 ON winthor2.pcmov
But, when I issue the query "select * from pcmov where dtmov between sysdate - 90 and sysdate - 60" the execution plan show me that oracle prefered perform a FULL TABLE SCAN instead of using the Index to find the data. If i runs the same query on the OLD server, the execution plan prefers to use the INDEX, as expected.
Searching on internet, I found that this issue could be caused by incorrect statistics on my schema objects, that causes oracle optimizer to not generate a optimal execution plan.
Does anybody knows how could I solve this problem?
I'm running Oracle 10G R2 x64 on a SuSE Linux Enterprise Server 10 box.
Thanks a lot!