Archive for May, 2009

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') > = 8566791 …but in the meantime, I also ended up dumping the blocks and writing... »

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 on pages 100 feedback off verify off variable service_name varchar2(100) exec :service_name := '&service_name' variable start_time number begin ... »

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 that deal with a large number of rows. We set up the obligatory test... »

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 where action like 'DRAIN%') loop sys.dbms_system.set_int_param_in_session(cur.sid,cur.serial#,'optimizer_dynamic_sampling',2); end loop; end; / »

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 of I/O. While we knew we needed to fix it, we... »

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 number variable max_snap number begin select min(snap_id) into :min_snap from dba_hist_snapshot where begin_interval_time > sysdate -... »

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, users began to realize that while moving database blocks between servers over... »