Avoiding the Perfect Storm My First Post as a Pythian DBA, hopefully many more to come! (And if you are in the mind of finding a positive career change, the link there at the top is a good read, too! :))
-
-
This is an Apex query and it no longer exists in the environment it once resided in- When I first came across it, the first red flag was the optimizer cost of 109 billion, (yeah, I had to count the numbers a couple times in disbelief…) I was reminded of it when Chet from OracleNerd tweeted about a poorly written Apex query and I had to share. All names and identities have been changed to protect the innocent, (and more likely, the guilty…) Enjoy! UgliestQuery!
-
This Statspack bug is an older one, but I ran into it this week again. The work around is very simple to correct the problem, but its surprising how disconcerting it can be for anyone seeing “unique constraint violated”. Seen in the alert log: Tue Dec 14 09:00:43 2010 Errors in file <dir>/xxxx_j000_xxxx.trc: ORA-12012: error on auto execute of job xxx ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated ORA-06512: at “PERFSTAT.STATSPACK”, line 5264 ORA-06512: at “PERFSTAT.STATSPACK”, line 104 ORA-06512: at line 1 ***************************************************** Pulled from the trace file listed above from the alert log: <dir>/xxxx_j000_xxxx.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0…
-
Any DBA worth the time of day knows how important having solid statistics is to a database. This is how the cost based optimizer is able to make solid decisions about the best path to take when performing a table scan, choosing an index, the amount of resources required to perform a task and so on and so forth. In any database that is built, post 10g, release 2 and on, there are maintenance jobs that are created by default, one of them, to collect statistics automatically, with it’s goal to ensure that the database has the newest, most updated…
-
After a very tearful goodbye at my previous company, (even received a big ol’ hug from the “lone wolf”- after working with the guy for four years, never thought I’d see that! :)) After they had a great time at my expense- two or three days of throwing paper airplanes over the cubicle walls at me, (to which I would response with screeches of “my eye!” or “my tibia!” and a demand for workman’s comp.. :)) The unix admin sending me bogus system messages to see if I was actually still working, “exporting partition P_33497 Oracle has experienced poopy pants the…
-
My Mother used to write poems and rhymes. She had so much fun at it that we kids used to have the most elaborate rhyming clues to finding Easter treats and small toys each year, Easter started to get a little out of hand just so my Mom could practice her poetic skills, (not that we kids minded! :)) I thought I would have some fun with rhyming, but I think I’m closer to Cat in the Hat than Keats or Byron… 🙂 If there were no developers, I would be so bored, Just ask them, they say DBA’s appreciate…
-
*****After much adieu, I’ve finally returned to this post to rewrite what concerned me and befuddled others… :)***** I’ve worked with some great developers. Developers who can write code in their sleep to perform the most complex functions and aggregations, (makes me quite envious, as I need to actually think how to write code… :)) Most of these developers are still tripped up by the Oracle Cost Based Optimizer, (CBO) and honestly, I think they should be. Just how I have to think about how to code, it should be expected that they need to think about how the CBO works. …
-
A follow up to this previous post. I thought I had this one down- had followed the standard protocol when you receive a 600 error. I looked it up on MOS and had my bug number, documentation and proof of what the bug was, right? Gotta love being a DBA- the day you think you’ve figured out, something comes up to surprise you and you learn something new. ORA-00600: internal error code, arguments: [kcvhvdf_1], [], [], [], [], [], [], [] Odd version of bug 9314439: RECOVERY FAILS ON CLONED DATABASE ORA-600 [KCVHVDF_1] which is an 11.1.0.7 bug, but I…
-
I used to collect comic books when I was a kid, so superheroes are close and dear to my heart, but just not as crazy about the expectation that technical support experts be held to the expecation of infallible like Superman. I’ve both experienced and have discussed with other technical folks regarding this situation that, as of late and in all common sense, just doesn’t make any sense in the technical world. I’ve nicknamed this technical environment situation the “Superman Conundrum”. In my history I’ve *survived* three companies who’ve treated their technical environment in this manner. This is the kind of company where hardware, software, applications and processes fail on…
-
Part of this is going to be the SQL Server DBA in me ranting, so be patient and know as it says in the lovely site disclaimer, this is MY OPIONION and yes, I stick by this opinion 100%. I have very strong feelings on who and how a SQL Server, especially one with SSRS is installed. Microsoft’s continued mis-marketing, (albeit successful for sales) and ease of installation has created database environments that are misconfigured, poor-performing and poorly designed- BY DEFAULT. Few experts in .Net or SSRS, when asked, know what transaction logs, filegroups, lock escalation or sp/dbcc procedures are. …
-
As busy as I am these days, I am seeing a light at the end of the tunnel, (no, it’s not a train!) We have a new DBA training that is doing bang up job and I do believe there is some lull in the demands of our busy season. As a database administrator, I’m happiest when I have a number of demanding tasks, along with mysteries to challenge me that often, the business isn’t even aware of the level of importance it is to having resolved until I’ve implemented the resolution and they have reaped the benefits. I have two solid…
-
One of the options for cloning an environment with limited disk space involves creating a parameters file for the RMAN clone memory script to run that contains the “skip tablespace” feature. This allows the DBA to retain a working list of tablespaces that contain data that isn’t required in development/test or for other cloning purposes when performing the actual clone. startup auxiliary nomount; run { allocate auxiliary channel c1 device type disk; …so on and so forth for all the channels desired… duplicate target database to SKIP TABLESPACE USERS,TBL_DATA1,TBL_INDX1,….; } This can also be extremely helpful in environments where disk…
-
This question seems to pop into my mind consistently over the years as a DBA. I’m a “build it right or don’t build it at all” kind of DBA, but due to my gift for finding problems and fixing them, I find myself more and more often performing the second build on processes/procedures/designs, which I often would like to avoid. I went through this repeatedly at a previous shop and it’s still fresh in my mind, even today… Don’t get me wrong- I think it’s a noble cause when you first come into a new shop and it’s either been neglected or didn’t…
-
A common initial resolution to reporting requirements without a reporting database is to utilize materialized views. Unfortunately, the cost to refresh these views can be extremely expensive on a database, especially if it’s an OLTP that rarely has a lull in activity. A lesser known feature of Oracle’s is the Change Data Capture, (a.k.a CDC http://download.oracle.com/docs/cd/B10500_01/server.920/a96520/cdc.htm ) The CDC process utilizes a materizalized view log on a source table with a destination copy of the source table that can be kept insync with the original in any interval chosen. The result is often a much lighter footprint than an mview refresh,…
-
In my years as an Oracle and SQL Server DBA and the many technical environments I’ve worked in, there’s almost always one database in each place that is a hybrid of OLTP and OLAP. A highly transactional database that also has reporting requirements, but no separate reporting database. This type of database is pretty much a guaranteed challenge for any DBA. On one hand, the DBA has demands of the application demanding micro-second response time for completion of transactions. On the other, he or she has users wanting information about the data resulting from all those transactions. A DBA’s best course for successfully taming these types of hybrid environments…
-
I’ve started working on my abstract to submit for RMOUG 2011. I want to present on some of the CBO challenges I’ve taken on in the last year with large data sets. There are a number of tricks that could be very useful to others in how to manipulate the CBO to work with tables over 100G in size. Many of these tricks were requirements in areas where there have been small design flaws that “confused” the CBO and are very challenging for any DBA. I would also like to go into the steps I take to ensure that I am completely…
-
Sigh….IF I could only live in “Uncle Larry’s” perfect database world where I have more control over what choices are made from the conception of the database’s life and know exactly what goals a development group has vs. Oracle. How much easier a DBA’s life would be if we could just say, “Sorry, Oracle’s new feature would work well only if you completely redesign the logic of your process in code A, B and C of our main systems that have naturally grown in complexity over the years. Of course, you don’t mind adjusting all priorities for the next six…
-
A little off technical topic and (hopefully) in good humor, I want to talk about DBA crushes. Most techies have them, but as a female DBA, I can refer to them as “crushes” without anyone taking too much offense to the term, (hey, I’m after these guys for their technically brilliant minds, mind you!) I’m sure some folks may bristle with the term, but we do have a tendency to follow certain “super stars” in the technical world like most people follow rock stars in the real world! I’ve read DBA and Developer, consistently gush over Tom Kyte and Jonathan…