Upgrade performance

January 25, 2007 at 1:45 pm | Posted in Documentum and Oracle, Performance, Troubleshooting | 3 Comments

Today I was testing an upgrade from Content Server 5.2.5 sp3 to 5.2.5 sp5. All was going well until I started applying the upgrade script (headstart.ebs, etc). The process seemed to get stuck on the dd_populate script. After an hour the system was still grinding away. Time to start finding why it was taking so long.

Whilst the CPU on the machine was averaging a reasonable 20% (this was a single CPU machine), I could see from the disk lights that the disk drives were being driven very hard. As the major contributor to the 20% CPU was Oracle my initial thought was that Oracle was also responsible for the disk IO as well. So I opened Oracle Enterprise Manager, opened the Instance node and clicked on Sessions. Sorting by Physical IO and refreshing the display a couple of times quickly lead me to the problem session that was generating all the IO.

Double-clicking on the session and selecting the SQL tab showed me the SQL being run:

select count(*) from dm_relation_s where relation_name = 'DM_fk_dmr_contai_component_'

The execution plan involved a full tablescan of the dm_relation_s table. Since this system used a large number of dm_relation objects this table was quite large. Scanning it was taking several minutes at a time causing the excessive physical IO.

Still, I reasoned that this query should only take a couple of minutes rather than several hours. To make it easier to see what was going on I started sqlplus in a command prompt and executed the following query every 30 seconds:


select username, time_remaining, sql_hash_value,sql_text
from v$session_longops l,v$sql q
where l.sql_hash_value = q.hash_value
and time_remaining > 0;

The v$session_longops view contains details of long-running operations. This showed me that numerous similar queries were being run one after the other which explained why dd_populate was taking so long.

It’s pretty obvious that these queries would benefit from an index on dm_relation_s.relation_name. A quick check showed that the index did not exist so I created one in DQL:

execute make_index with type = ‘dm_relation’, attribute = ‘relation_name’
go

The MAKE_INDEX method doesn’t update the database statistics so it’s a good idea to do this now. The easiest way for just a single index is to use Oracle Enterprise Manager. Select the Schema, Open the indexes node and select the index. Right-click and select analyze. The dialog box will guide you through the process.

Once I had updated the statistics I stopped the dd_populate script and restarted it and this time it completed in minutes.

Advertisements

3 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Robin,

    Recently, I upgraded to 10.2.0.1 from 9.2.0.6. After the upgade we experienced some horrible performance problems around r_folder_path queries. The 10g optimizer was selecting to use an index for some queries and a full-table scans for the poor performing queries.

    I found a workaround from one of your posts on powerlink to set CURSOR_SHARE=FORCE. Then applied a sql profile to the query. We got the queries to perform as well or better than they did in 9.2.0.6. Thanks for the solution!

    However, my dbas have been adding indexes directly to the database(instead of dql) and have advised me to turn off my dm_updatestats job, as 10g does automatic stats gathering. Any advice as to whether gathering stats via dm_updatestats should be continued? DCTM support has not advised me either way regarding 10g tuning/operation. Thanks.

  2. You either need to run the dm_UpdateStats or the database level stats gathering but not both. on 10g I would definitely be happier with the database level stats as it will be using the more uptodate dbms_stats package rather than the older ‘analyze’ feature which is used by dm_UpdateStats.

    Each new Oracle version brings new query plan possibilities and other features and DBMS_STATS is likely to have full support for these.

  3. I’ve expanded on this answer in my post https://robineast.wordpress.com/2007/02/02/dm_updatestats-vs-dbms_stats/


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: