Amit Oracle DBA Blog

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

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

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

| 1172 | 77 | 1095 | 0.00 | 15M | 537K | 114GB |

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

SQL Plan Monitoring Details (Plan Hash Value=2043253752)

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

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

| 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) |

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

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!!!

Monday, 19 June 2017

Script to Drop a user's objects.

This script will produce the alter/drop commands that we can use to drop all the user objects from a database.

REM Script to drop all user objects

SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF VERIFY OFF

BREAK ON username

TTITLE left _date center 'SQL To Drop A Users Objects' skip 2

PROMPT When prompted, enter the name of the user who''s objects you wish to drop

PROMPT Then cut and paste the commands presented to drop those objects.

SELECT 'alter table '

|| owner

|| '.'

|| table_name

|| ' drop constraint '

|| constraint_name

|| ' cascade;' code

FROM dba_constraints

WHERE (constraint_type = 'R') AND (owner = UPPER ('&&enter_user_name'))

UNION

SELECT DISTINCT 'DROP '

|| object_type

|| ' '

|| owner

|| '.'

|| object_name

|| ';' code

FROM dba_objects

WHERE (object_type NOT IN ('INDEX'))

AND (owner = UPPER ('&&enter_user_name'))

ORDER BY code DESC;

Sample Output

SQL> When prompted, enter the name of the user who’s objects you wish to drop

SQL> Then cut and paste the commands presented to drop those objects.

18-JUL-08 SQL To Drop A Users Objects

CODE

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

DROP TABLE SCOTT.UMCATEGORYCONVFACTOR;

DROP TABLE SCOTT.UOMCATEGORYCONVFACTOR;

DROP TABLE SCOTT.UDT_SOURCING;

DROP TABLE SCOTT.WEBSPRELOPTS;

DROP TABLE SCOTT.WEBSKUOPTS;

DROP TABLE SCOTT.WDDDATA;

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

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

Tuesday, 16 May 2017

Creating sql baseline to fix query with better execution plan

Hello everyone, hope you all doing good. It’s been too long I haven’t updated new post on my blog due to my busy schedule.

So here I will be sharing very interesting post of creating sql baseline and force query to use better execution plan (plan hash value).

Most of us have come across this scenario where query which was running fine till yesterday now suddenly running long.

So there could be many reasons why query performance has been changed suddenly, so one of the reason is change in query plan.

In such situation we need to find out best execution plan (Plan_hash_value) and force query to use that plan.

Below are the steps to create and fix bad query by creating sql baseline.

STEP 1: GENERATE ALL PREVIOUS HISTORY RUN DETAILS OF SQL_ID FROM AWR

break off sdate

set lines 2000

set linesize 2000

col SDATE format a10

col STIME format a10

select to_char(begin_interval_time,'YYYY/MM/DD') SDATE,to_char(begin_interval_time,'HH24:MI') STIME,s.snap_id,

sql_id, plan_hash_value PLAN,

ROUND(elapsed_time_delta/1000000,2) ET_SECS,

nvl(executions_delta,0) execs,

ROUND((elapsed_time_delta/decode(executions_delta,null,1,0,1,executions_delta))/1000000,2) ET_PER_EXEC,

ROUND((buffer_gets_delta/decode(executions_delta,null,1,0,1,executions_delta)), 2) avg_lio,

ROUND((CPU_TIME_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000, 2) avg_cpu_ms,

ROUND((IOWAIT_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000, 2) avg_iow_ms,

ROUND((DISK_READS_DELTA/decode(executions_delta,null,1,0,1,executions_delta)), 2) avg_pio,

ROWS_PROCESSED_DELTA num_rows

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where s.sql_id = '&sql_id'

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

order by sdate,stime;

Enter value for sql_id: 7hgwdax4mn20v

SDATE STIME SNAP_ID SQL_ID PLAN ET_SECS EXECS ET_PER_EXEC AVG_LIO AVG_CPU_MS AVG_IOW_MS AVG_PIO NUM_ROWS

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

2016/09/13 11:00 23468 7hgwdax4mn20v 2844841640 1421.36 1 1421.36 4,301.0 152720.78 312.25 269 336

2016/09/13 15:00 23472 7hgwdax4mn20v 2844841640 1070.12 1 1070.12 4,291.0 126987.69 348.34 270 336

2016/09/13 17:00 23474 7hgwdax4mn20v 2844841640 1211.98 1 1211.98 4,374.0 183347.13 157.32 270 336

2016/09/13 18:00 23475 7hgwdax4mn20v 2844841640 596.91 0 596.91 116.0 45123.14 0 0 0

2016/09/14 01:00 23482 7hgwdax4mn20v 2844841640 1038.41 1 1038.41 4,309.0 123455.23 413.94 272 336

2016/09/14 02:00 23483 7hgwdax4mn20v 1355798266 193.82 1 193.82 2,748.0 76799.32 28.95 16 168

2016/09/14 03:00 23484 7hgwdax4mn20v 2844841640 .04 0 .04 .0 38 0 0 0

2016/09/14 03:00 23484 7hgwdax4mn20v 0 .04 0 .04 .0 38.99 0 0 0

2016/09/14 10:00 23491 7hgwdax4mn20v 2844841640 626.06 0 626.06 64.0 54658.69 0 0 0

/*In this scenario sql_id=7hgwdax4mn20v and plan_hash_value for good plan that we want to force is 1355798266.*/

Follow the below steps to create sql baseline for sql_id

STEP 2: DROP SQL TUNING SET (STS) IF EXISTS

BEGIN

DBMS_SQLTUNE.DROP_SQLSET(

sqlset_name => 'SQL_FOR_7hgwdax4mn20v');

END;

STEP 3: CREATE SQL TUNING SET

BEGIN

DBMS_SQLTUNE.create_sqlset (

sqlset_name => 'SQL_FOR_7hgwdax4mn20v',

description => 'SQL tuning set for 7hgwdax4mn20v');

END;

/* Populate STS from AWR by specifying snapshot for desired plan which we found using above query.

In this scenario snap id's are 23483 and 23484 and change plan_hash_value accordingly.*/

DECLARE

l_cursor DBMS_SQLTUNE.sqlset_cursor;

BEGIN

OPEN l_cursor FOR

SELECT VALUE(p)

FROM TABLE (DBMS_SQLTUNE.select_workload_repository (

23483, -- begin_snap

23484, -- end_snap

q'<sql_id in ('7hgwdax4mn20v') and plan_hash_value in (1355798266)>', -- basic_filter

NULL, -- object_filter

NULL, -- ranking_measure1

NULL, -- ranking_measure2

NULL, -- ranking_measure3

NULL, -- result_percentage

100) -- result_limit

) p;

DBMS_SQLTUNE.load_sqlset (

sqlset_name => 'SQL_FOR_7hgwdax4mn20v',

populate_cursor => l_cursor);

END;

STEP 4: CHECK SQL SET DETAILS

column text format a20

select sqlset_name, sqlset_owner, sqlset_id, sql_id,substr(sql_text,1,20) text,elapsed_time,buffer_gets,

parsing_schema_name, plan_hash_value, bind_data from dba_sqlset_statements where sqlset_name ='SQL_FOR_7hgwdax4mn20v';

STEP 5: LOAD THE DESIRED PLAN FROM STS AS SQL PLAN BASELINE

DECLARE

L_PLANS_LOADED PLS_INTEGER;

BEGIN

L_PLANS_LOADED := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(

SQLSET_NAME => 'SQL_FOR_7hgwdax4mn20v');

END;

STEP 6: CHECK SQL PLAN BASELINE INFORMATION

SELECT sql_handle, plan_name,enabled,accepted,fixed FROM dba_sql_plan_baselines

WHERE signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')

order by accepted,enabled;

STEP 7: ENABLE FIXED=YES

var pbsts varchar2(30);

exec :pbsts := dbms_spm.alter_sql_plan_baseline('SQL_64e76e773c55f1c4','SQL_PLAN_69tvffwy5bwf480a6275e','FIXED','YES');

STEP 8: Check the SQL baseline details

Use the plan name received from the STEP 6 output to get the baseline details

SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_69tvffwy5bwf480a6275e'));

STEP 9: PURGE OLD EXECUTION PLAN FROM SHARED POOL

Find below two parameters required to purge specific sql from the shared pool.

select address||','||hash_value from gv$sqlarea where sql_id = '7hgwdax4mn20v';

ADDRESS||','||HASH_VALUE

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

000000058B4B7E40,1879818331

Now use the below command to purge sql from the shared pool.

exec sys.dbms_shared_pool.purge('000000058B4B7E40,1879818331','C',1);

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

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

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011

MYSQL

Tuesday, 26 November 2019

SQL Query is really hanged or not

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

Wednesday, 14 August 2019

Check the last incarnation restore time of oracle database

Monday, 19 June 2017

Script to Drop a user's objects.

Tuesday, 16 May 2017

Creating sql baseline to fix query with better execution plan

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