VMWare performance for Oracle at Oracle User Group Scotland Conference 2013

Main highlight Oracle User Group Scotland Conference 2013 was a presentation by Dean Richards of Confio Software on VMWare performance for Oracle. Confio’s servers were at 1-5% CPU, so underutilised, but databases use memory rather than CPU. They virtualised 50 physical servers > 1 esx host – was at 40-50% CPU. Later moved to two […]

Read More VMWare performance for Oracle at Oracle User Group Scotland Conference 2013
June 13, 2013

Indexed views – workaround for “Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.”

Indexed views (the SQL Server equivalent of Oracle’s materialized views) are a good performance fix, especially for reporting and data warehouse systems. However they only work with inner joins. Attempts to create an indexed view with outer joins fail with: Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, […]

Read More Indexed views – workaround for “Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.”
December 5, 2011

SQL profile drop

If you have accepted a sql profile from a tuning advisor recommandation like: execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_239607’, replace => TRUE); Then the regression commands to back it out are like: select name from dba_sql_profiles; exec dbms_sqltune.drop_sql_profile(‘MYNAME’)

Read More SQL profile drop
June 3, 2010

PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms with OUT or IN OUT parameters

Result cache functions are a good new feature in Oracle 11g, but they only work with normal functions. A function that returns multiple outputs using OUT parameters cannot be made a result cache function, at least not in 11gR1. Attempting to do so fails with this error: “PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is […]

Read More PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms with OUT or IN OUT parameters
May 5, 2010

Automatic gather stats job

check it is on with: select state, last_start_date from dba_scheduler_jobs where job_name = ‘GATHER_STATS_JOB’ ; Switch it on and off with: exec dbms_scheduler.disable(‘GATHER_STATS_JOB’) exec dbms_scheduler.enable(‘GATHER_STATS_JOB’)

Read More Automatic gather stats job
March 5, 2010

schema moves by the magic of partition exchange

Here’s an example of how to use partition exchange to move partitions or even entire unpartitioned tables from one schema to another. Is mean to be very fast and generate very little redo. Even more so if the partitions and tables are kept in the same tablespace. Process for doing partition exchange is like this: […]

Read More schema moves by the magic of partition exchange
April 17, 2009

One Comment

Use Foreign Key constraints with “on delete cascade” option for fast automated delete of child records

Here is a demo example. 1) Set up example tables: CREATE TABLE parent ( parent_id number(10) not null, CONSTRAINT parent_pk PRIMARY KEY (parent_id) ); CREATE TABLE child ( child_id        numeric(10)     not null, parent_id       numeric(10)     not null, CONSTRAINT fk_child FOREIGN KEY (parent_id) REFERENCES parent(parent_id) ); insert into parent values (1) ; insert into child values (1,1) […]

Read More Use Foreign Key constraints with “on delete cascade” option for fast automated delete of child records
March 13, 2009