-
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
-
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.
-
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
-
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.
-
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
-
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 :-)
-
-
come back here and it will be a cold shiver - feeeeezing
-
cascade => true
Did this did the trick?
Cheers!
OraKid.
-
Originally posted by davey23uk
come back here and it will be a cold shiver - feeeeezing
[whine]Can't I work remotely from Colorado?[/whine]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|