Pages

Saturday, September 26, 2020

ASH (Active Session History) Analysis

How To Generate ASH (Active Session History) Report


To generate ASH report:

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

The report provides below areas:

1. Top User Events


2. Top Service/Module


3. Top SQL Command Types



4. Top Sessions



5. Top Blocking Sessions



6. Top DB Objects


7. Top Phases of Execution


8. Top PL/SQL Procedures


9. Top SQL With Top Row Sources


11. Complete list of SQL text


12. Activity Over Time




How To Generate Explain Plan In Oracle

How To Generate Explain Plan In Oracle


1.Generating explain plan for a sql query:

We will generate the explain plan for the query "select * from test.qader_t1;"

LOADING THE EXPLAIN PLAN TO PLAN_TABLE

SQL> explain plan for select * from test.qader_t1;

 DISPLAYING THE EXPLAIN PLAN

SQL> select * from table(dbms_xplan.display);


2. Explain plan for a sql_id from cursor

set lines 2000

set pagesize 2000

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));


3. Explain plan of a sql_id from AWR:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

Above will display the explain plan for all the plan_hash_value in AWR. If you wish to see the plan for a particular plan_hash_value.

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id',&plan_hash_value));

 


Friday, September 25, 2020

How to Stop and Start Oracle RAC Services on Single Node

How to Stop and Start Oracle RAC Services on Single Node


1) Disable the automatic startup of the Oracle Clusterware software:

crsctl disable has


2) Stop the Oracle clusterware stack:

crsctl stop has


3) Once clusterware Services has been stopped we will restart those Services:

crsctl enable has


4) Enable the automatic startup of the Oracle Clusterware software:

crsctl start has


Check the status:

ps -ef|grep d.bin|wc -l

ps -ef|grep pmon


5) Start the Oracle clusterware stack:

crsctl start crs


6)Check the status of Oracle Clusterware Resources:

crsctl stat res -t


Long Running Sessions in Oracle

Long Running Sessions in Oracle


SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;


(or)

set lines 300

col TARGET for a40

col SQL_ID for a20

select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS,SQL_ID from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING; 


Above output you can further check the sql_id, sql_text and the wait event for which query is waiting


TO find out sql_id for the above sid:

SQL> select sql_id from v$session where sid='&SID';


To find sql text for the above sql_id:

SQL> select sql_fulltext from V$sql where sql_id='1uksqt2vzxbz5';


To find wait event of the query for which it is waiting for:

SQL>select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id';

Friday, September 11, 2020

Flashback Query (AS OF) in Oracle Database

Flashback Query (AS OF) in Oracle Database


Create a test table with no data and check the current SCN and time.

create table flashback_mqm_test (id  NUMBER(10));

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

584777 2020-09-11 23:55:06


Add a row to the test table and commit the change.

INSERT INTO flashback_mqm_test (id) VALUES (1);

COMMIT;


If we check the contents of the table, we can see there is a single row.

SELECT COUNT(*) FROM flashback_mqm_test;

  COUNT(*)

----------

         1

The following two examples use the AS OF clause to query the table using the timestamp and SCN we captured before the row was inserted.

select count(*) from flashback_mqm_test AS OF TIMESTAMP TO_TIMESTAMP('2020-09-11 23:55:06', 'YYYY-MM-DD HH24:MI:SS');

  COUNT(*)

----------

         0

select count(*) from flashback_mqm_test AS OF SCN 584777;

  COUNT(*)

----------

         0

As we can see, the data in the table reflect the point in time specified in the AS OF clause.

There are a few things to consider when using flashback query.

-Flashback query is based on UNDO. As a result, the amount of time you can flashback is dependent on how long undo information is retained, as specified by the UNDO_RETENTION parameter.

-The flashback operation is performed by winding back the data using the undo information, like a consistent get. The time it takes to wind back the data depends on the number of changes that have happened, so flashback queries of volatile data that go back a long time can be slow.

-Flashback Data Archive (FDA) was introduced in Oracle 11g to allow you to protect UNDO information, allowing guaranteed flashback queries.

-Each table in a query can reference a different point in time.

-The AS OF clause can be included in DML and DDL statements.