| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Tunning question
In comp.databases.oracle.server, you wrote:
>
>An oddity here is that your logical I/O count is in the
>tens of thousands (from your earlier post), but the
>optimizer is indicating 1M rows as the output from
>the second table access. The two numbers could
>only be consistent if you have a VERY large number
>for 'buffer is pinned count' when the query runs. This
>statistic is not reported by autotrace, but is a measure
>of visits to buffers. Rerun the queries but take a
>snapshot of the session's v$mystat values before
>and after each run.
You are right, number of 'buffer is pinned count' is around 6 million
during execution of the faster query and only around 40000 during
execution of slower query.
But I'm not sure what that means.
Real source of my problem is query like this:
select *
from conns,users
where user_id=users.id AND users.name='blah'
AND cend between '2003-08-01 0:00:00' AND '2003-09-01 0:00:00'
There are indexes on both columns. Table is around 66 milion rows.
Index on cend is highly selective and has very low clustering factor
(#DK: 34900662, CLUF: 1366351). In given date range there
are around 3.3 million rows.
Density of cend is DENS: 2.8663e-08
Index on column user_id is less selective and much higher
clustering factor (#DK: 68238, CLUF: 58725123). There are
39228 distinct user_ids in given date range.
Density of user_id is 1.4656e-05 - much higher.
Optimizer chooses index on user_id with execution time of 3-5 minutes. With hint to use index on cend execution time is around 1 minute.
I have tracked part of the problem in 10053 trace:
NDV: 34887948 NULLS: 0
DENS: 2.8663e-08 LO: 2452173 HI: 2452898
TABLE: CONNS ORIG CDN: 62233203 CMPTD CDN: 2747921
Access path: tsc Resc: 93637 Resp: 8513
Access path: index (scan)
...blah blah....
Join order[1]: USERS [USERS] CONNS [CONNS]
Now joining: CONNS [CONNS] *******
NL Join
Outer table: cost: 1 cdn: 1 rcz: 14 resp: 1
Inner table: CONNS
Access path: tsc Resc: 93637
Join resc: 93638 Resp: 93638
OPTIMIZER PERCENT INDEX CACHING = 90
Access path: index (scan)
INDEX#: 4071 TABLE: CONNS
CST: 63687 IXSEL: 4.4155e-02 TBSEL: 4.4155e-02
Join resc: 31844 resp:31844
OPTIMIZER PERCENT INDEX CACHING = 90
Access path: index (join index)
INDEX#: 34670 TABLE: CONNS
CST: 860 IXSEL: 0.0000e+00 TBSEL: 1.4656e-05
Join resc: 431 resp:431
Join cardinality: 40 = outer (1) * inner (2747921) *
sel (1.4656e-05) [flag=0]
Best NL cost: 431 resp: 431
Index 4071 is on cend and index 34670 is on user_id.
DENS parameter for cend is much larger than DENS on user_id. But the problem is in values LO: 2452173 HI: 2452898 for cend column.
I found that standard formula for BETWEEN operator is
(val2-val1)/(HI-LO)+2*c.density.
But HI-LO is only 725 and I assume that val2-val1 is around
30 which gives TBSEL of only 4.415e-2 and with that value
optimizer rejects that access path.
Interesting part is that 725 is really NUMBER OF DAYS between max(cend)-max(cend) - table has last two years of data. That why I assumed that val2-val1 is 30 - number of days in a month. 30/725 is (surprise surprise) 0.0413 - almost exactly the calculated TBSEL.
So the big question is why the optimizer uses only DATE part of cend column when all the rows in cend column has both date and time part down to the second precision.
I don't know is there any chance to 'convince' the optimizer to use 'the right' index on cend instead of using index on user_id. All the calculations from trace are correct so I can't find any flaw in logic. I tried using histograms on both columns but with no help (above trace is without histograms).
Using hints is complicated because that requires finding and changing many queries in application.
My last try will be to try to create index on both (user_id, cend) columns.
Any other suggestions will be highly appreciated.
a. Received on Wed Sep 24 2003 - 10:36:36 CDT
 |
 |