Joined twitter

I joined twitter.  I don’t really know how to use it.  I’m setup as Bobby Durrett, @bobbydurrettdba if that means anything to you. :)

– Bobby

1 Uncool Thing about the COMPARE_SYNC Package

It only works in version 12 :( That’s right, I didn’t test in previous versions and there was a bug. Please refer to the COMPARE_SYNC post for the new, improved version. Here is what I changed: The name is now COMPARE_SYNC. I don’t know what got into me to put those four meaningless letters in front. The […]

Different plan_hash_value same plan

I mentioned this same effect in an earlier post about SQL profiles: link

I get a different plan_hash_value values for a query each time I run an explain plan or run the query.  I see this in queries whose plan includes a system generated temporary segment like this:

|   1 |  TEMP TABLE TRANSFORMATION   |                             |
...
|  72 |    TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D668C_764DD84C |

For some reason the system generated temporary table (more...)

Comparing NULLable Values

If a column or expression may be NULL, it is a pain to compare it to something else. Why? Because comparisons involving a NULL do not result in TRUE or FALSE: they result in NULL. I use the DECODE function to work around this problem: here's why - and how.

Oracle Tidbits – February 2015

“Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in February 2015. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well… Hope you find these helpful to learn something new or to remind you of its existence and use… Publish Date TidBit 2-Feb #oratidbit […]

Bounce Counter Mismatch

EM agents can end up in several different error states and each of them have their own error image.  This one popped up in my environment this morning on one agent and all of its targets:

bounce_counter_mismatch00

A bounce counter mismatch (represented by the ‘N’ in the icon?) occurs when the agent has been restored from a backup on the remote host.  The agent is trying to upload metric data from timestampX but the repository contains (more...)

Speaking at Delphix User Group Webex March 11

On March 11 at 10 am California time I will be speaking in a Delphix User Group Webex session.

Here is the sign up url: WebEx sign up.

Adam Leventhal, the Delphix CTO, will also be on the call previewing the new Delphix 4.2 features.

I will describe our experience with Delphix and the lessons we have learned.  It is a technical talk so it should have enough details to have value to (more...)

RETURNING BULK COLLECT and database links

Looks like the nice PL/SQL facility for returning a set of updated rows is restricted when it comes to database links

(This tested on 12.1.0.1)

SQL> declare
  2    type int_list is table of number(12) index by pls_integer;
  3    l_results int_list;
  4
  5  begin
  6    update MY_TABLE b
  7    set b.my_col = ( select max(last_ddl_time) from user_objects@dblink where object_id = b.key_col)
  8    where b.my_col is null
  9    returning b. (more...)

Listing OEM Administrators

We still use database authentication for our OEM Administrator accounts.  Every once a while (like today) that list needs to be reviewed to evaluate contract employees and others that may have changed jobs.  We use the USER_DESCRIPTION field to store people’s names in OEM so we poll the user_name and its description.

Here’s a simple query, run as SYSMAN user, that creates a list for you in SQL+

COL user_name FORMAT a20
COL user_description FORMAT a35 (more...)

MOS “log file parallel write” reference note updated

Last year, I spent some time researching redo log related performance problems, which resulted in a mini-series, including one post devoted specifically to one previously unknown scenario of excessive log file sync waits.

I am happy to announce that a service request opened on the back of this research resulted in the MOS note on “log file parallel write” wait event (Doc ID 34583.1) having been updated with a general description of this scenario (more...)

Logical I/O

Except a few special cases, optimizing SQL is about minimizing I/O. And by “I/O” we normally mean “physical I/O”, because everybody knows that logical I/O (LIO) is much, much faster. But how much faster exactly? For a long time, this question has been bothering me. It looks like there has been little research in this area. Basically the only thorough investigation I managed to find on the subject was one by Cary Millsap and co-authors. (more...)

Travelling in a Wheelchair

It’s that time of the year again, when one of the best grass roots Oracle user group conferences takes place, the Rocky Mountain Oracle User Group Training Days in Denver Colorado. I’ve been privileged enough to present at this conference several times in the past, and always have an absolute blast at it, so I try to submit a few papers each year. This year, I had three papers accepted. Two of them were designed (more...)

A new index on a small table makes a big difference

A few weeks back on the weekend just before I went on call we got a complaint about slowness on an important set of reports.  I worried that the slowness of these reports would continue during my support week so I tried to figure out why they were slow.  I reviewed an AWR report for the 24 hours when the reports were running and found a simple query against a tiny table at (more...)

Java 8 and Spring 4

On March 27, 2014, the Spring community was happy to announce the Spring Framework 4.0.3. The reason why is that it was the first release of the Spring Framework with Java 8 support. Since then, the Spring team worked very hard to provide major updates for the Spring Framework to be even more supportive towards Java 8. As of September 4, 2014, the Spring Framework 4.1 has arrived with even more support towards the Java (more...)

No Oracle instances were discovered do you want to enter one

While try to configure Backup exec agent in database server

“No Oracle instances were discovered do you want to enter one”

Solution:

add the instance value in /etc/oratab


Anyone need remote help?

Does anyone know of any good contracts that would support mostly remote work?

I started this blog years ago with the idea that it would be a good place to search for work should I ever need it. I think the time has come to just ask to see if projects are available through this avenue. If you know of anything, I would love to speak with you. Would you mind commenting below or sending (more...)

GROUP BY – wrong results in 12.1.0.2

I encountered this during some testing – the optimizer can be a little overzealous when it encounters nested group-by’s

SQL> create table T ( ts number not null, c char(10));
 
Table created.
 
SQL> insert into T
  2  select 100+dbms_random.value(1,50),'x'
  3  from ( select 1 from dual  connect by level < 1000 ),
  4       ( select 1 from dual  connect by level < 1000 )
  5  /
 
998001 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL>
 (more...)

Long parse time with interval partitioning and subpartitions

In an earlier post I described how some queries with long parse times were causing long library cache lock waits.  Friday I applied a patch from Oracle that resolved the long parse times.  Here are the conditions which may relate to this bug:

  1. Interval partitioned table
  2. Partitioned by range
  3. Sub-partitioned by list
  4. Open ended range on partitioning column in where clause
  5. Tens of thousands of sub-partitions

Prior to applying the patch I did (more...)

High CPU during I/O (continued)

Last week I posted a blog on high CPU utilization during I/O. The blog title contained a question mark because it seemed like there were more questions than answers. Were the CPU utilization numbers real or just an instrumentation artifact? Were they distorted by double-counting? If real, where was it coming from? About a dozen top experts participated in that discussion, and everybody had a different theory. I’ve spent some time looking at the problem (more...)

Data Center of the Future – now

9087977014_a5a2d4bc11_k

 

photo by youflavio

In a recent blog post Eric D. Brown defined an Agile Data Center as

An Agile Data Center is one that allows organizations to efficiently and effectively add, remove and change services at the speed of the business, not the speed of technology - Eric D. Brown

In follow up post he said that a Agile Data Center could be implemented by Software Defined Data Center (SDDC) for example using machine (more...)