Hi,
Version 11201 ,RAC two node on linux rehat 5.5
I am gathering 100% stats against a table with about 96 million rows.
Yet , during the day im having performance problem due to a full table against the table.
About 100,000 rows are added to the table every day.
Only after running 100% stats AGAIN, fix the problem (plan is using index)
I am using the default 'FOR ALL COLUMN SIZE AUTO' method.
I also tried to gather statistics on "value" column with 254 bucket but it didnt help.
This is the statment:
SELECT distinct d.id, d.serial
FROM device_params p, device d
where d.id = p.device_id
and p.value like :value
order by serial;
There is an index on value column:
select index_name, index_type,blevel,distinct_keys,clustering_factor,num_rows,sample_size , last_analyzed
from dba_indexes
where table_name= 'DEVICE_PARAMS'
IDX_D_PARAM_VALUE NORMAL 3 894,998 23,254,820 59,377,225 59,377,225 02/12/2011 11:54:54
10046 trace file bellow shows the plan before and after gathering 100% statistics.
Please note that i am gathering TWICE a day 100% statistics against the table - and still having performance problem :
When having problem tkprof shows:
SELECT distinct d.id, d.serial
FROM device_params p, device d
where d.id = p.device_id and p.value like :value
order by serial
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 42.72 278.94 2080371 2081679 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 42.73 278.94 2080371 2081679 0 3
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 112
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT UNIQUE (cr=693893 pr=693457 pw=0 time=0 us cost=198666 size=478556 card=18406)
1 HASH JOIN (cr=693893 pr=693457 pw=0 time=0 us cost=198665 size=478556 card=18406)
1 VIEW VW_DTP_E0749C0B (cr=693768 pr=693457 pw=0 time=0 us cost=198631 size=101220 card=20244)
1 HASH UNIQUE (cr=693768 pr=693457 pw=0 time=0 us cost=198631 size=182196 card=20244)
4 TABLE ACCESS FULL DEVICE_PARAMS (cr=693768 pr=693457 pw=0 time=4395 us cost=198540 size=26698041 card=2966449)
18423 INDEX FAST FULL SCAN IDX_DEVICE_ID_SER (cr=125 pr=0 pw=0 time=2942 us cost=33 size=386526 card=18406)(object id 130585)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 6 1.60 2.07
ges message buffer allocation 16 0.00 0.00
enq: KO - fast object checkpoint 6 0.01 0.03
reliable message 4 0.00 0.00
kfk: async disk IO 24771 0.00 0.07
direct path read 21122 0.64 237.69
gc cr multi block request 7 0.00 0.00
asynch descriptor resize 8 0.00 0.00
gc cr block 2-way 1 0.00 0.00
KJC: Wait for msg sends to complete 1 0.00 0.00
gc current block 2-way 1 0.00 0.00
And after gathering 100% stats AGAIN , tkprof shows:
SELECT distinct d.id, d.serial
FROM dps.device_params p, dps.device d
where d.id = p.device_id and p.value like :value
order by serial
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 2 0.01 0.01 0 132 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.05 0 132 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT UNIQUE (cr=132 pr=0 pw=0 time=0 us cost=197556 size=478556 card=18406)
1 HASH JOIN (cr=132 pr=0 pw=0 time=0 us cost=197554 size=478556 card=18406)
1 VIEW VW_DTP_E0749C0B (cr=7 pr=0 pw=0 time=0 us cost=197521 size=101310 card=20262)
1 HASH UNIQUE (cr=7 pr=0 pw=0 time=0 us cost=197521 size=182358 card=20262)
4 TABLE ACCESS BY INDEX ROWID DEVICE_PARAMS (cr=7 pr=0 pw=0 time=53 us cost=197429 size=26719704 card=2968856)
4 INDEX RANGE SCAN IDX_D_PARAM_VALUE (cr=4 pr=0 pw=0 time=8 us cost=1602 size=0 card=534394)(object id 130615)
18423 INDEX FAST FULL SCAN IDX_DEVICE_ID_SER (cr=125 pr=0 pw=0 time=5846 us cost=33 size=386526 card=18406)(object id 130585)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
ges message buffer allocation 8 0.00 0.00
library cache lock 2 0.00 0.00
library cache pin 2 0.00 0.00
rdbms ipc reply 3 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 10.19 10.19