DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: performance trouble

  1. #1
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334

    Angry performance trouble

    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

    Code:
    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..

    Code:
    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Thanks for that,

    here is the output from dba_tab_partitions

    Code:
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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 :-)

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    * warm glow *
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    come back here and it will be a cold shiver - feeeeezing

  9. #9
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    cascade => true
    Did this did the trick?
    Cheers!
    OraKid.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by davey23uk
    come back here and it will be a cold shiver - feeeeezing
    [whine]Can't I work remotely from Colorado?[/whine]
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width