Show Users with High CPU Processing since Instance Startup

set feed off;
set pagesize 10000;
set wrap off;
set linesize 80;
set heading on;
set tab on;
set scan on;
set verify off;
–
spool show_users_with_high_cpu_processing.lst

ttitle ‘Show Users with High CPU Processing’ -
skip 2

column user_process format a10 heading “UserProcess(SID)”
column value format 999,999,999.99

select ss.username||’(‘||se.sid||’)’ user_process, value
from v$session ss, v$sesstat se, v$statname sn
where  se.statistic# = sn.statistic#
and  name  like ‘%CPU used by this session%’
and  se.sid = ss.sid
and  ss.username is not null
order  by substr(name,1,25), value desc
/
spool off;
set feed on echo off termout on pages 24 verify on
ttitle off

Show Installation Database Version and Option

Use the following script to view database version and components that already Install :

set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
set termout on;
set serveroutput on;

ttitle left ‘Oracle Version:’ skip 2

select banner
from   sys.v$version;

ttitle left ‘Installed Options:’ skip 2

select parameter
from   sys.v$option
where  value = ‘TRUE’;

ttitle left ‘Not Installed Options:’ skip 2

select parameter
from   sys.v$option
where  value <> ‘TRUE’;

prompt
begin
dbms_output.put_line(‘Specific Port Information: ‘||dbms_utility.port_string);
end;
/
prompt

set head on feed on

Converting Columns to Rows

Suppose you want to convert an Oracle table:

(id, sum1, sum2, sum3)

into another table:

(id, ‘1′, sum1)
(id, ‘2′, sum2)
(id, ‘3′, sum3)

That means converting 1 row from the first table into 3 rows in the other table. Of course, this can be done by scanning the source table 3 times, one for each «sum» column, but if the first table is pretty large (~50 million rows), we need another, faster approach.

Solution

Using an Inline View with the UNION ALL operator, all can be done in one single step.

CREATE TABLE t1 (
id     NUMBER PRIMARY KEY,
sum1   NUMBER,
sum2   NUMBER,
sum3   NUMBER
);

INSERT INTO t1 VALUES (1,20,40,50);
INSERT INTO t1 VALUES (2,30,20,25);
INSERT INTO t1 VALUES (3,15,60,55);
COMMIT;

select * from t1;

ID       SUM1       SUM2       SUM3
———- ———- ———- ———-
1         20         40         50
2         30         20         25
3         15         60         55

CREATE TABLE t2 AS
SELECT id, num, DECODE(num,’1′,sum1,’2′,sum2,’3′,sum3) data
from t1, (SELECT ‘1′ num FROM dual UNION ALL
SELECT ‘2′ num FROM dual UNION ALL
SELECT ‘3′ num FROM dual)
/

select * from t2 order by id;

ID N       DATA
———- – ———-
1 1         20
1 2         40
1 3         50
2 1         30
2 3         25
2 2         20
3 1         15
3 3         55
3 2         60

(Source : Internet)

Table Joins Versus EXISTS

SQL Tuning – Table Joins versus EXISTS

Consider Table Joins in Place of EXISTS

In general, consider joining tables rather than specifying subqueries when the percentage of successful rows returned from the driving table (i.e., the number of rows that need to be validated against the subquery) is high. For example, if we are selecting records from the EMP table and are required to filter those records that have a department category of “A”, then a table join will be more efficient.

Consider the following example:

SELECT emp_name
FROM   emp E
WHERE  EXISTS ( SELECT 'X'
                   FROM   dept
                   WHERE  dept_no  = E.dept_no
                   AND    dept_cat = 'A');
   Execution Plan
   ---------------------------------------------------
   SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
      FILTER
        TABLE ACCESS (FULL) OF 'EMP'
        TABLE ACCESS (BY ROWID) OF 'DEPT'
          INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)

To improve performance, specify:

SELECT emp_name
FROM   dept D,
       emp  E
WHERE  E.dept_no  = D.dept_no
AND    D.dept_cat = 'A';

Execution Plan
---------------------------------------------------
SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
  NESTED LOOPS
     TABLE ACCESS (FULL) OF 'EMP'
     TABLE ACCESS (BY ROWID) OF 'DEPT'
       INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)

Consider EXISTS in Place of Table Joins

Consider breaking some table joins out to separate subqueries when the percentage of successful rows returned from the driving table (i.e., the number of rows that need to be validated against the subquery) is small. When two tables are joined, all rows need to be matched from the driving table to the second table. If a large number of rows can be filtered from the driving table before having to perform the validation against the second table, the number of total physical reads can be dramatically reduced.

Consider the following example:

SELECT . . .                         SELECT . . .
FROM   dept D,                       FROM   dept D,
       emp  E                               emp E
WHERE  E.dept_no  = D.dept_no        WHERE  E.dept_no = D.dept_no
AND    E.emp_type = 'MANAGER'        AND    ( E.emp_type = 'MANAGER'
AND    D.dept_cat = 'A';             OR     D.dept_cat = 'A'     );

To improve performance, specify:

SELECT . . .
FROM   emp E
WHERE  EXISTS     ( SELECT 'X'
                       FROM   dept
                       WHERE  dept_no  = E.dept_no
                       AND    dept_cat = 'A' )
AND    E.emp_type = `MANAGER'

SELECT . . .
FROM   emp E
WHERE  E.emp_type = 'MANAGER'
OR     EXISTS     ( SELECT 'X'
                       FROM   dept
                       WHERE  dept_no  = E.dept_no
                       AND    dept_cat = 'A' )

Converting Column to Rows

Using an Inline View with the UNION ALL operator, all can be done in one single step.

CREATE TABLE t1 (
  id     NUMBER PRIMARY KEY,
  sum1   NUMBER,
  sum2   NUMBER,
  sum3   NUMBER
);

INSERT INTO t1 VALUES (1,20,40,50);
INSERT INTO t1 VALUES (2,30,20,25);
INSERT INTO t1 VALUES (3,15,60,55);
COMMIT;

select * from t1;

        ID       SUM1       SUM2       SUM3
———- ———- ———- ———-
         1         20         40         50
         2         30         20         25
         3         15         60         55

CREATE TABLE t2 AS
SELECT id, num, DECODE(num,’1‘,sum1,’2‘,sum2,’3‘,sum3) data
  from t1, (SELECT ‘1′ num FROM dual UNION ALL
            SELECT ‘2′ num FROM dual UNION ALL
            SELECT ‘3′ num FROM dual)
/

select * from t2 order by id;

        ID N       DATA
———- – ———-
         1 1         20
         1 2         40
         1 3         50
         2 1         30
         2 3         25
         2 2         20
         3 1         15
         3 3         55
         3 2         60

Uncommited Transaction

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid                     FORMAT 999              HEADING ‘SID’
COLUMN serial_id               FORMAT 99999999         HEADING ‘Serial ID’
COLUMN session_status          FORMAT a9               HEADING ‘Status’          JUSTIFY right
COLUMN oracle_username         FORMAT a14              HEADING ‘Oracle User’     JUSTIFY right
COLUMN os_username             FORMAT a12              HEADING ‘O/S User’        JUSTIFY right
COLUMN os_pid                  FORMAT 9999999          HEADING ‘O/S PID’         JUSTIFY right
COLUMN session_program         FORMAT a18              HEADING ‘Session Program’ TRUNC
COLUMN session_machine         FORMAT a15              HEADING ‘Machine’         JUSTIFY right
COLUMN number_of_undo_records  FORMAT 999,999,999,999  HEADING “# Undo Records”
COLUMN used_undo_size          FORMAT 999,999,999,999  HEADING  “Used Undo Size”

SELECT
    s.sid                  sid
  , lpad(s.status,9)       session_status
  , lpad(s.username,14)    oracle_username
  , lpad(s.osuser,12)      os_username
  , lpad(p.spid,7)         os_pid
  , b.used_urec            number_of_undo_records
  , b.used_ublk * d.value  used_undo_size
  , s.program              session_program
  , lpad(s.machine,15)     session_machine
FROM
    v$process      p
  , v$session      s
  , v$transaction  b
  , v$parameter    d
WHERE
      b.ses_addr =  s.saddr
  AND p.addr     =  s.paddr
  AND s.audsid   <> userenv(‘SESSIONID’)
  AND d.name     =  ‘db_block_size’;

SGA Free

OLUMN pool    HEADING “Pool”
COLUMN name    HEADING “Name”
COLUMN sgasize HEADING “Allocated” FORMAT 999,999,999
COLUMN bytes   HEADING “Free” FORMAT 999,999,999

SELECT
    f.pool
  , f.name
  , s.sgasize
  , f.bytes
  , ROUND(f.bytes/s.sgasize*100, 2) “% Free”
FROM
    (SELECT SUM(bytes) sgasize, pool FROM v$sgastat GROUP BY pool) s
  , v$sgastat f
WHERE
    f.name = ‘free memory’
  AND f.pool = s.pool
/

SGA Usage

SET LINESIZE 145
SET PAGESIZE 9999
SET FEEDBACK off
SET VERIFY   off

COLUMN bytes   FORMAT  999,999,999
COLUMN percent FORMAT  999.99999

break on report

compute sum of bytes on report
compute sum of percent on report

SELECT
    a.name
  , a.bytes
  , a.bytes/(b.sum_bytes*100)  Percent 
FROM sys.v_$sgastat a
   , (SELECT SUM(value)sum_bytes FROM sys.v_$sga) b
ORDER BY bytes DESC
/

Show User Privs

set feed off
set pagesize 30000
set linesize 200
clear breaks columns
set pause off
spool show_privileges_for_user.lis
–
ttitle left “Currently active Role(s) for User: ” sql.user -
skip 2
column username format A22 heading ‘User’ trunc
column role format A40 heading ‘Active|Role’ trunc
column default_role format A7 heading ‘Default|Role’ trunc
column admin_option format A7 heading ‘Admin|Option’ trunc
–
break on username

select  username,role,default_role,admin_option
  from    user_role_privs, session_roles
  where   granted_role = role
  order   by role
/
–
ttitle left “Currently inactive Role(s) for User: ” sql.user -
skip 2
column username format A22 heading ‘User’ trunc
column granted_role format A40 heading ‘Granted|Role’ trunc
column default_role format A7 heading ‘Default|Role’ trunc
column admin_option format A7 heading ‘Admin|Option’ trunc

select  username,granted_role,default_role,admin_option
from  user_role_privs
where not exists (select ‘x’
      from   session_roles
      where  role = granted_role)
union
select ‘All Role(s) are active’,”,”,”
from  dual
where 0 = (select count(‘x’)
     from   user_role_privs
     where  not exists (select ‘x’
             from  session_roles
             where role = granted_role))
order by 1,2
/
–
ttitle left “Sub-Role(s) for User: ” sql.user -
skip 2
column granted_role format A39 heading ‘These Role(s) are granted to …’ trunc
column role format A39 heading ‘… these Role(s)’

select  granted_role,role
from  role_role_privs
union
select  ‘No Sub-Role(s) found’,”
from  dual
where 0 = (select count(‘x’)
     from  role_role_privs)
/
–
ttitle “System Privileges through Roles and direct for User: ” sql.user -
skip 2
column role format A40 heading ‘Role’ trunc
column privilege format A30 heading ‘System|Privilege’ trunc
column admin_option format A7 heading ‘Admin|Option’ trunc
–
break on role skip 1

select  role,privilege,admin_option
from  role_sys_privs
union
select  ‘directly’ role,privilege,admin_option
from  user_sys_privs
order by 1,2
/
–
ttitle “Object Privileges through Roles and direct for User: ” sql.user -
skip 2
column role format a20 heading ‘Role’ trunc
column owner format a17 heading ‘Object|Owner’ trunc
column table_name format a20 heading ‘Object|Name’ trunc
column privilege format a12 heading ‘Privilege’ trunc
column grantable format a6 heading ‘Admin|Option’ trunc
break on role on owner on table_name

select  role, owner, table_name, privilege, grantable
from  role_tab_privs
where role in (select role
     from   session_roles)
and column_name is null
union
select  ‘directly’ role, owner, table_name, privilege, grantable
from  user_tab_privs_recd
order by 1,2,3,4
/
–
ttitle “Column Privileges for User: ” sql.user -
skip 2
column role format a16 heading ‘Role’ trunc
column owner format a10 heading ‘Object|Owner’ trunc
column table_name format a20 heading ‘Object|Name’ trunc
column column_name format a20 heading ‘Column|Name’ trunc
column privilege format a10 heading ‘Privilege’ trunc

select  role, owner, table_name, column_name, privilege, grantable
from  role_tab_privs
where role in (select role
     from   session_roles)
and column_name is not null
union
select  ‘directly’ role, owner, table_name, column_name,
  privilege, grantable
from  user_col_privs_recd
order by 1,2,3,4
/
spool off
clear breaks columns
ttitle off
prompt
prompt Listing created in “showpriv.lis”
prompt

List All Index Column for Table

break on index_name skip 1
col index_name  format a30
col uniuenes    format a12
col column_name format a30

prompt Indexes for table: &&1
select c.index_name, i.uniqueness, c.column_name
from   user_indexes i, user_ind_columns c
where  i.index_name = c.index_name
  and  i.table_name = upper(‘&&1′)
order  by c.index_name, c.column_position
/

undef 1