performance trouble

[davey23uk]

Hi, Having some trouble working out why my query isnt using an index (im pretty sure it should be as well)

I have this query

UPDATE fld_detail 
    SET imsi = 1, imei = 1, imei_ts = 1,
    imei_24h = :i_imei_imsi_24h, imsi_ts = :i_imsi_imei_ts, 
    imsi_24h = :i_imsi_imei_24h, overlap_calls_ts = 
    :i_overlap_calls_ts, overlap_calls_24h = :i_overlap_calls_24h,
    overlap_calls_start_ts = :i_overlap_calls_start_ts, 
    overlap_calls_end_ts = :i_overlap_calls_end_ts, short_call_ts
    = :i_short_call_ts, short_call_1h = :i_short_call_1h, 
    gen_duration_pattern_ts = :i_gen_duration_pattern_ts, 
    gen_duration_pattern_24h = :i_gen_duration_pattern_24h, 
    mobile_duration_pattern_ts = :i_mobile_duration_pattern_ts, 
    mobile_duration_pattern_24h = :i_mobile_duration_pattern_24h,
    int_duration_pattern_ts = :i_int_duration_pattern_ts, 
    int_duration_pattern_24h = :i_int_duration_pattern_24h, 
    prs_duration_pattern_ts = :i_prs_duration_pattern_ts, 
    prs_duration_pattern_24h = :i_prs_duration_pattern_24h, 
    prepaid_credit_balance_ts = :i_prepaid_credit_balance_ts, 
    prepaid_credit_balance = nvl(prepaid_credit_balance,0) + 
    :i_pre_paid_delta_value,  prepaid_credits = 
    :i_prepaid_number_of_credits 
    WHERE account_id = :i_account_id

It is taking hours, rather than minutes.

The plan I am getting is..

Step Name                                 Step    Cost   Rows   Kbytes
Update Statement                          4       1             
  Update                                  3
   PARTITION HASH (Single)                2
    CEREBRUS.FLD_DETAIL TABLE ACCESS FULL 1       1      1      0.086

This was taken from OEM while the session was still running.

The column account_id is the primary key on that table

I am surprised the cost is 1, seeing as though there are just under 400,000 records in that table (its grows daily)

I analyzed the table doing dbms_stats.gather_table_stats('SCHEMA_NAME','FLD_DETAIL') and it did that.

Still wont use my index though - in testing it has done as works quickly, so thats why I believe the optimizer is making a bad choice.

Weird this is, if I do a simple select count(*) from fld_detail where account_id = 'xxx' it does a unique scan on the index

Any advice?

Cheers

Dave

[slimdave]

Just a "stab in the dark", but if your table is hash partitioned and your query allows partition pruning, then you need partition-level statistics gathered.

The DBMS_STATS procedure ought to have gathered both table and partition-level stats, but it'd be worth checking that the partition stats are there and that they appear reasonable.

[davey23uk]

Thanks for that,

here is the output from dba_tab_partitions

SQL> select table_name, partition_name, NUM_ROWS from dba_tab_partitions where TABLE_NAME = 'FLD_DETAIL';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
FLD_DETAIL                     SYS_P913                            20094
FLD_DETAIL                     SYS_P914                            19880
FLD_DETAIL                     SYS_P917                            20257
FLD_DETAIL                     SYS_P918                            20265
FLD_DETAIL                     SYS_P919                            20047
FLD_DETAIL                     SYS_P920                            19844
FLD_DETAIL                     SYS_P923                            19935
FLD_DETAIL                     SYS_P924                            20121
FLD_DETAIL                     SYS_P925                            19788
FLD_DETAIL                     SYS_P926                            20080
FLD_DETAIL                     SYS_P915                            19891

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
FLD_DETAIL                     SYS_P916                            19720
FLD_DETAIL                     SYS_P921                            20014
FLD_DETAIL                     SYS_P922                            19920
FLD_DETAIL                     SYS_P927                            19686
FLD_DETAIL                     SYS_P928                            19987

All looks ok to me really - confusing me :-(

Cheers

Dave

[slimdave]

Yeah, looks good.

By default the GATHER_TABLE_STATS procedure does not gather index stats ... you might like to try that also ...
[code]
begin
dbms_stats.gather_table_stats
(
ownname => 'SCHEMA_NAME',
tabname => 'FLD_DETAIL',
cascade => true
);
end;
/
[code]

If the doesn't work, try collecting column stats for the account_id.

[davey23uk]

Thanks again.

I have gathered the stats and will see what happens next time the process is run.

Cheers for the help - wil let you know what happens in about an hour :-)

Dave

[davey23uk]

Dave you are the MAN! That seemed to do the trick.

I had always been using simple analyze before (because i didnt know better)

Thanks a lot - if you ever move back to England (think you are from here) and you need a job - let me know :-)

Dave :-)

[slimdave]

* warm glow *

[davey23uk]

come back here and it will be a cold shiver - feeeeezing

[balajiyes]

cascade => true
Did this did the trick?

[slimdave]

Originally posted by davey23uk
come back here and it will be a cold shiver - feeeeezing

[whine]Can't I work remotely from Colorado?[/whine]