Tuesday 26 November 2019

SQL Query is really hanged or not



 Monitoring Progress of a SQL Execution Plan / sql query is really hanged or not  

How to see the progress a query is making from within the execution plan used. Using this we can find sql query is really hanged or not.

1. Find the active sessions
2. Monitoring Progress of a SQL Execution Plan
3. DBMS_SQLTUNE.REPORT_SQL_MONITOR

1. Find the active seesions

select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where username='&username';
SELECT sql_id, status, sql_text FROM v$sql_monitor WHERE  username='&username' and status not like '%DONE%';
Output:
———–
SQL> select sid, serial#, username, status, sql_id, event from v$session where username='SH';

       SID    SERIAL# USERNAME                       STATUS   SQL_ID        EVENT
---------- ---------- ------------------------------ -------- ------------- ----------
        24         23 SH                             ACTIVE   5mxdwvuf9j3vp direct path read
        27         61 SH                             ACTIVE   5mxdwvuf9j3vp direct path read
SQL>

2. Monitoring Progress of a SQL Execution Plan

column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status
SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%' and sid='&sid' <---
ORDER BY 1,4;

-- OR ---

column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status
SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%'
ORDER BY 1,4;
Ouput:
       SID SQL_ID        STATUS      LINE OPERATION                       ROWS
---------- ------------- ---------- ----- ------------------------- ----------
        24 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0
                                        1 SORT AGGREGATE                     0
                                        2 NESTED LOOPS                      10
                                        3 PARTITION RANGE ALL            23714 <---
                                        4 TABLE ACCESS FULL              23714 <---
                                        5 TABLE ACCESS FULL                 10
        27 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0
                                        1 SORT AGGREGATE                     0
                                        2 NESTED LOOPS                      10
                                        3 PARTITION RANGE ALL            23781 <---
                                        4 TABLE ACCESS FULL              23781 <---
                                        5 TABLE ACCESS FULL                 10

12 rows selected.

SQL> /

       SID SQL_ID        STATUS      LINE OPERATION                       ROWS
---------- ------------- ---------- ----- ------------------------- ----------
        24 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0
                                        1 SORT AGGREGATE                     0
                                        2 NESTED LOOPS                      10
                                        3 PARTITION RANGE ALL            23737 <-- see diff
                                        4 TABLE ACCESS FULL              23737 <-- see diff
                                        5 TABLE ACCESS FULL                 10
        27 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0
                                        1 SORT AGGREGATE                     0
                                        2 NESTED LOOPS                      10
                                        3 PARTITION RANGE ALL            23804 <--- see diff
                                        4 TABLE ACCESS FULL              23804 <--- see diff
                                        5 TABLE ACCESS FULL                 10

12 rows selected.

SQL> /

       SID SQL_ID        STATUS      LINE OPERATION                       ROWS
---------- ------------- ---------- ----- ------------------------- ----------
        24 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0
                                        1 SORT AGGREGATE                     0
                                        2 NESTED LOOPS                      10
                                        3 PARTITION RANGE ALL            23794 <-- see diff
                                        4 TABLE ACCESS FULL              23794 <-- see diff
                                        5 TABLE ACCESS FULL                 10
        27 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0
                                        1 SORT AGGREGATE                     0
                                        2 NESTED LOOPS                      10
                                        3 PARTITION RANGE ALL            23861 <--- see diff
                                        4 TABLE ACCESS FULL              23861 <--- see diff
                                        5 TABLE ACCESS FULL                 10

12 rows selected.

SQL>

We can see the difference. Hence the query is not hanged

3. REPORT_SQL_MONITOR in HTML (OR) TEXT format
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL report_sql_monitor.txt
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '5mxdwvuf9j3vp', <--- SQLID
  type         => 'TEXT',         <--- HTML
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

Output
=======
SQL Monitoring Report

SQL Text
------------------------------
SELECT ......

Global Information
------------------------------
 Status              :  EXECUTING <----
 Instance ID         :  1
 Session             :  SH (27:61)
 SQL ID              :  5mxdwvuf9j3vp
 SQL Execution ID    :  16777216
 Execution Started   :  10/26/2016 20:19:01 <---
 First Refresh Time  :  10/26/2016 20:19:05
 Last Refresh Time   :  10/26/2016 20:38:25
 Duration            :  1164s <---
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@rac2.amitpawardba.com (TNS V1-V3)

Global Stats
======================================================================
| Elapsed |   Cpu   |    IO    | Concurrency | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Gets  | Reqs | Bytes |
======================================================================
|    1172 |      77 |     1095 |        0.00 |    15M | 537K | 114GB |
======================================================================

SQL Plan Monitoring Details (Plan Hash Value=2043253752)
===========================================================================================================================================================
| Id   |        Operation        |   Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |     Activity Detail     |
|      |                         |           | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |       (# samples)       |
===========================================================================================================================================================
|    0 | SELECT STATEMENT        |           |         |      |           |        |     1 |          |      |       |          |                         |
|    1 |   SORT AGGREGATE        |           |       1 |      |       811 |   +220 |     1 |        0 |      |       |          |                         |
| -> 2 |    NESTED LOOPS         |           |    5557 | 370M |      1167 |     +4 |     1 |        4 |      |       |          |                         |
| -> 3 |     PARTITION RANGE ALL |           |    919K |  499 |      1167 |     +4 |     1 |    10325 |      |       |          |                         |
| -> 4 |      TABLE ACCESS FULL  | SALES     |    919K |  499 |      1167 |     +4 |     5 |    10325 |    1 | 208KB |          |                         |
| -> 5 |     TABLE ACCESS FULL   | CUSTOMERS |       1 |  403 |      1170 |     +1 | 10326 |        4 | 535K | 114GB |   100.00 | Cpu (35)                |
|      |                         |           |         |      |           |        |       |          |      |       |          | direct path read (1128) |
===========================================================================================================================================================

I hope this article helped you. Your suggestions/feedback are most welcome.


Keep learning... Have a great day!!!







Wednesday 14 August 2019

Check the last incarnation restore time of oracle database


Check the last incarnation restore time when the database is started with resetlogs command with following command.

Incarnation is the time when the database is started with resetlogs command. If you start the database is the incarnation, then all available archivelog and backup become invalid. If you want to restore old incarnation, then you need to set the incarnation level in RMAN to restore or recover old SCN.

Always take a fresh backup of the database when you start the database in resetlog. Because when the database is open with resetlogs all redo Sequence no is reset and start from one.

It will show you the last incarnation time when the database is up with resetlogs commands.

SELECT incarnation#,
resetlogs_change#,
TO_CHAR(resetlogs_time, 'dd-mon-yyyy hh24:mi:ss') db_restored_time ,
scn_to_timestamp(resetlogs_change#) db_recovery_till_time
FROM v$database_incarnation
WHERE resetlogs_change# !=
(SELECT MIN(resetlogs_change#) FROM v$database_incarnation
);

List of incarnation:

select incarnation#, resetlogs_change# from v$database_incarnation;

List incarnation from RMAN prompt:

RMAN> list incarnation of database;

When you open the database in resetlogs then a new incarnation is created at the database level. If you tried to restore the database before incarnation state, then you got the error:
RMAN-20208: until change is before resetlogs change

Then you need to check the SCN from incarnation commands:
Example:

RMAN> list incarnation of database;

 DB Key Inc Key DB Name DB ID         STATUS  Reset SCN Reset Time
------ ------- ------- ------------- ------- --------- -----------
1      1       ORCL     1235491512   PARENT  1         03-AUG-17
2      2       ORCL     1235491512   PARENT  543000    10-OCT-17
3      3       ORCL     1235491512   CURRENT 548000    10-OCT-17


If I want to restore the SCN no 54700 previous then the current incarnation number, then I will get the rman error:
RMAN-20208: until change is before resetlogs change

run{
set until scn=547000;
restore database;
recover database;
}
executing command: set until clause
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== error message stack follows ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 10/10/2017 9:10:11
RMAN-20208: until change is before resetlogs change

Solution:
to overcome this problem, we need to set the database to old incarnation:

RMAN> shutdown immediate
RMAN> startup mount
RMAN> reset database to incarnation 2;
database reset to incarnation 2
Then after restore, you can check incarnation status again:

RMAN> run
2> {
3> set until scn=547000;
4> restore database;
5> recover database;
6> }

RMAN> alter database open resetlogs;
database opened
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations

DB Key Inc Key DB Name DB ID         STATUS  Reset SCN Reset Time
------ ------- ------- ------------- ------- --------- -----------
1      1       ORCL     1235491512   PARENT  1         03-AUG-17
2      2       ORCL     1235491512   PARENT  543000    10-OCT-17
4      4       ORCL     1235491512   CURRENT 547000    10-OCT-17
3      3       ORCL     1235491512   ORPHAN  548000    10-OCT-17







I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!