My Scripts For Baselines (Oracle)

Hi, in this post I would like to put a link to my github, where I have left a series of scripts related to the use of baselines in Oracle. I use them as if they were an API to perform optimizations, giving the change to “slow” execution plans (and I do not want to say that they are bad, about this we can talk a lot) for faster or more efficient ones.

Baselines Scripts

HTH – Antonio NAVARRO

Advertisements

Necessary Permissions To Execute SQL Advisor

Today a person from Dev Team ask me about the necessary permissions to execute the Oracle SQL Advisor. This Advisor has get better with the years and it is a powerfull tool for test and optimize SQL in an easy way. It don’t requeried a special expertise and can save many hours from other people (seniors dev, DBAs, etc.). Below show the permissions you need;

grant advisor to <USER>
grant administer sql tuning set to <USER>

In many clients where I worked it is usual create a rol (ess rol_developer_team) with this permissions (and others like debug) and assign to developers users.

NOTE;

Privilege escalate and security checking is guarantee by its (DBMS_SQLTUNE) being the main privilege ADMINISTER SQL MANAGEMENT OBJECT.

HTH – Antonio NAVARRO

AWR tool is not supported on Active Data Guard

I have recently been asked for performance reports from a database in ADG (Active Data Guard), the problem has been that AWRs are not available in this architecture until version 12.2 of Oracle. In my case this is a 12.1 so it can not be used.

As an alternative Oracle proposes to use adapted version of the statpack, known as standby statspack. For more information about this process you can see the note ML / MOS¬† “Installing and Using Standby Statspack (Doc ID 454848.1)”.

HTH – Antonio NAVARRO

Installing and Using OSWatcher (Release 8.1.2)

For those who do not know this tool, tell you that it is basically a shell script that collects a series of unix commands about the time dimension.

OSWatcher only use the next operating systems commands to recopile information;
VMSTAT
IOSTAT
MPSTAT
IFCONFIG
NETSTAT
TOP
TRACEROUTE

 

To install the latest version you must download it from ML / MOS, you need a license, even the most basic one.

To start it (in an easy way, many options are avaliables);

./startOSWbb.sh

To stop it;

./stopOSWbb.sh

To see the data;

Normally these traces are used to send information to Oracle, but you can see the data graphically with the following command

java -jar $OSWATCHER_HOME/oswbba.jar -i $OSWATCHER_HOME/archive

 

HTH – Antonio NAVARRO

 

 

DBMS_LOCK Must Be Declared In Swingbench

Today I was simulating workload with Swingbench (Version 2.5). I fan of this free tool it is a workload simulator for Oracle databases, of course, HammerDB is a worderful tool too. When executed the first workload I received lots of errors from event log into the Console. It says me that SOE.ORDERENTRY package body was invalid.

When I tryed compile it get the next error;

 
XXXXX>  alter package soe.ORDERENTRY compile body;

Advertencia: Cuerpo del paquete modificado con errores de compilación.

Transcurrido: 00:00:00.34
XXXXX>  show err
Errores para PACKAGE BODY SOE.ORDERENTRY:

LINE/COL ERROR
________ _________________________________________________________________
79/13    PL/SQL: Statement ignored
79/13    PLS-00201: identifier 'DBMS_LOCK' must be declared
82/13    PL/SQL: Statement ignored
82/13    PLS-00201: identifier 'DBMS_LOCK' must be declared

Of course, I usually never use the sys user to install anything, in my case I installed Swingbench with system user. The solution is so easy like

 

 
os11> sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 17 16:06:38 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL>
SQL> grant execute on dbms_lock to soe;

Grant succeeded.

 

And recompile the package body again;

 
XXXXX>  alter package soe.ORDERENTRY compile body;

Cuerpo del paquete modificado.

Transcurrido: 00:00:00.42
XXXXX>  show err
No hay errores.

HTH – Antonio NAVARRO

 

Get Stats Preferences

In a previous post I showed the way to get the stats preferences for all database. Today I post the method for all levels (Table, Schema and Database);

TABLE Level:

 
-- Replace  OWNER_USER  by owner user
-- Replace  TABLE_NAME by table name
SELECT 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'INCREMENTAL'),         
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'GRANULARITY'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'STALE_PERCENT'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'ESTIMATE_PERCENT'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'CASCADE')
FROM DUAL
/

SCHEMA Level:

   
-- Replace OWNER_USER  by owner user             
SELECT 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'INCREMENTAL'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'GRANULARITY'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'STALE_PERCENT'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'ESTIMATE_PERCENT'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'CASCADE') 
FROM DUAL
/

DATABASE Level:

 
SELECT 
  DBMS_STATS.get_prefs(pname=>'INCREMENTAL'),
  DBMS_STATS.get_prefs(pname=>'GRANULARITY'),
  DBMS_STATS.get_prefs(pname=>'STALE_PERCENT'),
  DBMS_STATS.get_prefs(pname=>'ESTIMATE_PERCENT'),
  DBMS_STATS.get_prefs(pname=>'CASCADE')
FROM DUAL
/

For all cases you can see histograms like show below;

 
SELECT 
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt   
FROM DUAL
/

HTH – Antonio NAVARRO