Category: Performance tuning

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

Posted in Oracle User Group, Performance tuning, VMWare

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:

Posted in Performance tuning, SQL server

sqltrpt readable formatting

Output of sqltrpt is more readable with these settings: set pages 9999 lines 192 col plan_plus_exp form a132 @?/rdbms/admin/sqltrpt

Posted in Performance tuning

Setting bind variables in sql*plus

variable b1 varchar2(6) ; exec :b1 := ‘2009’ Used mainly for sql tuning rather than in real code.

Posted in Performance tuning

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’)

Posted in Performance tuning

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

Posted in Performance tuning, PL/SQL

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’)

Posted in Performance tuning, Stats

Oracle Performance Tuning Notes

Click for the Course notes with scripts for a 3 day performance tuning course I did recently.

Posted in Performance tuning

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

Posted in Performance tuning, Scripts, Space

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

Posted in Performance tuning