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();…
Month: July 2009
More fun with SQL_BIND_CAPTURE
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.…
dbms_sqltune using AWR data
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…
How to gather stats for a single column
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…
Python based stress tester
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…
Compressing and Decompressing XMLTYPE’s
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…
Can you drop a tablespace with an active transaction?
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…
Great whitepaper on connection management in RAC
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