Oracle Database Discussions

In our system, we have one oracle database in LINUX server.
The application team told me that the system working slowly.
I want to find out whether is the hardware problem or system problem or database problem.

I have made one awrrpt report for the system in order to check whether the database has any problem.
As this forums do not support attachment. I only can paste the reuslt below:

WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
ORCL 1237865624 orcl 1 10.2.0.1.0 NO newdb

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 17425 05-Feb-12 00:00:27 232 5.8
End Snap: 17448 05-Feb-12 23:00:58 232 5.5
Elapsed: 1,380.51 (mins)
DB Time: 6,686.41 (mins)

Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 832M 832M Std Block Size: 8K
Shared Pool Size: 240M 240M Log Buffer: 14,408K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 4,181.03 1,866.57
Logical reads: 19,593.42 8,747.23
Block changes: 21.05 9.40
Physical reads: 13,033.86 5,818.80
Physical writes: 1.91 0.85
User calls: 7.07 3.16
Parses: 5.03 2.25
Hard parses: 0.81 0.36
Sorts: 0.67 0.30
Logons: 0.03 0.01
Executes: 6.02 2.69
Transactions: 2.24

% Blocks changed per Read: 0.11 Recursive Call %: 82.24
Rollback per transaction %: 35.48 Rows per Sort: 13.35

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 97.67 Redo NoWait %: 99.99
Buffer Hit %: 33.48 In-memory Sort %: 100.00
Library Hit %: 90.49 Soft Parse %: 83.89
Execute to Parse %: 16.39 Latch Hit %: 99.77
Parse CPU to Parse Elapsd %: 97.38 % Non-Parse CPU: 99.33

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 76.41 77.82
% SQL with executions>1: 74.20 88.40
% Memory for SQL w/exec>1: 82.64 74.27

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
log file sync 328,564 160,658 489 40.0 Commit
read by other session 37,877,168 120,352 3 30.0 User I/O
db file scattered read 68,175,024 92,572 1 23.1 User I/O
log file parallel write 192,059 44,704 233 11.1 System I/O
CPU time 27,145 6.8
-------------------------------------------------------------
Time Model Statistics DB/Inst: ORCL/orcl Snaps: 17425-17448
-> Total time in database user-calls (DB Time): 401184.9s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name

Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 242,157.8 60.4
DB CPU 27,145.3 6.8
RMAN cpu time (backup/restore) 646.1 .2
PL/SQL execution elapsed time 407.7 .1
parse time elapsed 192.9 .0
sequence load elapsed time 136.1 .0
hard parse elapsed time 60.7 .0
connection management call elapsed time 10.5 .0
inbound PL/SQL rpc elapsed time 4.5 .0
PL/SQL compilation elapsed time 3.5 .0
hard parse (sharing criteria) elapsed time 2.5 .0
hard parse (bind mismatch) elapsed time 2.1 .0
repeated bind elapsed time 0.0 .0
DB time 401,184.9 N/A
background elapsed time 88,569.8 N/A
background cpu time 772.6 N/A
-------------------------------------------------------------

Wait Class DB/Inst: ORCL/orcl Snaps: 17425-17448
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc

Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
User I/O 106,199,793 .0 216,626 2 572.4
Commit 328,564 30.4 160,658 489 1.8
System I/O 762,317 .0 67,098 88 4.1
Concurrency 53,623 8.7 5,202 97 0.3
Other 14,142 1.0 276 19 0.1
Configuration 98 61.2 72 731 0.0
Administrative 2 .0 1 309 0.0
Network 417,545 .0 1 0 2.3
Application 58 .0 0 0 0.0
-------------------------------------------------------------

Wait Events DB/Inst: ORCL/orcl Snaps: 17425-17448
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
log file sync 328,564 30.4 160,658 489 1.8
read by other session 37,877,168 .0 120,352 3 204.1
db file scattered read 68,175,024 .0 92,572 1 367.4
log file parallel write 192,059 .0 44,704 233 1.0
RMAN backup & recovery I/O 410,551 .0 16,661 41 2.2
os thread startup 5,581 82.9 5,018 899 0.0
control file parallel write 26,748 .0 4,607 172 0.1
db file sequential read 141,293 .0 3,703 26 0.8
control file sequential read 131,831 .0 1,100 8 0.7
enq: CF - contention 82 37.8 135 1651 0.0
enq: TX - index contention 230 .0 83 360 0.0
log file switch completion 92 65.2 72 779 0.0
enq: TC - contention 27 77.8 71 2627 0.0
latch free 12,648 .0 56 4 0.1
row cache lock 383 2.3 52 136 0.0
buffer busy waits 2,944 .5 40 14 0.0
log file sequential read 553 .0 19 35 0.0
enq: PR - contention 7 42.9 12 1697 0.0
latch: cache buffers chains 43,800 .0 8 0 0.2
log file single write 54 .0 3 64 0.0
Log archive I/O 477 .0 3 5 0.0
switch logfile command 2 .0 1 309 0.0
SQL*Net message to client 417,446 .0 1 0 2.2
buffer exterminate 1 .0 0 457 0.0
latch: shared pool 196 .0 0 2 0.0
latch: messages 3 .0 0 133 0.0
recovery area: computing obs 1 .0 0 206 0.0
enq: CI - contention 4 .0 0 46 0.0
latch: row cache objects 7 .0 0 20 0.0
control file single write 44 .0 0 2 0.0
db file single write 8 .0 0 9 0.0
LGWR wait for redo copy 178 1.7 0 0 0.0
latch: library cache 158 .0 0 0 0.0
latch: cache buffers lru cha 886 .0 0 0 0.0
latch: In memory undo latch 323 .0 0 0 0.0
direct path write temp 3,023 .0 0 0 0.0
latch: object queue header o 192 .0 0 0 0.0
direct path read temp 3,041 .0 0 0 0.0
recovery area: computing ide 1 .0 0 8 0.0
buffer deadlock 81 96.3 0 0 0.0
SQL*Net break/reset to clien 58 .0 0 0 0.0
latch: session allocation 8 .0 0 0 0.0
latch: redo writing 6 .0 0 0 0.0
latch: enqueue hash chains 4 .0 0 0 0.0
SQL*Net more data from clien 97 .0 0 0 0.0
rdbms ipc reply 14 .0 0 0 0.0
recovery area: computing dro 1 .0 0 0 0.0
direct path read 72 .0 0 0 0.0
recovery area: computing bac 1 .0 0 0 0.0
latch: redo allocation 2 .0 0 0 0.0
direct path write 164 .0 0 0 0.0
recovery area: computing app 1 .0 0 0 0.0
latch: library cache pin 1 .0 0 0 0.0
SQL*Net more data to client 2 .0 0 0 0.0
SQL*Net message from client 417,449 .0 620,367 1486 2.2
virtual circuit status 2,761 100.0 80,645 29209 0.0
Streams AQ: qmn slave idle w 5,865 .0 80,532 13731 0.0
Streams AQ: qmn coordinator 8,437 36.7 80,531 9545 0.0
jobq slave wait 23,972 98.8 69,877 2915 0.1
Streams AQ: waiting for time 20 100.0 56,501 ####### 0.0
Wait Events DB/Inst: ORCL/orcl Snaps: 17425-17448
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
class slave wait 31 100.0 151 4884 0.0
SGA: MMAN sleep for componen 129 97.7 1 11 0.0
-------------------------------------------------------------

Background Wait Events DB/Inst: ORCL/orcl Snaps: 17425-17448
-> ordered by wait time desc, waits desc (idle events last)

Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
log file parallel write 192,058 .0 44,703 233 1.0
os thread startup 5,581 82.9 5,018 899 0.0
control file parallel write 26,503 .0 4,594 173 0.1
control file sequential read 34,291 .0 1,083 32 0.2
events in waitclass Other 1,494 2.4 140 94 0.0
db file sequential read 4,794 .0 33 7 0.0
log file sequential read 497 .0 18 36 0.0
log file single write 54 .0 3 64 0.0
Log archive I/O 428 .0 2 5 0.0
latch: shared pool 4 .0 0 25 0.0
db file scattered read 1 .0 0 41 0.0
latch: redo writing 6 .0 0 0 0.0
latch: cache buffers chains 28 .0 0 0 0.0
latch: library cache 1 .0 0 0 0.0
direct path read 72 .0 0 0 0.0
direct path write 72 .0 0 0 0.0
rdbms ipc message 396,537 62.0 1,420,291 3582 2.1
pmon timer 27,814 100.0 80,850 2907 0.1
Streams AQ: qmn slave idle w 5,865 .0 80,532 13731 0.0
Streams AQ: qmn coordinator 8,437 36.7 80,531 9545 0.0
smon timer 320 76.3 78,344 244826 0.0
Streams AQ: waiting for time 20 100.0 56,501 ####### 0.0
SGA: MMAN sleep for componen 129 97.7 1 11 0.0
-------------------------------------------------------------

Operating System Statistics DB/Inst: ORCL/orcl Snaps: 17425-17448

Statistic Total
-------------------------------- --------------------
BUSY_TIME 3,586,938
IDLE_TIME 53,252,868
NICE_TIME 1,310
SYS_TIME 1,291,393
USER_TIME 2,294,235
LOAD 4
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 17,660
NUM_CPUS 8
NUM_CPU_SOCKETS 2
-------------------------------------------------------------

Service Statistics DB/Inst: ORCL/orcl Snaps: 17425-17448
-> ordered by DB Time

Physical Logical
Service Name DB Time (s) DB CPU (s) Reads Reads
-------------------------------- ------------ ------------ ---------- ----------
SYS$USERS 401,168.9 27,148.1 ########## ##########
orcl 27.2 1.6 4,935 26,840
SYS$BACKGROUND 0.0 0.0 6,541 887,038
orclXDB 0.0 0.0 0 0
-------------------------------------------------------------

Service Wait Class Stats DB/Inst: ORCL/orcl Snaps: 17425-17448
-> Wait Class info for services in the Service Statistics section.
-> Total Waits and Time Waited displayed for the following wait
classes: User I/O, Concurrency, Administrative, Network
-> Time Waited (Wt Time) in centisecond (100th of a second)

Service Name
----------------------------------------------------------------
User I/O User I/O Concurcy Concurcy Admin Admin Network Network
Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time
--------- --------- --------- --------- --------- --------- --------- ---------
SYS$USERS
######### 21654394 47998 18359 2 61 417381 59
orcl
366 769 11 0 0 0 118 0
SYS$BACKGROUND
6876 7375 5614 501850 0 0 0 0
-------------------------------------------------------------

SQL ordered by Elapsed Time DB/Inst: ORCL/orcl Snaps: 17425-17448
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100

Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
5,830 46 1 5830.1 1.5 42xny8ccuncy9
SELECT SITE_ID,SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 END) AS SINGLE_PAGE_VIS
ITS,SUM(CASE WHEN TOTAL_PV = 2 THEN 1 ELSE 0 END) AS TOW_PAGE_VISITS,SUM(CASE W
HEN TOTAL_PV = 3 THEN 1 ELSE 0 END) AS THREE_PAGE_VISITS,SUM(CASE WHEN TOTAL_P
V = 4 THEN 1 ELSE 0 END) AS FOUR_PAGE_VISITS,SUM(CASE WHEN TOTAL_PV = 5 THEN

5,750 38 1 5750.1 1.4 djs5r152pjfb9
Select SITE_ID,SEARCH_KEYWORD AS KEYWORD ,SUM(TOTAL_PV) AS TOTAL_PV ,SUM(TOTAL_V
ISITS) AS TOTAL_VISITS, SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 END) AS SINGLE_
PV_VISITS, SUM(CASE WHEN NEW_MEMBER = '1' THEN 1 ELSE 0 END) AS NEW_MEMBER, SUM(
TOTAL_SECONDS) AS TOTAL_SECONDS FROM VIEW_ANALYSE_SOURCE WHERE TO_CHAR(CREATE_

5,635 36 0 N/A 1.4 8k2dprjnhbr71
SELECT SUM(CASE WHEN COUNT(DISTINCT SESSION_ID) = 1 THEN 1 ELSE 0 END) AS VISIT
OR_LT_1_VISITS,SUM(CASE WHEN COUNT(DISTINCT SESSION_ID) = 2 THEN 1 ELSE 0 END) A
S VISITOR_LT_2_VISITS,SUM(CASE WHEN COUNT(DISTINCT SESSION_ID) = 3 THEN 1 ELSE 0
END) AS VISITOR_LT_3_VISITS,SUM(CASE WHEN COUNT(DISTINCT SESSION_ID) = 4 THEN 1

5,613 65 2 2806.4 1.4 85kxcd8y29kxy
SELECT SITE_ID,SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 END) AS SINGLE_PV_VISITS
,COUNT(DISTINCT CASE WHEN TOTAL_PV = 1 THEN MEMBER_ID ELSE '' END) AS SINGLE_PV_
VISITOR, SUM(TOTAL_VISITS) AS TOTAL_VISITS,COUNT(DISTINCT MEMBER_ID) AS TOTAL_VI
SITOR FROM VIEW_ANALYSE_SOURCE WHERE TO_CHAR(CREATE_TIME,'yyyy-MM-dd') = '2011-0

3,756 23 47,480 0.1 0.9 30u97cfzsa5y1
insert into ANALYSE_SOURCE (SESSION_ID, SCREEN_RES, REFERRER_DOMAIN, TRAFFIC_SOU
RCE, USER_AGENT, SITE_ID, MEMBER_ID, IP, PAGE_NAME, PAGE_URL, PAGE_TITLE, REFERR
ER, COLOR_DEPTH, JAVA_ENABLE, FLASH_VERSION, BROWSER_LANGUAGE, CONTENT_CHARSET,
HOST_NAME, WEBSITE_NAME, DOMAIN_HASH, NEW_MEMBER, CREATE_TIME, CREATE_USER, OPER

3,494 73 2 1746.8 0.9 ghzx2h66fht5m
SELECT SITE_ID,SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 END) AS SINGLE_PAGE_VIS
ITS,SUM(CASE WHEN TOTAL_PV = 2 THEN 1 ELSE 0 END) AS TOW_PAGE_VISITS,SUM(CASE W
HEN TOTAL_PV = 3 THEN 1 ELSE 0 END) AS THREE_PAGE_VISITS,SUM(CASE WHEN TOTAL_P
V = 4 THEN 1 ELSE 0 END) AS FOUR_PAGE_VISITS,SUM(CASE WHEN TOTAL_PV = 5 THEN

2,576 77 2 1288.1 0.6 58bhnamq75z70
SELECT SITE_ID,SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 END) AS SINGLE_PV_VISITS
,COUNT(DISTINCT CASE WHEN TOTAL_PV = 1 THEN MEMBER_ID ELSE '' END) AS SINGLE_PV_
VISITOR, SUM(TOTAL_VISITS) AS TOTAL_VISITS,COUNT(DISTINCT MEMBER_ID) AS TOTAL_VI
SITOR FROM VIEW_ANALYSE_SOURCE WHERE TO_CHAR(CREATE_TIME,'yyyy-MM-dd') = '2012-0

2,368 76 2 1184.0 0.6 fdr9zqrft0b06
SELECT SITE_ID,BROWSER,COUNT(*) AS TOTAL_VISITS,COUNT(DISTINCT MEMBER_ID) AS TOT
AL_VISITOR,SUM(TOTAL_PV) AS TOTAL_PV,SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 EN
D ) AS SINGLE_PV,SUM(TOTAL_SECONDS) AS TOTAL_SECONDS FROM VIEW_ANALYSE_SOURCE WH
ERE TO_CHAR(CREATE_TIME,'yyyy-MM-dd') = '2012-01-04' GROUP BY SITE_ID,BROWSER

2,262 69 2 1131.0 0.6 3qdq7g3qv7byf
SELECT SITE_ID,SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 END) AS SINGLE_PV_VISITS
,COUNT(DISTINCT CASE WHEN TOTAL_PV = 1 THEN MEMBER_ID ELSE '' END) AS SINGLE_PV_
VISITOR, SUM(TOTAL_VISITS) AS TOTAL_VISITS,COUNT(DISTINCT MEMBER_ID) AS TOTAL_VI
SITOR FROM VIEW_ANALYSE_SOURCE WHERE TO_CHAR(CREATE_TIME,'yyyy-MM-dd') = '2011-0

2,173 75 2 1086.3 0.5 c16amgbhvryrb
SELECT SITE_ID,BROWSER,COUNT(*) AS TOTAL_VISITS,COUNT(DISTINCT MEMBER_ID) AS TOT
AL_VISITOR,SUM(TOTAL_PV) AS TOTAL_PV,SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 EN
D ) AS SINGLE_PV,SUM(TOTAL_SECONDS) AS TOTAL_SECONDS FROM VIEW_ANALYSE_SOURCE WH
ERE TO_CHAR(CREATE_TIME,'yyyy-MM-dd') = '2011-05-31' GROUP BY SITE_ID,BROWSER

-------------------------------------------------------------

SQL ordered by CPU Time DB/Inst: ORCL/orcl Snaps: 17425-17448
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100

CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ----------- ------- -------------
96 1,269 2 48.15 0.3 3tqh2tdd3qv7n
SELECT SITE_ID,BROWSER,COUNT(*) AS TOTAL_VISITS,COUNT(DISTINCT MEMBER_ID) AS TOT
AL_VISITOR,SUM(TOTAL_PV) AS TOTAL_PV,SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 EN
D ) AS SINGLE_PV,SUM(TOTAL_SECONDS) AS TOTAL_SECONDS FROM VIEW_ANALYSE_SOURCE WH
ERE TO_CHAR(CREATE_TIME,'yyyy-MM-dd') = '2011-12-30' GROUP BY SITE_ID,BROWSER

95 192 2 47.33 0.0 99m0cz50c85v6
SELECT SITE_ID,SUM(CASE WHEN NEW_MEMBER = '1' THEN TOTAL_PV ELSE 0 END) AS NEW_
TOTAL_PV,SUM(TOTAL_PV) AS TOTAL_PV,SUM(CASE WHEN NEW_MEMBER = '1' THEN 1 ELSE 0
END) AS NEW_TOTAL_VISITS,COUNT(*) AS TOTAL_VISITS,SUM(CASE WHEN NEW_MEMBER = '1
' THEN TOTAL_SECONDS ELSE 0 END) AS NEW_TOTAL_SECONDS,SUM(TOTAL_SECONDS) AS TOT

95 194 2 47.27 0.0 9ry63qpmjgvz5
SELECT SITE_ID,SUM(CASE WHEN NEW_MEMBER = '1' THEN TOTAL_PV ELSE 0 END) AS NEW_
TOTAL_PV,SUM(TOTAL_PV) AS TOTAL_PV,SUM(CASE WHEN NEW_MEMBER = '1' THEN 1 ELSE 0
END) AS NEW_TOTAL_VISITS,COUNT(*) AS TOTAL_VISITS,SUM(CASE WHEN NEW_MEMBER = '1
' THEN TOTAL_SECONDS ELSE 0 END) AS NEW_TOTAL_SECONDS,SUM(TOTAL_SECONDS) AS TOT

94 195 2 47.25 0.0 dccuap6h1vytk
SELECT SITE_ID,SUM(CASE WHEN NEW_MEMBER = '1' THEN TOTAL_PV ELSE 0 END) AS NEW_
TOTAL_PV,SUM(TOTAL_PV) AS TOTAL_PV,SUM(CASE WHEN NEW_MEMBER = '1' THEN 1 ELSE 0
END) AS NEW_TOTAL_VISITS,COUNT(*) AS TOTAL_VISITS,SUM(CASE WHEN NEW_MEMBER = '1
' THEN TOTAL_SECONDS ELSE 0 END) AS NEW_TOTAL_SECONDS,SUM(TOTAL_SECONDS) AS TOT

94 190 2 47.05 0.0 faaqutn37w1uq
SELECT SITE_ID,SUM(CASE WHEN NEW_MEMBER = '1' THEN TOTAL_PV ELSE 0 END) AS NEW_
TOTAL_PV,SUM(TOTAL_PV) AS TOTAL_PV,SUM(CASE WHEN NEW_MEMBER = '1' THEN 1 ELSE 0
END) AS NEW_TOTAL_VISITS,COUNT(*) AS TOTAL_VISITS,SUM(CASE WHEN NEW_MEMBER = '1
' THEN TOTAL_SECONDS ELSE 0 END) AS NEW_TOTAL_SECONDS,SUM(TOTAL_SECONDS) AS TOT

92 316 2 46.17 0.1 4s0776w52uyxb
SELECT SITE_ID,BROWSER,COUNT(*) AS TOTAL_VISITS,COUNT(DISTINCT MEMBER_ID) AS TOT
AL_VISITOR,SUM(TOTAL_PV) AS TOTAL_PV,SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 EN
D ) AS SINGLE_PV,SUM(TOTAL_SECONDS) AS TOTAL_SECONDS FROM VIEW_ANALYSE_SOURCE WH
ERE TO_CHAR(CREATE_TIME,'yyyy-MM-dd') = '2011-12-08' GROUP BY SITE_ID,BROWSER

78 715 2 38.87 0.2 aykvzugtd4h10
SELECT SITE_ID,SUM(CASE WHEN NEW_MEMBER = '1' THEN TOTAL_PV ELSE 0 END) AS NEW_
TOTAL_PV,SUM(TOTAL_PV) AS TOTAL_PV,SUM(CASE WHEN NEW_MEMBER = '1' THEN 1 ELSE 0
END) AS NEW_TOTAL_VISITS,COUNT(*) AS TOTAL_VISITS,SUM(CASE WHEN NEW_MEMBER = '1
' THEN TOTAL_SECONDS ELSE 0 END) AS NEW_TOTAL_SECONDS,SUM(TOTAL_SECONDS) AS TOT

77 2,576 2 38.33 0.6 58bhnamq75z70
SELECT SITE_ID,SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 END) AS SINGLE_PV_VISITS
,COUNT(DISTINCT CASE WHEN TOTAL_PV = 1 THEN MEMBER_ID ELSE '' END) AS SINGLE_PV_
VISITOR, SUM(TOTAL_VISITS) AS TOTAL_VISITS,COUNT(DISTINCT MEMBER_ID) AS TOTAL_VI
SITOR FROM VIEW_ANALYSE_SOURCE WHERE TO_CHAR(CREATE_TIME,'yyyy-MM-dd') = '2012-0

77 1,495 2 38.31 0.4 878knrfjxsxy3
SELECT SITE_ID,SUM(CASE WHEN NEW_MEMBER = '1' THEN TOTAL_PV ELSE 0 END) AS NEW_
TOTAL_PV,SUM(TOTAL_PV) AS TOTAL_PV,SUM(CASE WHEN NEW_MEMBER = '1' THEN 1 ELSE 0
END) AS NEW_TOTAL_VISITS,COUNT(*) AS TOTAL_VISITS,SUM(CASE WHEN NEW_MEMBER = '1
' THEN TOTAL_SECONDS ELSE 0 END) AS NEW_TOTAL_SECONDS,SUM(TOTAL_SECONDS) AS TOT

76 325 2 38.20 0.1 9yrtcj3r4rf25
SELECT SITE_ID,SUM(CASE WHEN NEW_MEMBER = '1' THEN TOTAL_PV ELSE 0 END) AS NEW_
TOTAL_PV,SUM(TOTAL_PV) AS TOTAL_PV,SUM(CASE WHEN NEW_MEMBER = '1' THEN 1 ELSE 0
END) AS NEW_TOTAL_VISITS,COUNT(*) AS TOTAL_VISITS,SUM(CASE WHEN NEW_MEMBER = '1
' THEN TOTAL_SECONDS ELSE 0 END) AS NEW_TOTAL_SECONDS,SUM(TOTAL_SECONDS) AS TOT

SQL ordered by CPU Time DB/Inst: ORCL/orcl Snaps: 17425-17448
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100

CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ----------- ------- -------------
65 5,613 2 32.27 1.4 85kxcd8y29kxy
SELECT SITE_ID,SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 END) AS SINGLE_PV_VISITS
,COUNT(DISTINCT CASE WHEN TOTAL_PV = 1 THEN MEMBER_ID ELSE '' END) AS SINGLE_PV_
VISITOR, SUM(TOTAL_VISITS) AS TOTAL_VISITS,COUNT(DISTINCT MEMBER_ID) AS TOTAL_VI
SITOR FROM VIEW_ANALYSE_SOURCE WHERE TO_CHAR(CREATE_TIME,'yyyy-MM-dd') = '2011-0

46 5,830 1 45.57 1.5 42xny8ccuncy9
SELECT SITE_ID,SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 END) AS SINGLE_PAGE_VIS
ITS,SUM(CASE WHEN TOTAL_PV = 2 THEN 1 ELSE 0 END) AS TOW_PAGE_VISITS,SUM(CASE W
HEN TOTAL_PV = 3 THEN 1 ELSE 0 END) AS THREE_PAGE_VISITS,SUM(CASE WHEN TOTAL_P
V = 4 THEN 1 ELSE 0 END) AS FOUR_PAGE_VISITS,SUM(CASE WHEN TOTAL_PV = 5 THEN

38 5,750 1 38.31 1.4 djs5r152pjfb9
Select SITE_ID,SEARCH_KEYWORD AS KEYWORD ,SUM(TOTAL_PV) AS TOTAL_PV ,SUM(TOTAL_V
ISITS) AS TOTAL_VISITS, SUM(CASE WHEN TOTAL_PV = 1 THEN 1 ELSE 0 END) AS SINGLE_
PV_VISITS, SUM(CASE WHEN NEW_MEMBER = '1' THEN 1 ELSE 0 END) AS NEW_MEMBER, SUM(
TOTAL_SECONDS) AS TOTAL_SECONDS FROM VIEW_ANALYSE_SOURCE WHERE TO_CHAR(CREATE_

36 5,635 0 N/A 1.4 8k2dprjnhbr71
SELECT SUM(CASE WHEN COUNT(DISTINCT SESSION_ID) = 1 THEN 1 ELSE 0 END) AS VISIT
OR_LT_1_VISITS,SUM(CASE WHEN COUNT(DISTINCT SESSION_ID) = 2 THEN 1 ELSE 0 END) A
S VISITOR_LT_2_VISITS,SUM(CASE WHEN COUNT(DISTINCT SESSION_ID) = 3 THEN 1 ELSE 0
END) AS VISITOR_LT_3_VISITS,SUM(CASE WHEN COUNT(DISTINCT SESSION_ID) = 4 THEN 1