I was curious as to how many rows PL/SQL will fetch by default without a BULK COLLECT clause. For those of you that may not know, fetching multiple rows at a time can substantially reduce the run times of programs…
Category: Oracle
Setting a parameter in another session
Today I needed to set the optimizer_dynamic_sampling in several sessions to a lower value. It was set to 5, which is way too high for this application. I used what is below. begin for cur in (select sid,serial# from v$session…
gc buffer busy on the same instance??
Recently, we have been running a large data load against a three server RAC. This load is running against a single server in the cluster. We found a particular SQL statement used in a PLSQL procedure did a large amount…
Troubleshooting enq: TM contention – after the fact
We began to experience this event yesterday afternoon. Normally, oracle takes the TM enqueue (table lock) out when activity is occurring such as an insert and we want to ensure the structure is not changed during the insert. variable min_snap…
Analyzing cache transfers between instances in a RAC
We were initially taught by oracle that RAC was a panacea. Just add nodes on the fly, and the workload of a database instance will be automatically distributed over all available serves in the cluster. However, as time wore on,…
Java Lock class is great for test casing
OK, so while trying to understand Oracle’s redo latching mechanisms (we have been experiencing a nightmare with log file sync since switching to a new DMX subsystem), I found that java 1.5 has a new Lock class. How cool is…
Standby created without a standby controlfile?!!
Today, a junior DBA created a standby database by copying the controlfile to another host, restoring the 4TB database it serves, and recovering all available logs via RMAN. Sounds good, right? Well, it was until we went into SQL*PLUS and…
Read only tablespace requires no outstanding transactions…anywhere…
Nice little gotcha I received yesterday morning. Our intention is to rebuild two large tables from range partitioning to hash partitioning on another server. One of the datafiles for the tablespace that houses one of the tables in question is…
Oracle function to calculate wait event correlation
Cool little function to provide statistical correlation between two events. This helped us to identify performance drivers in our database. Statistically, anything above 60 is considered “statistically significant”, or correlated. If one goes up, so does the other. create or…
Control file writes when updating???
I had never seen this before, but while testing a migration strategy for a large table with XMLTYPE’s, almost half our wait time was spent on control file sequential read and control file parallel write waits. Each of these occurred…