Oracle Text – 5 million rows, ADDM insight.

Now that the indices are built, Oracle Text query performance is fine with typically sub-second -> second response time observed.

As mentioned previously, at some point, I’m going to have to rebuild these and investigate this crippling index creation but not just yet.

This is a vanilla development 10gR2 install which has given me a short but sweet introduction to ADDM via OEM, as mentioned previously.

OEM homepage > Advisor Central > ADDM

The default on install has given me a week’s worth of AWR snapshots gathered at hourly intervals. Whether this will be enough for most problem resolution depends on the problem and the system. General best practice is to gather statspack snaps at a certain level every 15 minutes. What we’re looking for is for a short enough time period that the problem is significant and identifiable without imposing too large an overhead in terms of storage and performance of doing the snap itself. Jonathan Lewis advocates, in general, a level 0 snap every 15 minutes with a level 5 or greater every hour but with some caveats.

 Below are the reports from one particular one-hour period during the six day journey:

AWR Report

Tuning Task Report

One Response to Oracle Text – 5 million rows, ADDM insight.

  1. Gosse Adema says:

    1. create a pl/sql dummy datastore which gives empty strings to the text-search index. 2. index the table using the dummy datastore. This will give an index with no data.3. replace the pl/sql datastore with a real datastore. This one should return real indexing data.4. modify n-indexed (e.g. n=1000) columns and remember which columns are updated. Now update the index. And keep track of how long this takes. 5.When things slow down optimize the index and goto step 4 until all records are updated. After these steps you have a 5.000.000 rows text-index. regards Gosse Adema

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: