Oracle Performance and Engineering

All "portlets" (snapshot dashboard reports that do sorting) taking (initially) 30~60 sec on first execution. On immediate second execution the time comes down to low single digit figure.

We resolved the issue in two-phase improvements. Right now, most of the portlets come back within 3~5 seconds on any given execution.

We also expect the same resolution to effect *all* 10.1.0.3 customers to give a much better portlet performance and bring down the total database load on the client DB by around 20%.

Phase One – Data Skewness: Response time improved from 50-60 sec to about 20-30 sec.

We found that more than 95% of the requests belong to just one request type. In other words, requests are not evenly distributed across the request types. We deleted the column statistics on REQUEST_TYPE_ID to trick Oracle into believing that requests are evenly distributed – and thus using the index on request_type_id. This gave us relatively smaller, but very visible improvement of up to 30%-50% on many portlets. Still some of the portlets were taking up to 20 sec!

Phase Two – Oracle Bug /Configuration Issue: Response time improved from 20 sec to 0.2 sec

Observation One - We found that every time there’s a “sort” operation (ORDER BY) in a portlet – the first execution takes up to 20 sec. Immediate next execution comes down to <>

select min(bitmapped)
from
ts$ where dflmaxext =:1 and  bitand(flags,1024) =1024

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 261649 10.06 9.72 0 0 0 0
Execute 261649 8.24 7.53 0 0 0 0
Fetch 523298 68.57 68.96 0 18577079 0 261649
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1046596 86.87 86.22 0 18577079 0 261649

Observation Three – We also found that the said Oracle user uses a “Temporary Tablespace Group” (a new feature in Oracle10g) rather than a single temporary tablespace.

A temporary tablespace group consists of several temporary tablespace and the recursive query above tries to determine which of these tablespaces is best suited for a particular sort-ing (ORDER BY – present in all portlets!) operation. For some reason (probably an Oracle bug) this recursive SQL is ill-optimized and consumes a lot of resource.

Thus, we changed the user from using a “Temporary Tablespace Group” to a “Temporary Tablespace” – so that the above check does not have to occur.

posted by Nilendu  # 11:59 AM