1

I have a problem with data querying (Select) perfomance.

in the table Receivables we have account_id which is Indexed. When I run below script then for Account_ID=2003975183 it's OK, but for Account_ID=900025281 process is slowly.It's happend after the oracle server was unexpectedly stopped. Both accounts have nearly the same count of rows.

SQL Script for Accounts:

SELECT nvl(sum(invoice_amt),0) inv_amt
FROM ardb.receivables
WHERE
   acct_id = 2003975183
  AND  entry_date >= trunc(SYSDATE)
  AND  entry_date < trunc(SYSDATE) + 1;

According the trace data it seems like that Index is not working properly, is it?

Both traces for Fast ans Slow query attached:

FAST_QUERY_RESULT_TRACE

SLOWLY_QUERY_TRACE_RESULT

Kindly check and help to fix the problem. Thanks!

Ikrom
  • 474
  • 4
  • 10
  • Why don't you `gather stats` of the table receivables and then run your query once again and see. – XING Sep 29 '16 at 12:00
  • I forgot to tell: 1) tables data partitioned and 2) already did gather stats. No effect. By the way before the gather stat num rows of stats and partition were the same. – Ikrom Sep 29 '16 at 17:40

1 Answers1

-1

I solved the problem. After run this script and increase share pool size query working fine and in trace I can see index scan (before it was not):

ANALYZE INDEX IDX_RECEIVABLES_ACCTID VALIDATE STRUCTURE online;
Pang
  • 9,564
  • 146
  • 81
  • 122
Ikrom
  • 474
  • 4
  • 10