More on Oracle Articles

(We marked visited pages blue)
  • How to install Oracle Database 12c Step by Step
  • How to fix Oracle ORA-00904 Error Message
  • How to Start and Stop an Oracle Database
  • How to extract an user definition in Oracle
  • Complete Oracle Cold database Restore Routine
  • Complete Oracle Cold Backup Routine
  • How to find and Recompile Invalid Objects in Oracle
  • How to Fix ORA-01031 insufficient privileges - sqlplus / as sysdba
  • Unable to lock Central Inventory - OPatch will attempt to re-lock
  • Solution for Different Oracle Archive Log Sequence on Primary to Standby
  • Steps to Stop and Start Oracle Standby Database
  • How to Extract any object definition in Oracle
  • Script to Clone an Oracle User
  • Como redefinir a senha de um usuário bloqueado no Oracle
  • Como multiplexar arquivos de controle no Oracle
  • Script to generate Statistics script in Oracle
  • Script to find who is using UNDO space in Oracle
  • Script to find used Buffer Cache Block per Object
  • Script to find optimal UNDO retention size in Oracle
  • How to open an Oracle Standby database for READ ONLY mode
  • Oracle Script to list who is using the undo space
  • Oracle Script to list the Memory Allocation Per User/Process
  • How to Move the Flash Recovery Area location in Oracle
  • Oracle Fix for "NID-00137 All datafiles that..." Error
  • Oracle script to find undo generated by session
  • Simplified Oracle Database 12c and 11g Installation
  • How to recompile all objects in Oracle
  • How Oracle Database Redo and Archived Logs function
  • Oracle Database Processes definition and purposes
  • How to use Oracle Database oratop utility
  • What are the components of the Oracle Database Memory Architecture
  • Oracle Backup Metadata Only with Expdp
  • Move Oracle from file system storage to ASM storage.
  • Extract Metadata from Oracle Full Dump with IMPDP
  • How to Enable ARCHIVELOG Mode in Oracle
  • How to drop an Oracle database using SQL Plus commands
  • How to customize your SQLPLUS utility
  • How to clone an Oracle Database Home
  • How to alter the control file name or alter it's file path in Oracle
  • Fix for ORA-01103 database name 'DB01' in control file is not 'DBDG'
  • Script to show SQL by HASH value in Oracle
  • Script to find Total wait time by resource types in Oracle
  • Script to Find Last time and Objects was Analyzed in Oracle
  • How to Find Stale Materialized Views in Oracle
  • How to Find duplicate executed Sql Statements in Oracle
  • Extract Procedure,Package,Package Body,Function in Oracle
  • How to find Hit Ratio Buffer Cache in Oracle Database
  • How to see the Oracle database Disk Balancing Report Script
  • How to Generate Oracle Create Trigger Script
  • How to find the Wait time spent by Type in Oracle Database

    This script will display total time spent waiting info(from V$SYSTEM_EVENT) along with total time spent processing info (from V$SYSSTAT for "CPU used by this session" statistic), along with a calculation of the percentage of time the instance spent doing each thing;

    col type format a5 heading "Svc,|Idle,|Wait"
    col name format a35 heading "Name" truncate
    col tot_secs_spent format 999,999,999,990.00 heading "Total|Seconds|Spent"
    col pct_total format 990.00 heading "%|Total"
    col nonidle_total format 990.00 heading "%|NonIdle"
    
    select	type,
    	name,
    	tot_secs_spent,
    	(tot_secs_spent / (sum(tot_secs_spent) over ()))*100 pct_total,
    	(nonidle_secs_spent / (sum(nonidle_secs_spent) over ()))*100 nonidle_total
    from	(select	decode(event,
    			'rdbms ipc message', 'Idle',
    			'rdbms ipc reply', 'Idle',
    			'SQL*Net message from client', 'Idle',
    			'SQL*Net break/reset to client', 'Idle',
    			'pipe get', 'Idle',
    			'pmon timer', 'Idle',
    			'smon timer', 'Idle',
    			'dispatcher timer', 'Idle',
    			'virtual circuit status', 'Idle',
    			'PX Idle Wait', 'Idle',
    			'PX Deq: Execute Reply', 'Idle',
    			'PX Deq: Execution Msg', 'Idle',
    			'PX Deq: Table Q Normal', 'Idle',
    			'PX Deq Credit: send blkd', 'Idle',
    			'PX Deq Credit: need buffer', 'Idle',
    			'PX Deq: Parse Reply', 'Idle',
    			'PX Deq: Signal ACK', 'Idle',
    			'PX Deq: Join ACK', 'Idle',
    			'PX qref latch', 'Idle',
    			'PX Deq: Msg Fragment', 'Idle',
    			'PL/SQL lock timer', 'Idle',
    			'inactive session', 'Idle',
    				'Wait') type,
    		event name,
    		time_waited/100 tot_secs_spent,
    		decode(event,
    			'rdbms ipc message', 0,
    			'rdbms ipc reply', 0,
    			'SQL*Net message from client', 0,
    			'SQL*Net break/reset to client', 0,
    			'pipe get', 0,
    			'pmon timer', 0,
    			'smon timer', 0,
    			'dispatcher timer', 0,
    			'virtual circuit status', 0,
    			'PX Idle Wait', 0,
    			'PX Deq: Execute Reply', 0,
    			'PX Deq: Execution Msg', 0,
    			'PX Deq: Table Q Normal', 0,
    			'PX Deq Credit: send blkd', 0,
    			'PX Deq Credit: need buffer', 0,
    			'PX Deq: Parse Reply', 0,
    			'PX Deq: Signal ACK', 0,
    			'PX Deq: Join ACK', 0,
    			'PX qref latch', 0,
    			'PX Deq: Msg Fragment', 0,
    			'PL/SQL lock timer', 0,
    			'inactive session', 0,
    				time_waited/100) nonidle_secs_spent
    	 from	v$system_event
    	 where	time_waited  0
    	 union all
    	 select	'Svc' type,
    		'other cpu usage' name,
    		(t.value - (p.value + r.value))/100 tot_secs_spent,
    		(t.value - (p.value + r.value))/100 nonidle_secs_spent
    	 from	v$sysstat t,
    		v$sysstat p,
    		v$sysstat r
    	 where	t.name = 'CPU used by this session'
    	 and	p.name = 'recursive cpu usage'
    	 and	r.name = 'parse time cpu'
    	 union all
    	 select	'Svc' type,
    		name,
    		value/100 tot_secs_spent,
    		value/100 nonidle_secs_spent
    	 from	v$sysstat
    	 where	name = 'recursive cpu usage'
    	 and	value  0
    	 union all
    	 select	'Svc' type,
    		name,
    		value/100 tot_secs_spent,
    		value/100 nonidle_secs_spent
    	 from	v$sysstat
    	 where	name = 'parse time cpu'
    	 and	value  0)
    order by 5 desc, 4 desc, 3 desc, 2

    </p>


    Leave a comment or a question in the comment area bellow if you fell like


    Be a sport and

    Search

    Loading... Please wait

    Subscribe to our Newsletter

    Be a sport and

    x