Oracle and other Microsoft are putting more and more automatic and self – everything features into their database. There are of course many reasons why that makes sense (for Oracle and Microsoft), but does it make sense for all Oracle Systems and their DBAs? I don’t think so. Consider this:
All these automatic and self-tuning features will manage resources and make decisions that can and will change the behavior of your system. Now consider that you are the DBA of a mission critical Oracle system. Do you want a system that runs good enough and stable or do you want a system that sometimes runs perfect and sometimes runs badly? Let me know.
A utility to add parent ID and execution order information to plans reported by DBMS_XPLAN. XPlan includes DISPLAY, DISPLAY_CURSOR and DISPLAY_AWR functionality for use in exactly the same way as the DBMS_XPLAN equivalents. Supports versions from 10g onwards. ***Update*** Now available in two formats: 1) as a PL/SQL package and 2) as a collection of three free-standing SQL*Plus scripts (i.e. no installation/database objects needed). January 2009 (updated October 2011)
So ... not really 30 days (21 to be precise) but it feels like forever.Late November, early December (while I was in the U.K.) several sites were installed with an updated version of one of the tools. Everything seemed to be going well, until all the databases ran out of space roughly around the same time. (We lost a team member a few months ago and he used to monitor sites until they achieved
Here is an interesting question posed to me one time and I had found a solution. After 9 years, I encountered the same question and was shocked to find that many people still don't know about a little trick that could avoid a potential problem later.
Problem
So is there a better approach? Yes, there is. In this document I am going to explain a better approach for creating an MV that makes the alterations possible without rebuilding the MV – a task accomplished in mere seconds as opposed to potentially days.
Concept of Segments
Segments are stored units in Oracle. So, a table has a segment; not a view – since the contents of the view are not stored; only the view definition is. A Materialized View, however, stores the contents; so it is a segment.
Being inherently lazy, I am always a sucker for shortcuts, neat tricks to cut my work and, most important, not to do the same thing again and again. Here is a tip I find useful.
Have you ever been frustrated to find that some line has changed some important shell variable such as ORACLE_BASE inside a shell script? The list of variables that are important to safety and efficiency of your shell is a long one - PS1, ORACLE_BASE, PATH, and so on. Using this little known command, you can easily "protect" a variable. The trick is to make it readonly. First, set the variable:
# export ORACLE_BASE=/opt/oracle
Then make it readonly:
# readonly ORACLE_BASE
Now if you want to set it:
# export ORACLE_BASE=/opt/oracle1
-bash: ORACLE_BASE: readonly variable
You can't. You can't even unset the variable:
# unset ORACLE_BASE
-bash: unset: ORACLE_BASE: cannot unset: readonly variable
This is a cool way to protect important variables.
To get a list of variables that are readonly, use
# declare -r
declare -ar BASH_VERSINFO='([0]="3" [1]="00" [2]="15" [3]="1" [4]="release" [5]="i386-redhat-linux-gnu")'
declare -ir EUID="500"
declare -rx ORACLE_BASE="/opt/oracle"
declare -ir PPID="13204"
declare -r SHELLOPTS="braceexpand:emacs:hashall:histexpand:history:interactive-comments:monitor"
The other day, we had a serious issue in the ASM diskgroups - one diskgroup refused to come up because one disk was missing; but it was not clear from the message which of the 283 devices was missing. This underscores the difficulty in diagnosing ASM discovery related issues. In this post, I have tried to present a way to diagnose this sort of issues through a real example.
We had planned to move from one storage device to another (EMC DMX-3 to DMX-4) using SRDF/A technology. The new storage was attached to a new server. The idea was to replicate data at the storage level using SRDF/A. At the end of the replication process, we shut the database and ASM down and brought up the ASM instance in the newer storage on the new server. Since the copy was disk level, the disk signatures were intact and the ASM disks retained their identity from the older storage. So, when the ASM instance was started, it recognized all the disks and mounted all the diskgroups (10 of them) except one.
While bringing up a disk group called APP_DG3 on the new server it complained that disk number “1” is missing; but it was not clear which particular disk was. In this blog the situation was diagnosed and performed.
Note: the asm disk paths changed on the storage. This was not really a problem; since we could simply define a new asm_diskstring. Remember: the diskstring initialization parameter simply tells the ASM instance which disks should be looked at while discovering. Once those disks are identified, ASM looks at its signature on the disk headers to check the properties - the disk number, the diskgroup it belongs to, the capacity, version compatibilty and so on. So as long as the correct asm_diskstring init parameter is provided, ASM can readily discover the disks and get the correct names.
Diagnosis
A variation on Jonathan Lewis's SNAP_MY_STATS package to report the resource consumption of a unit of work between two snapshots. Designed to work under constrained developer environments, this version has enhancements such as time model statistics and the option to report on specific statistics. ***Update*** Now available in two formats: 1) as a PL/SQL package and 2) as a free-standing SQL*Plus script (i.e. no installation/database objects needed). June 2007 (updated October 2011)
A variation on Tom Kyte's invaluable RUNSTATS utility that compares the resource consumption of two alternative units of work. Designed to work under constrained developer environments and builds on the original with enhancements such as "pause and resume" functionality, time model statistics and the option to report on specific statistics. ***Update*** Now available in two formats: 1) as a PL/SQL package and 2) as a free-standing SQL*Plus script (i.e. no installation/database objects needed). January 2007 (updated October 2011)
One of my customers has an Oracle based system with a large number of concurrent users. For time to time, the users would report that the system would 'grind to a halt'. Examination of AWR data showed lots of time spent waiting on latch: cache buffers chains (I discussed how to graph AWR data in Excel in a previous blog entry, which is how I produced this graph of database wait events).
--- just found this one. I wrote it in Jan 09 and didn't publish it: I think I planned on adding a lot of links and ran out of time. Publishing it under the original date, but if you've been here before, you didn't miss it the first time. I slipped it in ... oh no! I've cheated on the version control system !! :)For the past month, my work has been somewhat disconnected from the database.
Recent comments
1 year 4 weeks ago
1 year 16 weeks ago
1 year 20 weeks ago
1 year 21 weeks ago
1 year 26 weeks ago
1 year 47 weeks ago
2 years 15 weeks ago
2 years 45 weeks ago
3 years 29 weeks ago
3 years 29 weeks ago