A developer friend of mine sent me an email today frustrated that none of the DBA’s he worked with could explain this error to him and sent it onto me. I thought I would get a post out with this one as I think it trips up a lot of folks: “I have a report query that worked fine on Wednesday and today produced a ‘single-row query returns too many rows’ error. So when I went to track it back through Toad, the query just flat out stopped working. ” Here was my explanation and a few hints to correct…
-
-
The Challenge: You want one process in your application to use a separate temp tablespace created on faster “ram disks” even though it executes the same packages on the same objects as the other applications. The complication in the challenge is that TEMP is not like other resources such as default tablespace. Even though you might create a new user with a separate default temp tablespace, the database will allocate temp by the default tablespace of the owner of the package/objects involved. The object’s owner temp tablespace will then be used for all temp tables, hashing and sorting. Now I’ve…
-
OK, Who else out there has a 3.8TB tablespace? Anyone, anyone? Bueller, Bueller?? I know, I know- Oracle can support terabytes and terabytes of data in one tablespace, so what is my complaint? Most developers, both database and application do not have the technology to access that data efficiently enough and DBA’s rarely have the disk read and write hardware that will support the kind of pressure that will result from large tablespaces. I am moving more and more of my hardest hit data to ram disk, but it’s expensive and all of us know the challenges at budget time…
-
This is a subject that I see a lot of debate on, but as a DBA who works in mostly large data warehouse and mart environments, I am a pro-rebuild DBA. Many of the large systems I work on are home-grown and designed. This means that there is either design or code issues that lend itself to poor choices that can leave high fragmentation and ridiculouly over-sized indexes. I have rarely seen this in smaller, OLTP environments, but offer me a large table in a warehouse that somebody has mistakenly decided to stage data to and I will bet you…
-
So I’ve been accepted to present at the 2010 RMOUG Conference and I am understandably nervous. This is my first large presentation I’ve given and I’m thrilled to be doing it. My topic is the power of parallel processing, something I’ve been working heavily in as of late and I feel I have a lot to offer on the subject matter. I’ve already written and submitted my abstract, now comes the daunting task of putting together a presentation that is beneficial and that everyone can follow,(vs. how my ADHD brain works, running rampant from one aspect of the challenging subject…
-
You have a statement that somebody, without thinking about resources, requested 96 total parallel processes for. They have now come to you, the DBA and want to know why, they aren’t getting the parallel they have requested: The statement starts out something like this: create table table_too_large_data tablespace mart_data1 compress nologging pctfree 0 parallel 16 as SELECT /*+ use_hash(i itmaff) parallel(i, 16) parallel(itmaff, 16) */ last_name ,… The developer who wrote it, did not take the parameter PARALLEL_THREADS_PER_CPU into consideration, so they didn’t even realize they have requested pretty much everything above *2.. As a DBA, you should first query…
-
OEM Reports that a server load is critical and top confirms this- PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 14721 oracle 15 0 4243m 201m 196m S 1.7 0.6 0:43.83 oracle 14998 oracle 15 0 8338m 47m 42m S 1.3 0.1 0:00.44 oracle 14713 oracle 15 0 4248m 2.8g 2.8g S 0.7 8.8 0:53.50 oracle 14715 oracle 15 0 4243m 2.8g 2.8g S 0.7 9.1 0:55.10 oracle 13734 oracle 16 0 4241m 693m 690m D 0.3 2.2 0:02.96 oracle 13744 oracle 15 0 4241m 697m 693m S 0.3 2.2 0:02.97 oracle Upon inspecting the database,…
-
method_opt=>’for all columns size 1′ This is the method option for DBMS_STATS.GATHER_TABLE_STATS that is commonly recommended- “Let Oracle look at all the columns and decide what kind of statistics, histograms, etc. need to be gathered on each…” I’ve never been real thrilled about it, especially in a data warehouse or mart environment where we rarely utilize more than a few columns per table in our where clauses. Really, why does Oracle need so much specific info on columns it’s not doing anything other than selecting? We have a set of columns that we consistently use in our where clause- these…