Optimizer Funkiness…

I have two simple tables that I want to join in an outer join: SQL> @bug32052.sql SQL> set feedback on SQL>  SQL> drop table jh1; Table dropped. SQL> create table jh1 (x number(10)); Table created. SQL>  SQL> drop table jh2; Table dropped. SQL> create table jh2 (x number(10)); Table created. SQL>  SQL> insert into jh1 values (1); 1 row created. SQL> commit; Commit complete. SQL>  SQL

When will RHEL6 be certified?

Red Hat Enterprise Linux has been out since November of 2010.  Here we are 16 months later and it's not certified for database use by Oracle yet. Yes, I know the database can be installed on RHEL6.  In fact, I have it running in my development environment right now with no issues related to the OS/DB integration.  Try to get Oracle Support to help you on those platforms for an issue unrelated to

Resumes & Job Objectives

I've been reviewing a lot of resume's lately.  Please tell me, what is the purpose stating your "Job Objective"?  Isn't it implied that your objective is to find a new job, specifically my job? I assume you're dynamic and technical and your vast expertise will help my company conquer the world. Also, what is the purpose of summarizing your experience on the first page and then giving me eight

For People That Have Managers

Interesting take on what managers are thinking: http://quickbase.intuit.com/blog/2012/01/09/10-things-your-boss-isnt-telling-you/

Is SQL*Plus Resumable?

I am using resumable space for a lot of my operations. However, it seems as though the SQL*Plus copy command doesn't respect the resumable feature? Note, I am issuing an "alter session enable resumable timeout 14400" through a login trigger. I know this works because if I do the same type of operation through an INSERT/SELECT through a dblink, my session waits for more space to be added.

Interesting Error

We just upgraded one of our 10.2.0.3 dbs to 11.2.0.2 plus some patches.  Today an interesting ORA-07445 was thrown to the alert.log: ORA-07445: exception encountered: core dump [__intel_new_memcpy()+382] [SIGILL] [ADDR:0x3FE5CAE] [PC:0x3FE5CAE] [Illegal operand] [] Searching metalink didn't get us anywhere, so we opened a TAR (or iTar, or SR, or ServReq, or whatever the heck they are calling

Common Sense

Developer: I am running this query: DELETE FROM tab; It's been running since 2PM yesterday.  Can you make it go faster? DBA: You shouldn't delete 80M rows in a single transaction.  You should have used TRUNCATE instead of DELETE.  I have killed your session and it will be a while before it completes. Developer (5 minutes later): I tried TRUNCATE but it says my table is locked. DBA: I know, the

KEEP Pool

I have a persnickety problem with a particular table being aged out of the buffer cache.  I have one query that runs on a defined basis.  Sometimes when it is run, it does a lot of physical reads for this table.  Other times, it does no physical reads. So I decided to play around with a different buffer pool and let this table age out of cache on it's own terms rather than competing in the

Compression and SE

While researching a corrupt block on 11g SE, we came across a number of objects that were compressed according to the data dictionary. How could that be?  Compression is not a feature of SE, or so we thought. The objects in question were all indexes.  In fact, Oracle creates compressed indexes in ?/apex/core/tab.sql even though we are on SE. Further investigation lead me to Doc 1084132.1 which

Time Spent, Time Wasted

Sometimes tuning a database can be rewarding.  I get great satisfaction when helping a user take their query from 10 minutes down to 3 seconds.  But it doesn't always work that way.  Consider this recent exchange: Developer to DBA Manager: The database is slow. DBA Manager to DBA: Trace his process. The DBA traces the developer's process and comes up with about 12 queries that can benefit from

It’s always X, except when it’s not

I drill into my DBAs heads that the first step in tuning any process is to get a trace.  Don't show me the query plan until you show me what the database actually executed.  The trace is the fundamental clue in any performance investigation.  The wait events tell you exactly what's going on and there is no question where the bulk of the time is being spent. When we see a large number of waits on

11g Gotcha

Something I discovered recently is that the DEFAULT profile for Oracle 11g sets the PASSWORD_LIFE_TIME to 180 instead of UNLIMTED by default.  Applications will encounter an "ORA-28002: the password will expire within X days" error message if you keep the default value. To change the PASSWORD_LIFE_TIME, you: ALTER PROFILE default LIMIT password_life_time UNLIMITED; One thing to keep in mind is

What’s old is new again

We had a fair number of problems related to bind variable peeking in our 10.2.0.3 and 10.2.0.4 dbs.  In fact, the plans became so erratic that we decided to just turn bind variable peeking off by setting _optim_peek_user_binds=false in all the init.ora files. Flash forward to Oracle 11g (11.2.0.2) and the optimizer has been enhanced to use bind variable peeking much better.  After converting to

The Cost of Mediocre

Over the years I've written tons of scripts to monitor all sorts of things on the database.  Some of those scripts turned out to be helpful on an ongoing basis and some ended up dying on the vine when new features of Oracle came out. One of my scripts looks at the top processes in a database from the CPU's perspective.  In other words, how much time did the process use on the CPU over it's

Upgrading to Oracle 11gR2

I am about half way through upgrading all my dbs to Oracle 11gR2 from various versions of 10gR2.  Out of all the upgrades I've performed from 7.0 up to 10gR2, the 10g to 11g upgrade is by far the most hassle free of all. I have abandoned the GUI upgrade tool this time.  The GUI tool is fine, but internal issues prevented me from running the GUI remotely.  This meant that somebody had to be

Seriously?

I haven't done any research on this yet, so I don't know if it is fixed in a subsequent patch or a later version.  But seriously, a space? SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

My first 11g show stopper: Purging the Recyclebin

I've run into my first issue with 11.2.0.2.  After one of my dbs was upgraded, every time I try to purge the recyclebin, I get an ORA-00600 error: SQL> purge dba_recyclebin; purge dba_recyclebin * ERROR at line 1: ORA-00600: internal error code, arguments: [ktcdso-1], [], [], [], [], [], [], [], [], [], [], [] It doesn't matter if the recyclebin is on or off. I have done about six upgrades and

Listener Logging in 11g

By default, the listener logging is not turned on in 11r2.  You have to turn it on by specifying: LOGGING_listener_name=ON in your listener.ora file.

Something Interesting about the 11g Client

If you're just beginning to implement Oracle 11g, you need to be aware of the Automatic Diagnostic Repository (ADR).  It has nothing to do with AWR, ADDM, ASM, ASSM, or any of the other acronyms Oracle has come up with over the last two releases. In terms of the database, ADR could be a great thing.  It's integration with Enterprise Manager is probably one of the best parts about the feature. 

Removing OLAP from 10g

There is a metalink note that describes how to remove the OLAP option if it was installed in your database incorrectly.  See ML Note 739032.1 for details.  Also know that the described procedure doesn't fully remove your OLAP object and that you have to drop some manually according to Note 1060023.1.