Oracle Database Commands and Practical Examples

Monday 11 January 2021

PT - Using Session Waiting Events with Hung or Very Slow Sessions

Using Session Waiting Events with Hung or Very Slow Sessions

Scenario Preview:

We will update and check wait events of sessions

SQL> conn sys/system123@pdb as sysdba

Connected.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

C:\Users\varun>hostname

DESKTOP-H1NR6BO

C:\Users\varun>sqlplus sys/system123@pdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 6 19:39:09 2021

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn soe/soe@pdb

Connected.

Execute update command on the table salary for scenario.

SQL> UPDATE EMP SET SALARY=SALARY WHERE EMP_NO=104;

1 row updated.

SQL> SELECT SID, EVENT

2 FROM V$SESSION

3 WHERE STATE='WAITING' AND USERNAME ='SOE' AND WAIT_CLASS<>'Idle';

SID EVENT

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

382 log file sync

Wait event information of the hung session from the V$SESSION and V$SESSION_EVENT.

SQL> SELECT SID, EVENT

2 FROM V$SESSION

3 WHERE STATE='WAITING' AND USERNAME ='SOE' AND WAIT_CLASS<>'Idle';

SID EVENT

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

8 enq: TX - row lock contention

138 log file sync

260 log file sync

382 log file sync

871 log file sync

SQL> col SESSION_WAITS format a100

SQL> SELECT 'SID: '|| SID||

2 CHR(10)||'USERNAME: '|| USERNAME||

3 CHR(10)||'STATE: '|| STATE||

4 CHR(10)||'EVENT: '|| EVENT||

5 CHR(10)||'WAIT_TIME: '|| WAIT_TIME||

6 CHR(10)||'SECONDS_IN_WAIT: '|| SECONDS_IN_WAIT||

7 CHR(10)||'WAIT_CLASS: '|| WAIT_CLASS||

8 CHR(10)||'P1TEXT: '|| P1TEXT||

9 CHR(10)||'P1: '|| P1||

10 CHR(10)||'P2TEXT: '|| P2TEXT||

11 CHR(10)||'P2: '|| P2 ||

12 CHR(10)||'P3TEXT: '|| P3TEXT||

13 CHR(10)||'P3: ' || P3 AS SESSION_WAITS

14 FROM V$SESSION

15 WHERE USERNAME='SOE' AND EVENT LIKE 'enq: TX%'

16 ORDER BY WAIT_TIME;

SESSION_WAITS

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

SID: 8

USERNAME: SOE

STATE: WAITING

EVENT: enq: TX - row lock contention

WAIT_TIME: 0

SECONDS_IN_WAIT: 87

WAIT_CLASS: Application

P1TEXT: name|mode

P1: 1415053318

P2TEXT: usn<<16 | slot

P2: 524313

SESSION_WAITS

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

P3TEXT: sequence

P3: 9186

SQL> col EVENT for a30

SQL> SELECT E.EVENT,

2 TO_CHAR(ROUND(E.TIME_WAITED/100),'999,999,999') TIME_SECONDS

3 FROM V$SESSION_EVENT E, V$SESSION S

4 WHERE E.SID=S.SID AND S.USERNAME='SOE' AND E.EVENT LIKE 'enq: TX%'

5 ORDER BY TIME_WAITED;

EVENT TIME_SECONDS

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

enq: TX - index contention 0

enq: TX - row lock contention 468

SQL> SELECT COUNT(*) FROM V$SESSION_WAIT_HISTORY

2 WHERE EVENT LIKE 'enq: TX%';

COUNT(*)

----------

Rollback or commit statement.

SQL> rollback;

Rollback complete.

Using v$session now there is no currently waiting events.

SQL> col EVENT format a40

SQL> col WAIT_CLASS format a10

SQL> SELECT SID, EVENT, WAIT_CLASS

2 FROM V$SESSION

3 WHERE USERNAME='SOE';

SID EVENT WAIT_CLASS

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

8 SQL*Net message from client Idle

138 SQL*Net message from client Idle

260 SQL*Net message from client Idle

382 log file sync Commit

505 SQL*Net message to client Network

871 SQL*Net message from client Idle

6 rows selected.

View reports the wait event that has been waited by the session using V$SESSION_EVENT

SQL> SELECT E.EVENT,

2 TO_CHAR(ROUND(E.TIME_WAITED/100),'999,999,999') TIME_SECONDS

3 FROM V$SESSION_EVENT E, V$SESSION S

4 WHERE E.SID=S.SID AND S.USERNAME='SOE' AND E.EVENT LIKE 'enq: TX%'

5 ORDER BY TIME_WAITED;

EVENT TIME_SECONDS

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

enq: TX - index contention 0

enq: TX - row lock contention 497

SQL>

Information about the wait event that has just expired from the V$SESSION_WAIT_HISTORY

SQL> col SESSION_WAITS format a100

SQL> SELECT 'SID: '|| SID||

2 CHR(10)||'EVENT: '|| EVENT||

3 CHR(10)||'WAIT_TIME: '|| WAIT_TIME||

4 CHR(10)||'P1TEXT: '|| P1TEXT||

5 CHR(10)||'P1: '|| P1||

6 CHR(10)||'P2TEXT: '|| P2TEXT||

7 CHR(10)||'P2: '|| P2 ||

8 CHR(10)||'P3TEXT: '|| P3TEXT||

9 CHR(10)||'P3: ' || P3 AS SESSION_WAITS

10 FROM V$SESSION_WAIT_HISTORY

11 WHERE EVENT LIKE 'enq: TX%'

12 ORDER BY WAIT_TIME;

SESSION_WAITS

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

SID: 8

EVENT: enq: TX - row lock contention

WAIT_TIME: 49718

P1TEXT: name|mode

P1: 1415053318

P2TEXT: usn<<16 | slot

P2: 524313

P3TEXT: sequence

P3: 9186

SQL>

SELECT COUNT(*) FROM V$SESSION_WAIT_HISTORY WHERE EVENT LIKE 'enq: TX%';

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

Scenario Preview:

Recently we perform some row migration and row chaining activity as we find out on window operating system we are unable to increase block size 32kb might be the reason due to page size in window.

Error:

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

C:\Users\varun>sqlplus sys/system123@orcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 4 23:07:56 2021

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> ALTER SYSTEM SET DB_32K_CACHE_SIZE= 160M;

ALTER SYSTEM SET DB_32K_CACHE_SIZE= 160M

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

SQL> CREATE TABLESPACE TBS16K111 DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 32K;

CREATE TABLESPACE TBS16K111 DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 32K

ERROR at line 1:

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

SQL>

SQL> ALTER SYSTEM SET DB_16K_CACHE_SIZE= 160M;

System altered.

SQL> SELECT NAME, BLOCK_SIZE, CURRENT_SIZE FROM V$BUFFER_POOL;

NAME BLOCK_SIZE CURRENT_SIZE

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

DEFAULT 8192 3552

DEFAULT 16384 160

SQL>

SQL> CREATE TABLESPACE TBS16K DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 16K;

Tablespace created.

SQL>

Solution Testing :

Set DB_16K_CACHE_SIZE scope is memory

SQL> ALTER SYSTEM SET DB_16K_CACHE_SIZE= 160M;

System altered.

SQL> SELECT NAME, BLOCK_SIZE, CURRENT_SIZE FROM V$BUFFER_POOL;

NAME BLOCK_SIZE CURRENT_SIZE

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

DEFAULT 8192 3552

DEFAULT 16384 160

Create Tablespace TBS16K with block size 16K

SQL> CREATE TABLESPACE TBS16K DATAFILE 'C:\APP\VARUN\ORADATA\ORCL\PDB\tbs16k.dbf' SIZE 160M AUTOEXTEND ON MAXSIZE 1000M BLOCKSIZE 16K;

Tablespace created.

Set Quota to schema on tablespace

SQL> ALTER USER SOE QUOTA UNLIMITED ON TBS16K;

User altered.

SQL> conn soe/soe@pdb

Connected.

Move table cust to new tablespace

SQL> ALTER TABLE CUST MOVE TABLESPACE tbs16k;

Table altered.

Thursday 7 January 2021

Install SwingBench On Window

Download swingbench for window.
Pre Requestors : install Java JDK 8 on winow10.
Install oracle client.
Open command Prompt and set oracle client path.

set PATH=C:\app\client\varun\product\19.0.0\client_1\jdk\jre\bin;%PATH%

Go to swingbench path location :

\swingbenchlatest\swingbench\winbin

run Swingbench.bat

Set database connection string

Oracle Database connection through cloud.

HOME

Monday 11 January 2021

PT - Using Session Waiting Events with Hung or Very Slow Sessions

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

Thursday 7 January 2021

Install SwingBench On Window