SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Slow flash chart loading in Apex due to SQL performance!

886626Feb 24 2012 — edited Feb 24 2012

I am very new with Oracle. I have an application with lots of flash charts which are mostly very slow to load. My table size is over 10 million and growing. I think that most of my problems come from the SQL quries that I use, but do not understand most of the techniques I find in internet to tune it. Here is one example:

select null,c.mydate label,c7.cnt "Rattle",c8.cnt "Climb",c9.cnt "Kick",c1.cnt "Footstep",c2.cnt "Vehicle",c4.cnt "Start"

from (select unique TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI') as mydate,TO_CHAR(RECORDDATE,' yyyy-mm-dd HH24:MI') as sort from SCOTT.SCDATABASE4 WHERE RECORDDATE> TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND ID = :P2_ID AND RECORDDATE< TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS') AND (CLASS in (7,8,9,1,2,4))) c
left join
(select TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI') as mydate, count(1) cnt from SCOTT.SCDATABASE4 WHERE RECORDDATE> TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND ID = :P2_ID AND RECORDDATE< TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS') AND CLASS = 7 group by TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI')) c7 on (c.mydate = c7.mydate)
left join
(select TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI') as mydate, count(1) cnt from SCOTT.SCDATABASE4 where RECORDDATE> TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND ID = :P2_ID AND RECORDDATE< TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS') AND CLASS = 8 group by TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI')) c8 on (c.mydate= c8.mydate)
left join
(select TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI') as mydate, count(1) cnt from SCOTT.SCDATABASE4 where RECORDDATE> TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND ID = :P2_ID AND RECORDDATE< TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS') AND CLASS = 9 group by TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI')) c9 on (c.mydate = c9.mydate)
left join
(select TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI') as mydate, count(1) cnt from SCOTT.SCDATABASE4 WHERE RECORDDATE> TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND ID = :P2_ID  AND RECORDDATE< TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS') AND CLASS = 1 group by TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI')) c1 on (c.mydate = c1.mydate)
left join
(select TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI') as mydate, count(1) cnt from SCOTT.SCDATABASE4 WHERE RECORDDATE> TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND ID = :P2_ID AND RECORDDATE< TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS') AND CLASS = 2 group by TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI')) c2 on (c.mydate = c2.mydate)
left join
(select TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI') as mydate, count(1) cnt from SCOTT.SCDATABASE4 WHERE RECORDDATE> TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND ID = :P2_ID AND RECORDDATE< TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS') AND CLASS = 4 group by TO_CHAR(RECORDDATE,'MM/DD/YY HH24:MI')) c4 on (c.mydate = c4.mydate)

order by c.sort

I have oracle 11g working with Apex 4.1. I used the SQL workshop's explain plan:

Operation Options Object Rows Time Cost Bytes Filter
Predicates * Access
Predicates
SELECT STATEMENT
66 703 58,573 9,966
SORT
ORDER BY 66 703 58,573 9,966
HASH JOIN
RIGHT OUTER 66 703 58,572 9,966 "C"."MYDATE" = "C1"."MYDATE"(+)
VIEW
1 101 8,358 22
HASH
GROUP BY 1 101 8,358 18
FILTER
TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS')<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
TABLE ACCESS
FULL SCDATABASE4 1 101 8,357 18 "CLASS" = 1 AND "ID" = TO_NUMBER(:P2_ID) AND "RECORDDATE">TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND "RECORDDATE"<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
HASH JOIN
OUTER 66 603 50,214 8,514 "C"."MYDATE" = "C2"."MYDATE"(+)
HASH JOIN
OUTER 66 503 41,853 7,062 "C"."MYDATE" = "C4"."MYDATE"(+)
HASH JOIN
OUTER 66 402 33,494 5,610 "C"."MYDATE" = "C9"."MYDATE"(+)
HASH JOIN
OUTER 66 302 25,135 4,158 "C"."MYDATE" = "C8"."MYDATE"(+)
HASH JOIN
OUTER 66 202 16,777 2,706 "C"."MYDATE" = "C7"."MYDATE"(+)
VIEW
66 102 8,418 1,254
HASH
UNIQUE 66 102 8,418 1,188
FILTER
TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS')<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
TABLE ACCESS
FULL SCDATABASE4 66 102 8,417 1,188 "ID" = TO_NUMBER(:P2_ID) AND ("CLASS" = 1 OR "CLASS" = 2 OR "CLASS" = 4 OR "CLASS" = 7 OR "CLASS" = 8 OR "CLASS" = 9) AND "RECORDDATE">TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND "RECORDDATE"<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
VIEW
1 101 8,358 22
HASH
GROUP BY 1 101 8,358 18
FILTER
TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS')<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
TABLE ACCESS
FULL SCDATABASE4 1 101 8,357 18 "CLASS" = 7 AND "ID" = TO_NUMBER(:P2_ID) AND "RECORDDATE">TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND "RECORDDATE"<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
VIEW
1 101 8,358 22
HASH
GROUP BY 1 101 8,358 18
FILTER
TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS')<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
TABLE ACCESS
FULL SCDATABASE4 1 101 8,357 18 "CLASS" = 8 AND "ID" = TO_NUMBER(:P2_ID) AND "RECORDDATE">TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND "RECORDDATE"<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
VIEW
1 101 8,358 22
HASH
GROUP BY 1 101 8,358 18
FILTER
TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS')<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
TABLE ACCESS
FULL SCDATABASE4 1 101 8,357 18 "CLASS" = 9 AND "ID" = TO_NUMBER(:P2_ID) AND "RECORDDATE">TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND "RECORDDATE"<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
VIEW
15 101 8,359 330
HASH
GROUP BY 15 101 8,359 270
FILTER
TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS')<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
TABLE ACCESS
FULL SCDATABASE4 15 101 8,358 270 "CLASS" = 4 AND "ID" = TO_NUMBER(:P2_ID) AND "RECORDDATE">TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND "RECORDDATE"<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
VIEW
49 101 8,361 1,078
HASH
GROUP BY 49 101 8,361 882
FILTER
TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS')<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')
TABLE ACCESS
FULL SCDATABASE4 49 101 8,360 882 "CLASS" = 2 AND "ID" = TO_NUMBER(:P2_ID) AND "RECORDDATE">TO_TIMESTAMP(:P2_DATE,'DD-MON-YYYYHH24:MI:SS') AND "RECORDDATE"<TO_TIMESTAMP(:P2_DATE2,'DD-MON-YYYYHH24:MI:SS')

From this I gather that the full database access is a bad thing, but do not know how to prevent it. I would appreciate any help and sorry for the long post!!

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 23 2012

Added on Feb 24 2012

4 comments

615 views