Home » RDBMS Server » Performance Tuning » How to decrease time to execute SUM function? (oracle10gR2 Solaris5.9)
How to decrease time to execute SUM function? [message #310640] Tue, 01 April 2008 22:15 Go to next message
wxfjordan
Messages: 92
Registered: December 2006
Member

How to decrease time to execute SUM function? Like the following:

It always cost much time to execute SUM function.

Give me some direction.



SQL> SELECT count(postedtt."ttDateTime"),
NVL(SUM(postedtt."Stake"), 0) AS "SumStake"
FROM postedtt
WHERE TO_CHAR(postedtt."ttDateTime", 'YYYY-MM') = '2008-03'

ORDER BY TO_CHAR(postedtt."ttDateTime", 'YYYY-MM-DD');

COUNT(POSTEDtt."ttDATETIME") SumStake
------------------------------------------- ----------
453803 71478561


plan:
----------------------------------------------------------
Plan hash value: 1879894972

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1462 (1)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| POSTEDtt | 7710 | 92520 | 1462 (1)| 00:00:18 |
|* 3 | INDEX RANGE SCAN | PT_IDX_J | 3084 | | 1058 (1)| 00:00:13 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access(TO_CHAR(INTERNAL_FUNCTION("ttDateTime"),'YYYY-MM')='2008-03')


message:
----------------------------------------------------------
15 recursive calls
0 db block gets
56330 consistent gets
55867 physical reads
0 redo size
514 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Re: How to decrease time to execute SUM function? [message #310665 is a reply to message #310640] Wed, 02 April 2008 00:16 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> It always cost much time to execute SUM function.

Specify "much" (in comparition with "little" without it).

Maybe you would benefit with putting "Stake" into index PT_IDX_J.

However if your statistics are correct, 7710 rows selected from "POSTEDtt" are not so much, so it should take reasonable time (even when taken through index). Is that optimizer's estimation correct? How many rows are in "POSTEDtt"? Do you frequently insert/delete data from it?
Re: How to decrease time to execute SUM function? [message #310696 is a reply to message #310640] Wed, 02 April 2008 01:53 Go to previous messageGo to next message
wxfjordan
Messages: 92
Registered: December 2006
Member
total is:

SQL> select count(2) from postedtt;

COUNT(2)
----------
797817

Executed in 0.25 seconds

[Updated on: Wed, 02 April 2008 02:48]

Report message to a moderator

Re: How to decrease time to execute SUM function? [message #310710 is a reply to message #310696] Wed, 02 April 2008 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
select count(2) from postedtt

Laughing

Re: How to decrease time to execute SUM function? [message #310712 is a reply to message #310696] Wed, 02 April 2008 02:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Check the plan for that last SQL. You will find it is performing a FAST FULL SCAN of an index.

This is much faster than the index range scan you are performing in the other query with associated table access.

As suggested by @flyboy, you could add "ttDateTime" to the PT_IDX_J index and avoid the table lookup. That would be better.

It's got nothing to do with the SUM(), except for the fact that you are summing a non-indexed column.

Ross Leishman
Re: How to decrease time to execute SUM function? [message #310715 is a reply to message #310640] Wed, 02 April 2008 03:06 Go to previous messageGo to next message
wxfjordan
Messages: 92
Registered: December 2006
Member

Oh! I have added "ttDateTime" before.

The index PT_IDX_J is:

create index PT_IDX_J
on "postedtt"(
to_char("ttDateTime", 'YYYY-MM')
);

Mr. rleishman, you are right. "FAST FULL SCAN of an index"

If I change SQL to the following. it's very fast. Just add SUM function will become very slow. Wooo...

SQL> SELECT count(postedtt."ttDateTime"),
FROM postedtt
WHERE TO_CHAR(postedtt."ttDateTime", 'YYYY-MM') = '2008-03'
ORDER BY TO_CHAR(postedtt."ttDateTime", 'YYYY-MM-DD');

COUNT(POSTEDTICKET."TICKETDATE
------------------------------
453803

Executed in 0.531 seconds
Re: How to decrease time to execute SUM function? [message #311097 is a reply to message #310715] Thu, 03 April 2008 07:05 Go to previous message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
ya , jordon i am also tired of sum() , and if u find a solution , please do let me.
at times i have used my own function as Mysum() , where possible and it has performed much better.
but its always very confusing
?? good luck
and do share your solution
Previous Topic: query tuning using explain plan
Next Topic: Which Performance hint we should use when?
Goto Forum:
  


Current Time: Tue Dec 06 04:30:26 CST 2016

Total time taken to generate the page: 0.10618 seconds