Re: performance issue high CPU and DISK

On 17 Apr 2006 00:18:23 -0700, "lakshmananl" <lakshmananlena_at_gmail.com> wrote:

>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

No explain plans included, no information about indexes. In at least two statements (the first one and the last one) you are probably conducting full table scans. Also you don't use array fetch, so you have high consistent gets, and are performing a sqlnet roundtrip for every row.
More information however is needed to determine the root cause.

--
Sybrand Bakker, Senior Oracle DBA