Posted by John Hallas on April 12, 2012
For anybody who is interested in reading about optimizer statistics and gaining a clear understanding on what they can do and how they can be managed then I suggest reading the following two white papers
Part 1 – Understanding Optimizer Statistics
Part 2 – Best Practises for Gathering Optimizer Statistics
Part 2 contains the best, most easily understood explanation of the problems with bind variable peaking and how they were addressed by using adaptive cursor sharing that I have seen.
Overall both documents are well written with good explanations and diagrams and I think anybody who has any interest in the Oracle Database engine and the tuning of databases for both consistency and performance should make these articles a must read.
Posted in 11g new features, Oracle | Tagged: adaptive cursor sharing, bind variable peaking, cbo, optimizer | Leave a Comment »
Posted by John Hallas on January 4, 2012
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
WHERE SYNOPSIS# IN
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
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. Read the rest of this entry »
Posted in 11g new features, Oracle | Tagged: incremental global stats, performance, synopsis, WRI$_OPTSTAT_SYNOPSIS | 13 Comments »
Posted by John Hallas on December 9, 2011
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.
Posted in 11g new features, Oracle, UKOUG | Tagged: 2011 UKOUG, Connor McDonald, Goldengate, Joel Goodman, Marc Fielding, Mike Swing, Parallel Bulk Update, Pythian | 1 Comment »
Posted by John Hallas on December 7, 2011
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 3rd 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 126.96.36.199) 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.
Posted in 11g new features, Oracle, UKOUG | Tagged: EBR, Edition Based Redefinition, John Beresniewicz, John King, Julian Dyke, kitrace, Michael Salt, Tanel Poder, Tony Hasler, tusc, UKOUG, UKOUG 2011 | 6 Comments »
Posted by John Hallas on December 6, 2011
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. Read the rest of this entry »
Posted in 11g new features, Oracle, UKOUG | Tagged: Alex Gorbachev, Doug Burns, Greg Rahn, Kyle Hailey, Mariusz Piorkowski, UKOUG, UKOUG conference 2011 | Leave a Comment »
Posted by John Hallas on October 19, 2011
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. Read the rest of this entry »
Posted in 11g new features, Oracle | Tagged: automatic maintenance window, dbms_scheduler.set_attribute, DEFAULT_MAINTENANCE_PLAN, Resource Manager, resource_manager =’FORCE:', X$DBGALERTEXT | Leave a Comment »
Posted by John Hallas on September 23, 2011
I was pleased with my the presentation on ADR I gave yesterday at the Unix SIG in Reading. The timing was bang on at 60 minutes and the audience had not seen or used many of the features I discussed so that was a bonus. I had the benefit of being able to give the same talk two days earlier to a team of DBAs at work and I learned a lot from the comments I received and that made for a better presentation yesterday.
One question I was asked afterwards was ‘ are Health Monitor and Support Workbench Enterprise Edition features or are they also available in Standard Edition?’. As we do not use Standard Edition I could only hazard a guess that these were tools that were advantageous to Oracle support as well as users and therefore I figured that they would be included in both editions. I have since looked at the list of features for 11GR1 and can see nothing that suggests that the features are licenseable or are different between editions. If anybody knows different then please feel free to correct me.
The presentation is available in PDF format from the UKOUG site, provided you have a member access that is.
Posted in 11g new features, Oracle, UKOUG | Tagged: ADR, adrci, Health Monitor, Support Workbench, UKOUG | 2 Comments »
Posted by John Hallas on September 16, 2011
I am presenting a talk around the use of the Automatic Diagnostic Repository at the UKOUG SIG in Reading on 22nd September 2011. I will be covering, amongst other things, the management of files, the Health Monitor, incidents and problems and the Support Workbench utility. I am hoping that, whilst everyone will probably be already aware of ADR, some of the things I mention might be new or have not been fully looked at before. The Health Monitor was new to me and the ease of use of the Support Workbench when raising an SR is certainly the way forward.
I will also be quite critical of how ADR has been delivered, particularly in respect of the management of diagnostic data and the trace and alert logs that are generated. ADR currently lacks features such as the management of the alert log which still needs external management using such commands as logrotate on unix. The standard alert log has not been replaced by an alert log in xml format (log.xml) and the old alert log is only created for backward compatibility and is not guaranteed to be available in the future. Listener logs are not purged and there are ongoing problems in removing core dumps (on HPUX at least). Overall I will be suggesting that ADR is not as automatic as it could be but some of the additional features other than file management are well worth investigating.
I look forward to meeting fellow UKOUG members and hopefully I may learn a few new things about ADR myself if I get good audience interaction.
Posted in 11g new features, Oracle, UKOUG | Tagged: ADR, adrci, Health Monitor, Support Workbench, UKOUG | Leave a Comment »
Posted by John Hallas on September 6, 2010
After applying PSU 4 on Oracle RDBMS home, we saw the below error whilst extending a datafile:
SQL>ALTER DATABASE DATAFILE '+DATA/sid/datafile/system.276.723752265' RESIZE 1524816K;
ALTER DATABASE DATAFILE '+DATA/sid/datafile/system.276.723752265' RESIZE 1524816K
ERROR at line 1:
ORA-01237: cannot extend datafile 2
ORA-01110: data file 2: '+DATA/sid/datafile/system.276.723752265'
ORA-17505: ksfdrsz:1 Failed to resize file to size 190602 blocks
ORA-15061: ASM operation not supported 
Solution. The problem is that we are using separate installations for the ASM and RDBM binaries and there is a conflict betwen the two if PSU 4 has not been applied to both sets of binaries. Therefore PSU needs to be applied to the ASM binaries as well.
Not a major issue but a trap that is easy enough to fall into.
a) Apply OPatch to have the latest version i.e. 188.8.131.52.2
b) Apply PSU 4 patch (9654987)
SQL>ALTER DATABASE DATAFILE '+DATA/sid/datafile/system.283.723752313' RESIZE 1524816K;
Metalink Id: ORA-15061 reported while doing a file operation with 11.1 or 11.2 ASM after PSU applied in database home [ID 1070880.1]
Posted in 11g new features, Oracle | Tagged: 9654987, ORA-01237, ORA-15061, PSU 4, PSU4 | 1 Comment »
Posted by John Hallas on September 2, 2010
I recently posted on the oracle-l mailing list about how to stop denial of serice attack. My message is below
We had an application that repeatedly connects to the database via java connection pool fail because the account had become locked. The application kept on trying, the database did not allow the connection and we ended up with thousands of ‘dead’ processes causing the unix server to hang as all memory was used up.
The obvious thing to fix in our case was some form of application logic to recognise that failed connections had been made and stop the repeated connection attempts.
However this could also be used in a denial of service attack. What steps could we take to reduce that risk. The problem as I see it is that the database has reacted correctly and there is not much more we could do at the database level. However I am always open to suggestions.
I received two responses, both of which were valuable. Freek DHooge suggested enabling dead connection detection by using the sqlnet.expire time setting and another mail from Grzegorz Goryszewski directing me to the 11g new feature listener connection rate feature. I set up a test to use both features and here are the results. Read the rest of this entry »
Posted in 11g new features, Oracle | Tagged: CONNECTION_RATE, dcd, dead connection detection, denil of service, ORA-28001, RATE_LIMIT=YES, sqlnet.expire_time | 7 Comments »