Optimize / tune the log buffer space in Oracle
Tune log buffer space or log file sequential read waits in Oracle In AWR report, we are getting waits for log event. Event Waits TotalWaitTime(s) Avg wait Waits/txn %bgtime log…
Tune log buffer space or log file sequential read waits in Oracle In AWR report, we are getting waits for log event. Event Waits TotalWaitTime(s) Avg wait Waits/txn %bgtime log…
Find the query taking long time to execute in Oracle Select module,parsing_schema_name,inst_id,sql_id,CHILD_NUMBER,sql_plan_baseline,sql_profile,plan_hash_value,sql_fulltext, to_char(last_active_time,’DD/MM/YY HH24:MI:SS’ ),executions, elapsed_time/executions/1000/1000, rows_processed,sql_plan_baseline from gv$sql where executions <> 0 order by elapsed_time/executions desc Find the highest…
Sniped Session Sniped Session is occurred when IDLE_TIME is set in users or the default profile exceed then this will kill the sessions in the database then status column value…
Check the query is triggered from any procedure SELECT s.sql_id, s.sql_text FROM gv$sqlarea s JOIN dba_objects o ON s.program_id = o.object_id and o.object_name = ‘&procedure_name’
Find top 10 Queries from high physical read SELECT schema, sql_text, disk_reads, round(cpu,2) FROM (SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads, t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time FROM v$sqlstats t join v$sql…
Trace logon failure in Oracle In Oracle we are going to trace the logon session which is not successfully for some reasons. Trace with Auditing 1. Check the auditing…
1. Identify your SQL ID of SQL Query which need to trace. SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_textFROM v$sqlWHERE sql_text like ‘SQL QUERY%’;2. Enable the trace of SQL with using SQLID…
Acknowledge Over PGA limit is a new wait event introduced with PGA_AGGREGATE_LIMIT in 12.1 version of Oracle Database. If process need more PGA then process has to wait if instance is…
Understand the terminology involved in Buffer Cache Buffer CacheOracle having copies of database blocks for frequent access instead of fetching from disks known as buffer cache in SGA area. It’s…
Check session in waiting and how much time from its waiting COLUMN event FORMAT A30COLUMN wait_class FORMAT A15COLUMN sid format 9999select sid, seq#, EVENT, WAIT_CLASS, SECONDS_IN_WAIT from v$session_wait ORDER BY…