This was fun, and I’ve been trying to remember to post about the things that are fun.
I was working with a customer using a purchased application from a relatively over-bearing vendor. You know the type: they have very specific (and somewhat out-of-date) demands for the Oracle database environment on which their application is hosted.
My customer complained that some reports were taking 15-20 minutes to complete, when everyone who knew what the reports were doing were certain that they shouldn’t take more than a few seconds at most. The search parameters to the report were very specific, the report didn’t do a whole lot, only supposed to bring back a few bits n pieces, not the whole kit n kaboodle.
So they extracted the text of the SQL statement from the application and emailed it. Using that text, I searched for the text in the V$SQL view and found the SQL_ID. Now we can have some fun…
A recent posting on OTN came up with a potentially interesting problem – it started roughly like this:
I have two queries like this:
select * from emp where dept_id=10 and emp_id=15;
select * from emp where dept_id=10 and emp_id=16;
When I run them separately I get the execution plan I want, but when I run a union of the two the plans change.
Following the webinars about 11g stats that I presented on Monday John Goodhue emailed me a few questions that had come through the chat line while I was speaking, but hadn’t appeared on my screen. He’s emailed them to me, so here are the questions and answers.
1. I’d like to know what parameter to use for faster results on dbms_stats.gather_dictionary_stats
If you manage Oracle on Windows, you probably have wondered why it is so difficult to work out which Oracle instances are running and which ORACLE_HOMEs they use. On Unix or Linux, this is a very simple task. Oracle services and their ORACLE_HOMEs are listed in the oratab file, located in /etc/ on most platforms and in /var/opt/oracle/ on Solaris. To find what is running, we would usually use the ‘ps’ command and pipe it through grep to find and run PMON processes.
On Windows, it just isn’t this easy. Each Oracle instance runs in a single monolithic oracle.exe process. Nothing about the process indicates the name of the instance. When we want to find all of the configured Oracle services, we can use the ‘sc’ command, and pipe the results through find (I have added emphasis to the ASM and database instances):
When you create a table with a primary key or a unique constraint, Oracle automatically creates a unique index, to ensure that the column does not contain a duplicate value on that column; or so you have been told. It must be true because that is the fundamental tenet of an Oracle database, or for that matter, any database.
Well, the other day I was checking a table. There is a primary key on the column PriKey. Here are the rows:
Select PriKey from TableName;
I got two rows with the same value. The table does have a primary key on this column and it is enforced. I can test it by inserting another record with the same value - “1”:
SQL> insert into TableName values (1,…)
I came across a rather annoying little bug yesterday…
One of the guys was trying to compile an Oracle Form on the app server and got this message.
*** ASSERT at file pdw1.c, line 4061 PSDGON missing. Can't get object number Source Location = XNSPC0P99_2013_06_12_17_44_38__AB[71, 7]
Uncle Google pointed me in the direction of this MOS Note [ID 1276725.1].
It turns out it is a problem with the 18.104.22.168 client, which happens to be the version that ships with Forms and Reports Services. The Forms compilation fails when the form references a database function that uses the Function Result Cache in 11g.
Actually it’s probably not the NOT IN that’s nasty, it’s the thing you get if you don’t use NOT IN that’s more likely to be nasty. Just another odd little quirk of the optimizer, which I’ll demonstrate with a simple example (running under 22.214.171.124 in this case):
Amazon Web Services has offered Relational Database Service as part of their cloud offering since 2011. These days, RDS provides easy to deploy, on-demand database-as-a-service for MySQL, Oracle, and SQL Server. When you compare it to essentially any other method of hosting and licensing Oracle, RDS seems to have a variety of really appealing qualities.
With RDS/Oracle, you don’t really need a DBA to take care of your database. With the notable exception of tuning, most of the DBA tasks, such as database creation and configuration, backups, upgrades, and disaster recovery are simply features of the service.
Here’s a funny little bug – which probably won’t cause any damage – that may remind you that (most of) the people who work for Oracle are just ordinary people like you and me who make ordinary little mistakes in their programming. It’s a bug I discovered by accident because I just wanted to check something about how a particular undo tablespace had been defined, and I called dbms_metadata instead of querying dba_tablespaces. Here’s the cut-n-paste from an SQL*Plus session on 126.96.36.199:
I wrote a note a few years ago about SQL*Net compression (this will open in a new window so that you can read the posts concurrently), showing how the order of the data returned by a query could affect the amount of network traffic. An example in the note demonstrated, using autotrace statistics that the number of bytes transferred could change dramatically if you sorted your return data set. At the time I asked, and postponed answering, the question: “but how come the number of SQL*Net round trips has not changed ?”
A couple of weeks ago someone asked me if I had ever got around to answering this question – and I hadn’t. So I started by writing an answering comment, then decided it was getting a little long so transferred it to a separate blog note, and here it is.
We can start with the two sets of stats: