How to check cursor usage and find high version count sqls? (Oracle)

prompt ====================================================
prompt == Start of check_cursor.sql
prompt ====================================================
prompt ====================================================
prompt == Monitor cursor usage
prompt == Due to bug 21135007, the session_cached_cursor usage might be out
prompt == number due to parallel execution.
prompt ====================================================
set lines 300
col value format a30
col usage format a30
SELECT
   'session_cached_cursors' PARAMETER,
   lpad(VALUE, 5) VALUE,
   decode(VALUE, 0, 'n/a', to_char(100 * used / VALUE, '990') || '%') usage , used
FROM
   (SELECT MAX(s.VALUE) used
      FROM v$statname n, v$sesstat s
      WHERE n.name = 'session cursor cache count'
         AND s.statistic# = n.statistic#
   ),
   (SELECT VALUE
      FROM v$PARAMETER
      WHERE name = 'session_cached_cursors'
   )
UNION ALL
SELECT 'open_cursors', lpad(VALUE, 5), to_char(100 * used / VALUE, '990') || '%', used
FROM(SELECT MAX(SUM(s.VALUE)) used
      FROM v$statname n, v$sesstat s
      WHERE n.name ='opened cursors current'
         AND s.statistic# = n.statistic#
      GROUP BY s.sid
   ),
   (  SELECT VALUE
      FROM v$PARAMETER
      WHERE  name = 'open_cursors'
   );

prompt ====================================================
prompt == Top 10 cursor hungry sql statements
prompt ====================================================
select *
from (select  t.value ,s.sid, s.sql_id
from v$sesstat t, v$statname n, v$session s
where t.sid=s.sid and n.name= 'session cursor cache count' and t.statistic# = n.statistic#  order by 1 desc) where rownum <=10;

prompt ====================================================
prompt == Total cursors open, by username & machine
prompt ====================================================
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine
from   v$sesstat a, v$statname b, v$session s
where  a.statistic# = b.statistic# and s.sid=a.sid
and    b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;

prompt ====================================================
prompt == Show sqls having high child cursor count > 100
prompt ====================================================

select inst_id, sql_id, sql_profile, plan_hash_value, max(child_number)
from gv$sql group by inst_id, sql_id, sql_profile, plan_hash_value
having max(child_number) >100;


prompt ====================================================
prompt == End of check_cursor.sql
prompt ====================================================

How to merge two large tables? (Oracle)

Scenario:
There are two large tables, table A has 3 billion records and table B has 80 million records. How to merge them into one table?

Solutions:
################
# Solution 1:
################
Use MERGE sql statement with parallel.
--pros: simple and easy to understand
--cons: the requirement to hardware is relatively high

################
# Solution 2:
################

Hash partition both tables by ID, then use MERGE with parallel. This will use Oracle's new feature PWJ -- Partition Wise Join. 

################
# Solution 3:
################

Create a new table C, then insert into C using full outer job of table A and B. Then rename table C, rebuild indexes...etc.

--PWJ + parallel + nologging + compress

################
# Solution 4:
################

11g + version only

Use DBMS_PARALLEL_EXECUTE to separate table B(which has less records) by ROWID. Then use cursor/plsql to loop thru the smaller tables.

--pros: less rollback/UNDO

--cons: very slow using cursor .

################
# Solution 5:
################
Compare records in both A and B and remove duplicate ones. Then do a direct INSERT.
--pros: no MERGE, less expensive
--cons: more DML than other solutions

How to see progress of long running sql statements? (Oracle)

From now and then, you are asked by your teammates about when his/her sql statements will finish. Here is a script that shows an estimation. It can be used for any long running sqls including RMAN jobs as well.

Sometimes this estimation might not be accurate. The elapsed_seconds might change to zero or a few seconds frequently for some long running jobs. That means the job is progressing and it is not hang.

prompt ====================================================
prompt == Start of check_longops.sql
prompt ====================================================

prompt ====================================================
prompt == Check Long Operation notes
prompt == These are just Estimations and sometimes not accurate.
prompt ==
prompt ==
prompt ====================================================

SET PAGES 100
SET LINE 200
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A20
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
COLUMN message FORMAT a80
COLUMN opname FORMAT a20
column username format a20

SELECT s.sid,
       sl.username,
       opname,
       MESSAGE,
       s.serial#,
       s.machine,
       ROUND (sl.elapsed_seconds / 60) || ':' || MOD (sl.elapsed_seconds, 60)
           elapsed,
       ROUND (sl.time_remaining / 60) || ':' || MOD (sl.time_remaining, 60)
           remaining,
       DECODE (totalwork, 0, 0, ROUND (sl.sofar / sl.totalwork * 100, 2))
           progress_pct
  FROM gv$session s, gv$session_longops sl
 WHERE s.sid = sl.sid AND s.serial# = sl.serial# AND sofar <> totalwork;

 prompt ====================================================
 prompt == End of check_longops.sql
 prompt ====================================================

How to check locking status? (Oracle)

When you are called to help an application hang issue, you would like to know if there are any database sessions are blocked and what transactions are blocking them and who/which session/which machines are blocking them.

The following script would help you find out if there are any locks in the database and who is blocking them.

prompt ====================================================

prompt == Start of check_lock.sql

prompt ====================================================

prompt ====================================================

prompt == Check Locks notes

prompt == 

prompt == Who is blocking whom?

prompt == 

prompt ====================================================

select s1.username || ' '|| s1.machine 

|| ' ( SID=' || s1.sid || ' ) is blocking ' 

|| s2.username || ' '|| s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status , s2.event as wait_event

from v$lock l1, v$session s1, v$lock l2, v$session s2 

where s1.sid=l1.sid and s2.sid=l2.sid 

and l1.BLOCK=1 and l2.request > 0 

and l1.id1 = l2.id1 

and l2.id2 = l2.id2 ; 

prompt ====================================================

prompt == End of check_lock.sql

prompt ====================================================

 

How to check IO performance? (Oracle)

During performance tuning or being called to debug a performance issues of the database system, we always would like to know the current I/O status and how it compares with baseline periods. This is a handy script that would give you useful information.

Please keep in mind that busy I/O might not necessary lead to the conclusion that I/O is the bottleneck or culprit of the performance issue.

prompt ====================================================
prompt == Start of check_io.sql
prompt ====================================================

prompt ====================================================
prompt == Check IO notes
prompt == OS level should support Async for better performance.
prompt == In general, for waits 1000+ per hour, elapsed time
prompt == should be less than 15~20ms.
prompt ==
prompt == Single Block -->wait on db file sequential read -->index visit or rollback
prompt ==
prompt == Multi Block -->wait on db file scattered read -->full table scan or index fast full scan
prompt ==
prompt ==
prompt ==
prompt == How to Tell if the I/O of the Database is Slow (Doc ID 1275596.1)
prompt ====================================================
prompt ====================================================
prompt == Show IO Stats
prompt ====================================================
set lines 300
set pages 300
select * from DBA_HIST_IOSTAT_DETAIL;

prompt ====================================================
prompt == Show IO Metric
prompt ====================================================
select * from dba_hist_sysmetric_history where metric_name like 'I/O%Megabytes%';

prompt ====================================================
prompt == End of check_io.sql
prompt ====================================================

How to find the sessions that are using most CPUs? (Oracle)

Here is a handy script I usually use.

check_cpu.sql

prompt ====================================================
prompt == Start of check_cpu.sql
prompt ====================================================

prompt ====================================================
prompt == Check CPU notes
prompt == OS level CPU usage can be monitor by top or topas
prompt == Where you should be able to pin point the top
prompt == process ID
prompt == 
prompt ====================================================
prompt ====================================================
prompt == Show top 20 CPU usage sessions
prompt ====================================================
set lines 300
set pages 300
col machine format a20
col program format a30
col name format a30
select * from
(SELECT se.username, ss.sid,se.machine,se.program, name, se.status, ROUND (value/100) "CPU Usage Seconds", se.sql_id
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
    AND name LIKE  '%CPU used by this session%'
   AND se.sid = ss.SID 
   AND se.username IS NOT NULL
  ORDER BY value DESC)
where rownum <=20;


prompt ====================================================
prompt == Check Sql Text related to above sql_id
prompt ====================================================
SET LONG 100000 
select sql_id, SQL_TEXT from v$SQL where SQL_ID in (
select sql_id from
(SELECT se.username, ss.sid,se.machine,se.program, name, se.status, ROUND (value/100) "CPU Usage Seconds", se.sql_id
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
    AND name LIKE  '%CPU used by this session%'
   AND se.sid = ss.SID 
   AND se.username IS NOT NULL
  ORDER BY value DESC)
where rownum <=20
);

prompt ====================================================
prompt == End of check_cpu.sql
prompt ====================================================

/app keep growing in Cassandra (Cassandra)

During nodetool repair job, we found an interesting issue that /app goes up quickly and drops down after job finishing.

The official document says:
"By default, the repair command takes a snapshot of each replica immediately and then sequentially repairs each replica from the snapshots. For example, if you have RF=3 and A, B and C represents three replicas, this command takes a snapshot of each replica immediately and then sequentially repairs each replica from the snapshots (A<->B, A<->C, B<->C) instead of repairing A, B, and C all at once. This allows the dynamic snitch to maintain performance for your application via the other replicas, because at least one replica in the snapshot is not undergoing repair."

So basically during this repair job, data will be checked thru all 3 nodes(if RF=3) and sync any inconsistency (if there is any).

If the repair jobs on all nodes are set start at the same time, this causes HUGE resource issues and data usage issues because snapshots will be created for this job cross all nodes!!

So the solution is:
1) run the repair job on one node, mark the time.
2) cron the jobs accordingly for all nodes without overlapping each other.
3) keep an eye on the cron jobs to ensure they work as expected.