Speeding up the gathering of incremental stats on partitioned tables

11G introduced incremental  global stats and the table WRI$_OPTSTAT_SYNOPSIS$ contains synopsis data for use in maintaining the global statistics. This table can grow very large and Robin Moffat has produced a good blog  post about  the space issues  - note we both worked at the same site so it is the same DW being discussed by both of us.

Apart from the space usage that Robin refers to, another worrying aspect is the time taken when gathering stats on a partitioned table and most of that time is taken by running a delete statement

DELETE
FROM SYS.WRI$_OPTSTAT_SYNOPSIS$
WHERE SYNOPSIS# IN
(SELECT H.SYNOPSIS#
FROM SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$ H
WHERE H.BO# = :B1
AND H.GROUP# NOT IN
(SELECT T.OBJ# * 2
FROM SYS.TABPART$ T
WHERE T.BO# = :B1
UNION ALL
SELECT T.OBJ# * 2
FROM SYS.TABCOMPART$ T
WHERE T.BO# = :B1))

I will demonstrate the problem and a simple solution and you will be able to see the significant performance improvements achieved.

My blog analysis of 2011

WordPress send out a nice email with lots of details about your blog in the last year. Apart from visits growing month on month two  points interested me. My most popular posts were all written several years ago and the ‘purging stats from SYSAUX’ is definitely my most widely read blog post. The reason for later posts not being as popular is that they are not as detailed. That is due to my change of role where I am managing a lot more and not having the time to devote a day or more to a problem and put  up a very detailed analysis. However in response to that I would suggest that I am putting a more eclectic set of posts up as I pick up on interesting problems we see on site and post about them, although not necessarily having done all the research myself. That helps me keep abreast of the technology and still be very closely linked in with what is happening, sufficiently well-informed as to be able to produce a post on the subject.

These are the posts that got the most views in 2011.

• 1 Purging statistics from the SYSAUX tablespace 15 comments May 2009
• 2 RMAN backup script – example – logging output 3 comments March 2008
• 3 ORA-19809: limit exceeded for recovery files – db_recovery_file_dest_size and archiver error 6 comments July 2008
• 4 11g – library cache mutex X – known bug 14 comments March 2008
• 5 Where has consistent=y gone when using Datapump export 3 comments December 2008

The second surprise of the WordPress summary is that I only created 17 new posts. That has really caught me out as I thought I had been more prolific than that. I will certainly try harder in 2012.

Nice notebook style blog site

It is not often that I would post just to mention someone else’s blog but there is always a first time.

In the idle time that generally happens between Xmas and New Year I was catching up with various mails that I  had marked as ‘follow-up – today’, which is quite a misnomer for me as I rarely follow-up and never on the same day. I came across a post from Karl Arao on Oracle-L in which he mentioned a  script stored on a site he runs.

Karl describes it as “Just another web notebook about Oracle,Linux,Troubleshooting,Performance,etc..etc” and that is a good summary. It just contains excerpts of notes and lots of links to other web articles. It just seems a different format to most blogs and I found it interesting to browse down the r/h menu list and look at a few interesting notelets. I suspect mots of us have something similar, mine is a Word document called vodafone_notes.do although it is 5 years and 6 sites since I worked there. The difference is that this is an online shared utility and that is what attracted it to me.

Karl no doubt will be sending me as hamper as a thank you for directing thousands of hits to his site.

My next 2 posts will cover  :-

• altering WRI$_OPTSTAT_SYNOPSIS  tables to speed up the gathering of stats
• adding a trigger to detect when a standby database is open, in which case it will  to turn off the dataguard broker.

There I have committed myself  – but note that I am using the nowait option of commit so it might be a while

Best wishes to my readers in 2012.

 

 

UKOUG 2011 – day 3

The final day of the 2011 UKOUG conference and it was straight in at the deep end with Joel Goodman talking about automatic parallelism in 11GR2. The talk was full of information, as Joel’s talks normally are. He also had time to cover Parallel Bulk Update which groups sets of rows into chunks. Each chunk can have a success or fail independently of other chunks which removes the ‘all or nothing’ approach normally seen with PDML. He has a good blog entry on this which is well worth perusing if you are interested. http://dbatrain.wordpress.com/2011/07/01/add-bulk-to-your-parallel-updates/

 My site is just going down the road with Goldengate so the talk by Pythian’s Marc Fielding on a real life Goldengate migration was very useful. This was a large financial institution where the system was crucial to business continuity and GG was to be used to provide a rapid fallback facility if things went wrong. The main thing I took away from the talk was how small-minded they must be not to provide adequate testing facilities for such a large project. Not being able to use full data sets and similar sized hardware (OK it was a 14TB database) does add a lot of risk and no small matter of frustration to the technicians involved in the migration. Some of the diagnostics that Marc talked about will be very useful to use and I was interested in the alternatives to supplementary logging which may be required if there is no primary key and it is difficult to identify a row specifically.

I did start to listen to another talk but after around 10 people had left I plucked up courage and made a hasty exit myself. It was just not for me.

The best presentation I saw at the conference was Connor Macdonald on a fresh approach to the optimizer statistics. Connor is a real showman and his easy on-stage manner belies the degree of effort he must spending preparing his numerous slides. The set of slides associated with the ITIL process deserved a round of applause by itself and indeed it received one.  This was the second session I went  to that mentioned the value of index key compression and the way it can be calculated by using ‘analyze index validate structure’. A very good presentation that provided food for thought.

My final session was Mike Swing talking about database tuning for packaged apps. He had way too much content and rushed through it much too fast. As several people said to me afterwards, all he really recommended was getting faster disk and more memory.  I liked his presentation style and easy manner but it was a bit light on useful content.

So here endeth day 3. I think this was the conference I have enjoyed the most and got the most from. The presentations were of a top standard and even though I was only interested in the RDBMS stream I had plenty of choice for most time slots. I know that cancellations and changes are hard to avoid but there did seem to be a lot and that made planning harder than it should have been. I think my only constructive critique would be that there were a number of presentations repeated from last year (and some from other SIGs almost 2 years ago). I fully understand that a good presentation is  still a good presentation a year later and not everyone has the chance to have seen it but personally  I am not in favour of too much repeated material.

 

UKOUG 2011 Part Deux

Day 2 of the UKOUG conference at the ICC in Birmingham and back into the fray.

First up was Thomas Presslie talking about Dataguard fast start failover. How he managed to demonstrate transactions and network connectivity using whisky and toilet paper could not be done full justice in a blog – it had to be seen to be believed.

It did make me want to do more with FSFO, especially noting how easy the setup was using OEM. However my belief that the database is only part of the end solution and failing that over to a second datacentre after a network flicker may leave the application stack in a mess does still concern me. Co-incidentally I have a requirement to set up a second standby configuration cascaded from a physical standby but keeping the 3^rd database perhaps one hour behind whilst the standby is in real time apply mode with no lag. That might give us a chance to determine the status of the data before a logical corruption (user error) had occurred. Much more likely to be of value is flashback query but we are going to look at both avenues. It is highly unlikely we would ever be in a position to flashback the database.

Julian Dyke then talked for an hour about RAC trouble-shooting (mostly 11.2.0.2) and the time flew past. I made quite a few notes of things to think about. The pros and cons of putting the scan addresses in /etc/hosts (HPUX) to be used in the event of a DNS failure was one thought. Looking at the exectask function and the scripts used to call various function was another action I took for myself. Another was a big list of asmcmd commands, some of which I did not recognise. I think they must have come in with 11GR2 which I have not really used myself although we are using it on site.

Tanel Poder’s biggest ever problem was next up. I had seen this presentation last year and knew the answer but how he got there was still interesting. The use of the HPUX command kitrace (similar to dtrace on Solaris – see reply below for more details) reminded me that I was going to look at that in some detail but have never got around to it. As my site is likely to be moving away from HPUX sooner rather than later perhaps there is not much point now.

After lunch John Beresniewicz was talking about ASH outliers. Quite mathematically based, which is always a challenge for me but he will be posting a script (possibly via Doug Burn’s blog) which he has developed as another means of dissecting and analysing ASH data.

Michael Salt’s talk on indexes was full of real world examples and there were lots of nice little hints and tips, none of which were earth-shattering but all of which were good practise and I found it a useful reminder of what I should be doing when looking at code. On the same theme two slots later Tony Hasler was presenting a beginners guide to SQL tuning.   I have never seen Tony present before but I really liked both his style and the content. A lot of information thrown in and good explanations of various autotrace outputs. I will definitely be downloading his presentation to run through it and see what I can put to further use. Whilst I do not think I am expert in the field of SQL tuning, indeed far from it, I do like to think I know what to look for. Sometimes listening to others you realise in the same lecture both how much you already know and how little you actually follow best practises. There is no real substitute from looking at code and trying to improve performance. For a lot of us who have a very wide-ranging DBA role then that opportunity to practise odes not appear often enough which is why it is good to review and refresh your approach now and then.

At every conference I like to try and hear something new or touch on an area that is outside my day job. John King’s talk on Edition Based Redefinition was just that. I am not really in a position to take advantage of the ability to let users run differing sets of code and then migrate them across to a new release in a seamless manner, all without any outages or interruption to service. However I could see how useful it could be, especially in the world of the Apps DBA, say for EBS. Apparently no less a person than Tom Kyte referred to EBR as the ‘killer feature’ within 11GR2.  John had an easy, comfortable manner  and the time flew past, so much so that he had to be dragged kicking and screaming from the stage by the next presenter.

All in all another good day, rounded off with a couple of beers with work colleagues and a few presenters, all with plenty of Oracle chat included.

UKOUG 2011 – Day 1

The first day of UKOUG conference at the ICC in Birmingham and there were a lot of familiar faces are around. Due to a lot of traffic I missed the first presentation I wanted to see which was from Kyle Hailey on SQL tuning. I will have to download the presentation later. I did make it to Greg Rahn on the SQL Monitoring report and that was well worth the time spent. Whilst I am familiar with the functionality, he opened my eyes by providing  a number of examples of what he would look at first to try and determine a better resolution. His presentation style was comfortable and he had a small number of examples which covered quite a lot of scenarios. He did point out it was only to be used if you had paid for the Tuning and Diagnostic pack but as it is turned on by default I did wonder how many use it without any further consideration.

Crash caused by AMM resize operations and high ‘direct file read’

 Much off the following diagnosis  was performed by a colleague Scott Chisholm. The outcome is a lot of new information (to me at least) about  the parameter _small_table_threshold but I have also developed thoughts around which set of checks we should put in place after restarts of major systems.

We start of with the executive summary for those that like to cut to the chase.

Instance 1 of a 2 node RAC cluster (11GR1 HPUX) crashed and the evidence indicates this was related to excessive AMM resize operations. The resize operations left the instance with an undersized buffer cache at instance start-up and this has steadily grown since. However, the _small_table_threshold value appears to have been set too low and does not appear to have been dynamically sized. As such the method for buffer cache or direct file read has changed and was the cause for a change in disk read activity on instance 1.  This would be exacerbated by multiple high disk IO operations such as RMAN running.

Standby databases – a few of gotchas

All of these items refer to Dataguard standby databases on 11GR1 and the  active dataguard license comment is applicable in 11GR2 as well.

LGWR: Insufficient standby redo logfiles to archive

The first is an error I see on a few databases and had assumed that it was to do with insufficient standby log files. The recommendation is that there should be at least one more than the normal redo log files so that applying the redo will not be delayed on standby, which would be critical in maximum protection mode when the primary would wait until log was applied remotely.

Wed Oct 19 11:58:13 2011
LGWR: Insufficient standby redo logfiles to archive thread 1 sequence 47505
LGWR: Standby redo logfile selected for thread 1 sequence 47505 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 47505 (LGWR switch)
Current log# 20 seq# 47505 mem# 0: +DATA/SID/onlinelog/group_20.2083.735986363
Current log# 20 seq# 47505 mem# 1: +FRA/SID/onlinelog/group_20.1668.735986365

MyOracleSupport document  798361.1 “LGWR: Insufficient standby redo logfiles to archive” Messages in Primary Database

This states:- Bug 8358103 – will be fixed in 10.2.0.5 and 11.1.0.8. “Insufficient standby redo logfiles” message is misleading as it is followed by “Standby redo logfile selected”, which indicates the standby redo logs is selected and being used. You can safely ignore these messages.  Note that 11.1.0.8 is unlikely to happen as 11.1.0.7 is the terminal release

Lag apply alert on standby database

The second is something I came across yesterday after we had performed a switchover from primary to standby. Normally we set the lag apply metric in OEM to alert us if standby gets more than 900 seconds behind). This is set in OEM for each database (unless you want to issue a modified template)

Database > Metric and Policy Settings > then first metric is Apply Lag (seconds) In order to see graph, you can go to all metrics > Data Guard Performance > Apply Lag (Seconds).

The alert is set on standby and yet when a switchover is invoked (I am assuming either by DG broker or manually as we did it, then we would expect that alert to be still in place. However it is removed and has to be re-applied.

Block change tracking on a standby requires active dataguard license

From 11GR1 onwards running a standby database with the block change tracking file enabled (which helps make incremental backups much faster) requires an active dataguard license to be in place. Whilst I think this policy is very mean spirited, especially when a license was not required in a similar situation on 10G it is a requirement and should not be ignored.

Hope these heads-up are useful

Resource Manager plans during the automatic maintenance window

Whilst ensuring that Resource manager was working properly I noticed a problem that it seemed to be dropping out of the plan I wanted to run ( DW_PLAN) and returning to the default plan whilst in the automatic maintenance windows.

The fix for that is to set the parameter resource_manager =’FORCE:DW_PLAN’ and the DW_PLAN is retained. The reason why the default plan is started off is so that  scheduler knows it has sufficient resources to get the job done and it will not be artificially constrained. If your plan does not limit the scheduler required resources then there is no harm in making your normal plan the plan for 24*7

I had noticed that  the plan was defaulting from a couple of entries in the alert log but wanted to get an exact listing of what was happening. I used the xml logs that came with the ADR package and the X$DBGALERTEXT view.

The value of UKOUG presentation feedback

One of the benefits of presenting at UKOUG, whether it be a SIG or the Conference is the feedback received from the audience via the questionnaire. I received mine for the ADR presentation I did at the Unix SIG recently. I was pleased to see I received very positive marks and the comments were supportive. Probably the most important factor to me was the marks given for presentation skills which were good and seem to get better with each presentation, which should not be too much of a surprise.

I think self-development is an important part of presenting. Yes I know the altruistic believe it should all be for the audience but I am happy to take away confidence and self-belief from my experiences. I set myself a target originally of doing a SIG presentation and then a conference presentation. My next step was to talk on a technical subject which has now been achieved and my final objective is to try and do an interactive technical demonstration using a laptop. At the moment I have no idea on what that will be about and I have no timescale so I can consider it a work in progress at the moment.