Performance Tuning

 Check the count of CPU count with SQL in Oracle SQL> SELECT value CPU_COUNT FROM v$system_parameter2 WHERE LOWER(name) = ‘cpu_count’; CPU_COUNT ———— 8 Check the count of CPU present with …

Check which application cause more number of sessions and processes in Oracle —Check the count of session for particular applications SELECT s.program,count(*) “noofsessions” from v$session s group by s.program; –Check …

 For error: ORA-01000: maximum open cursors exceeded ORA-00604: error occurred at recursive SQL level 1 Check the session which causes the issue: select a.value, s.username, s.sid, s.serial# from v$sesstat a, …

Find the uncommitted or incomplete transaction in Oracle Query with V$transaction give result which transaction is not commited yet. set lines 250 column start_time format a20 column sid format 999 …

Monitor and manage the Memory Usage of PDBs databases in Oracle. Check the memory usage of PDB databases COLUMN PDB_NAME FORMAT A10 SELECT r.CON_ID, p.PDB_NAME, r.SGA_BYTES, r.PGA_BYTES, r.BUFFER_CACHE_BYTES, r.SHARED_POOL_BYTES FROM …

 STATS of table is STALE after gathering stats in Oracle I tried to gather the stats of the table but after that gather still its in STALE state. Solution To …

 Find the Session/ program / module / SQL Query using high temp tablespace usages Find the session using high temporary tablespace: col inst_id format 999 col sid format 99999 col …

💡 Check stuck job with resume time or error detail with dba_resumable view DBA_RESUMABLE view will help to see the all suspended session and cause of error. If we will fixed …

 What is database response time in Oracle Basic definition of Database Response Time means Service Time plus Wait time. Response Time = Service Time(execution time) + Wait Time Service time is the …

Script to find the Username which causing bottleneck of CPU in Oracle select * from ( SELECT se.username, ROUND (value/100) “CPU Usage” FROM v$session se, v$sesstat ss, v$statname st WHERE …