performance trouble

Printable View

02-23-2004, 01:36 PM
davey23uk

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
02-23-2004, 02:02 PM
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.
02-23-2004, 02:21 PM
davey23uk

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
02-23-2004, 02:25 PM
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.
02-23-2004, 03:28 PM
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
02-23-2004, 06:41 PM
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 :-)
02-23-2004, 07:01 PM
slimdave

* warm glow *
02-23-2004, 07:39 PM
davey23uk

come back here and it will be a cold shiver - feeeeezing
02-24-2004, 11:49 AM
balajiyes

cascade => true
Did this did the trick?
02-24-2004, 01:21 PM
slimdave

Quote:

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

[whine]Can't I work remotely from Colorado?[/whine]
02-24-2004, 02:17 PM
tamilselvan

Slimdave,

Scoth whisky is better than Colorado wine (California wine?).

Tamil
02-24-2004, 03:31 PM
slimdave

Whisk(e)y travels, but I can't take my 250 days of sunshine a year with me. :)
02-24-2004, 04:31 PM
Mr.Hanky

If you are serious about Scotch you should join the Scotch Malt Whiskey society. The best dram out there, once you get used to cask strength unfiltered, there is no going back to shelf scotch.

http://www.smwsa.com/

MH
02-24-2004, 05:25 PM
slimdave

Quote:

Originally posted by Mr.Hanky
If you are serious about Scotch you should join the Scotch Malt Whiskey society. The best dram out there, once you get used to cask strength unfiltered, there is no going back to shelf scotch.

http://www.smwsa.com/

MH

"Scotch Malt Whiskey society"?

!
02-25-2004, 03:46 AM
DaPi

'Usquebaugh' has an 'e' in it ;)