SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR IN DAY

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR IN DAY

Using below query you can find how many redo log switched per hours in oracle database .it will show you how much database is busy .


Login as sys user and run below query .

Query:
============

select Hour , round(avg(LOG_SWITCHES)) LOG_SWITCHES
from (
 select to_char(trunc(first_time, 'HH'),'HH24') Hour, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
 from v$log_history 
 group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
 order by 1
 )
group by Hour
order by Hour;


Example :

SQL> select Hour , round(avg(LOG_SWITCHES)) LOG_SWITCHES
from (
 select to_char(trunc(first_time, 'HH'),'HH24') Hour, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
  2    3    4   from v$log_history
 group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
 order by 1
  5    6    7   )
group by Hour
  8    9  order by Hour;

HO LOG_SWITCHES
-- ------------
00           56
01          439
02          412
03          120
04          306
05          419
06            7
07            8
08          137
09          182
10          113
11           17
12            2
14            1
17          119
18          301
19          117
21            7
22            3

19 rows selected.

SQL>

How to run SQL Tuning Advisor in Oracle

Steps to Run SQL Tuning Advisor Against Slow Running Queries in Oracle

===============================================================

STEP 1: Create tuning task for the specific SQL_ID:

declare
task_nm varchar2(100);
begin
task_nm := dbms_sqltune.create_tuning_task(SQL_ID=> '&SQL_ID',TASK_NAME => 'SQL_TUNNING_TASK_SQL_ID');
end;
/

NOTE: Replace sql_id in above statement


STEP 2: Check the status of newly created task:

SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';

STEP 3: Execute the newly created task:

exec dbms_sqltune.execute_tuning_task (TASK_NAME => '&TASK_NAME');
Note: Please replace the task name as mentioned in step 1

STEP 4: Check the status after executing the task:

SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';

*Task should be completed, it will take some time to complete.

STEP 5: Execute the Below Query to get the Advisory Report:


SET LONG 99999;
SET PAGESIZE 1000
SET LONGCHUNKSIZE 65536
SET LINESIZE 200

SQL> SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual;


NOTE: Replace task name in above query

STEP 6: To Drop the Tuning Task:

execute dbms_sqltune.drop_tuning_task('&TASK_NAME');


To execute tuning advisory using AWR snap ID if sql_id is not present in cursor:


STEP 7: Find the snap ID using below query:

SQL>
SELECT SQL_ID, PLAN_HASH_VALUE, TIMESTAMP FROM DBA_HIST_SQL_PLAN WHERE SQL_ID='&SQL_ID';

SQL>
select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='&SQL_ID' and plan_hash_value='&plan_hash_value' order by snap_id desc;



STEP 8: Create Tuning Task:

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 841,
end_snap => 891,
sql_id => '663zvxqjuxtqn',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '663zvxqjuxtqn_AWR_tuning_task',
description => 'Tuning task for statement 663zvxqjuxtqn in AWR');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

--NOTE: Replace the above highlighted values:



STEP 9: After creation of tuning task you can follow the above steps 

Monitor job progress :-

COL ADVISOR_NAME FORMAT a20
COL SOFAR FORMAT 999
COL TOTALWORK FORMAT 999
SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$ADVISOR_PROGRESS;

How to Identify SID Based on OS PID in Oracle

How to Identify SID Based on OS PID in Oracle

 ===================================================

Query to check SID from PID (get from top command )

=========================================

set lines 200 pages 500

col sid format 99999
col username format a30
col osuser format a15
select p.spid,s.sid, s.serial#,s.username, s.osuser
from gv$session s, gv$process p
where s.paddr= p.addr
and p.spid='&spid'

order by p.spid;

Purge AWR Snapshots in oracle

Purge AWR Snapshots

=========================

• Get the snapshot details  : 

SELECT MIN(snap_id), min(BEGIN_INTERVAL_TIME),MAX(snap_id), max(BEGIN_INTERVAL_TIME) FROM dba_hist_snapshot;

SELECT MIN(snap_id),MAX(snap_id) FROM dba_hist_snapshot;

• purge snapshot by given range :

First Method:

exec dbms_workload_repository.drop_snapshot_range(881,991);



Second Method:

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id  => 881,
high_snap_id => 991);
END;
/