CPU usage Queries Historical CPU Usage From the below query we can modify the END_INTERVAL_TIME as required by adjusting from where clause. set linesize 200 set pagesize 120 col module for a60 SELECT mymodule “Module”, SUM (cpu_time) “CPU Time”, SUM (wait_time) “Wait Time”, SUM (cpu_time) + SUM (wait_time) “Total Time” FROM (SELECT a.module…
Scripts for Object Locks and Blocking Sessions. Blocking Session using v$lock SELECT l1.sid || ‘ is blocking ‘ || l2.sid blocking_sessions FROM v$lock l1, v$lock l2 WHERE l1.block = 1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l1.id2 = l2.id2; Blocking session with More information SELECT s1.username…
Advanced Compression with Oracle Database 11g for OLTP Oracle introduced Table Compression from version 9i, to compress data for bulk loaded. Its been introduced from 11gR1 with OLTP Table Compression that allows data to be compressed for all DML’s(Inserts/Updates/Deletes), OLTP Table Compression reduces the associated compression overhead of write operations making it suitable for transactional…
Shrink Datafiles – Adjusting HWM This below example is derived from “Shrinking datafiles” scripts from asktom.oracle.com Scenario:- Lets suppose the datafile size is 100m with autoexted upto maxsize 32gb. 1) Created a table 2) Inserted 50millions of rows with commit — Data file size extended from 100mb to 20gb. 3) performed so many DML’s —…