performance issue high CPU and DISK

From: lakshmananl <lakshmananlena_at_gmail.com>
Date: 17 Apr 2006 00:18:23 -0700
Message-ID: <1145258303.660115.242680@i39g2000cwa.googlegroups.com>

hi people,

I have the following performance issue, when i run query and look out for the statistics i get the following. which i have enclosed below. i am using oracle8.1.7 in solaris OS.

count 	= number of times OCI procedure was executed
cpu 	= cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk 	= number of physical reads of buffers from disk
query 	= number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

SELECT PRIORITYCODEID,PROGRAMEVENTID,NUM_INVITEES,ROWID FROM
PE_STATS WHERE TRIM(PROGRAMEVENTID) = TRIM(:b1)

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------

Parse 	1 	0.00 	0.01 	0 	0 	0 	0
Execute 1138 	0.29 	0.30 	0 	0 	0 	0
Fetch 	1138 	11.00 	12.25 	179 	6826 	4552 	1138

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

total 2277 11.29 12.56 179 6826 4552 1138

SELECT SUM(OPPORTUNITY.OFFERURLCLICKCOUNT) FROM
PROGRAMEVENT,OPPORTUNITY WHERE ((OPPORTUNITY.PRIORITYCODEID LIKE PROGRAMEVENT.CODE || '%' )) AND PROGRAMEVENT.CODE = :b1

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------

Parse 	1 	0.05 	0.04 	0 	0 	0 	0
Execute 1138 	0.70 	0.76 	0 	0 	0 	0
Fetch 	1138 	523.06 	917.86 	1530136 1976573 10317 	1138

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

total 2277 523.81 918.66 1530136 1976573 10317 1138

SELECT COUNT(DISTINCT OPPORTUNITY.OPPORTUNITYID) FROM
PROGRAMEVENT,OPPORTUNITY WHERE NVL(OPPORTUNITY.OFFERURLCLICKCOUNT,0) != 0
AND ((OPPORTUNITY.PRIORITYCODEID LIKE PROGRAMEVENT.CODE || '%' )) AND PROGRAMEVENT.CODE = :b1

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------

Parse 	1 	0.01 	0.01 	0 	0 	0 	0
Execute 1138 	0.70 	0.91 		0 	0	0 		0
Fetch 	1138 	344.81 	594.38 	909098 	1319705 8645 		1138

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

total 2277 345.52 595.30 909098 1319705 8645 1138

Kindly help me in the above issue.

Regards
Lakshmanan Received on Mon Apr 17 2006 - 02:18:23 CDT