Today, I had to identify all rows on a given block. I used DBMS_ROWID, but initially I wasn’t using it correctly. I figured out what I should have been doing (below)… select work_id from xwc.frbr_holding_display partition(FRBR_HOLDING_DISPLAY_P12) where dbms_rowid.ROWID_BLOCK_NUMBER(rowid,’BIGFILE’) > =…
Month: May 2009
CPU used by service
What is below will query AWR and print CPU for a given service in a three instance cluster. This hardcodes the three instances, as well as eight processors with half hour AWR samples (14,400 seconds of CPU available). set serveroutput…
How many rows does PL/SQL array fetch by default?
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…
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,…