Monitoring Oracle Change Data Capture Queues

I shared a shell script that will check Oracle CDC Queues to make sure they aren't going stale. If your CDC consumer has not picked up changes in 48-72 hours then something may be wrong. The scripts can be customized to alert at any interval, hopefully this will serve as a good 'nudge' to get you going in the right direction.

Everything you need is here.

If you are an OEM user you can probably grab the SQL I shared and modify it for use with OEM User Defined Metrics or alerting.


WHERE column IS NULL and index usage...

I have a customer with a million row 125mb table. A query they are running returns very slowly:
explain plan for SELECT * FROM BIGTABLE WHERE col1 IS NULL;
SELECT * FROM TABLE(dbms_xplan.display);

| Id  | Operation            |  Name             | Rows  | Bytes | Cost  |      
|   0 | SELECT STATEMENT     |                   |     1 |    96 |  1436 |      

Recording User Defined Metrics in Oracle Enterprise Manager OEM

Did you know that OEM has a built in interface for executing simple ad-hoc queries and recording the results? Here is how I set up a recurring query to find the number of user logins every 15 minutes and record the data. 1) Navigate to a database instance in OEM
2) Select the Related Links -> User Defined Metrics link from the bottom of the Database Instance Home tab.
3) Select the Create button to create a new User Defined Metric a. Enter the Metric Name

Finding Query Block names for a SQL that you can't modify...

I recently had an issue where a query being submitted to the database had a large change in performance. I could attribute the change to a recent change in object statistics, but the query wasn't behaving because of a combination of a view, subquery, UNION, and LIKE operators. After spending too much time on this statement I decided to hint it to make it perform like it did before the most recent stats change.

Need to audit changes? Can't change source database at all? This may work...

If you want to record copies of changed records on a target system you can do this with Golden Gate Software and not incur much in the way of source side resource drain.

Unloading data from Oracle?

I recently fielded a question about getting data out of Oracle quickly. Without much detail on the systems involved here is my answer:


I need to migrate data from Oracle to MySQL quickly (in less than 1 day). What are my options?

My answer:

Oracle does not supply an out-of-the-box unload utility. Keep in mind without comprehensive info about your environment (oracle version? server platform? how much data? what datatypes?) everything here is YMMV and you would want to give it a go on your system for performance and timing.

Oracle IN Condition and Multi Column Subqueries

I keep coming across a construct in some legacy SQL that has been causing all kinds of performance issues for us. I guess you could call it using the IN condition with multi-column subqueries. I located the syntax for the IN condition here but it doesn't really get into much detail about using this construct. Here is an example of the subquery (it returns the lowest salary and department_id for each department):

SQL For Dinosaurs


Quickie script to run dbv on your database...

This script will generate dbv commands into a shell script and then execute the shell script. run-dbv.sql:
set head off
set lines 200
set feedback off
set define off

select 'dbv file='||name||' blocksize = '||block_size||
       ' LOGFILE=FILE-'||FILE#||'.LOG' from v$datafile;
spool off

host chmod 755
spool dbv_results.log
host ./
spool off
Output will be created as separate log files. You can run it and review results like this:
$ sqlplus "/ as sysdba" @run-dbv.sql

Permissions for Autotrace

If you want to run AutoTrace using Toad using a non sys or system user, give your user the following select privileges:
grant select on v_$session to &traceuser.;
grant select on v_$sesstat to &traceuser.;
grant select on v_$statname to &traceuser.;
Alternately you can run $ORACLE_HOME/sqlplus/admin/plustrce.sql and then grant the PLUSTRACE role to the user that needs to run AutoTrace. -Dave


Subscribe to RSS