Users complained that a monthly financial report would no longer run SQLServer Reporting Services, (SSRS.) Upon investigation, it was found that this was a stored procedure that ran from one Annex database, sourcing from another and outer joins to a SQLServer database on a remote server through a linked server configuration. In attempts to run the report, my SQL Server Profile traces on both the source SQL Server and the remote SQLServer resulted in consistent sp_reset_connection results from the source and no activity in the remote. I ran my trusty and favorite script to tell me what processes were…
-
-
“Oh what tangled web we weave when complexity is added we can’t see…” How many times has complexity of design in an application, outside of the database, lead to the database blamed for slow performance? This is where a manager thanks the technical Gods for a DBA with great Sherlock Holmes skills to track down and prove the database not only innocent, but figure out what the real problem is. The database is guilty until proven innocent, we all know that, so when user’s come to the DBA and demands, “Why can’t I run this report? I should be able…
-
If you desire a subject that will invoke deep passion and often combined with disgust from a group of DBA’s, disaster recovery is the one. It is the subject that rarely we feel our butts are not out there hanging, no matter how much we’ve attempted to secure our environment. I’ve observed a consistent flow of articles, conversation and email discussions on the subject and it is apparent that rarely is the business as aware as the technical specialists, (aka the DBA) of just how vulnerable their environments are. Rarely are the budget dollars allotted to the task of insuring…
-
I truly believe that database myths are the scourge of the database world. With that said, I have to blame myself for not being specific enough in one of my own blog posts and propagating one myself! As it was brought to my attention by Tanel Poder and Greg Rahn, I hadn’t put a post out here until now, so apologies! I have flipped back to an almost exclusively 10g environment the last three months, but even though I’m back to my old stomping grounds, now fully staffed with almost double the DBA’s that were present when I left, have…
-
This is another blog post about how TEMP can kill you in performance. The Program Global Area, aka PGA, is a memory region that allows Oracle to perform many processes that once fell to static calculations in parameters that had to be managed by a DBA. As frustrating as it might be to a DBA to not have enough memory to allocate to the performance enhancing feature, I found it even more frustrating to find NO PGA allocation to window sorts. We were experiencing poor performance in one of our environments, but I noted it was only during heavy workloads,…
-
The introduction of the temp tablespace was a crucial step in the RDBMS to separating the “work area” for sorting and specific join operations from permanent segments. To eliminate the performance challenges of sorts and hash against the I/O threshold involved with performing this word in the temp tablespace, Oracle 9i introduced the Program Global Area, (PGA) to complete sorts and hashes within memory. DBA’s spent considerable time tuning the PGA, attempting to continually ensure that as more complex sorting and join features emerged, that these processes would complete their work within the PGA and not require such a large…
-
A blog post by Simon Cooper sent up a reminder of a subject that has been at the forefront of my mind for the last couple weeks and is a follow up to my blog post The Superman Conundrum. I’d had lunch with a previous coworker from years ago about less than stellar management behavior directed towards him as an employee and also spoke last week to another previous co-worker about challenges in her current workplace. I’m pretty content with how I am treated at my company. Even though the company does not comprise of techies, they at least attempt to…
-
This is when I know I’ve been doing too much database work in one day. I awoke from a dream, quite early in the morning and was replaying it in my head. The scenario went like this: Scene: City streets, obviously a murder scene with yellow tape drawn and bystanders watching off to the sides as the main characters, two seasoned detectives are discussing the decision to enter the crime scene. Officer #1: “Are we sure we covered our bases? Is there anyway the criminal could have escaped the scene or we could have compromised the evidence when we entered…
-
I’ve always had a *thing* for trace files. They serve me well- I like them and they seem to really like me. I have a special affinity for deadlock trace files and someone needs to remind me to pester Cary Millsap about a tool to decipher them so I have even more data to go through…:) Even without any tools, I can easily pull out what is important and work from this data to run queries and reports to drill down to find work-arounds when changes to logic or design are not an option. Deadlocks, although quite unpleasant, I find…
-
The SMON spit out this error after the cycle of the db on the rollback of the huge insert into the largest table in one of our warehouse environments. I had serious concerns about a poor execution plan and wondered what we were going to be in for on the rollback and with good reason. The source to the performance hit was a combination of missing column statistics on the main table used for the joins from the staging table, but also fragmentation caused by a poor design choice, (yes, get exchange partition in there ASAP!) Here is the issue w/ fix with the…
-
Like Chet, from OracleNerd, I received my official Blogger Approval for Oracle Open World yesterday! This will be my first attendance, as something always came up before that kept me from going, so I am looking forward to the event. A number of people asked me why I didn’t submit a presentation, but I think I’m going to check out the event as a blogger for my first time out and see about presenting next year! 🙂
-
So we have a parallel process, coordinator 739 running, just not very well… I have an ugly pink section in my OEM grid and as we know, pink is not a DBA Girl’s favorite color… 🙂 Not so fast… the process, due to the wait on temp and then to buffer, has decided to “force” it to serial? Description OBJECT Obj Node Order Rows Bytes Cost In/Out PQ Dist. CREATE TABLE STATEMENT 12 3,200 PX COORDINATOR FORCED SERIAL 11 PX SEND QC (RANDOM) SYS.:TQ20001 :Q2001 10 13,717,999…
-
I started to write this blog post almost two months ago and finally updated it to post to the site. I think it’s a bit more fun in hindsight anyway… 🙂 With my children at an age where they really would like their mother to just go get a life, (and get out of theirs as much as possible… :)) I chose this year to approach Ron Bich and Tim Gorman at RMOUG training days and state my interest in participating more. I was quickly informed there was no escape at that point and told I had to put my…
-
Thanks to Chet Justice of OracleNerd, I’m now registered for Oracle Open World 2011 with the Blogger’s registration. I will be sitting here impatiently waiting for October now… 🙂 Anyone who remembers last year’s will also remember that I (again) did not get to attend and worked miserably through the week, listening to the tweets on Twitter and blurbs on Facebook, then was reminded on Blogs afterwards of all I missed. Not this year! I am going and I am going to enjoy it- try and stop me… 🙂
-
Although Streams is a goner, (Golden Gate taketh over…) My notes on performance considerations when utilizing streams as I work with it is always valuable. These came from a number of blogs, notes from Oracle and simple grief, so thought I would share… 🙂 1. TYPE OF DATA REPLICATION Due to the way this table is loaded, we need to be concious of “queue spill”. Queue spill, per Oracle’s support documentation, is associated with LCR’s not being processed in a timely fashion per interval from the buffered queue, resulting in the old LCRs to “spill” from memory out to disk…
-
Jeff Smith, via Twitter, asked a very good question about my previous post regarding baseline plans when upgrading from 10g to 11g, (or for previous versions, which I can also go into the steps in how to ensure more consistent performance during upgrades from earlier versions as well to 11g…) “…this assumes that new 11g plans wouldn’t be ‘better’..right? Are people gun-shy b/c 9-10 upgrade issues?” Why would we want to go back to 10g performance when we have just upgraded to a beautiful new version of 11g? Many responded in the Twitter conversation in regards to insurance and this…
-
When involved in an 11g upgrade of a 10g database, one of the important goals for a DBA is to maintain the same performance in the newly upgraded environment as the previous one. One of the best ways to accomplish this is through baseline collection of the execution plans that then can be imported and loaded directly into the SQL Plan Baseline once the database is upgraded. The disclaimer needs to be added, you must have a license for the tuning pack, so always check your DBA_FEATURE_USAGE_STATISTICS view and ensure you are in compliance. The steps to complete this are…
-
Another little known set of AWR reports are ones that are RAC specific and cluster wide versions, giving the RAC DBA a unique view of the cluster, no individual instance AWR report can provide. The first report can be run from the command line, resides in the $ORACLE_HOME/rdbms/admin directory and is called awrgrpt.sql. Like the other versions of AWR, there is an HTML and a text version of the report offered, so when your manager or user would like to have something formatted and presented in an attractive format, there is an option. The report breaks down the cluster by…