Sunday, 23 July 2017

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 :
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
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   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
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   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
             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 = 'Internal Associate' OR C.LM_R
             OLE = 'External Associate' ORDER BY 1


Report written to awrsqlrpt_1_46522_46523.txt
SQL>

Not service affecting
Passing to ASG to tune the code
***  Changed to Priority  3  at 11/07/2016 12:05:53  *** ccaxh08 : TEMP tablespace so not service affecting
*** STATUS CHANGE  14/07/2016 21:11:22 nurqk00
Temp usage was high only for 5 to 10min. later It went down. No issue right now hence restoring the case

*** Case Parked 14/07/2016 21:11:25 nurqk00
Service has been restored


*** STATUS CHANGE  28/07/2016 22:12:19 nurqk00
closing as server type

*** Case  Un-Parked 28/07/2016 22:12:23 nurqk00



*** CASE CLOSE 28/07/2016 17:42:23 sa
Case Closed

*** Case Parked 28/07/2016 22:12:25 nurqk00
Case has been solved


*** NOTES 28/07/2016 17:42:54 sa Action Type: PM Team
**--OST Calculation--**
**Pre-suspend OST**
Contract = Priority 3
Biz Cal Hdr ID = 268435758
Report Time = 11/07/2016 09:21:33
Restore Time = 14/07/2016 04:51:38
Pre-suspend OST (secs) = 103107
**Suspend OST Calculation**
Contract = Priority 3
Biz Cal Hdr ID = 268435758
**Suspend OST Calculation**
Pre-Suspend OST Seconds = 103107
Suspend Seconds= 0
Final OST Seconds= 103107
**End OST Calculation**

[!<For Internal Use Only
OST Calculation!]



Thanks & Regards   
Rakesh
Rakesh Karam| TCS | Application DBA
AS&M| BT Technology, Service & Operations|TelOff: 03366881494| Mob: +918792287671|E: rakesh.karam@bt.com


No comments:

Post a Comment