Oracle User Group Scotland Conference 2015

Oracle User Group Scotland Conference 2015 1) SQLcl (SQL command line) new enhanced SQL*Plus, easy to output index info, DDL, output/load data in csv, xml. Early adopter (beta) version available for download now, production version expected soon and to be bundled by default into Oracle 12cR2. More at Kris Rice. 2) Relax and Recover Martin […]

Read More Oracle User Group Scotland Conference 2015
June 11, 2015
Posted in Oracle User Group, Performance tuning
One Comment

Calculate optimal PGA size

Simple script to calculate optimal pga_aggregate_target size (in gb): col avg_tpi form 999.99 col max_tpi form 999.99 select instance_number, avg(value)/1024/1024/1024 avg_tpi , max(value)/1024/1024/1024 max_tpi from dba_hist_pgastat where name = ‘total PGA inuse’ group by instance_number ; dba_hist_pgastat has 1 weeks worth of data by default, snapshots taken at 1 hour intervals. Memory not assigned to […]

Read More Calculate optimal PGA size
July 25, 2013
Posted in 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 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
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: 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
Posted in Performance tuning, SQL server

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

Read More Automatic gather stats job
March 5, 2010
Posted in Performance tuning, Stats