print java stack trace

this is a Java static method that prints on the System.out the stack trace of a Throwable object. It goes recursively through the chain of Throwable objects, printing also the causing objects.

public static void printStackTrace(Throwable th) {
    for (StackTraceElement ste : th.getStackTrace()) {
      System.out.println("file " + ste.getFileName() + ", method " + ste.getMethodName() + ", line "
          + ste.getLineNumber() + ", " + th.getMessage());
    }
    if (th.getCause() != null) {
      System.out.println("... cause ...");
      printStackTrace(th.getCause());
    }
  }

free space in tablespace

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
--AND  df.tablespace_name  LIKE  'UNDO%'
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
--and   df.tablespace_name LIKE  'UNDO%'
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

 ORDER BY 4 DESC;

Oracle, resource-consuming queries

SELECT DISTINCT p.Sql_Id AS Sql_Id,
                CASE
                  WHEN p.Id = 0 THEN
                   Sq.Module
                  ELSE
                   NULL
                END AS Module,
                p.Id,
                Lpad(' ', 4 * Depth) || p.Operation AS Operation,
                p.Options AS "Access",
                p.Object_Name AS "Object",
                p.Cost AS "Cost",
                Trunc(p.Cpu_Cost / 1000000, 2) AS "Cpu_Seconds",
                p.Cardinality,
                p.Io_Cost AS "IO Cost",
                p.TIME,
                p.Filter_Predicates,
                p.Access_Predicates,
                CASE
                  WHEN p.Id = 0 THEN
                   Sq.Sql_Text
                  ELSE
                   NULL
                END AS Sql_Text,
                CASE
                  WHEN p.Id = 0 THEN
                   Sq.Last_Active_Time
                  ELSE
                   NULL
                END AS Last_Active_Time,
                Filtro.Costosum
  FROM V$sql_Plan p
INNER JOIN V$sql Sq
    ON Sq.Sql_Id = p.Sql_Id
INNER JOIN (SELECT Filtro_Rownum.Sql_Id, Filtro_Rownum.Costosum
               FROM (SELECT Tmp.Sql_Id, SUM(Tmp.Cost) AS Costosum
                       FROM (SELECT s.Sql_Text,
                                    p.Sql_Id,
                                    p.Plan_Hash_Value,
                                    p.Cost
                               FROM V$sql s
                              INNER JOIN V$sql_Plan p
                                 ON s.Sql_Id = p.Sql_Id
                              WHERE 1 = 1
                                AND Upper(s.Sql_Text) NOT LIKE
                                    '%EXPLAIN PLAN%'
                                AND s.Last_Active_Time > SYSDATE - 0.3
                                AND upper(s.Module) <> Upper('PL/SQL Developer')
                                AND p.Cost IS NOT NULL
                                AND p.ID = 0
                              ORDER BY p.Cost     DESC,
                                       p.Cpu_Cost DESC,
                                       p.Io_Cost  DESC) Tmp
                     GROUP BY Tmp.Sql_Id
                      order by sum(tmp.cost) desc) Filtro_Rownum
              WHERE Rownum < 200) Filtro
    ON Filtro.Sql_Id = p.Sql_Id
ORDER BY Filtro.Costosum DESC, p.Sql_Id, p.Id

Oracle profiling session

SQL> select
  2    u_dump.value   || '/'     ||
  3    db_name.value  || '_ora_' ||
  4    v$process.spid ||
  5    nvl2(v$process.traceid,  '_' || v$process.traceid, null )
  6    || '.trc'  "Trace File"
  7  from
  8               v$parameter u_dump
  9    cross join v$parameter db_name
10    cross join v$process
11          join v$session
12            on v$process.addr = v$session.paddr
13  where
14   u_dump.name   = 'user_dump_dest' and
15   db_name.name  = 'db_name'        and
16   v$session.audsid=sys_context('userenv','sessionid');

Trace File
--------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/cfdev_ora_9576.trc

SQL> alter session set sql_trace = true;

Session altered.

SQL> -- your SQL or PL/SQL CODE
  2  /

PL/SQL procedure successfully completed.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>

then analyze C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/cfdev_ora_9576.trc with tkprof

tkprof C:\ORACLE\PRODUCT\10.2.0\ADMIN\CFDEV\UDUMP/cfdev_ora_9576.trc out.txt sys=no

inspect Oracle active sessions

Here come queries useful to see the current running sessions, together with the sql being executed and the associated events

select u.username, ash.session_id, ash.session_serial#, ash.event, count(*), ash.SQL_ID, s.sql_text
      from gv$active_session_history ash, v$sql s, V$SESSION u
      where ash.sql_id=s.sql_id and u.user# = ash.user_id
    group by ash.session_id, ash.session_serial#, u.username, ash.SQL_ID, ash.event, sql_text
    order by 1;

select se.username, se.sid, se.serial#, sq.sql_fulltext
      from V$SESSION se, v$sql sq
      where se.sql_id = sq.sql_id
order by 1

gathering statistics on Oracle DB

Here comes a script to gather statistics on Oracle DB

begin

  dbms_stats.unlock_schema_stats('SCHEMA');
  dbms_stats.delete_schema_stats('SCHEMA');
  dbms_stats.gather_schema_stats(ownname       => 'SCHEMA',
                                estimate_percent => 40.0,
                                granularity      => 'GLOBAL',
                                block_sample     => TRUE,
                                cascade          => TRUE,
                                degree           => DBMS_STATS.DEFAULT_DEGREE,
                                method_opt       => 'for all columns size 1');

  dbms_stats.lock_schema_stats('SCHEMA');

end;



select t.owner, t.table_name, t.last_analyzed from dba_all_tables t
where lower(t.owner) = 'your_schema';

foreign keys without indexes

In Oracle database a foreign keys without an index defined on it could lead to contention problems (enq: TM – contention wait event). The following script retrieves foreign keys not having indexes

--the script finds the missing indexes on FK

SELECT * FROM (
   SELECT c.table_name, cc.column_name, cc.position column_position
   FROM   user_constraints c, user_cons_columns cc
   WHERE  c.constraint_name = cc.constraint_name
   AND    c.constraint_type = 'R'
   MINUS
   SELECT i.table_name, ic.column_name, ic.column_position
   FROM   user_indexes i, user_ind_columns ic
   WHERE  i.index_name = ic.index_name
   )
ORDER BY table_name, column_position;