Category Archives: SQL*Plus

An Enterprise Manager-like chart in SQL*Plus

Português/English

After showing how to use 256 colors and parsing parameters both in SQL*Plus, I now present a very interesting script (at least for me ;-)) I created that mimic the main Enterprise Manager chart in SQL*Plus.

The script builds a “chart” by using coloured blocks (or characters instead) to show active sessions broken down by Wait Class, or broken down by Wait Event for a provided Wait Class.

The script can receive a lot of parameters that control the period and other details as I present below.

Please note that the script gather information from ASH repository, so you should have Oracle Diagnostics Pack license to use it.

Here are the parameters you can provide:

  • class: specify the Wait Class (just the 3 first letters are enough) to show their events breakdown. If not specified, show the Wait Classes breakdown.
  • begin: begin date as a DATE expression, like sysdate-1/24.
  • end: end date as a DATE expression, like sysdate-1/24.
  • minutes: if a positive number is provided, the period is defined starting with &BEGIN and ending the number of minutes after that (&END parameter is ignored).
    if a negative number is provided, the period is defined starting the number of minutes before now (sysdate) and ending now (&BEGIN and &END are ignored).
  • view: the ASH view to query. Enter D to query DBA_ACTIVE_SESS_HISTORY. Any other value will use GV$ACTIVE_SESSION_HISTORY.
  • group: group (Y) or not (N) the rows by minute.
  • color: define if you want to see characters instead of colors (N), 8 colors (8) or 256 colors (256).
  • show: SHOW (Y) or not (N) the number of sessions for each Wait Class or Event.
  • mult: the multiplier to define how many boxes will be painted for each Average Active Session. If mult=2 and there are 3 sessions, 6 (3 * 2) boxes will be painted.
  • zeros: show (Y) or not (N) the samples that have no active session (especially useful when showing just one Wait Class
  • inst: specify an Instance to show (for RAC environments). NULL show all instances.

Here are some examples on how to call the script:

-- Execute the script with all the default parameters:
@em

-- Show the events break down for the APPLICATION wait class, from DBA_HIST ash view, don't show samples with ZERO active sessions
@em class=app view=D zeros=n

-- Show the last 120 minutes of data, don't group by minute
@em minutes=-120 group=N

-- Show the chart from yesterday, don't show the numbers of each wait class
@em begin=trunc(sysdate-1) end=trunc(sysdate) show=n

-- Show the chart with characters instead of colors, show 3 characters for each active session
@em color=n mult=3

And here are three sample outputs:

em main

The main chart showing some Application issues

em app

Drilling down to realize it is a row lock contention

em1

Another main chart, now showing User I/O issues

em2

User I/O events break down

em char

Using characters instead of colours

In the beginning of the script, right after the header, you will find the default values for every parameter. You can change the values as you like.

Please note that the main script can call some auxiliar ones: my parameters parser (read this) and one of three to define the colours (or characters) that will be used (read this).

Also note that I am using the PLAN_TABLE to save the ASH data that I will work with, just to avoid reading it many times in my subqueries. This solution is used in eDB360 and SQLd360 tools, from my fellow friends Carlos Sierra and Mauro Pagano.

So here you have the scripts. Please copy the content and save them all in the same directory. Then run em.sql as explained above. You can also download the scripts from github.

Any comments, suggestions, corrections, please let me know.

Enjoy!!!!

$cat em_nocolors.sql
define BG_ONCPU=""
define BG_USER =""
define BG_SYST =""
define BG_CONC =""
define BG_APPL =""
define BG_COMM =""
define BG_CONF =""
define BG_ADMI =""
define BG_SCHE =""
define BG_CLUS =""
define BG_QUEU =""
define BG_NETW =""
define BG_OTHE =""

define CH_ONCPU="."
define CH_USER ="U"
define CH_SYST ="S"
define CH_CONC ="C"
define CH_APPL ="A"
define CH_COMM ="M"
define CH_CONF ="F"
define CH_ADMI ="a"
define CH_SCHE ="s"
define CH_CLUS ="c"
define CH_QUEU ="q"
define CH_NETW ="n"
define CH_OTHE ="o"

define CH_EV1="1"
define CH_EV2="2"
define CH_EV3="3"
define CH_EV4="4"
define CH_EV5="5"
$cat em_8colors.sql
define BG_ONCPU="[42m"
define BG_USER ="[44m"
define BG_SYST ="[46m"
define BG_CONC ="[47m"
define BG_APPL ="[41m"
define BG_COMM ="[43m"
define BG_CONF ="[40m"
define BG_ADMI ="[34m"
define BG_SCHE ="[36m"
define BG_CLUS ="[31m"
define BG_QUEU ="[33m"
define BG_NETW ="[35m"
define BG_OTHE ="[45m"

define CH_ONCPU=" "
define CH_USER =" "
define CH_SYST =" "
define CH_CONC =" "
define CH_APPL =" "
define CH_COMM =" "
define CH_CONF =" "
define CH_ADMI ="a"
define CH_SCHE ="s"
define CH_CLUS ="c"
define CH_QUEU ="q"
define CH_NETW ="n"
define CH_OTHE ="o"

define CH_EV1=" "
define CH_EV2=" "
define CH_EV3=" "
define CH_EV4=" "
define CH_EV5=" "
$cat em_256colors.sql
define BG_ONCPU="[42m"
define BG_USER ="[44m"
define BG_SYST ="[46m"
define BG_CONC ="[48;5;1m"
define BG_APPL ="[48;5;9m"
define BG_COMM ="[48;5;202m"
define BG_CONF ="[48;5;52m"
define BG_ADMI ="[48;5;95m"
define BG_SCHE ="[48;5;157m"
define BG_CLUS ="[48;5;230m"
define BG_QUEU ="[48;5;144m"
define BG_NETW ="[48;5;131m"
define BG_OTHE ="[48;5;224m"

define CH_ONCPU=" "
define CH_USER =" "
define CH_SYST =" "
define CH_CONC =" "
define CH_APPL =" "
define CH_COMM =" "
define CH_CONF =" "
define CH_ADMI =" "
define CH_SCHE =" "
define CH_CLUS =" "
define CH_QUEU =" "
define CH_NETW =" "
define CH_OTHE =" "

define CH_EV1=" "
define CH_EV2=" "
define CH_EV3=" "
define CH_EV4=" "
define CH_EV5=" "
$cat em.sql
/*
This script emulates the EM performance chart
Created by Eduardo Claro, 2018-03-14
Last changes on 2018-03-20

WARNING: It uses ASH repository, so YOU NEED Diagnostics Pack

It can be used to display the Wait Class or the 5 main events of a specific Wait Class

Parameters (can be passed in any order in the form parameter=value)

	class    : specify the Wait Class (just the 3 first letters are enough) to show their events breakdown. If not specified, show the Wait Classes breakdown.
	begin    : begin date as a DATE expression, like sysdate-1/24.
	end      : end   date as a DATE expression, like sysdate-1/24.
	minutes  : if a positive number is provided, the period is defined starting with &BEGIN and ending the number of minutes after that (&END parameter is ignored).
	           if a negative number is provided, the period is defined starting the number of minutes before now (sysdate) and ending now (&BEGIN and &END are ignored).
	view     : the ASH view to query. Enter D to query DBA_ACTIVE_SESS_HISTORY. Any other value will use GV$ACTIVE_SESSION_HISTORY.
	group    : group (Y) or not (N) the rows by minute.
	color    : define if you want to see characters instead of colors (no), 8 colors (8) or 256 colors (256).
	show     : SHOW (Y) or not (N) the number of sessions for each Wait Class or Event.
	mult     : the multiplier to define how many boxes will be painted for each Average Active Session. If mult=2 and there are 3 sessions, 6 (3 * 2) boxes will be painted.
	zeros    : show (Y) or not (N) the samples that have no active session (especially useful when showing just one Wait Class
	inst     : specify an Instance to show (for RAC environments). NULL show all instances.

Example:

	@em
	@em class=app view=D
	@em group=N
	@em begin=sysdate-3/24 show=n

Attention:
	If the script seems to hang waiting for something, it is probably due to the SQL*Plus number variables (&1-&16) not be defined
	The SQL*Plus variables &1-&16 must be defined BEFORE running this script
	It is usually done by putting some statements in the login.sql, like this:
		set termout off
		COLUMN  1  NEW_VALUE  1    noprint
		COLUMN  2  NEW_VALUE  2    noprint
		COLUMN  3  NEW_VALUE  3    noprint
		...
		SELECT '' as "1",'' as "2",'' as "3" ... FROM dual;
		set termout on
*/

-- Default values for the parameters
define CLASS=""
define BEGIN="sysdate-1/24"
define END="sysdate"
define MINUTES=""
define VIEW=""
define GROUP=Y
define COLOR=256
define SHOW=N
define MULT=1
define ZEROS=Y
define INST=""

-- Parse the received parameters
@@parser

clear breaks
set echo off timing off ver off feed off

define RESET =[0m
alter session set nls_Date_format="dd-MON hh24:mi:ss";
set pages 500

set term off

col MULT new_value MULT
col xVIEW new_value VIEW
col xEND new_value END
col xBEGIN new_value BEGIN
col QUERY new_value QUERY
col INSTCOL new_value INSTCOL
col SCRIPT new_value SCRIPT
select
--	case when upper('&GROUP') = 'Y' then &MULT else 1 end MULT,
	case when upper('&VIEW') = 'D' then 'DBA_HIST_ACTIVE_SESS_HISTORY' else 'GV$ACTIVE_SESSION_HISTORY' end xVIEW,
	case when upper('&VIEW') = 'D' then 'INSTANCE_NUMBER' else 'INST_ID' end INSTCOL,
	case when to_number('&MINUTES') < 0 then q'[trunc(sysdate,'mi')+(&MINUTES./24/60)]' else q'[&BEGIN]' end xBEGIN,
	case when '&MINUTES' is null then q'[&END]' when to_number('&MINUTES') < 0 then 'sysdate' else q'[&BEGIN.+&MINUTES./24/60]' end xEND,
	case when '&CLASS' IS NULL then 'ash_class_final' else 'ash_event_final' end || case when upper('&SHOW') = 'N' then '2' end QUERY,
	case when '&COLOR' in ('8','256') then 'em_&COLOR.colors' else 'em_nocolors' end SCRIPT
from dual;

-- Set the colors
@@&SCRIPT

col MINUTE head "TIME"
col chart for a400 trunc
col ONCPU  for 99.9 head "OnCPU"
col USERIO for 99.9 head "UsrIO"
col SYSTIO for 99.9 head "SysIO"
col CONC   for 99.9 head "Conc"
col APPLIC for 99.9 head "Appl"
col COMM   for 99.9 head "Comm"
col CONF   for 99.9 head "Conf"
col ADMIN  for 99.9 head "Admin"
col SCHED  for 99.9 head "Sched"
col CLUST  for 99.9 head "Clust"
col QUEUE  for 99.9 head "Queue"
col NETW   for 99.9 head "Netwk"
col OTHER  for 99.9 head "Other"
col TOTAL  for 99.9 head "TOTAL"
col EV1  for 99.9 
col EV2  for 99.9 
col EV3  for 99.9 
col EV4  for 99.9 
col EV5  for 99.9 

-- Using DELETE instead of TRUNCATE just for not to end a transaction in course
delete from plan_table where STATEMENT_ID = 'EM-SQLPLUS'
;
insert into plan_table (STATEMENT_ID, TIMESTAMP, REMARKS)
select
	'EM-SQLPLUS',
	cast(sample_time as date) sample_time, --remove sub-seconds difference of the RAC instances
	'ALL SAMPLES' wait_class
from &VIEW
where sample_time between &begin and &end
and ('&INST' is null OR &INSTCOL = nvl('&INST','&INSTCOL'))
;
insert into plan_table (STATEMENT_ID, TIMESTAMP, REMARKS, COST)
select
	'EM-SQLPLUS',
	cast(sample_time as date) sample_time, --remove sub-seconds difference of the RAC instances
	nvl(wait_class,'On CPU') wait_class, count(*) sessions
from &VIEW
where '&class' IS NULL and sample_time between &begin and &end 
and ('&INST' is null OR &INSTCOL = nvl('&INST','&INSTCOL'))
group by cast(sample_time as date), wait_class
;
insert into plan_table (STATEMENT_ID, TIMESTAMP, REMARKS, PLAN_ID, COST)
select
	'EM-SQLPLUS',
	cast(sample_time as date) sample_time, --remove sub-seconds difference of the RAC instances
	event, event_id, count(*) sessions
from &VIEW
where '&class' IS NOT NULL and sample_time between &begin and &end 
	and wait_class = (select wait_class from v$event_name where upper(wait_class) like upper('&class%') and rownum = 1)
and ('&INST' is null OR &INSTCOL = nvl('&INST','&INSTCOL'))
group by cast(sample_time as date), event, event_id
;
set term on

WITH
ash_allsamples AS (
	select distinct
		TIMESTAMP sample_time
	from plan_table
	where STATEMENT_ID = 'EM-SQLPLUS' and REMARKS = 'ALL SAMPLES'
),
ash_class AS (
	select * from (
		select TIMESTAMP sample_time, REMARKS wait_class, COST sessions,
			count(distinct TIMESTAMP) over (partition by trunc(TIMESTAMP,'MI')) SAMPLES_PER_MIN
		from plan_table
		where STATEMENT_ID = 'EM-SQLPLUS'
	) where wait_class  'ALL SAMPLES'
),
ash_event AS (
	select * from (
		select TIMESTAMP sample_time, REMARKS event, PLAN_ID event_id, COST sessions,
			count(distinct TIMESTAMP) over (partition by trunc(TIMESTAMP,'MI')) SAMPLES_PER_MIN
		from plan_table
		where STATEMENT_ID = 'EM-SQLPLUS'
	) where event  'ALL SAMPLES'
),
ash_mainevents AS (
	select 
		event, position
	from (
		select 
			event,
			rank() over (order by sum(sessions) desc, event_id) position
		from ash_event
		group by event, event_id
		order by sum(sessions) desc
	)
	where position  0)
/

col CHART for a30
col xGROUP head "WAIT CLASS" for a20 trunc
SELECT * FROM (
	select 'On CPU'         xGROUP, '&BG_ONCPU' || lpad('&CH_ONCPU', 5 , '&CH_ONCPU') || '&RESET' chart from dual UNION ALL
	select 'User I/O'       xGROUP, '&BG_USER'  || lpad('&CH_USER' , 5 , '&CH_USER' ) || '&RESET' chart from dual UNION ALL
	select 'System I/O'     xGROUP, '&BG_SYST'  || lpad('&CH_SYST' , 5 , '&CH_SYST' ) || '&RESET' chart from dual UNION ALL
	select 'Application'    xGROUP, '&BG_APPL'  || lpad('&CH_APPL' , 5 , '&CH_APPL' ) || '&RESET' chart from dual UNION ALL
	select 'Concurrency'    xGROUP, '&BG_CONC'  || lpad('&CH_CONC' , 5 , '&CH_CONC' ) || '&RESET' chart from dual UNION ALL
	select 'Configuration'  xGROUP, '&BG_CONF'  || lpad('&CH_CONF' , 5 , '&CH_CONF' ) || '&RESET' chart from dual UNION ALL
	select 'Commit'         xGROUP, '&BG_COMM'  || lpad('&CH_COMM' , 5 , '&CH_COMM' ) || '&RESET' chart from dual UNION ALL
	select 'Administrative' xGROUP, '&BG_ADMI'  || lpad('&CH_ADMI' , 5 , '&CH_ADMI' ) || '&RESET' chart from dual UNION ALL
	select 'Scheduler'      xGROUP, '&BG_SCHE'  || lpad('&CH_SCHE' , 5 , '&CH_SCHE' ) || '&RESET' chart from dual UNION ALL
	select 'Cluster'        xGROUP, '&BG_CLUS'  || lpad('&CH_CLUS' , 5 , '&CH_CLUS' ) || '&RESET' chart from dual UNION ALL
	select 'Queueing'       xGROUP, '&BG_QUEU'  || lpad('&CH_QUEU' , 5 , '&CH_QUEU' ) || '&RESET' chart from dual UNION ALL
	select 'Network'        xGROUP, '&BG_NETW'  || lpad('&CH_NETW' , 5 , '&CH_NETW' ) || '&RESET' chart from dual UNION ALL
	select 'Other'          xGROUP, '&BG_OTHE'  || lpad('&CH_OTHE' , 5 , '&CH_OTHE' ) || '&RESET' chart from dual 
) WHERE '&class' IS NULL
;
col xGROUP head "EVENT" for a64 trunc
WITH events AS (
	select 
		event, position
	from (
		select 
			REMARKS event,
			rank() over (order by SUM(COST) desc, PLAN_ID) position
		from plan_table
		where STATEMENT_ID = 'EM-SQLPLUS' and REMARKS  'ALL SAMPLES'
		group by REMARKS, PLAN_ID
		order by SUM(COST) desc
	)
	where position <= 5
)
select * from (
	select event   xGROUP, '&BG_USER'  || lpad('&CH_EV1' , 5 , '&CH_EV1' ) || '&RESET' chart from events WHERE position = 1 UNION ALL
	select event   xGROUP, '&BG_SYST'  || lpad('&CH_EV2' , 5 , '&CH_EV2' ) || '&RESET' chart from events WHERE position = 2 UNION ALL
	select event   xGROUP, '&BG_APPL'  || lpad('&CH_EV3' , 5 , '&CH_EV3' ) || '&RESET' chart from events WHERE position = 3 UNION ALL
	select event   xGROUP, '&BG_CONC'  || lpad('&CH_EV4' , 5 , '&CH_EV4' ) || '&RESET' chart from events WHERE position = 4 UNION ALL
	select event   xGROUP, '&BG_CONF'  || lpad('&CH_EV5' , 5 , '&CH_EV5' ) || '&RESET' chart from events WHERE position = 5 UNION ALL
	select 'Other' xGROUP, '&BG_OTHE'  || lpad('&CH_OTHE' , 5 , '&CH_OTHE' ) || '&RESET' chart from dual 
) WHERE '&class' IS NOT NULL
;

define 1=""
define 2=""
define 3=""
define 4=""
define 5=""
define 6=""
define 7=""
define 8=""
define 9=""
define 10=""
define 11=""
define 12=""
define 13=""
define 14=""
define 15=""
define 16=""

Um gráfico do Enterprise Manager no SQL*Plus

Português/English

Depois de mostrar como usar 256 cores and como definir parâmetros no SQL*Plus, eu agora apresento um script bem interessante (pelo menos pra mim ;-)) que eu criei e imita o gráfico principal do Enterprise Manager no SQL*Plus.

O script constrói um “gráfico” usando blocos coloridos (ou caracteres) para mostrar sessões ativas por Wait Class, or por evento para uma Wait Class fornecida.

O script pode receber vários parâmetros que controlam o período e outros detalhes que eu explico abaixo.

Note que o script coleta informação do repositório do ASH, e portanto você deve ter licença do Oracle Diagnostics Pack para utilizá-lo.

Aqui estão os parâmetros que você pode fornecer:

  • class: define uma Wait Class (apenas as 3 primeiras letras são suficientes) para mostrar seus eventos. Se não especificado, mostra as sessões para cadaWait Class.
  • begin: data inicial em uma expressão tipo DATE, como em sysdate-1/24.
  • end: data final em uma expressão tipo DATE, como em sysdate-1/24.
  • minutes: se for um número positivo, o período é definido começando em &BEGIN e terminando após o número de minutos fornecido (o parâmetro &END é ignorado).
    se for um número negativo, o período é definido começando o número de minutos antes de agora (sysdate) e terminando agora (&BEGIN e &END são ignorados).
  • view: a view do ASH a ser consultada. Entre D para consultar a DBA_ACTIVE_SESS_HISTORY. Qualquer outro valor vai usar a view GV$ACTIVE_SESSION_HISTORY.
  • group: agrupa (Y) ou não (N) as linhas por minuto.
  • color: define se você quer ver characteres (N), 8 cores (8) ou 256 cores (256).
  • show: mostra (Y) ou não (N) a quantidade de sessões para cadaWait Class ou evento.
  • mult: o multiplicador que define quantos blocos coloridos serão mostrados para cada sessão ativa. Se mult=2 e há 3 sessões, 6 (3 * 2) blocos serão mostrados.
  • zeros: mostra (Y) ou não (N) os momentos em que não houve sessão ativa (especialmente útil quando mostrando os eventos de uma Wait Class).
  • inst: especifique uma instância do RAC para mostrar. NULL show all instances.

Aqui estão alguns exemplos de como executar o script:

-- Execute the script with all the default parameters:
@em

-- Show the events break down for the APPLICATION wait class, from DBA_HIST ash view, don't show samples with ZERO active sessions
@em class=app view=D zeros=n

-- Show the last 120 minutes of data, don't group by minute
@em minutes=-120 group=N

-- Show the chart from yesterday, don't show the numbers of each wait class
@em begin=trunc(sysdate-1) end=trunc(sysdate) show=n

-- Show the chart with characters instead of colors, show 3 characters for each active session
@em color=n mult=3

E aqui alguns exemplos de resultado:

em main

O gráfico principal mostrando alguns problemas de Aplicação

em app

Aprofundando para verificar que o problema era de lock de linhas

em1

Outro gráfico principal, agora mostrando predominância de User I/O

em2

Quebrando os eventos de User I/O

em char

Usando caracteres ao invés de cores

No início do script, logo depois do cabeçalho, você vai encontrar os valores default para cada parâmetro. Você pode mudar os valores como quiser.

Note que o script principal pode chamar alguns auxiliares: o meu parser (leia isto) e um dos três para definir as cores (ou caracteres) que serão usados (leia isto).

Note também que eu estou usando a PLAN_TABLE para salvar os dados do ASH que em seguida vou processar, pra evitar de ter que lê-los várias vezes do repositório. Essa solução é utilizada nas ferramentas eDB360 and SQLd360 tools, dos meus amigos Carlos Sierra e Mauro Pagano.

Aqui estão os scripts. Copie o conteúdo e salve-os no mesmo diretório. Então rode o em.sql como explicado acima. Você também pode baixar os scripts do github.

Qualquer comentário, sugestão, correção, por favor me avise.

Divirtam-se!!!!

$cat em_nocolors.sql
define BG_ONCPU=""
define BG_USER =""
define BG_SYST =""
define BG_CONC =""
define BG_APPL =""
define BG_COMM =""
define BG_CONF =""
define BG_ADMI =""
define BG_SCHE =""
define BG_CLUS =""
define BG_QUEU =""
define BG_NETW =""
define BG_OTHE =""

define CH_ONCPU="."
define CH_USER ="U"
define CH_SYST ="S"
define CH_CONC ="C"
define CH_APPL ="A"
define CH_COMM ="M"
define CH_CONF ="F"
define CH_ADMI ="a"
define CH_SCHE ="s"
define CH_CLUS ="c"
define CH_QUEU ="q"
define CH_NETW ="n"
define CH_OTHE ="o"

define CH_EV1="1"
define CH_EV2="2"
define CH_EV3="3"
define CH_EV4="4"
define CH_EV5="5"
$cat em_8colors.sql
define BG_ONCPU="[42m"
define BG_USER ="[44m"
define BG_SYST ="[46m"
define BG_CONC ="[47m"
define BG_APPL ="[41m"
define BG_COMM ="[43m"
define BG_CONF ="[40m"
define BG_ADMI ="[34m"
define BG_SCHE ="[36m"
define BG_CLUS ="[31m"
define BG_QUEU ="[33m"
define BG_NETW ="[35m"
define BG_OTHE ="[45m"

define CH_ONCPU=" "
define CH_USER =" "
define CH_SYST =" "
define CH_CONC =" "
define CH_APPL =" "
define CH_COMM =" "
define CH_CONF =" "
define CH_ADMI ="a"
define CH_SCHE ="s"
define CH_CLUS ="c"
define CH_QUEU ="q"
define CH_NETW ="n"
define CH_OTHE ="o"

define CH_EV1=" "
define CH_EV2=" "
define CH_EV3=" "
define CH_EV4=" "
define CH_EV5=" "
$cat em_256colors.sql
define BG_ONCPU="[42m"
define BG_USER ="[44m"
define BG_SYST ="[46m"
define BG_CONC ="[48;5;1m"
define BG_APPL ="[48;5;9m"
define BG_COMM ="[48;5;202m"
define BG_CONF ="[48;5;52m"
define BG_ADMI ="[48;5;95m"
define BG_SCHE ="[48;5;157m"
define BG_CLUS ="[48;5;230m"
define BG_QUEU ="[48;5;144m"
define BG_NETW ="[48;5;131m"
define BG_OTHE ="[48;5;224m"

define CH_ONCPU=" "
define CH_USER =" "
define CH_SYST =" "
define CH_CONC =" "
define CH_APPL =" "
define CH_COMM =" "
define CH_CONF =" "
define CH_ADMI =" "
define CH_SCHE =" "
define CH_CLUS =" "
define CH_QUEU =" "
define CH_NETW =" "
define CH_OTHE =" "

define CH_EV1=" "
define CH_EV2=" "
define CH_EV3=" "
define CH_EV4=" "
define CH_EV5=" "
$cat em.sql
/*
This script emulates the EM performance chart
Created by Eduardo Claro, 2018-03-14
Last changes on 2018-03-20

WARNING: It uses ASH repository, so YOU NEED Diagnostics Pack

It can be used to display the Wait Class or the 5 main events of a specific Wait Class

Parameters (can be passed in any order in the form parameter=value)

	class    : specify the Wait Class (just the 3 first letters are enough) to show their events breakdown. If not specified, show the Wait Classes breakdown.
	begin    : begin date as a DATE expression, like sysdate-1/24.
	end      : end   date as a DATE expression, like sysdate-1/24.
	minutes  : if a positive number is provided, the period is defined starting with &BEGIN and ending the number of minutes after that (&END parameter is ignored).
	           if a negative number is provided, the period is defined starting the number of minutes before now (sysdate) and ending now (&BEGIN and &END are ignored).
	view     : the ASH view to query. Enter D to query DBA_ACTIVE_SESS_HISTORY. Any other value will use GV$ACTIVE_SESSION_HISTORY.
	group    : group (Y) or not (N) the rows by minute.
	color    : define if you want to see characters instead of colors (no), 8 colors (8) or 256 colors (256).
	show     : SHOW (Y) or not (N) the number of sessions for each Wait Class or Event.
	mult     : the multiplier to define how many boxes will be painted for each Average Active Session. If mult=2 and there are 3 sessions, 6 (3 * 2) boxes will be painted.
	zeros    : show (Y) or not (N) the samples that have no active session (especially useful when showing just one Wait Class
	inst     : specify an Instance to show (for RAC environments). NULL show all instances.

Example:

	@em
	@em class=app view=D
	@em group=N
	@em begin=sysdate-3/24 show=n

Attention:
	If the script seems to hang waiting for something, it is probably due to the SQL*Plus number variables (&1-&16) not be defined
	The SQL*Plus variables &1-&16 must be defined BEFORE running this script
	It is usually done by putting some statements in the login.sql, like this:
		set termout off
		COLUMN  1  NEW_VALUE  1    noprint
		COLUMN  2  NEW_VALUE  2    noprint
		COLUMN  3  NEW_VALUE  3    noprint
		...
		SELECT '' as "1",'' as "2",'' as "3" ... FROM dual;
		set termout on
*/

-- Default values for the parameters
define CLASS=""
define BEGIN="sysdate-1/24"
define END="sysdate"
define MINUTES=""
define VIEW=""
define GROUP=Y
define COLOR=256
define SHOW=N
define MULT=1
define ZEROS=Y
define INST=""

-- Parse the received parameters
@@parser

clear breaks
set echo off timing off ver off feed off

define RESET =[0m
alter session set nls_Date_format="dd-MON hh24:mi:ss";
set pages 500

set term off

col MULT new_value MULT
col xVIEW new_value VIEW
col xEND new_value END
col xBEGIN new_value BEGIN
col QUERY new_value QUERY
col INSTCOL new_value INSTCOL
col SCRIPT new_value SCRIPT
select
--	case when upper('&GROUP') = 'Y' then &MULT else 1 end MULT,
	case when upper('&VIEW') = 'D' then 'DBA_HIST_ACTIVE_SESS_HISTORY' else 'GV$ACTIVE_SESSION_HISTORY' end xVIEW,
	case when upper('&VIEW') = 'D' then 'INSTANCE_NUMBER' else 'INST_ID' end INSTCOL,
	case when to_number('&MINUTES') < 0 then q'[trunc(sysdate,'mi')+(&MINUTES./24/60)]' else q'[&BEGIN]' end xBEGIN,
	case when '&MINUTES' is null then q'[&END]' when to_number('&MINUTES') < 0 then 'sysdate' else q'[&BEGIN.+&MINUTES./24/60]' end xEND,
	case when '&CLASS' IS NULL then 'ash_class_final' else 'ash_event_final' end || case when upper('&SHOW') = 'N' then '2' end QUERY,
	case when '&COLOR' in ('8','256') then 'em_&COLOR.colors' else 'em_nocolors' end SCRIPT
from dual;

-- Set the colors
@@&SCRIPT

col MINUTE head "TIME"
col chart for a400 trunc
col ONCPU  for 99.9 head "OnCPU"
col USERIO for 99.9 head "UsrIO"
col SYSTIO for 99.9 head "SysIO"
col CONC   for 99.9 head "Conc"
col APPLIC for 99.9 head "Appl"
col COMM   for 99.9 head "Comm"
col CONF   for 99.9 head "Conf"
col ADMIN  for 99.9 head "Admin"
col SCHED  for 99.9 head "Sched"
col CLUST  for 99.9 head "Clust"
col QUEUE  for 99.9 head "Queue"
col NETW   for 99.9 head "Netwk"
col OTHER  for 99.9 head "Other"
col TOTAL  for 99.9 head "TOTAL"
col EV1  for 99.9 
col EV2  for 99.9 
col EV3  for 99.9 
col EV4  for 99.9 
col EV5  for 99.9 

-- Using DELETE instead of TRUNCATE just for not to end a transaction in course
delete from plan_table where STATEMENT_ID = 'EM-SQLPLUS'
;
insert into plan_table (STATEMENT_ID, TIMESTAMP, REMARKS)
select
	'EM-SQLPLUS',
	cast(sample_time as date) sample_time, --remove sub-seconds difference of the RAC instances
	'ALL SAMPLES' wait_class
from &VIEW
where sample_time between &begin and &end
and ('&INST' is null OR &INSTCOL = nvl('&INST','&INSTCOL'))
;
insert into plan_table (STATEMENT_ID, TIMESTAMP, REMARKS, COST)
select
	'EM-SQLPLUS',
	cast(sample_time as date) sample_time, --remove sub-seconds difference of the RAC instances
	nvl(wait_class,'On CPU') wait_class, count(*) sessions
from &VIEW
where '&class' IS NULL and sample_time between &begin and &end 
and ('&INST' is null OR &INSTCOL = nvl('&INST','&INSTCOL'))
group by cast(sample_time as date), wait_class
;
insert into plan_table (STATEMENT_ID, TIMESTAMP, REMARKS, PLAN_ID, COST)
select
	'EM-SQLPLUS',
	cast(sample_time as date) sample_time, --remove sub-seconds difference of the RAC instances
	event, event_id, count(*) sessions
from &VIEW
where '&class' IS NOT NULL and sample_time between &begin and &end 
	and wait_class = (select wait_class from v$event_name where upper(wait_class) like upper('&class%') and rownum = 1)
and ('&INST' is null OR &INSTCOL = nvl('&INST','&INSTCOL'))
group by cast(sample_time as date), event, event_id
;
set term on

WITH
ash_allsamples AS (
	select distinct
		TIMESTAMP sample_time
	from plan_table
	where STATEMENT_ID = 'EM-SQLPLUS' and REMARKS = 'ALL SAMPLES'
),
ash_class AS (
	select * from (
		select TIMESTAMP sample_time, REMARKS wait_class, COST sessions,
			count(distinct TIMESTAMP) over (partition by trunc(TIMESTAMP,'MI')) SAMPLES_PER_MIN
		from plan_table
		where STATEMENT_ID = 'EM-SQLPLUS'
	) where wait_class  'ALL SAMPLES'
),
ash_event AS (
	select * from (
		select TIMESTAMP sample_time, REMARKS event, PLAN_ID event_id, COST sessions,
			count(distinct TIMESTAMP) over (partition by trunc(TIMESTAMP,'MI')) SAMPLES_PER_MIN
		from plan_table
		where STATEMENT_ID = 'EM-SQLPLUS'
	) where event  'ALL SAMPLES'
),
ash_mainevents AS (
	select 
		event, position
	from (
		select 
			event,
			rank() over (order by sum(sessions) desc, event_id) position
		from ash_event
		group by event, event_id
		order by sum(sessions) desc
	)
	where position  0)
/

col CHART for a30
col xGROUP head "WAIT CLASS" for a20 trunc
SELECT * FROM (
	select 'On CPU'         xGROUP, '&BG_ONCPU' || lpad('&CH_ONCPU', 5 , '&CH_ONCPU') || '&RESET' chart from dual UNION ALL
	select 'User I/O'       xGROUP, '&BG_USER'  || lpad('&CH_USER' , 5 , '&CH_USER' ) || '&RESET' chart from dual UNION ALL
	select 'System I/O'     xGROUP, '&BG_SYST'  || lpad('&CH_SYST' , 5 , '&CH_SYST' ) || '&RESET' chart from dual UNION ALL
	select 'Application'    xGROUP, '&BG_APPL'  || lpad('&CH_APPL' , 5 , '&CH_APPL' ) || '&RESET' chart from dual UNION ALL
	select 'Concurrency'    xGROUP, '&BG_CONC'  || lpad('&CH_CONC' , 5 , '&CH_CONC' ) || '&RESET' chart from dual UNION ALL
	select 'Configuration'  xGROUP, '&BG_CONF'  || lpad('&CH_CONF' , 5 , '&CH_CONF' ) || '&RESET' chart from dual UNION ALL
	select 'Commit'         xGROUP, '&BG_COMM'  || lpad('&CH_COMM' , 5 , '&CH_COMM' ) || '&RESET' chart from dual UNION ALL
	select 'Administrative' xGROUP, '&BG_ADMI'  || lpad('&CH_ADMI' , 5 , '&CH_ADMI' ) || '&RESET' chart from dual UNION ALL
	select 'Scheduler'      xGROUP, '&BG_SCHE'  || lpad('&CH_SCHE' , 5 , '&CH_SCHE' ) || '&RESET' chart from dual UNION ALL
	select 'Cluster'        xGROUP, '&BG_CLUS'  || lpad('&CH_CLUS' , 5 , '&CH_CLUS' ) || '&RESET' chart from dual UNION ALL
	select 'Queueing'       xGROUP, '&BG_QUEU'  || lpad('&CH_QUEU' , 5 , '&CH_QUEU' ) || '&RESET' chart from dual UNION ALL
	select 'Network'        xGROUP, '&BG_NETW'  || lpad('&CH_NETW' , 5 , '&CH_NETW' ) || '&RESET' chart from dual UNION ALL
	select 'Other'          xGROUP, '&BG_OTHE'  || lpad('&CH_OTHE' , 5 , '&CH_OTHE' ) || '&RESET' chart from dual 
) WHERE '&class' IS NULL
;
col xGROUP head "EVENT" for a64 trunc
WITH events AS (
	select 
		event, position
	from (
		select 
			REMARKS event,
			rank() over (order by SUM(COST) desc, PLAN_ID) position
		from plan_table
		where STATEMENT_ID = 'EM-SQLPLUS' and REMARKS  'ALL SAMPLES'
		group by REMARKS, PLAN_ID
		order by SUM(COST) desc
	)
	where position <= 5
)
select * from (
	select event   xGROUP, '&BG_USER'  || lpad('&CH_EV1' , 5 , '&CH_EV1' ) || '&RESET' chart from events WHERE position = 1 UNION ALL
	select event   xGROUP, '&BG_SYST'  || lpad('&CH_EV2' , 5 , '&CH_EV2' ) || '&RESET' chart from events WHERE position = 2 UNION ALL
	select event   xGROUP, '&BG_APPL'  || lpad('&CH_EV3' , 5 , '&CH_EV3' ) || '&RESET' chart from events WHERE position = 3 UNION ALL
	select event   xGROUP, '&BG_CONC'  || lpad('&CH_EV4' , 5 , '&CH_EV4' ) || '&RESET' chart from events WHERE position = 4 UNION ALL
	select event   xGROUP, '&BG_CONF'  || lpad('&CH_EV5' , 5 , '&CH_EV5' ) || '&RESET' chart from events WHERE position = 5 UNION ALL
	select 'Other' xGROUP, '&BG_OTHE'  || lpad('&CH_OTHE' , 5 , '&CH_OTHE' ) || '&RESET' chart from dual 
) WHERE '&class' IS NOT NULL
;

define 1=""
define 2=""
define 3=""
define 4=""
define 5=""
define 6=""
define 7=""
define 8=""
define 9=""
define 10=""
define 11=""
define 12=""
define 13=""
define 14=""
define 15=""
define 16=""

 

Advertisements

SQL*Plus 256 Colours in terminal

Português/English

 

As I use a lot SQL*Plus – and I like it 😉 – sometimes I try to use some interesting tips to make my outputs more readable and interesting.

If you use a terminal emulator (like putty in Windows or most terminal emulators in Unix/Linux/Mac environments), you know it has color capabilities. And maybe you probably know that SQL*Plus also can access this capabilities, as explained here and here.

I have been using colours is scripts for some time, but just the 8-color capability. But it happens that some terminals have instead the capability to show up to 256 different colours, which is cool!

The fish.sql script from Tanel Poder (second link above) exploits very well these capability. Stay tuned!

Here I just provide a script that shows all that 256 colours as background and foreground, to ease your job of picking which ones you want to use in your scripts.

$cat term_256colors.sql
/* Show all the 256 colors (if the terminal have this capability) */

col color for a200

prompt
prompt Background colors. USAGE: chr(27) || '[48;5;' || number
define x=48
select
	'id=' || to_char(4 * (level-1) + 1,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 1 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 2,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 2 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 3,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 3 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 4,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 4 ) || 'm' || ' COLOR ' || chr(27) || '[0m' color
from dual connect by level <= 256/4;

prompt
prompt Foreground colors USAGE: chr(27) || '[38;5;' || number
define x=38
select
	'id=' || to_char(4 * (level-1) + 1,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 1 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 2,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 2 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 3,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 3 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 4,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 4 ) || 'm' || ' COLOR ' || chr(27) || '[0m' color
from dual connect by level <= 256/4;

Of course it will only show the colours if you are using a terminal that has this capability.

Here you have an excerpt of the output:

Screen Shot 2018-03-20 at 14.39.18

Screen Shot 2018-03-20 at 14.39.42

Soon you will se a nice script that I created using such capability.

That’s it! I hope you enjoy 😉 .


256 Cores no SQL*Plus usando um terminal

Português/English

Como eu uso bastante o SQL*Plus – e gosto 😉 – às vezes tento usar algumas dicas interessantes para tornar meus resultados mais legíveis e interessantes.

Se você usa um emulador de terminal (como o putty no Windows ou a maioria dos emuladores de terminal em Unix/Linux/Mac), você sabe que ele pode mostrar cores. E você também provavelmente sabe que o SQL*Plus pode também usar essa capacidade, como demonstrado aqui e aqui.

Eu tenho usado há algum tempo cores em scripts, mas apenas a capacidade de 8 cores. Mas acontece que alguns terminais têm a capacidade de mostrar 256 cores ao invés de 8, o que é bem legal!

O script fish.sql do Tanel Poder (segundo link acima) explora muito bem esse recurso.

Aqui eu demonstro um script que mostra as 256 cores de fundo e de frente, para facilitar o seu trabalho de escolher as que deseja utilizar nos seus scripts.

$cat term_256colors.sql
/* Show all the 256 colors (if the terminal have this capability) */

col color for a200

prompt
prompt Background colors. USAGE: chr(27) || '[48;5;' || number
define x=48
select
	'id=' || to_char(4 * (level-1) + 1,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 1 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 2,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 2 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 3,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 3 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 4,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 4 ) || 'm' || ' COLOR ' || chr(27) || '[0m' color
from dual connect by level <= 256/4;

prompt
prompt Foreground colors USAGE: chr(27) || '[38;5;' || number
define x=38
select
	'id=' || to_char(4 * (level-1) + 1,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 1 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 2,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 2 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 3,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 3 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 4,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 4 ) || 'm' || ' COLOR ' || chr(27) || '[0m' color
from dual connect by level <= 256/4;

Claro que as cores só vão aparecer se você utilizar um terminal que tem essa capacidade.

Aqui você pode ver um pedaço do resultado:

Screen Shot 2018-03-20 at 14.39.18

Screen Shot 2018-03-20 at 14.39.42

Em breve você vai ver por aqui um script bem legal que utiliza estes recursos. Fique ligado!

É isso! Espero que gostem 😉 .

A simple SQL*Plus parameter parser

Português/English

One of the things I like to do as a DBA is to write scripts to facilitate my daily work. Any experienced DBA has his own scripts toolset that is being built over time. Writing scripts help you to automate part of your job and also allow you to learn a lot.

Usually the scripts require or allow to pass some parameter values. For example, if a script show information about tables, I could build it to accept an OWNER and a TABLE_NAME and then show only those tables from that specific owner and with that kind of name.

My old scripts used to accept the parameters by using the ACCEPT SQL*Plus statement, like this:

accept table prompt 'Table Name: '

But this forces me to answer something that not always I want to. What if, for example, I want to see all the tables? I would need to press ENTER to bypass the parameter.

Well, recently I am changing my scripts and building new ones with a different approach: using command line parameters, that could be informed or not.

For example, I could call the script that show tables with a first parameter being the owner, and the second being the table. It would be called this way:

SQL> @tables HR EMP%

In this example I am asking to show the tables that starts with EMP from owner HR.

But there is a caveat in this approach: I need to pass the parameters in the exact order. Moreover, what if I want to define just the second parameter without providing the first?

This type of issue inspired me to write a short and simple script to parse SQL*Plus parameters. This is what I am presenting in this post.

The script simply process all the parameters received in the form “parameter=value” and define SQL*Plus variables with these values, that could then be used in your script. For example, if you provide a parameter as “SHOW=Y”, after the parser script to be executed you will have a variable SHOW with the value “Y”.

This allows you to provide parameters in any arbitrary order for your scripts.

In our tables script, I could provide just the table by calling that this way:

SQL> @tables table=EMP%

Of course, the script should call the parser to perform the “magic”, and then filter the tables with the new “&table” variable.

No more speech, let me show you the parser script and a sample of how to use it.

$cat parser.sql
/*
Parameter parser

This script transform all the parameters (&1-&16) into SQL*Plus variables
It allows you to pass parameters in no specific order in the format PAR=val

Created by Eduardo Claro, 2018-8-15
Last changes on 2018-03-20

Example: if you run the script and the values of &1 is "VAR1='X'", it will define the SQL*Plus variable VAR1 to 'X'

For this to work properly, the number variables should be pre-defined as NULL in the login.sql
*/

store set set.tmp replace
set term off
set feed off ver off echo off head off timing off
spool parser.tmp REPLACE
select
	case when q'!&1!'  is not null then q'!define &1!'  end || chr(10) ||
	case when q'!&2!'  is not null then q'!define &2!'  end || chr(10) ||
	case when q'!&3!'  is not null then q'!define &3!'  end || chr(10) ||
	case when q'!&4!'  is not null then q'!define &4!'  end || chr(10) ||
	case when q'!&5!'  is not null then q'!define &5!'  end || chr(10) ||
	case when q'!&6!'  is not null then q'!define &6!'  end || chr(10) ||
	case when q'!&7!'  is not null then q'!define &7!'  end || chr(10) ||
	case when q'!&8!'  is not null then q'!define &8!'  end || chr(10) ||
	case when q'!&9!'  is not null then q'!define &9!'  end || chr(10) ||
	case when q'!&10!' is not null then q'!define &10!' end || chr(10) ||
	case when q'!&11!' is not null then q'!define &11!' end || chr(10) ||
	case when q'!&12!' is not null then q'!define &12!' end || chr(10) ||
	case when q'!&13!' is not null then q'!define &13!' end || chr(10) ||
	case when q'!&14!' is not null then q'!define &14!' end || chr(10) ||
	case when q'!&15!' is not null then q'!define &15!' end || chr(10) ||
	case when q'!&16!' is not null then q'!define &16!'end statement
from dual;
spool off
@parser.tmp
@set.tmp
set term on

As you can see, the script simply creates some “DEFINE” statements and then executes them.

One important thing: in order for this to work properly, all the SQL*Plus variables from &1 until &16 need to be initially defined. You can do this by including in your login.sql script the following rows (as in SQL*Plus tips. #1):

-- Reset parameters
set termout off
COLUMN 1 NEW_VALUE 1 noprint
COLUMN 2 NEW_VALUE 2 noprint
COLUMN 3 NEW_VALUE 3 noprint
COLUMN 4 NEW_VALUE 4 noprint
COLUMN 5 NEW_VALUE 5 noprint
COLUMN 6 NEW_VALUE 6 noprint
COLUMN 7 NEW_VALUE 7 noprint
COLUMN 8 NEW_VALUE 8 noprint
COLUMN 9 NEW_VALUE 9 noprint
COLUMN 10 NEW_VALUE 10 noprint
COLUMN 11 NEW_VALUE 11 noprint
COLUMN 12 NEW_VALUE 12 noprint
COLUMN 13 NEW_VALUE 13 noprint
COLUMN 14 NEW_VALUE 14 noprint
COLUMN 15 NEW_VALUE 15 noprint
COLUMN 16 NEW_VALUE 16 noprint
SELECT '' "1", '' "5", '' "9",  '' "13"
      ,'' "2", '' "6", '' "10", '' "14"
      ,'' "3", '' "7", '' "11", '' "15"
      ,'' "4", '' "8", '' "12", '' "16"
FROM dual;
set termout on;

Here is the tables script that shows how to use the parser:

$cat tables.sql
-- Define the default parameters values
define OWNER=""
define TABLE=""

-- Call the parser to set the parameters received
@@parser

select owner, table_name, tablespace_name, temporary
from dba_tables
where owner like nvl(upper('&OWNER'),owner)
and table_name like nvl(upper('&TABLE'),table_name)
/

In the upcoming days I will present some interesting scripts I created that use this parser.

That’s it! I hope you enjoy 😉 .


Definindo parametros para scripts SQL*Plus

Português/English

Uma das coisas que eu gosto de fazer como DBA é escrever scripts que facilitam meu trabalho diário. Qualquer DBA experiente tem seu próprio conjunto de scripts que vai sendo construído ao longo do tempo. Escrever scripts ajuda a automatizar parte do trabalho diário e também nos permite aprender bastante.

Normalmente os scripts exigem ou permitem que se passe alguns valores como parâmetros. Por exemplo, se um script mostra informações de tabelas, eu poderia construí-lo para aceitar um OWNER e um TABLE_NAME e então mostrar somente as tabelas daquele “owner” específico e com aquele tipo de nome.

Meus scripts antigos aceitam parâmetros usando o comando ACCEPT do SQL*Plus, assim:

accept table prompt 'Table Name: '

Mas isto me força a responder algo que nem sempre quero. Por exemplo, e se eu quiser ver todas as tabelas? Eu teria que pressionar ENTER para ignorar o parâmetro.

Ultimamente eu tenho alterado meus scripts e escrito novos com uma abordagem diferente: usando parâmetros na linha de comando, que podem ser informados ou não.

Por exemplo, eu poderia chamar o script que mostra as tabelas com um primeiro parâmetro sendo o owner, e o segundo sendo a tabela. Ele seria chamado assim:

SQL> @tables HR EMP%

Neste exemplo estou pedindo pra ver as tabelas que começam com EMP do owner HR.

Mas há um problema com esta abordagem: eu preciso passar os parâmetros na ordem certa. Mais, como fazer se eu quiser definir apenas o segundo parâmetro sem fornecer o primeiro?

Este tipo de problema me inspirou para escrever um script (parser) simples e curto que simplesmente define parâmetros no SQL*Plus. É isto que estou apresentando neste post.

O script simplesmente processa todos os parâmetros recebidos na forma “parâmetro=valor” e define variáveis SQL*Plus com estes valores, que podem em seguida ser usadas pelo seu script. Por exemplo, se você fornece um parâmetro “SHOW=Y”, você terá uma variável SHOW com o valor “Y”.

Isto permite que os parâmetros sejam passados em qualquer ordem para os seus scripts.

Em nosso script de tabelas, eu poderia fornecer simplesmente a tabela chamando-o assim:

SQL> @tables table=EMP%

Claro, o script precisa chamar o parser para fazer a “mágica”, e então filtrar as tabelas com a variável “&table”.

Sem mais conversa, deixe-me mostrar o script parser e um exemplo de como usá-lo.

$cat parser.sql
/*
Parameter parser

This script transform all the parameters (&1-&16) into SQL*Plus variables
It allows you to pass parameters in no specific order in the format PAR=val

Created by Eduardo Claro, 2018-8-15
Last changes on 2018-03-20

Example: if you run the script and the values of &1 is "VAR1='X'", it will define the SQL*Plus variable VAR1 to 'X'

For this to work properly, the number variables should be pre-defined as NULL in the login.sql
*/

store set set.tmp replace
set term off
set feed off ver off echo off head off timing off
spool parser.tmp REPLACE
select
	case when q'!&1!'  is not null then q'!define &1!'  end || chr(10) ||
	case when q'!&2!'  is not null then q'!define &2!'  end || chr(10) ||
	case when q'!&3!'  is not null then q'!define &3!'  end || chr(10) ||
	case when q'!&4!'  is not null then q'!define &4!'  end || chr(10) ||
	case when q'!&5!'  is not null then q'!define &5!'  end || chr(10) ||
	case when q'!&6!'  is not null then q'!define &6!'  end || chr(10) ||
	case when q'!&7!'  is not null then q'!define &7!'  end || chr(10) ||
	case when q'!&8!'  is not null then q'!define &8!'  end || chr(10) ||
	case when q'!&9!'  is not null then q'!define &9!'  end || chr(10) ||
	case when q'!&10!' is not null then q'!define &10!' end || chr(10) ||
	case when q'!&11!' is not null then q'!define &11!' end || chr(10) ||
	case when q'!&12!' is not null then q'!define &12!' end || chr(10) ||
	case when q'!&13!' is not null then q'!define &13!' end || chr(10) ||
	case when q'!&14!' is not null then q'!define &14!' end || chr(10) ||
	case when q'!&15!' is not null then q'!define &15!' end || chr(10) ||
	case when q'!&16!' is not null then q'!define &16!'end statement
from dual;
spool off
@parser.tmp
@set.tmp
set term on

Como você pode ver, o script simplesmente cria alguns comandos “DEFINE” e depois os executa.

Importante: pra que isso funcione corretamente, todas as variáveis SQL*Plus de &1 até &16 precisam ser definidas inicialmente. Você pode fazer isso incluindo no seu login.sql as seguintes linhas (como em SQL*Plus tips. #1):

-- Reset parameters
set termout off
COLUMN 1 NEW_VALUE 1 noprint
COLUMN 2 NEW_VALUE 2 noprint
COLUMN 3 NEW_VALUE 3 noprint
COLUMN 4 NEW_VALUE 4 noprint
COLUMN 5 NEW_VALUE 5 noprint
COLUMN 6 NEW_VALUE 6 noprint
COLUMN 7 NEW_VALUE 7 noprint
COLUMN 8 NEW_VALUE 8 noprint
COLUMN 9 NEW_VALUE 9 noprint
COLUMN 10 NEW_VALUE 10 noprint
COLUMN 11 NEW_VALUE 11 noprint
COLUMN 12 NEW_VALUE 12 noprint
COLUMN 13 NEW_VALUE 13 noprint
COLUMN 14 NEW_VALUE 14 noprint
COLUMN 15 NEW_VALUE 15 noprint
COLUMN 16 NEW_VALUE 16 noprint
SELECT '' "1", '' "5", '' "9",  '' "13"
      ,'' "2", '' "6", '' "10", '' "14"
      ,'' "3", '' "7", '' "11", '' "15"
      ,'' "4", '' "8", '' "12", '' "16"
FROM dual;
set termout on;

Aqui está o script tables que mostra como usar o parser:

$cat tables.sql
-- Define the default parameters values
define OWNER=""
define TABLE=""

-- Call the parser to set the parameters received
@@parser

select owner, table_name, tablespace_name, temporary
from dba_tables
where owner like nvl(upper('&OWNER'),owner)
and table_name like nvl(upper('&TABLE'),table_name)
/

Nos próximos dias eu vou apresentar alguns scripts interessantes que eu criei e usam o parser.

É isso! Espero que gostem 😉 .