We were recently required to get rid of 250 million rows in a partitioned audit table. While the table is partitioned, we have global indexes that would be rendered unusable after dropping or truncating a partition. The UPDATE GLOBAL INDEXES…
Category: Oracle
How does connection re-direction occur in RAC? (Part 1)
I have always been curious as to how a connection request is redirected in a RAC. In other words, let’s assume we have a two node cluster with a given service that only runs on one of the nodes. In…
Are you actually *using* Oracle partitioning?
Partitioning is sometimes sold as the panacea to all performance problems. Anyone who has added local indexes on a column other than the one on which the table was partitioned quickly finds out this isn’t always the case. Keep in…
Using DBMS_METADATA to get table and index definitions
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();…
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…