Oracle DBA Topics

TEMP consuming queries findout method.

How to find query which consumed high temp .

It might be useful .

Thanks & Regards

Rakesh

Rakesh Karam| TCS | Application DBA

AS&M| BT Technology, Service & Operations|TelOff: 03366881494| Mob: +918792287671|E: rakesh.karam@bt.com

From: Karam,R,Rakesh,TAJ C
Sent: 21 July 2017 20:18
To: Karam,R,Rakesh,TMR C (rakesh.karam@bt.com)
Subject: IDENTIFYING QUERY WHICH CONSUMED WHOLE TEMP - BX409105

*** NOTES 11/07/2016 09:21:34 sa Action Type: PM Team

AutoClarify Fault Raised By OMNIBUS User cwhpopsdba

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

Search for Guided Actions on the BT Operations (Non-DBA) Guided Actions System :

http://omnihist2.nat.bt.com/cgi-bin/alarmmis/new/gas3.pl?action=Lookup&project=THEBTPEOPLESYSTEM&class=&nodealias=dyl03839dat01&summary=Database%20Entity%20Failed:%20AUTOCLARIFY:DBAORA2L:2%20~%20HP1D%20elmd1%20ORA-01652%20~%20ORA-1652:%20

Event ID: 668107361

PROJECT: THEBTPEOPLESYSTEM

APPLICATION: The BT People System

APPID: APP03839

NODE: dyl03839dat01

IP ADDRESS: 10.35.33.100

PROBE: trapd from dycol01

SITE: Derby Data Centre,Raynesway,Alvaston,Derby,DERBY,DE21 7BX

BUILDING: RYN-DE

SKYLINE: DE0070A1

TALLY: 1

FIRST: 11/07/16,09:18

LAST: 11/07/16,09:18

SUBPROJECT: NULL DATA

SUMMARY: Database Entity Failed: AUTOCLARIFY:DBAORA2L:2 ~ HP1D elmd1 ORA-01652 ~ ORA-1652:

*** RESEARCH LOG 11/07/2016 09:23:33 ccaxh08 Action Type: Action Taken

*** Master CWHPOPSDBA Andrew Heaps 0114 277 4032 ccaxh08

alert log shows

Mon Jul 11 09:18:00 2016

ORA-1652: unable to extend temp segment by 8 in tablespace PSTEMP

*** RESEARCH LOG 11/07/2016 09:24:41 ccaxh08 Action Type: Action Taken

*** Master CWHPOPSDBA Andrew Heaps 0114 277 4032 ccaxh08

database ok

log switching ok

NAME DATABASE_ROLE OPEN_MODE HOST_NAME INSTANCE_NAME STATUS STARTUP_TIME

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

ELM PRIMARY READ WRITE dyl03839dat01 elmd1 OPEN 08-03-16 01:02:53

SQL> SQL> alter system switch logfile;

System altered.

*** RESEARCH LOG 11/07/2016 09:25:58 ccaxh08 Action Type: Action Taken

*** Master CWHPOPSDBA Andrew Heaps 0114 277 4032 ccaxh08

FILE_NAME Size(MB) AUT MaxSize(MB)

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

+DATA_DG01/elm_dy/tempfile/pstemp.1024.747673845 12288 YES 12288

+DATA_DG01/elm_dy/tempfile/pstemp.1025.747673849 12288 YES 12288

Temp tablespace - not service affecting

*** RESEARCH LOG 11/07/2016 11:43:55 ccaxh08 Action Type: Action Taken

*** Master CWHPOPSDBA Andrew Heaps 0114 277 4032 ccaxh08

SQL> select distinct to_char (timestamp, 'dd-mm-yy hh24:mi:ss')as timestamp, SQL_ID, TEMP_SPACE

from DBA_HIST_SQL_PLAN

where timestamp like '%11-JUL-16%'

and TEMP_SPACE is not null order by timestamp;

2 3 4

TIMESTAMP SQL_ID TEMP_SPACE

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

11-07-16 03:46:25 bmhc6q7hcfvuc 3064000

11-07-16 03:46:54 g42puaptcfz0z 2606000

11-07-16 03:46:54 g42puaptcfz0z 30589000

11-07-16 09:09:50 bddjks97k003q 36357000

11-07-16 09:09:50 bddjks97k003q 89277000

11-07-16 09:09:50 bddjks97k003q 1.8447E+19

11-07-16 09:35:15 39vr01mym6bs6 36357000

11-07-16 09:35:15 39vr01mym6bs6 89277000

11-07-16 09:35:15 39vr01mym6bs6 1.8447E+19

11-07-16 10:33:50 9rq12x1x46qm8 19497000

10 rows selected.

bddjks97k003q

39vr01mym6bs6

checking sql

Using the report name awrsqlrpt_1_46522_46523.txt

WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name DB Id Instance Inst Num Startup Time Release RAC

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

ELM 3884532509 elmd1 1 08-Mar-16 01:02 11.1.0.7.0 YES

Snap Id Snap Time Sessions Curs/Sess

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

Begin Snap: 46522 11-Jul-16 09:00:08 215 3.9

End Snap: 46523 11-Jul-16 10:00:17 217 3.9

Elapsed: 60.14 (mins)

DB Time: 37.17 (mins)

SQL Summary DB/Inst: ELM/elmd1 Snaps: 46522-46523

Elapsed

SQL Id Time (ms)

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

bddjks97k003q 490,155

Module: QUERY_MANAGER

QUERY_VIEWER

SELECT E.LM_HR_EMPLID, D.NAME, G.LM_HR_DEPTID, A.BT_LM_INSTR_SKILL, B.LM_CRSE_CO

DE, B.LM_CS_LONG_NM, C.LM_ROLE, B.LM_CI_STATUS, F.LM_ACTIVE, TO_CHAR(SYSDATE,'YY

YY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'),A.LM_PERSON_ID,A.LM_CI_ID,B.LM_CI_ID,T

O_CHAR(B.EFFDT,'YYYY-MM-DD'),C.LM_ROLE_ID FROM PS_LM_INSTR_QUAL A, PS_LM_CI_T

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

SQL ID: bddjks97k003q DB/Inst: ELM/elmd1 Snaps: 46522-46523

-> 1st Capture and Last Capture Snap IDs

refer to Snapshot IDs witin the snapshot range

-> SELECT E.LM_HR_EMPLID, D.NAME, G.LM_HR_DEPTID, A.BT_LM_INSTR_SKILL, B....

Plan Hash Total Elapsed 1st Capture Last Capture

# Value Time(ms) Executions Snap ID Snap ID

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

1 3324042429 490,155 1 46523 46523

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

Plan 1(PHV: 3324042429)

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

Plan Statistics DB/Inst: ELM/elmd1 Snaps: 46522-46523

-> % Total DB Time is the Elapsed Time of the SQL statement divided

into the Total Database Time multiplied by 100

Stat Name Statement Per Execution % Snap

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

Elapsed Time (ms) 490,155 490,154.8 22.0

CPU Time (ms) 328,234 328,234.1 27.9

Executions 1 N/A N/A

Buffer Gets 72,874 72,874.0 0.3

Disk Reads 71,283 71,283.0 9.5

Parse Calls 1 1.0 0.0

Rows 0 0.0 N/A

User I/O Wait Time (ms) 4,338 N/A N/A

Cluster Wait Time (ms) 0 N/A N/A

Application Wait Time (ms) 1 N/A N/A

Concurrency Wait Time (ms) 0 N/A N/A

Invalidations 0 N/A N/A

Version Count 1 N/A N/A

Sharable Mem(KB) 187 N/A N/A

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

Execution Plan

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

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

| 0 | SELECT STATEMENT | | | | | 18E(100)| |

| 1 | SORT ORDER BY | | 18E| 15E| 15E| 18E (0)|999:59:59 |

| 2 | FILTER | | | | | | |

| 3 | MERGE JOIN | | 18E| 15E| | 18E (0)|999:59:59 |

| 4 | MERGE JOIN | | 18E| 15E| | 18E (0)|999:59:59 |

| 5 | SORT JOIN | | 18E| 15E| 15E| 18E (0)|999:59:59 |

| 6 | MERGE JOIN CARTESIAN | | 18E| 15E| | 18E (0)|999:59:59 |

| 7 | MERGE JOIN CARTESIAN | | 18E| 15E| | 18E (0)|999:59:59 |

| 8 | MERGE JOIN CARTESIAN | | 18E| 15E| | 18E (0)|999:59:59 |

| 9 | MERGE JOIN CARTESIAN | | 87P| 9636P| | 464T (2)|999:59:59 |

| 10 | MERGE JOIN CARTESIAN | | 22G| 2137G| | 48M (2)|188:36:00 |

| 11 | MERGE JOIN CARTESIAN | | 60645 | 5093K| | 1249 (1)| 00:00:18 |

| 12 | TABLE ACCESS FULL | PS_LM_ROLE_TBL | 13 | 299 | | 3 (0)| 00:00:01 |

| 13 | BUFFER SORT | | 4665 | 287K| | 1246 (1)| 00:00:18 |

| 14 | TABLE ACCESS FULL | PS_LM_CI_TBL | 4665 | 287K| | 96 (2)| 00:00:02 |

| 15 | BUFFER SORT | | 374K| 5488K| | 48M (2)|188:35:59 |

| 16 | TABLE ACCESS FULL | PS_LM_PERSON | 374K| 5488K| | 800 (2)| 00:00:12 |

| 17 | BUFFER SORT | | 3850K| 84M| | 464T (2)|999:59:59 |

| 18 | TABLE ACCESS FULL | PS_LM_PERSON_ATTRB | 3850K| 84M| | 20436 (2)| 00:04:47 |

| 19 | BUFFER SORT | | 1156K| 23M| | 18E (0)|999:59:59 |

| 20 | TABLE ACCESS FULL | PS_LM_ORGANIZATION | 1156K| 23M| | 6046 (3)| 00:01:25 |

| 21 | BUFFER SORT | | 8668 | 135K| | 18E (0)|999:59:59 |

| 22 | TABLE ACCESS FULL | PS_LM_INSTR_QUAL | 8668 | 135K| | 16 (0)| 00:00:01 |

| 23 | BUFFER SORT | | 374K| 1829K| | 18E (0)|999:59:59 |

| 24 | INDEX FULL SCAN | PS_LM_PERSON | 374K| 1829K| | 5 (20)| 00:00:01 |

| 25 | SORT JOIN | | 740K| 31M| 85M| 9196 (3)| 00:02:09 |

| 26 | TABLE ACCESS FULL | PS_LM_PERSON_NAME | 740K| 31M| | 3942 (3)| 00:00:56 |

| 27 | SORT JOIN | | 453K| 10M| 34M| 22938 (4)| 00:05:22 |

| 28 | TABLE ACCESS FULL | PS_LM_PERSON_ATTRB | 453K| 10M| | 20849 (4)| 00:04:52 |

| 29 | INDEX RANGE SCAN | PSBLM_PERSON_NAME | 1 | 9 | | 1 (0)| 00:00:01 |

| 30 | SORT AGGREGATE | | 1 | 23 | | | |

| 31 | INDEX RANGE SCAN | PS_LM_CI_TBL | 2 | 46 | | 1 (0)| 00:00:01 |

| 32 | SORT AGGREGATE | | 1 | 25 | | | |

| 33 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 11 | 275 | | 1 (0)| 00:00:01 |

| 34 | SORT AGGREGATE | | 1 | 24 | | | |

| 35 | INDEX RANGE SCAN | PS_LM_ORGANIZATION | 3 | 72 | | 1 (0)| 00:00:01 |

| 36 | TABLE ACCESS BY INDEX ROWID | PS_LM_PERSON_ATTRB | 1 | 17 | | 1 (0)| 00:00:01 |

| 37 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 1 | | | 1 (0)| 00:00:01 |

| 38 | SORT AGGREGATE | | 1 | 25 | | | |

| 39 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 11 | 275 | | 1 (0)| 00:00:01 |

| 40 | SORT AGGREGATE | | 1 | 19 | | | |

| 41 | FILTER | | | | | | |

| 42 | TABLE ACCESS BY INDEX ROWID| PS_LM_PERSON_ATTRB | 1 | 19 | | 1 (0)| 00:00:01 |

| 43 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 1 | | | 1 (0)| 00:00:01 |

| 44 | SORT AGGREGATE | | 1 | 25 | | | |

| 45 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 1 | 25 | | 1 (0)| 00:00:01 |

| 46 | TABLE ACCESS BY INDEX ROWID| PS_LM_PERSON_ATTRB | 1 | 19 | | 1 (0)| 00:00:01 |

| 47 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 1 | | | 1 (0)| 00:00:01 |

| 48 | SORT AGGREGATE | | 1 | 25 | | | |

| 49 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 1 | 25 | | 1 (0)| 00:00:01 |

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

Full SQL Text

SQL ID SQL Text

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

bddjks97k003 SELECT E.LM_HR_EMPLID, D.NAME, G.LM_HR_DEPTID, A.BT_LM_INSTR_SKIL

LL, B.LM_CRSE_CODE, B.LM_CS_LONG_NM, C.LM_ROLE, B.LM_CI_STATUS, F

.LM_ACTIVE, TO_CHAR(SYSDATE, 'YYYY-MM-DD'), TO_CHAR(SYSDATE, 'YYY

Y-MM-DD'), A.LM_PERSON_ID, A.LM_CI_ID, B.LM_CI_ID, TO_CHAR(B.EFFD

T, 'YYYY-MM-DD'), C.LM_ROLE_ID FROM PS_LM_INSTR_QUAL A, PS_LM_CI_

TBL B, PS_LM_ROLE_TBL C, PS_LM_PERS_NAME_VW D, PS_LM_PERSON E, PS

_LM_PERSON_ATTRB F, PS_LM_ORGANIZATION G WHERE A.LM_CI_ID = B.LM_

CI_ID AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_LM_CI_TBL B_E

D WHERE B.LM_CI_ID = B_ED.LM_CI_ID AND B_ED.EFFDT <= SYSDATE) AND

C.LM_ROLE_ID = A.LM_ROLE_ID AND C.LM_ROLE_TYPE = '0020' AND A.LM

_PERSON_ID = D.LM_PERSON_ID AND D.LM_PERSON_ID = E.LM_PERSON_ID A

ND A.LM_PERSON_ID = F.LM_PERSON_ID AND F.EFFDT = (SELECT MAX(F_ED

.EFFDT) FROM PS_LM_PERSON_ATTRB F_ED WHERE F.LM_PERSON_ID = F_ED.

LM_PERSON_ID AND F.LM_EMPL_RCD = F_ED.LM_EMPL_RCD AND F_ED.EFFDT

<= SYSDATE) AND G.LM_ORGANIZATION_ID = F.LM_ORGANIZATION_ID AND G

.EFFDT = (SELECT MAX(G_ED.EFFDT) FROM PS_LM_ORGANIZATION G_ED WHE

RE G.LM_ORGANIZATION_ID = G_ED.LM_ORGANIZATION_ID AND G_ED.EFFDT

<= SYSDATE) AND G.LM_HR_DEPTID LIKE 'BA%' AND F.LM_ACTIVE = 'Y' A

ND A.BT_LM_INSTR_SKILL = '01' AND B.LM_CI_STATUS = '10' AND C.LM_

ROLE = 'Instructor' OR C.LM_ROLE = 'External Associate' OR C.LM_R

OLE = 'Internal Associate' ORDER BY 1

Report written to awrsqlrpt_1_46522_46523.txt

WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name DB Id Instance Inst Num Startup Time Release RAC

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

ELM 3884532509 elmd1 1 08-Mar-16 01:02 11.1.0.7.0 YES

Snap Id Snap Time Sessions Curs/Sess

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

Begin Snap: 46522 11-Jul-16 09:00:08 215 3.9

End Snap: 46523 11-Jul-16 10:00:17 217 3.9

Elapsed: 60.14 (mins)

DB Time: 37.17 (mins)

SQL Summary DB/Inst: ELM/elmd1 Snaps: 46522-46523

Elapsed

SQL Id Time (ms)

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

39vr01mym6bs6 614,101

Module: PSQRYSRV@dyl03839app05 (TNS V1-V3)

xyzzy

SELECT E.LM_HR_EMPLID, D.NAME, G.LM_HR_DEPTID, A.BT_LM_INSTR_SKILL, B.LM_CRSE_CO

DE, B.LM_CS_LONG_NM, C.LM_ROLE, B.LM_CI_STATUS, F.LM_ACTIVE, TO_CHAR(SYSDATE,'YY

YY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'),A.LM_PERSON_ID,A.LM_CI_ID,B.LM_CI_ID,T

O_CHAR(B.EFFDT,'YYYY-MM-DD'),C.LM_ROLE_ID FROM PS_LM_INSTR_QUAL A, PS_LM_CI_T

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

SQL ID: 39vr01mym6bs6 DB/Inst: ELM/elmd1 Snaps: 46522-46523

-> 1st Capture and Last Capture Snap IDs

refer to Snapshot IDs witin the snapshot range

-> SELECT E.LM_HR_EMPLID, D.NAME, G.LM_HR_DEPTID, A.BT_LM_INSTR_SKILL, B....

Plan Hash Total Elapsed 1st Capture Last Capture

# Value Time(ms) Executions Snap ID Snap ID

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

1 3324042429 614,101 1 46523 46523

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

Plan 1(PHV: 3324042429)

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

Plan Statistics DB/Inst: ELM/elmd1 Snaps: 46522-46523

-> % Total DB Time is the Elapsed Time of the SQL statement divided

into the Total Database Time multiplied by 100

Stat Name Statement Per Execution % Snap

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

Elapsed Time (ms) 614,101 614,100.8 27.5

CPU Time (ms) 340,203 340,203.3 28.9

Executions 1 N/A N/A

Buffer Gets 72,878 72,878.0 0.3

Disk Reads 69,627 69,627.0 9.3

Parse Calls 1 1.0 0.0

Rows 0 0.0 N/A

User I/O Wait Time (ms) 1,297 N/A N/A

Cluster Wait Time (ms) 0 N/A N/A

Application Wait Time (ms) 14 N/A N/A

Concurrency Wait Time (ms) 0 N/A N/A

Invalidations 0 N/A N/A

Version Count 1 N/A N/A

Sharable Mem(KB) 187 N/A N/A

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

Execution Plan

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

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

| 0 | SELECT STATEMENT | | | | | 18E(100)| |

| 1 | SORT ORDER BY | | 18E| 15E| 15E| 18E (0)|999:59:59 |

| 2 | FILTER | | | | | | |

| 3 | MERGE JOIN | | 18E| 15E| | 18E (0)|999:59:59 |

| 4 | MERGE JOIN | | 18E| 15E| | 18E (0)|999:59:59 |

| 5 | SORT JOIN | | 18E| 15E| 15E| 18E (0)|999:59:59 |

| 6 | MERGE JOIN CARTESIAN | | 18E| 15E| | 18E (0)|999:59:59 |

| 7 | MERGE JOIN CARTESIAN | | 18E| 15E| | 18E (0)|999:59:59 |

| 8 | MERGE JOIN CARTESIAN | | 18E| 15E| | 18E (0)|999:59:59 |

| 9 | MERGE JOIN CARTESIAN | | 87P| 9636P| | 464T (2)|999:59:59 |

| 10 | MERGE JOIN CARTESIAN | | 22G| 2137G| | 48M (2)|188:36:00 |

| 11 | MERGE JOIN CARTESIAN | | 60645 | 5093K| | 1249 (1)| 00:00:18 |

| 12 | TABLE ACCESS FULL | PS_LM_ROLE_TBL | 13 | 299 | | 3 (0)| 00:00:01 |

| 13 | BUFFER SORT | | 4665 | 287K| | 1246 (1)| 00:00:18 |

| 14 | TABLE ACCESS FULL | PS_LM_CI_TBL | 4665 | 287K| | 96 (2)| 00:00:02 |

| 15 | BUFFER SORT | | 374K| 5488K| | 48M (2)|188:35:59 |

| 16 | TABLE ACCESS FULL | PS_LM_PERSON | 374K| 5488K| | 800 (2)| 00:00:12 |

| 17 | BUFFER SORT | | 3850K| 84M| | 464T (2)|999:59:59 |

| 18 | TABLE ACCESS FULL | PS_LM_PERSON_ATTRB | 3850K| 84M| | 20436 (2)| 00:04:47 |

| 19 | BUFFER SORT | | 1156K| 23M| | 18E (0)|999:59:59 |

| 20 | TABLE ACCESS FULL | PS_LM_ORGANIZATION | 1156K| 23M| | 6046 (3)| 00:01:25 |

| 21 | BUFFER SORT | | 8668 | 135K| | 18E (0)|999:59:59 |

| 22 | TABLE ACCESS FULL | PS_LM_INSTR_QUAL | 8668 | 135K| | 16 (0)| 00:00:01 |

| 23 | BUFFER SORT | | 374K| 1829K| | 18E (0)|999:59:59 |

| 24 | INDEX FULL SCAN | PS_LM_PERSON | 374K| 1829K| | 5 (20)| 00:00:01 |

| 25 | SORT JOIN | | 740K| 31M| 85M| 9196 (3)| 00:02:09 |

| 26 | TABLE ACCESS FULL | PS_LM_PERSON_NAME | 740K| 31M| | 3942 (3)| 00:00:56 |

| 27 | SORT JOIN | | 453K| 10M| 34M| 22938 (4)| 00:05:22 |

| 28 | TABLE ACCESS FULL | PS_LM_PERSON_ATTRB | 453K| 10M| | 20849 (4)| 00:04:52 |

| 29 | INDEX RANGE SCAN | PSBLM_PERSON_NAME | 1 | 9 | | 1 (0)| 00:00:01 |

| 30 | SORT AGGREGATE | | 1 | 23 | | | |

| 31 | INDEX RANGE SCAN | PS_LM_CI_TBL | 2 | 46 | | 1 (0)| 00:00:01 |

| 32 | SORT AGGREGATE | | 1 | 25 | | | |

| 33 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 11 | 275 | | 1 (0)| 00:00:01 |

| 34 | SORT AGGREGATE | | 1 | 24 | | | |

| 35 | INDEX RANGE SCAN | PS_LM_ORGANIZATION | 3 | 72 | | 1 (0)| 00:00:01 |

| 36 | TABLE ACCESS BY INDEX ROWID | PS_LM_PERSON_ATTRB | 1 | 17 | | 1 (0)| 00:00:01 |

| 37 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 1 | | | 1 (0)| 00:00:01 |

| 38 | SORT AGGREGATE | | 1 | 25 | | | |

| 39 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 11 | 275 | | 1 (0)| 00:00:01 |

| 40 | SORT AGGREGATE | | 1 | 19 | | | |

| 41 | FILTER | | | | | | |

| 42 | TABLE ACCESS BY INDEX ROWID| PS_LM_PERSON_ATTRB | 1 | 19 | | 1 (0)| 00:00:01 |

| 43 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 1 | | | 1 (0)| 00:00:01 |

| 44 | SORT AGGREGATE | | 1 | 25 | | | |

| 45 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 1 | 25 | | 1 (0)| 00:00:01 |

| 46 | TABLE ACCESS BY INDEX ROWID| PS_LM_PERSON_ATTRB | 1 | 19 | | 1 (0)| 00:00:01 |

| 47 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 1 | | | 1 (0)| 00:00:01 |

| 48 | SORT AGGREGATE | | 1 | 25 | | | |

| 49 | INDEX RANGE SCAN | PS_LM_PERSON_ATTRB | 1 | 25 | | 1 (0)| 00:00:01 |

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

Full SQL Text

SQL ID SQL Text

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

39vr01mym6bs SELECT E.LM_HR_EMPLID, D.NAME, G.LM_HR_DEPTID, A.BT_LM_INSTR_SKIL