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
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));
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
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';
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.