Formatting Query Results

--- The Print Table Procedure from Tom Kyte:
--- Reference: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1035431863958

Using this you can have the SPOOL vertically instead of the standard way... 

1.) Compile the procedure:

create or replace procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
execute immediate
'alter session set
nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;
/

2.) Example to use:

[oracle@hqsdboitt01bt / SID : kdbpp]$ sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 18 12:02:43 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SYS @  > set serveroutput on
SYS @  > exec print_table('select * from v$instance');
INSTANCE_NUMBER               : 1
INSTANCE_NAME                 : kdbpp
HOST_NAME                     : hqsdboitt01bt.kclx.kneip.com
VERSION                       : 11.2.0.4.0
STARTUP_TIME                  : 16-nov-2016 18:28:11
STATUS                        : OPEN
PARALLEL                      : NO
THREAD#                       : 1
ARCHIVER                      : STOPPED
LOG_SWITCH_WAIT               :
LOGINS                        : ALLOWED
SHUTDOWN_PENDING              : NO
DATABASE_STATUS               : ACTIVE
INSTANCE_ROLE                 : PRIMARY_INSTANCE
ACTIVE_STATE                  : NORMAL
BLOCKED                       : NO
-----------------

PL/SQL procedure successfully completed.

FORMAT_CALL_STACK

This function formats the current call stack. 
This can be used on any stored procedure or trigger to access the call stack. 
This can be useful for debugging.

http://docs.oracle.com/database/121/ARPLS/d_util.htm#ARPLS73240

1.) Procedure to execute FORMAT_CALL_STACK:

CREATE OR REPLACE PROCEDURE PR_FORMAT_CALL_STACK AS
BEGIN
  DBMS_OUTPUT.put_line('---------------------------------------------------');
  DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
  DBMS_OUTPUT.put_line('---------------------------------------------------');
END;
/

2.) Procedure to test:


create or replace procedure pr_test_stack (p_stack in char) as

cursor c1 is select * from emp;

begin

for xx in c1
loop
    
    dbms_output.put_line(xx.emp_name || ' dep id:' || to_char(xx.id_dep));
    
end loop;

    if upper(p_stack) ='Y' then
        pr_format_call_stack;
    end if;

end;
/

3.) Testing

set serveroutput on;
SQL> exec pr_test_stack('y');
emp 1 dep id:1
emp 2 dep id:1
emp 3 dep id:1
emp 4 dep id:2
emp 5 dep id:2
emp 6 dep id:2
emp 7 dep id:3
emp 8 dep id:3
emp 9 dep id:3
---------------------------------------------------
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00007FFAE1321688         4  procedure SYS.PR_FORMAT_CALL_STACK
00007FFADFB68D80        15  procedure SYS.PR_TEST_STACK
00007FFAE12B5118         1  anonymous block

---------------------------------------------------

PL/SQL procedure successfully completed.

SQL> exec pr_test_stack('n');
emp 1 dep id:1
emp 2 dep id:1
emp 3 dep id:1
emp 4 dep id:2
emp 5 dep id:2
emp 6 dep id:2
emp 7 dep id:3
emp 8 dep id:3
emp 9 dep id:3

PL/SQL procedure successfully completed.

Checking BALANCE IO on ASM

-- Reference:
-- https://community.oracle.com/docs/DOC-995178

prompt "Checking balance IO on ASM...";

SELECT dg.group_number "GROUP#",
       dg.name,
       DECODE (total_dg.total_io, 0, 100, 100 * (DECODE (SIGN (1 - df.sum_io / total_dg.total_io), -1, 0, (1 - df.sum_io / total_dg.total_io)))) "IO_BALANCED"
  FROM (SELECT d.group_number group_number,
                 SUM (ABS ((d.reads + d.writes) - tot.avg_io)) sum_io
            FROM v$asm_disk_stat d,
                 (SELECT group_number,
                           SUM (reads) + SUM (writes),
                           DECODE (COUNT (*), 0, 0, (SUM (reads) + SUM (writes)) / COUNT (*)) avg_io
                      FROM v$asm_disk_stat
                     WHERE header_status = 'MEMBER'
                  GROUP BY group_number) tot
           WHERE header_status = 'MEMBER' AND tot.group_number = d.group_number
        GROUP BY d.group_number) df,
       (SELECT group_number,
                 SUM (reads) + SUM (writes) total_io
            FROM v$asm_disk_stat
           WHERE header_status = 'MEMBER'
        GROUP BY group_number) total_dg,
        V$ASM_DISKGROUP dg
 WHERE df.group_number = total_dg.group_number
 AND df.group_number = dg.group_number;

Using LIKE on Long Data Types

-- Constraints SEARCH_CONDITION
-- Carlos Magno

create or replace function getlong_const( p_owner in varchar2,
                                          p_table_name in varchar2,
                                          p_cons_name in varchar2) return varchar2
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(4000);
l_long_len number;
l_buflen number := 4000;
l_curpos number := 0;
begin
dbms_sql.parse( l_cursor,
'select search_condition from dba_constraints where owner = :x and table_name=:y and constraint_name=:z ',
dbms_sql.native );
dbms_sql.bind_variable( l_cursor, ':x', p_owner );
dbms_sql.bind_variable( l_cursor, ':y', p_table_name );
dbms_sql.bind_variable( l_cursor, ':z', p_cons_name );


dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);

if (dbms_sql.fetch_rows(l_cursor)>0)
then
   dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,l_long_val, l_long_len );
end if;
dbms_sql.close_cursor(l_cursor);
return l_long_val;
end getlong_const;
/

select * 
from dba_constraints 
where getlong_const(owner, table_name, constraint_name) like '%NOT NULL%';

Creating several AWR Reports

Execute:
 ./mk_awr.sh 1 --> Number of days
Will generate files for AWRRPT:
 -rw-r--r-- 1 oracle oinstall 178628 Feb 25 16:17 AWR_clonetst_370_371.txt
 -rw-r--r-- 1 oracle oinstall 172747 Feb 25 16:17 AWR_clonetst_371_372.txt
 -rw-r--r-- 1 oracle oinstall 171340 Feb 25 16:17 AWR_clonetst_372_373.txt
 -rw-r--r-- 1 oracle oinstall 169183 Feb 25 16:17 AWR_clonetst_373_374.txt
 -rw-r--r-- 1 oracle oinstall 168289 Feb 25 16:17 AWR_clonetst_374_375.txt
Script Name: mk_awr.sh
 -------------------------------------------------------------
 snapINI=
 snapEND=
 nomeReport=
 reportMODE=text
>snaps.txt
# Number of Days to create
 Days=$1
sqlplus -s " / as sysdba" <<EOF
 set lin 100
 set pages 0
 set head off
 set term off
 set echo off
 set feed off
 col snap_id format 99999
 spool snaps.txt
SELECT snap_id  FROM DBA_HIST_SNAPSHOT WHERE
 TRUNC(BEGIN_INTERVAL_TIME) = TRUNC(SYSDATE)-$Days AND
 INSTANCE_NUMBER in (select instance_number from v\$instance)
 ORDER BY SNAP_ID;
spool off
 exit
 EOF
cat snaps.txt | awk '{print $1}' > snaps1.txt
 mv snaps1.txt snaps.txt
for x in `cat snaps.txt`
 do
snapINI=$x
 snapEND=`expr $x + 1`
 echo $snapINI
 echo $snapEND
 nomeReport='AWR_'$ORACLE_SID'_'$snapINI'_'$snapEND'.txt'
echo 'Creating...'$nomeReport
sqlplus " / as sysdba" @?/rdbms/admin/awrrpt.sql <<EOF
 $reportMODE
 $Days
 $snapINI
 $snapEND
 $nomeReport
 exit
 EOF
 done
-------------------------------------------------------------

Daily Scripts – Part 2

-- Datafile Tuning
 select vtablespace.name tablespace_name,
 vdatafile.name datafile_name,
 vfilestat.phyrds physical_reads,
 vfilestat.phywrts physical_writes,
 vfilestat.avgiotim average_time_for_io
 from v$datafile vdatafile,
 v$filestat vfilestat,
 v$tablespace vtablespace
 where vdatafile.file# = vfilestat.file#
 and vdatafile.ts# = vtablespace.ts#
 order by 3 desc ,4 desc;
-- Tuning Queries, to identify SQL commands to improve
 * DISK:
 prompt 'Gathering information by Disk ...';
 set lin 300
 set pages 10000
select exec, buff, disk, num_rows, sql_text, ROUND(buff/exec,2) "Buffer_Exec", ROUND(num_rows / exec,2) "rows_Exec",
 round( round(buff/exec,2)/decode(round(num_rows / exec,2),0,1,round(num_rows / exec,2)),2) "Blocks per Row",
 round(disk/exec,0) Disk_Exec,address, hash
 from (
 select sum(executions) exec, sum(buffer_gets) buff, sum(disk_reads) disk, sum(rows_processed) num_rows,
 min(sql_text) sql_text, min(address) address, min(hash_value) hash
 from gv$sql
 WHERE COMMAND_TYPE <> 47
 group by address
 order by 3 desc)
 where rownum<20;
* BUFFER GET
 prompt 'Gathering information per Buffer gets ...';
select * from
 (
 select exec, buff, disk, num_rows, sql_text, round(buff/exec,2) "Buffer_Exec", round(num_rows / exec,2) "num_rows_Exec",
 round( round(buff/exec,2)/decode(round(num_rows / exec,2),0,1,round(num_rows / exec,2)),2) "Block per Row",
 address, hash_value
 from (
 select
 sum(executions) exec,
 sum(buffer_gets) buff,
 sum(disk_reads) disk,
 sum(rows_processed) num_rows,
 min(sql_text) sql_text,
 min(address) address,
 min(hash_value) hash_value
 from gv$sql
 where COMMAND_TYPE <> 47
 group by address, hash_value
 order by 2 desc)
 where rownum<20)
 order by 8 desc;
* CPU
 prompt 'Gathering information per CPU ...';
select exec, cpu, round(cpu / exec,2) cpu_exec,buff, disk, num_rows, sql_text, round(buff/exec,2) "Buffer_Exec", round(num_rows / exec,2) "num_rows_Exec",
 round( round(buff/exec,2)/decode(round(num_rows / exec,2),0,1,round(num_rows / exec,2)),2) "Blocks per row",
 address, hash_value
 from (
 select
 sum(executions) exec,
 sum(buffer_gets) buff,
 sum(disk_reads) disk,
 sum(cpu_time) cpu,
 sum(rows_processed) num_rows,
 min(sql_text) sql_text,
 min(address) address,
 min(hash_value) hash_value
 from gv$sql
 where COMMAND_TYPE <> 47
 group by address
 order by 4 desc)
 where rownum<20
 order by 3 desc;
-- Library Cache PIN
 set lin 100
 set timing off
 COL event FORMAT a20 TRUNC;
 tti "Users Waiting for Library Cache Pins";
SELECT sid, event, p1raw, seconds_in_wait, wait_time
 FROM sys.v_$session_wait
 WHERE
 event = 'library cache pin'
 AND
 state = 'WAITING';
tti "Object blocker ..."
 COL owner format a8
 COL object format a70
 SELECT kglnaown AS owner, kglnaobj as Object
 FROM sys.x$kglob
 WHERE kglhdadr='&P1RAW';
tti "Waiting users ..."
 col SID_SERIAL format a12
 SELECT s.sid||','||s.serial# SID_SERIAL,s.username, kglpnmod "Mode Held", kglpnreq "Request"
 FROM sys.x$kglpn p, sys.v_$session s
 WHERE p.kglpnuse = s.saddr
 AND kglpnhdl   = '&P1RAW';
set lin 1000
 set timing on
-- Long OPS
 select distinct a.inst_id, a.start_time, a.last_update_time,
 round((a.time_remaining /60 ),0) minutos_restante, round((a.elapsed_seconds /60 ),0) minutos_percorridos,
 round((a.time_remaining / (a.elapsed_seconds + a.time_remaining)) * 100,2) "% Faltante",
 decode(round((a.time_remaining /60 ),0),0,'Finalizado','Executando ...') Status,
 b.sid, b.serial#, b.username, c.address, b.program, b.module, b.action, c.address, c.sql_text, a.message, a.units
 from GV$SESSION_LONGOPS a, Gv$session b, Gv$sql c
 where a.sid=b.sid and
 a.serial#=b.serial# and
 b.sql_address=c.address(+) and
 a.time_remaining> 0;

LOCK – Scripts

Scripts to help identify Locks
1.)
 col sid format a10;
select decode(a.lmode,0,'|_','') || to_char(a.sid) sid, a.id1, a.type, a.lmode, a.ctime,
 decode(a.block,0,'blocked','*** Blocker Session *** ') Status
 from v$lock a
 where
 a.id1 in
 (select id1 from v$lock where block>0)
 order by a.ID1, lmode desc, block desc, a.ctime desc
 /
select inst_id, sid, serial#, username, module, program, round(last_call_et/60,0) Minutes, client_info
 from gv$session where
 (inst_id, sid) in
 (
 select blocking_instance, blocking_session
 from gv$session where blocking_session is not null
 );
SELECT vh.sid locking_sid,
 vs.status status,
 vs.program program_holding,
 vw.sid waiter_sid,
 vsw.program program_waiting
 FROM v$lock vh,
 v$lock vw,
 v$session vs,
 v$session vsw
 WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2
 FROM v$lock
 WHERE request = 0
 INTERSECT
 SELECT id1, id2
 FROM v$lock
 WHERE lmode = 0)
 AND vh.id1 = vw.id1
 AND vh.id2 = vw.id2
 AND vh.request = 0
 AND vw.lmode = 0
 AND vh.sid = vs.sid
 AND vw.sid = vsw.sid;
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
 id1, id2, lmode, request, type
 FROM V$LOCK
 WHERE (id1, id2, type) IN
 (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
 ORDER BY id1, request ;
SELECT o.subobject_name, L.SESSION_ID,L.ORACLE_USERNAME,L.OS_USER_NAME,L.LOCKED_MODE,
 O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE
 FROM V$LOCKED_OBJECT L,
 DBA_OBJECTS O
 WHERE L.OBJECT_ID = O.OBJECT_ID;
2.)
 select
 b.inst_id "Inst waiter",
 c.inst_id "Inst blocker",
 a.waiting_session "waitingSession", a.holding_session "Holding Session",
 d.SQL_TEXT "CMD waiting",
 e.SQL_TEXT "CMD Blocking",e.address,
 a.lock_type, a.mode_held, a.lock_id1, a.lock_id2, f.spid "Pid Blocker"
 from dba_waiters a, gv$session b, gv$session c, gv$sql d, gv$sql e, gv$process f
 where
 c.paddr=f.addr and
 a.waiting_session=b.sid and
 a.holding_session=c.sid and
 b.SQL_ADDRESS=d.ADDRESS(+) and
 c.sql_address=e.ADDRESS(+);
3.)
 select distinct b.last_call_et, a.holding_session, b.username, b.osuser, b.module, b.machine, b.program, round(b.last_call_et/60,2) Minutos , substr(c.sql_text,1,60) sql_text
 from dba_waiters a, v$session b, v$sql c
 where
 a.holding_session=b.sid and
 b.sql_address=c.address and
 a.waiting_session in
 (
 select sid from v$session_wait where event like 'enq%'
 )
 order by 1 desc
 /
select distinct 'alter system kill session ' || chr(39) || to_char(sid) || ',' || to_char(serial#) || chr(39) || ';'
 from dba_waiters a, v$session b
 where
 a.holding_session=b.sid and
 a.waiting_session in
 (
 select sid from v$session_wait where event like 'enq%'
 )
 order by 1 desc
 /