Archive for July, 2009

Below is a quick script I use to extract table and index definitions I may need from time to time. Enjoy… set lines 255 trims on set serveroutput on size unlimited declare l_tab_clob clob := empty_clob(); l_ind_clob clob := empty_clob(); begin for cur in (select table_name from user_tables) loop select dbms_metadata.get_ddl('TABLE',cur.table_name,user)... »

We had a query that was running slow during a performance test, but extremely fast with my laptop based threaded java test. The SQL was exactly the same, so I was driven bonkers by why it wasn’t using an index. It finally occurred to me that not only could the bind values... »

Today I wanted to use dbms_sqltune on a SQL statement that was run on Friday, but had not been executed since then. dbms_sqltune by default will assume the statement is still in the shared pool. If it is not, you must use one of the overloaded procedures for create_tuning_task. See below… variable l varchar2(4000) begin ... »

I needed to gather stats for only a single column. For whatever reason, the auto stats job was not getting it, but we actually found our execution plans were much better when we had it. I ran what is below to generate a HEIGHT BALANCED histogram with 254 evenly divided buckets. We have... »

I often find myself building something in anticipation of needing it at some point.  This is one of those cases :)  Below is a template (a real working one for my needs) that you can edit to build a simple load testing tool.  We were having a performance problem with one SQL statement, and... »

We are testing with compression of our XMLTYPE data. We are initially testing with the following… create or replace package compression_utils is function comp_data(p_data in xmltype) return blob; function decomp_data(p_data in blob) return xmltype; end; / create or replace package body compression_utils is function comp_data (p_data in xmltype) return blob is ... »

It occurred to me that it is sometime necessary to drop an UNDO tablespace after creating a new UNDO tablespace and changing our instance to use the new one. It occurred to me that Oracle should not allow you to drop and UNDO tablespace that has any active transactions against. If it... »

This is a fantastic whitepaper on workload management in RAC.  It is essential reading for anyone supporting a RAC environment, or an architect of a middle tier that wants to understand how connections are handled by the database. http://www.pythian.com/documents/Pythian_RAC_Whitepaper.pdf »