Category Archives: Performance tuning

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.”

Posted on 5 December 2011 by Andrew Fraser

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: … Continue reading →

Posted in Performance tuning, SQL server | Leave a comment

sqltrpt readable formatting

Posted on 3 June 2010 by Andrew Fraser

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 | Leave a comment

Setting bind variables in sql*plus

Posted on 3 June 2010 by Andrew Fraser

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

Posted in Performance tuning | Leave a comment

SQL profile drop

Posted on 3 June 2010 by Andrew Fraser

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 | Leave a comment

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

Posted on 5 May 2010 by Andrew Fraser

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 … Continue reading →

Posted in Performance tuning, PL/SQL | Leave a comment

Automatic gather stats job

Posted on 5 March 2010 by Andrew Fraser

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 | Leave a comment

Oracle Performance Tuning Notes

Posted on 17 April 2009 by Andrew Fraser

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

Posted in Performance tuning | Leave a comment

schema moves by the magic of partition exchange

Posted on 17 April 2009 by Andrew Fraser

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 … Continue reading →

Posted in Performance tuning, Scripts, Space | 1 Comment

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

Posted on 13 March 2009 by Andrew Fraser

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 … Continue reading →

Posted in Performance tuning | Leave a comment

Cache LOBs for Better Performance

Posted on 6 March 2009 by Andrew Fraser

LOBs generally(*) perform better if cached – that is, stored in the database buffer cache. However, that is not switched on by default. To change a lob to be cached: alter table mytable modify lob (mycolumn) (cache) ; To set … Continue reading →

Posted in Performance tuning | Leave a comment