[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Jobs take Extremely long time to run. Please help.

Posted on 2004-11-08
19
Medium Priority
?
1,520 Views
Last Modified: 2010-05-18
Hello everyone,

(running system: Oracle 9i (9.024), Windows 2000, Pentium 4, dual processes, 2GB RAM)

Here's the problem.  I have a few scheduled nightly jobs and for some reason lately, they take a very long time to run.  Originally, this one particular job used to take about 15 minutes; now it takes about 2 hours.  I haven't changed any parameters on the dB nor did I make any changes to the software.  Also, the flat files that are being imported haven't changed either (data amount that is).  So now I'm trying to figure out that the problem may be.  I did analyze tables for all schemas, but that didn't help much.  the job process would decrease to about 1 hour and  the next day it would go back to 2 hours.  
Would anyone know what I should try or do to bring this job process back to normal?  Please let me know.
Here are some stats from OEM:
Cache hit %:75.46.
SGA Buffer Cache advice: it's ok..currently at 120,000 reads, next lower is about 90,000
SGA Shared Pool is flat.

here's the output for the faulty job that I"m having problems with.  As you can see I'm uptading about 7400 rows.  That's not that much, yet it takes over 2 hours.

SQL> EXEC ADM_LOAD('LOAN_PRODUCT_DIMENSION_MAP','&2','&1');
7363 row(s) updated for DLS_DM.LOAN_PRODUCT_DIMENSION
38 row(s) inserted into DLS_DM.LOAN_PRODUCT_DIMENSION

PL/SQL procedure successfully completed.

Elapsed: 02:21:45.07

Thank you.

-Metro.
0
Comment
Question by:metro2003
  • 9
  • 5
  • 2
  • +3
19 Comments
 
LVL 6

Expert Comment

by:chedgey
ID: 12525281
Metro,

virtually impossible to tell without more information on what the process ADM_LOAD actually does - can you post the code here?

Have you created execution plans for each statement in ADM_LOAD?
How about putting in some debug statements at each stage of the process that write out a timestamp to a log table so that you can see where the delay is being caused.
When you say that you analyzed all of the tables have you also analyzed all of the indices?

Regards

Chedgey
0
 

Author Comment

by:metro2003
ID: 12525765
Chedgey,

thank you for the prompt response.  Ok, I guess I should have said that I'm not an expert at tuning dB's.  I am now beginning this process.  
I have used the following command to analyze:

>execute dbms_stats.gather_schema_stats('dls_dm');

dls_dm is the schema.  I'm not sure if this process analyzes the indices too. Could you please tell me how to analyze the indices?
And I have not created an execution plan for ADM_LOAD.

here's the code for ADM_LOAD.  This called up some other procedures:

REATE OR REPLACE PROCEDURE ADM_LOAD(p_map_name IN VARCHAR2,
       p_Group_Name IN VARCHAR2, p_processing_group IN VARCHAR2)AUTHID CURRENT_USER AS
  v_Job_Name VARCHAR2(30);
  v_num_jobs number;
  v_job_status varchar2(45);
  v_object_type CHAR(1);
BEGIN
   -- Create name of Mapping Program based on Map Definition
   SELECT DECODE(ADM_DATAMART_OBJECTS_V.OBJECT_TYPE,'DIMENSION','D','FACT','F')
   INTO v_object_type
   FROM ADM_DATAMART_OBJECTS_V , ADM_DM_MAP_DEFINITIONS
   WHERE ( (TRIM(ADM_DM_MAP_DEFINITIONS.TARGET_TABLE) = ADM_DATAMART_OBJECTS_V.OBJECT_NAME)
   AND (TRIM(ADM_DM_MAP_DEFINITIONS.MAP_DESCRIPTION) = UPPER(TRIM(p_map_name))));

   v_job_name := TRIM(SUBSTR(v_object_type||'_'||upper(p_map_name),1,30));

   select count(*) into v_num_jobs
   from adm_job_status
   where job_name = v_Job_Name and
   adm_job_status.PROCESS_GROUP=p_processing_group;
   if v_num_jobs = 0 then
      INSERT INTO ADM_JOB_STATUS VALUES(p_Group_Name,v_Job_Name,'RUNNING',p_processing_group,NULL,NULL);
      commit;
      v_job_status := 'RUNNING';
      EXECUTE IMMEDIATE 'BEGIN '||v_Job_Name||'; END;'||CHR(0);
      UPDATE ADM_JOB_STATUS SET STATUS = 'COMPLETE'
      WHERE JOB_NAME = v_Job_Name AND PROCESS_GROUP = p_processing_group;
      commit;
      v_job_status := 'COMPLETE';
   else
      select status into v_job_status
      from adm_job_status
      where job_name = v_Job_Name and
      adm_job_status.PROCESS_GROUP=p_processing_group;
      if v_job_status = 'FAIL' then
        UPDATE ADM_JOB_STATUS SET STATUS = 'RUNNING'
        WHERE JOB_NAME = v_Job_Name  AND PROCESS_GROUP = p_processing_group;
        commit;
        v_job_status := 'RUNNING';
        EXECUTE IMMEDIATE 'BEGIN '||v_Job_Name||'; END;'||CHR(0);
        UPDATE ADM_JOB_STATUS SET STATUS = 'COMPLETE'
        WHERE JOB_NAME = v_Job_Name AND PROCESS_GROUP = p_processing_group;
        commit;
        v_job_status := 'COMPLETE';
      elsif v_job_status = 'COMPLETE' then
        DBMS_OUTPUT.PUT_LINE('Skipping '||v_Job_Name||', job ran succesfully.');
      elsif v_job_status = 'CANCEL' then
        DBMS_OUTPUT.PUT_LINE('Cannot start '||v_Job_Name||', because job status is at CANCEL.'||
        '  You may re-start the job by delete the cancel flag from adm_job_status;' ||
        '  then re-submit the job');
     elsif v_job_status = 'RUNNING' then
        DBMS_OUTPUT.PUT_LINE('Cannot start '||v_Job_Name||', because job status is at RUNNING.'||
        '  You may re-start the job by killing the running job; delete the running flag from adm_job_status;' ||
        '  then re-submit the job');
     end if;
   end if;
   EXCEPTION
     WHEN OTHERS THEN
     if v_job_status = 'RUNNING' then
        UPDATE ADM_JOB_STATUS SET STATUS = 'FAIL'
        WHERE JOB_NAME = v_Job_Name and
        adm_job_status.PROCESS_GROUP=p_processing_group;
        commit;
       end if;
     RAISE;
END ;
/
0
 
LVL 6

Expert Comment

by:morphman
ID: 12526114
Has an index been dropped recently? Long shot, but you never know.

I would personally put some

dbms_output.put_line('statement1 time: ' || to_char(sysdate,'dd-mon-yyyy hh24:mi_ss');

statements in, and see which statement is causing the performance problem. You can then look at that one individually.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 2

Expert Comment

by:KurtR
ID: 12526727
you must provide more information for us:
try this (in sqlplus):
(1) logon as an user who can execute your procedure.
(2) type: "show parameter user_dump_dest" (value contains the path where you will find your trace file)
(3) ALTER SESSION SET SQL_TRACE = true;
(4) exec ADM_LOAD(..)
(5) exit sqlplus
(6) on the commandline jump to your udump directory. the most recent tracefile should contain your trace information.
(7) type: tkprof [name of the tracefile] result.txt (tkprof.exe can be found in $ORACLE_HOME\bin)
(8) copy & paste the tkprof file to expert-exchange so sombody might be able to help you.

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 12527138
Besides the trace, you will find that the problem may not reside with ADM_LOAD proceure, but with the '<D/F>_LOAN_PRODUCT_DIMENSION_MAP' procedures.
0
 

Author Comment

by:metro2003
ID: 12527353
Great !! I'm doing it right now.  I'll take some time (about 2 hours) and I'll post as soon as I have something.  

-metro.
0
 

Author Comment

by:metro2003
ID: 12532678
here is the trace file:
Thank you all for looking at it.

-Metro.



TKPROF: Release 9.2.0.4.0 - Production on Tue Nov 9 00:18:46 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: D:\Oracle\admin\ACBSDM\udump\acbsdm_ora_3212.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

alter session set sql_trace=true


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 42  
********************************************************************************

select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
  spare2
from
 obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
  and linkname is null and subname is null


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute     12      0.00       0.00          0          0          0           0
Fetch       12      0.00       0.00          1         36          0          12
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       28      0.00       0.01          1         36          0          12

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************

select audit$,options
from
 procedure$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0          8          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.00       0.00          0          8          0           4

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID PROCEDURE$ (cr=2 r=0 w=0 time=53 us)
      1   INDEX UNIQUE SCAN I_PROCEDURE1 (cr=1 r=0 w=0 time=31 us)(object id 115)

********************************************************************************

select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
  o.dataobj#,o.flags
from
 obj$ o where o.obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute     16      0.00       0.00          0          0          0           0
Fetch       16      0.00       0.00          0         48          0          16
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       35      0.00       0.00          0         48          0          16

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************

select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
  d_owner#, nvl(property,0),subname
from
 dependency$,obj$ where d_obj#=:1 and p_obj#=obj#(+) order by order#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       10      0.00       0.00          0          0          0           0
Execute     10      0.01       0.00          0          0          0           0
Fetch       75      0.00       0.07          5        181          0          65
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       95      0.01       0.07          5        181          0          65

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
     29  SORT ORDER BY (cr=64 r=3 w=0 time=29246 us)
     29   NESTED LOOPS OUTER (cr=64 r=3 w=0 time=28712 us)
     29    TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=4 r=3 w=0 time=27998 us)
     29     INDEX RANGE SCAN I_DEPENDENCY1 (cr=2 r=1 w=0 time=10888 us)(object id 127)
     29    TABLE ACCESS BY INDEX ROWID OBJ$ (cr=60 r=0 w=0 time=525 us)
     29     INDEX UNIQUE SCAN I_OBJ1 (cr=31 r=0 w=0 time=260 us)(object id 36)

********************************************************************************

select order#,columns,types
from
 access$ where d_obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       10      0.01       0.00          0          0          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch       53      0.00       0.02          3        106          0          43
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       73      0.01       0.02          3        106          0          43

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
     21  TABLE ACCESS BY INDEX ROWID ACCESS$ (cr=44 r=1 w=0 time=2349 us)
     21   INDEX RANGE SCAN I_ACCESS1 (cr=23 r=0 w=0 time=730 us)(object id 129)

********************************************************************************

select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece
from
 idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        7      0.00       0.00          0          0          0           0
Execute      7      0.00       0.00          0          0          0           0
Fetch       17      0.00       0.07          4         44          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       31      0.00       0.07          4         44          0          10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS BY INDEX ROWID IDL_SB4$ (cr=6 r=1 w=0 time=20374 us)
      2   INDEX RANGE SCAN I_IDL_SB41 (cr=4 r=0 w=0 time=87 us)(object id 123)

********************************************************************************

select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece
from
 idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        7      0.00       0.00          0          0          0           0
Execute      7      0.00       0.00          0          0          0           0
Fetch       18      0.00       0.23         11         55          0          15
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       32      0.00       0.23         11         55          0          15

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID IDL_UB1$ (cr=4 r=1 w=0 time=18752 us)
      1   INDEX RANGE SCAN I_IDL_UB11 (cr=3 r=0 w=0 time=61 us)(object id 120)

********************************************************************************

select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece
from
 idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        7      0.00       0.00          0          0          0           0
Execute      7      0.00       0.00          0          0          0           0
Fetch       12      0.00       0.03          4         29          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       26      0.00       0.03          4         29          0           5

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      3  TABLE ACCESS BY INDEX ROWID IDL_CHAR$ (cr=8 r=3 w=0 time=23375 us)
      3   INDEX RANGE SCAN I_IDL_CHAR1 (cr=5 r=0 w=0 time=113 us)(object id 121)

********************************************************************************

select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece
from
 idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        7      0.00       0.00          0          0          0           0
Execute      7      0.00       0.00          0          0          0           0
Fetch       17      0.00       0.06         12         50          0          13
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       31      0.00       0.06         12         50          0          13

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      9  TABLE ACCESS BY INDEX ROWID IDL_UB2$ (cr=19 r=9 w=0 time=19071 us)
      9   INDEX RANGE SCAN I_IDL_UB21 (cr=10 r=1 w=0 time=1493 us)(object id 122)

********************************************************************************

BEGIN d_loan_product_dimension_map; END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.09       0.21         18        180          0           0
Execute      1      0.14       0.03    1683559   78852373          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.23       0.25    1683577   78852553          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42  
********************************************************************************

select user#
from
 sys.user$ where name = 'OUTLN'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID USER$ (cr=2 r=0 w=0 time=49 us)
      1   INDEX UNIQUE SCAN I_USER1 (cr=1 r=0 w=0 time=23 us)(object id 44)

********************************************************************************

select node,owner,name
from
 syn$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID OBJ#(61) (cr=3 r=0 w=0 time=53 us)
      1   INDEX UNIQUE SCAN OBJ#(106) (cr=2 r=0 w=0 time=33 us)(object id 106)

********************************************************************************

select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
 objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
  grantee#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      8      0.00       0.00          0          0          0           0
Fetch        9      0.00       0.01          1         17          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       20      0.00       0.01          1         17          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT GROUP BY (cr=3 r=1 w=0 time=14253 us)
      1   TABLE ACCESS BY INDEX ROWID OBJ#(90) (cr=3 r=1 w=0 time=14100 us)
      1    INDEX RANGE SCAN OBJ#(108) (cr=2 r=0 w=0 time=74 us)(object id 108)

********************************************************************************

select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled  
from
 obj$ o,user$ u,trigger$ t  where t.baseobject=:1 and t.obj#=o.obj# and
  o.owner#=u.user#  order by o.obj#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          2          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          2          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=1 r=0 w=0 time=94 us)
      0   NESTED LOOPS  (cr=1 r=0 w=0 time=47 us)
      0    NESTED LOOPS  (cr=1 r=0 w=0 time=45 us)
      0     TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=1 r=0 w=0 time=43 us)
      0      INDEX RANGE SCAN I_TRIGGER1 (cr=1 r=0 w=0 time=39 us)(object id 130)
      0     TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 r=0 w=0 time=0 us)
      0      INDEX UNIQUE SCAN I_OBJ1 (cr=0 r=0 w=0 time=0 us)(object id 36)
      0    TABLE ACCESS CLUSTER USER$ (cr=0 r=0 w=0 time=0 us)
      0     INDEX UNIQUE SCAN I_USER# (cr=0 r=0 w=0 time=0 us)(object id 11)

********************************************************************************

UPDATE ADM_ACBS_PERIODS SET
LAST_PROCESSING_DATE = TO_DATE(GET_PROCESSING_DATE('YYYYMMDD',0),'YYYYMMDD'),
CURRENT_PROCESSING_DATE = TO_DATE(GET_PROCESSING_DATE('YYYYMMDD',1),'YYYYMMDD'),
NEXT_PROCESSING_DATE = TO_DATE(GET_PROCESSING_DATE('YYYYMMDD',2),'YYYYMMDD')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0          3          0           0
Execute      1      0.01       0.01          0         15          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.04       0.04          0         18          3           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42     (recursive depth: 1)
********************************************************************************

select line,position#,text
from
 error$ where obj# = :1  order by sequence#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=2 r=0 w=0 time=91 us)
      0   TABLE ACCESS BY INDEX ROWID ERROR$ (cr=2 r=0 w=0 time=46 us)
      0    INDEX RANGE SCAN I_ERROR1 (cr=2 r=0 w=0 time=41 us)(object id 125)

********************************************************************************

select procedure#,procedurename,properties,itypeobj#
from
 procedureinfo$ where obj#=:1 order by procedurename desc, overload# desc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.01          1          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          1          3          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT ORDER BY (cr=3 r=1 w=0 time=13683 us)
      1   TABLE ACCESS BY INDEX ROWID PROCEDUREINFO$ (cr=3 r=1 w=0 time=13487 us)
      1    INDEX RANGE SCAN I_PROCEDUREINFO1 (cr=2 r=0 w=0 time=60 us)(object id 116)

********************************************************************************

select position#,sequence#,level#,argument,type#,charsetid,charsetform,
  properties,nvl(length, 0), nvl(precision#, 0),nvl(scale, 0),nvl(radix, 0),
  type_owner,type_name,type_subname,type_linkname,pls_type
from
 argument$ where obj#=:1 and procedure#=:2 order by sequence# desc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.02          1          3          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.02          1          3          0           4

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      4  SORT ORDER BY (cr=3 r=1 w=0 time=20409 us)
      4   TABLE ACCESS BY INDEX ROWID ARGUMENT$ (cr=3 r=1 w=0 time=20168 us)
      4    INDEX RANGE SCAN I_ARGUMENT2 (cr=2 r=0 w=0 time=148 us)(object id 118)

********************************************************************************

select max(procedure#)
from
 procedurec$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          1          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1 r=0 w=0 time=43 us)
      0   INDEX RANGE SCAN I_PROCEDUREC$ (cr=1 r=0 w=0 time=35 us)(object id 317)

********************************************************************************

select max(procedure#)
from
 procedurejava$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          1          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1 r=0 w=0 time=44 us)
      0   INDEX RANGE SCAN I_PROCEDUREJAVA$ (cr=1 r=0 w=0 time=36 us)(object id 315)

********************************************************************************

select procedure#,entrypoint#
from
 procedurec$ where obj#=:1 order by procedure#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          1          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=1 r=0 w=0 time=87 us)
      0   TABLE ACCESS BY INDEX ROWID PROCEDUREC$ (cr=1 r=0 w=0 time=43 us)
      0    INDEX RANGE SCAN I_PROCEDUREC$ (cr=1 r=0 w=0 time=36 us)(object id 317)

********************************************************************************

select procedure#,ownerlength,classlength,methodlength,siglength, flagslength,
  cookiesize
from
 procedurejava$ where obj#=:1 order by procedure#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          1          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=1 r=0 w=0 time=86 us)
      0   TABLE ACCESS BY INDEX ROWID PROCEDUREJAVA$ (cr=1 r=0 w=0 time=40 us)
      0    INDEX RANGE SCAN I_PROCEDUREJAVA$ (cr=1 r=0 w=0 time=36 us)(object id 315)

********************************************************************************

select ownername,classname,methodname,signature,flags
from
 procedurejava$ where obj#=:1 and procedure#=:2 order by procedure#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Parsing user id: SYS   (recursive depth: 2)
********************************************************************************

select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),
  audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,
  avgspc,chncnt,avgrln,analyzetime, samplesize,cols,property,nvl(degree,1),
  nvl(instances,1),avgspc_flb,flbcnt,kernelcols,nvl(trigflag, 0),nvl(spare1,0)
  ,nvl(spare2,0),spare4,spare6
from
 tab$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS CLUSTER OBJ#(4) (cr=3 r=0 w=0 time=73 us)
      1   INDEX UNIQUE SCAN OBJ#(3) (cr=2 r=0 w=0 time=32 us)(object id 3)

********************************************************************************

select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,
  i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey, i.lblkkey,
  i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
  nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
  i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
  nvl(i.spare1,i.intcols),i.spare4,spare2,spare6, decode(i.pctthres$,null,
  null, mod(trunc(i.pctthres$/256),256))
from
 ind$ i, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1)))
  deferrable#, min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=
  :1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.bo#=
  :1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          6          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  MERGE JOIN OUTER (cr=6 r=0 w=0 time=588 us)
      1   SORT JOIN (cr=3 r=0 w=0 time=187 us)
      1    TABLE ACCESS CLUSTER OBJ#(19) (cr=3 r=0 w=0 time=80 us)
      1     INDEX UNIQUE SCAN OBJ#(3) (cr=2 r=0 w=0 time=36 us)(object id 3)
      1   SORT JOIN (cr=3 r=0 w=0 time=251 us)
      1    VIEW  (cr=3 r=0 w=0 time=205 us)
      1     SORT GROUP BY (cr=3 r=0 w=0 time=201 us)
      1      TABLE ACCESS CLUSTER OBJ#(31) (cr=3 r=0 w=0 time=65 us)
      1       INDEX UNIQUE SCAN OBJ#(30) (cr=2 r=0 w=0 time=16 us)(object id 30)

********************************************************************************

select pos#,intcol#,col#,spare1,bo#,spare2
from
 icol$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0          4          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID OBJ#(20) (cr=4 r=0 w=0 time=111 us)
      1   INDEX RANGE SCAN OBJ#(40) (cr=3 r=0 w=0 time=74 us)(object id 40)

********************************************************************************

select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
  nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
  scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
  rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
  nvl(spare3,0)
from
 col$ where obj#=:1 order by intcol#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      7      0.00       0.00          0          0          0           0
Fetch      268      0.00       0.00          0         22          0         261
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      277      0.00       0.01          0         22          0         261

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
    245  SORT ORDER BY (cr=19 r=0 w=0 time=4165 us)
    245   TABLE ACCESS CLUSTER OBJ#(21) (cr=19 r=0 w=0 time=1301 us)
      6    INDEX UNIQUE SCAN OBJ#(3) (cr=12 r=0 w=0 time=198 us)(object id 3)

********************************************************************************

select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
  NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0)
from
 seg$ where ts#=:1 and file#=:2 and block#=:3


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          6          0           2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS CLUSTER SEG$ (cr=3 r=0 w=0 time=71 us)
      1   INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 r=0 w=0 time=31 us)(object id 9)

********************************************************************************

select con#,obj#,rcon#,enabled,nvl(defer,0)
from
 cdef$ where robj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      9      0.00       0.00          0          0          0           0
Fetch        9      0.00       0.00          0          9          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       20      0.00       0.00          0          9          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS BY INDEX ROWID OBJ#(31) (cr=1 r=0 w=0 time=43 us)
      0   INDEX RANGE SCAN OBJ#(52) (cr=1 r=0 w=0 time=39 us)(object id 52)

********************************************************************************

select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
  rowid,cols,nvl(defer,0),mtime,nvl(spare1,0)
from
 cdef$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      9      0.00       0.00          0          0          0           0
Fetch       14      0.00       0.00          0         24          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       25      0.00       0.00          0         24          0           5

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  TABLE ACCESS CLUSTER OBJ#(31) (cr=8 r=0 w=0 time=221 us)
      1   INDEX UNIQUE SCAN OBJ#(30) (cr=2 r=0 w=0 time=29 us)(object id 30)

********************************************************************************

select intcol#,nvl(pos#,0),col#
from
 ccol$ where con#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.00          0         20          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.00       0.00          0         20          0           5

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  TABLE ACCESS BY INDEX ROWID OBJ#(32) (cr=20 r=0 w=0 time=269 us)
      5   INDEX RANGE SCAN OBJ#(55) (cr=15 r=0 w=0 time=180 us)(object id 55)

********************************************************************************

select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
from
 objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
  grantee# order by col#, grantee#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      7      0.00       0.00          0          0          0           0
Fetch        7      0.00       0.00          0         14          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.00       0.00          0         14          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT GROUP BY (cr=12 r=0 w=0 time=535 us)
      0   TABLE ACCESS BY INDEX ROWID OBJ#(90) (cr=12 r=0 w=0 time=291 us)
      0    INDEX RANGE SCAN OBJ#(108) (cr=12 r=0 w=0 time=266 us)(object id 108)

********************************************************************************

select condition
from
 cdef$ where rowid=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          4          0           2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY USER ROWID CDEF$ (cr=1 r=0 w=0 time=34 us)

********************************************************************************

select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
  sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
  spare1, spare2, avgcln
from
 hist_head$ where obj#=:1 and intcol#=:2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0          6          0           2

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: SYS   (recursive depth: 3)

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS BY INDEX ROWID OBJ#(219) (cr=6 r=0 w=0 time=112 us)
      2   INDEX RANGE SCAN OBJ#(221) (cr=4 r=0 w=0 time=70 us)(object id 221)

********************************************************************************

SELECT SCHEMA_NAME
FROM
 ADM_SCHEMA_ROLES WHERE SCHEMA_ROLE = 'STG'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          3          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.02          2         10          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.01       0.04          2         13          0           5

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42     (recursive depth: 2)
********************************************************************************

SELECT TO_CHAR(ACBSDATE_TO_DATE(J$MXA8),'YYYYMMDD')
FROM
 DLS_STAGE.J$SYDC WHERE J$RDQI = :1 AND J$DWKC IN (SELECT DISTINCT
  PROCESSING_GROUP FROM ADM_EXTRACT_LOGS)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.01       0.00          0         15          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.01       0.01          0         15          0           3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  NESTED LOOPS SEMI (cr=5 r=0 w=0 time=306 us)
      3   TABLE ACCESS FULL J$SYDC (cr=3 r=0 w=0 time=90 us)
      1   INDEX FULL SCAN SYS_C003419 (cr=2 r=0 w=0 time=191 us)(object id 7850)

********************************************************************************

SELECT TO_DATE(:b1,'YYYYMMDD')
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute 127216      6.46       6.15          0          0          0           0
Fetch   127216     10.09       9.87          0     381648          0       61983
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   254433     16.57      16.03          0     381648          0       61983

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44     (recursive depth: 3)
********************************************************************************

SELECT TO_CHAR(ACBSDATE_TO_DATE(J$MWY8),'YYYYMMDD')
FROM
 DLS_STAGE.J$SYDC WHERE J$RDQI = :1 AND J$DWKC IN (SELECT DISTINCT
  PROCESSING_GROUP FROM ADM_EXTRACT_LOGS)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          5          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  NESTED LOOPS SEMI (cr=5 r=0 w=0 time=301 us)
      3   TABLE ACCESS FULL J$SYDC (cr=3 r=0 w=0 time=82 us)
      1   INDEX FULL SCAN SYS_C003419 (cr=2 r=0 w=0 time=192 us)(object id 7850)

********************************************************************************

SELECT TO_CHAR(ACBSDATE_TO_DATE(J$MXB8),'YYYYMMDD')
FROM
 DLS_STAGE.J$SYDC WHERE J$RDQI = :1 AND J$DWKC IN (SELECT DISTINCT
  PROCESSING_GROUP FROM ADM_EXTRACT_LOGS)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          5          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  NESTED LOOPS SEMI (cr=5 r=0 w=0 time=302 us)
      3   TABLE ACCESS FULL J$SYDC (cr=3 r=0 w=0 time=86 us)
      1   INDEX FULL SCAN SYS_C003419 (cr=2 r=0 w=0 time=190 us)(object id 7850)

********************************************************************************

COMMIT


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          3           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0          0          3           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42     (recursive depth: 1)
********************************************************************************

select text
from
 view$ where rowid=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       65      0.00       0.00          0          0          0           0
Execute     65      0.00       0.00          0          0          0           0
Fetch       65      0.01       0.00          0        130          0          65
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      195      0.01       0.01          0        130          0          65

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY USER ROWID VIEW$ (cr=1 r=0 w=0 time=34 us)

********************************************************************************

select cols,audit$,textlength,intcols,property,flags,rowid
from
 view$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         18          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.00       0.00          0         18          0           6

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  TABLE ACCESS BY INDEX ROWID OBJ#(62) (cr=15 r=0 w=0 time=215 us)
      5   INDEX UNIQUE SCAN OBJ#(104) (cr=10 r=0 w=0 time=124 us)(object id 104)

********************************************************************************

select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#,
  nvl(typidcol#, 0)
from
 coltype$ where obj#=:1 order by intcol# desc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         19          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.00       0.00          0         19          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=16 r=0 w=0 time=415 us)
      0   TABLE ACCESS CLUSTER OBJ#(283) (cr=16 r=0 w=0 time=251 us)
      5    INDEX UNIQUE SCAN OBJ#(3) (cr=10 r=0 w=0 time=124 us)(object id 3)

********************************************************************************

select intcol#, toid, version#, intcols, intcol#s, flags, synobj#
from
 subcoltype$ where obj#=:1 order by intcol# asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         19          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.00       0.00          0         19          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=16 r=0 w=0 time=350 us)
      0   TABLE ACCESS CLUSTER OBJ#(286) (cr=16 r=0 w=0 time=199 us)
      5    INDEX UNIQUE SCAN OBJ#(3) (cr=10 r=0 w=0 time=105 us)(object id 3)

********************************************************************************

select col#,intcol#,ntab#
from
 ntab$ where obj#=:1 order by intcol# asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         19          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.00       0.00          0         19          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=16 r=0 w=0 time=333 us)
      0   TABLE ACCESS CLUSTER OBJ#(352) (cr=16 r=0 w=0 time=197 us)
      5    INDEX UNIQUE SCAN OBJ#(3) (cr=10 r=0 w=0 time=103 us)(object id 3)

********************************************************************************

select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk,
  l.pctversion$, l.flags, l.property, l.retention, l.freepools
from
 lob$ l where l.obj# = :1 order by l.intcol# asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        6      0.01       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         19          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       18      0.01       0.00          0         19          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=3 r=0 w=0 time=113 us)
      0   TABLE ACCESS CLUSTER LOB$ (cr=3 r=0 w=0 time=65 us)
      1    INDEX UNIQUE SCAN I_OBJ# (cr=2 r=0 w=0 time=33 us)(object id 3)

********************************************************************************

select col#,intcol#,reftyp,stabid,expctoid
from
 refcon$ where obj#=:1 order by intcol# asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         19          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.00       0.00          0         19          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=16 r=0 w=0 time=421 us)
      0   TABLE ACCESS CLUSTER OBJ#(362) (cr=16 r=0 w=0 time=249 us)
      5    INDEX UNIQUE SCAN OBJ#(3) (cr=10 r=0 w=0 time=127 us)(object id 3)

********************************************************************************

select col#,intcol#,charsetid,charsetform
from
 col$ where obj#=:1 order by intcol# asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch      261      0.03       0.00          0         19          0         255
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      269      0.03       0.00          0         19          0         255

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
    239  SORT ORDER BY (cr=16 r=0 w=0 time=2336 us)
    239   TABLE ACCESS CLUSTER OBJ#(21) (cr=16 r=0 w=0 time=852 us)
      5    INDEX UNIQUE SCAN OBJ#(3) (cr=10 r=0 w=0 time=100 us)(object id 3)

********************************************************************************

select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,  elemnum
from
 opqtype$ where obj# = :1 order by intcol# asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0         19          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.00       0.00          0         19          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=16 r=0 w=0 time=402 us)
      0   TABLE ACCESS CLUSTER OBJ#(365) (cr=16 r=0 w=0 time=240 us)
      5    INDEX UNIQUE SCAN OBJ#(3) (cr=10 r=0 w=0 time=127 us)(object id 3)

********************************************************************************

SELECT DISTINCT
TO_CHAR(:b1,'YYYYMM'),
A.LOAN_PORT_ID,
D.PORT_FULL_NAME,
A.OBLIGATION_NO,
A.CA_NO,
C.CA_DSC_TEXT,
A.CA_CUST_ID,
F.CU_CUST_NAME_1_DISPLAY,
A.CA_CNCY_CD,
A.DEAL_PORT_ID,
A.DEAL_CUST_ID,
A.DEAL_CA_NO,
A.DEAL_CNCY_CD,
A.ACCT_STRUCTURE_CD,
A.IS_TIED_TO_CA,
A.ACCTING_METHOD,
A.LENDER_TYPE_CD_CUST_SHARE,
A.LENDER_TYPE_CD_OUR_SHARE,
A.INSTR_TYPE_CD,
L.PROD_TYPE_NAME_FULL,
A.LOAN_STATUS,
A.CLOSURE_TYPE,
B.PROD_GRP,
B.PROD_TYPE_CD,
P.FULL_DSC,
B.PURPOSE_CD,
B.OBLIGATION_BOOKING_UNIT,
M.UNIT_FULL_NAME,
B.COUNTRY_OF_BOOKING,
B.LOAN_FEDERAL_LOAN_CLASSIFICATI,
B.ADVANCE_TYPE_CD,
B.REGULATION_Z_FLAG,
B.REGULATION_U_FLAG,
B.ELIGIBLE_FOR_1098,
B.IND_CLASSIFICATION,
J.INDNAME,
B.CALLABLE_ON_DEMAND,
B.SECURED_CD,
B.PLEDGED_FOR_COLLATERAL,
B.BOOKING_TYPE_CD,
B.PREV_OBLIGATION_NO,
B.INTEREST_RATE_TYPE,
B.AGENT_BANK_CUST_NO,
ACBSDATE_TO_DATE(A.EFFECTIVE_DATE),
ACBSDATE_TO_DATE(A.LEGAL_MATURITY_DATE),
ACBSDATE_TO_DATE(A.INT_RATE_MATURITY_DATE),
ACBSDATE_TO_DATE(A.RATE_SETTING_DATE),
ACBSDATE_TO_DATE(A.STATUS_DATE),
A.CA_SECTION_BOOKED_UNDER,
A.PORT_BASE_CNCY_CD,
A.LOAN_CNCY_CD,
A.CL_SERVICING_USER_ID,
E.USER_NAME,
A.OBLIGATION_CURR_OFFICER,
I.UNIT_FULL_NAME,
A.GL_UNIT,
G.UNIT_FULL_NAME,
A.SERVICING_UNIT,
H.UNIT_FULL_NAME,
A.SERVICING_UNIT_SECTION,
K.UNIT_FULL_NAME,
B.RISK_RATING_OFFICER,
B.RISK_RATING_REGULATOR,
B.WATCH_MONITOR,
B.USER_CD_1,
B.USER_CD_2,
B.USER_CD_3,
B.USER_CD_4,
ACBSDATE_TO_DATE(B.USER_DATE_1),
ACBSDATE_TO_DATE(B.USER_DATE_2),
A.STANDARD_REFERENCE_ID,
T.FULL_DSC,
U.FULL_DSC,
V.FULL_DSC,
W.FULL_DSC,
A.OBLIGOR_NO,
D.PROC_GRP_CD,
N.COUNTRY_OF_EXPOSURE_RISK,
A.ADMINISTRATIVE_USER_ID,
O.USER_NAME,
ACBSDATE_TO_DATE(B.ORIG_MATURITY_DATE),
B.ORIG_GL_COND_CD,
ACBSDATE_TO_DATE(A.DATE_BOOKED),
B.INTERNAL_REFERENCE_NO,
B.EXTERNAL_REFERENCE_NO,
B.BENEFICIARY_CUST_NO,
X.CU_CUST_NAME_1_DISPLAY,
A.GL_COND_CD,
A.PORT_TYPE,
A.BANK_LIAB_TYPE_CD,
A.CUST_LIAB_TYPE_CD,
A.STATUS_CD,
A.STANDARD_REFERENCE_ID,
A.CAN_BE_TRADED,
A.MTM_ELIGIBLE,
A.STANDARD_REFERENCE_ID_TYPE,
B.OBLGTN_ORIG_RESPONSIBILITY_OFF,
B.OBLIGATION_ORIG_GL_UNIT,
Y.UNIT_FULL_NAME,
B.RISK_RATING_CRED_REVIEW,
ACBSDATE_TO_DATE(B.RISK_DATE_OFFICER),
ACBSDATE_TO_DATE(B.RISK_DATE_CRED_REVIEW),
ACBSDATE_TO_DATE(B.RISK_DATE_REGULATOR),
ACBSDATE_TO_DATE(B.NEXT_REVIEW_DATE),
B.REVIEW_FREQ,
ACBSDATE_TO_DATE(B.LAST_REVIEW_DATE),
B.NOTIFY_WHEN_RATE_CHANGES,
B.FIN_TRAN_DDA_ACCT_NO,
ACBSDATE_TO_DATE(B.LAST_RENEWAL_DATE),
B.REPORTING_ACCT_TYPE_CD,
B.INCOMING_PMT_INSTR_AGENT,
B.OUTGOING_PMT_INSTR_AGENT,
B.AGENT_BANK_CORRESPONDENCE_ID,
B.AGENT_BANK_ADDR_ID,
ACBSDATE_TO_DATE(B.SELF_LIQUIDATION_DATE),
A.PRICING_TMPLT_ID,
B.RISK_SCENARIO,
L.INSTR_PROC_CLASSIFICATION,
L.DISCOUNT_CALC_TYPE,
L.ACCT_TYPE_CD,
L.PROD_TYPE_CAN_BE_SOLD_OR_SYND,
L.PRI_BILLING_SCHED_REQUIREMENT,
L.ALLOW_NEGATIVE_PRIN_BALS,
ACBSDATE_TO_DATE(B.ACCTING_METHOD_DATE),
A.BID_REFERENCE_NO,
A.NO_OF_ACCT_RECORDS,
A.CA_SECTION_1,
A.CA_SECTION_2,
A.CA_SECTION_3,
A.CA_SECTION_4,
A.CA_SECTION_5,
R.FULL_DSC,
N.CA_TYPE_CD
FROM
DLS_STAGE.CL_MASTER_RECORD A,
DLS_STAGE.CL_MISC_CDS_VALUES B,
DLS_STAGE.CA_MASTER_RECORD C,
DLS_STAGE.PORT_DFN_TABLE D,
DLS_STAGE.SECURITY_USER_MASTER_RECORD E,
DLS_STAGE.CUST_MASTER_FILE F,
DLS_STAGE.BANK_ORG_UNIT_TABLE G,
DLS_STAGE.BANK_ORG_UNIT_TABLE H,
DLS_STAGE.BANK_ORG_UNIT_TABLE I,
DLS_STAGE.COMMON_TABLES_IND_DETAILS J,
DLS_STAGE.BANK_ORG_UNIT_TABLE K,
DLS_STAGE.ACCT_DFN_TABLE L,
DLS_STAGE.BANK_ORG_UNIT_TABLE M,
DLS_STAGE.CA_MISC_CDS_INFO N,
DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES P,
DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES R,
DLS_STAGE.SECURITY_USER_MASTER_RECORD O,
DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES T,
DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES U,
DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES V,
DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES W,
DLS_STAGE.CUST_MASTER_FILE X,
DLS_STAGE.BANK_ORG_UNIT_TABLE Y
WHERE
A.LOAN_PORT_ID =  B.LOAN_PORT_ID AND
A.OBLIGATION_NO =  B.OBLIGATION_NO AND
A.LOAN_PORT_ID =  C.PORT_ID(+) AND
A.CA_NO =  C.CA_NO(+) AND
A.LOAN_PORT_ID =  D.PORT_ID AND
A.CL_SERVICING_USER_ID =  E.USER_ID(+) AND
A.CA_CUST_ID =  F.CU_CUST_ID(+) AND
A.GL_UNIT =  G.UNIT_ID(+) AND
A.SERVICING_UNIT =  H.UNIT_ID(+) AND
A.OBLIGATION_CURR_OFFICER =  I.UNIT_ID(+) AND
B.IND_CLASSIFICATION =  J.IND_ID(+) AND
A.SERVICING_UNIT_SECTION =  K.UNIT_ID(+) AND
A.INSTR_TYPE_CD =  L.PROD_TYPE_CD(+) AND
B.OBLIGATION_BOOKING_UNIT =  M.UNIT_ID(+) AND
C.PORT_ID =  N.PORT_ID(+) AND
C.CA_NO =  N.CA_NO(+) AND
A.ADMINISTRATIVE_USER_ID =  O.USER_ID(+) AND
B.USER_CD_1 =  T.KEY_VALUE_1(+) AND
B.USER_CD_2 =  U.KEY_VALUE_1(+) AND
B.USER_CD_3 =  V.KEY_VALUE_1(+) AND
B.USER_CD_4 =  W.KEY_VALUE_1(+) AND
B.BENEFICIARY_CUST_NO =  X.CU_CUST_ID(+) AND
B.OBLIGATION_ORIG_GL_UNIT =  Y.UNIT_ID(+) AND
B.PROD_TYPE_CD =  P.KEY_VALUE_1(+) AND
B.PROD_GRP =  P.KEY_VALUE_2(+) AND
N.CA_TYPE_CD =  R.KEY_VALUE_1(+)
AND
G.UNIT_TYPE_CD(+) = '02' AND
H.UNIT_TYPE_CD (+) = '05' AND
I.UNIT_TYPE_CD (+) = '10' AND
K.UNIT_TYPE_CD (+) = '08' AND
M.UNIT_TYPE_CD(+) = '02' AND
T.TABLE_ID(+) = 'T2320' AND
U.TABLE_ID(+) = 'T2325' AND
V.TABLE_ID(+) = 'T2330' AND
W.TABLE_ID(+) = 'T2335' AND
Y.UNIT_TYPE_CD(+) = '02' AND
P.TABLE_ID(+) = 'T0335' AND
R.TABLE_ID(+) = 'T1095'
AND
EXISTS (SELECT 1 FROM DLS_DM.LOAN_PRODUCT_DIMENSION WHERE
PORTFOLIO_ID = A.LOAN_PORT_ID
AND
OBLIGATION_NUMBER = A.OBLIGATION_NO
AND
PRODUCT_MONTH = TO_CHAR(:b1,'YYYYMM')
)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.10          0         42          0           0
Execute      1      0.34       0.34          0          0          0           0
Fetch        1     36.35      37.27          9      65290          0        7483
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     36.76      37.72          9      65332          0        7483

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42     (recursive depth: 1)
********************************************************************************

UPDATE DLS_DM.LOAN_PRODUCT_DIMENSION SET
PORTFOLIO_DESCRIPTION=:b130,
CA_NUMBER=:b129,
CA_DESCRIPTION=:b128,
CA_CUSTOMER_ID=:b127,
CA_CUSTOMER_NAME=:b126,
CA_CURRENCY_CODE=:b125,
DEAL_PORTFOLIO_ID=:b124,
DEAL_CUSTOMER_ID=:b123,
DEAL_CA_NUMBER=:b122,
DEAL_CURRENCY_CODE=:b121,
ACCOUNT_STRUCTURE_CODE=:b120,
IS_TIED_TO_CA=:b119,
ACCOUNTING_METHOD_CODE=:b118,
CUSTOMER_LENDER_TYPE_CODE=:b117,
OUR_SHARE_LENDER_TYPE_CODE=:b116,
INSTRUMENT_TYPE_CODE=:b115,
INSTRUMENT_TYPE_DESCRIPTION=:b114,
LOAN_STATUS_CODE=:b113,
CLOSURE_TYPE_CODE=:b112,
PRODUCT_GROUP_CODE=:b111,
PRODUCT_TYPE_CODE=:b110,
PRODUCT_TYPE_DESCRIPTION=:b109,
PURPOSE_CODE=:b108,
BOOKING_UNIT_CODE=:b107,
BOOKING_UNIT_DESCRIPTION=:b106,
COUNTRY_CODE_OF_BOOKING=:b105,
FEDERAL_LOAN_CLASS_CODE=:b104,
ADVANCE_TYPE_CODE=:b103,
REGULATION_Z=:b102,
REGULATION_U=:b101,
ELIGIBLE_FOR_1098=:b100,
INDUSTRY_CLASS_CODE=:b99,
INDUSTRY_CLASS_DESCRIPTION=:b98,
CALLABLE_ON_DEMAND=:b97,
LOAN_SECURED=:b96,
PLEDGED_FOR_COLLATERAL=:b95,
BOOKING_TYPE_CODE=:b94,
PREVIOUS_OBLIGATION_NUMBER=:b93,
INTEREST_RATE_TYPE_CODE=:b92,
AGENT_BANK_CUSTOMER_NUMBER=:b91,
EFFECTIVE_DATE=:b90,
LEGAL_MATURITY_DATE=:b89,
INT_RATE_MATURITY_DATE=:b88,
RATE_SETTING_DATE=:b87,
STATUS_DATE=:b86,
CA_SECTION_BOOKED_UNDER=:b85,
PORTFOLIO_BASE_CURRENCY_CODE=:b84,
LOAN_CURRENCY_CODE=:b83,
SERVICING_USER_ID=:b82,
SERVICING_USER_NAME=:b81,
CURRENT_LOAN_OFFICER_CODE=:b80,
CURRENT_LOAN_OFFICER_NAME=:b79,
LOAN_GL_UNIT_CODE=:b78,
LOAN_GL_UNIT_DESC=:b77,
LOAN_SERVICING_UNIT_CODE=:b76,
LOAN_SERVICING_UNIT_DESC=:b75,
LOAN_SERVICING_UNIT_SECT_CODE=:b74,
LOAN_SERVICING_UNIT_SECT_DESC=:b73,
INTERNAL_RISK_RATING_CODE=:b72,
REGULAR_RISK_RATING_CODE=:b71,
WATCH_LIST=:b70,
USER_CODE_1=:b69,
USER_CODE_2=:b68,
USER_CODE_3=:b67,
USER_CODE_4=:b66,
USER_DATE_1=:b65,
USER_DATE_2=:b64,
CL_STANDARD_REFERENCE_ID=:b63,
USER_CODE_DESCRIPTION_1=:b62,
USER_CODE_DESCRIPTION_2=:b61,
USER_CODE_DESCRIPTION_3=:b60,
USER_CODE_DESCRIPTION_4=:b59,
CL_CUSTOMER_ID=:b58,
PROCESSING_GROUP=:b57,
CA_COUNTRY_OF_EXPOSURE_RISK=:b56,
CL_ADMINISTRATIVE_USER_ID=:b55,
CL_ADMINISTRATIVE_USER_NAME=:b54,
ORIGINAL_MATURITY_DATE=:b53,
ORIGINAL_GL_COND_CD=:b52,
DATE_BOOKED=:b51,
INTERNAL_REFERENCE_NO=:b50,
EXTERNAL_REFERENCE_NO=:b49,
BENEFICIARY_CUSTOMER_NO=:b48,
BENEFICIARY_CUSTOMER_NAME=:b47,
GL_CONDITION_CODE=:b46,
PORT_TYPE=:b45,
BANK_LIABILITY_TYPE_CODE=:b44,
CUSTOMER_LIABILITY_TYPE_CODE=:b43,
STATUS_CODE=:b42,
STANDARD_REFERENCE_ID=:b41,
CAN_BE_TRADED=:b40,
MTM_ELIGIBLE=:b39,
STANDARD_REFERENCE_ID_TYPE=:b38,
ORIG_RESPONS_OFFICER_NO=:b37,
ORIGINAL_GL_UNIT_NO=:b36,
ORIGINAL_GL_UNIT_NAME=:b35,
RISK_RATING_CRED_REVIEW=:b34,
RISK_OFFICER_DATE=:b33,
RISK_CRED_REVIEW_DATE=:b32,
RISK_REGULATOR_DATE=:b31,
NEXT_REVIEW_DATE=:b30,
REVIEW_FREQ_CODE=:b29,
LAST_REVIEW_DATE=:b28,
NOTIFY_WHEN_RATE_CHANGES=:b27,
FIN_TRAN_DDA_ACCT_NO=:b26,
LAST_RENEWAL_DATE=:b25,
REPORTING_ACCT_TYPE_CODE=:b24,
INCOMING_PMT_INSTR_AGENT=:b23,
OUTGOING_PMT_INSTR_AGENT=:b22,
AGENT_BANK_CORRESPONDENCE_ID=:b21,
AGENT_BANK_ADDR_ID=:b20,
SELF_LIQUIDATION_DATE=:b19,
PRICING_TEMPLATE_ID=:b18,
RISK_SCENARIO=:b17,
INSTR_PROC_CLASSIFICATION=:b16,
DISCOUNT_CALCULATION_TYPE=:b15,
ACCOUNT_TYPE_CODE=:b14,
PROD_TYPE_CAN_BE_SOLD_OR_SYND=:b13,
PRI_BILLING_SCHED_REQUIREMENT=:b12,
ALLOW_NEGATIVE_PRINCIPAL_BAL=:b11,
ACCOUNTING_METHOD_DATE=:b10,
BID_REFERENCE_NUMBER=:b9,
NUMBER_OF_ACCOUNT_RECORDS=:b8,
CA_SECTION_1=:b7,
CA_SECTION_2=:b6,
CA_SECTION_3=:b5,
CA_SECTION_4=:b4,
CA_SECTION_5=:b3,
FACILITY_TYPE_DESC=:b2,
FACILITY_TYPE_CODE=:b1
WHERE
PORTFOLIO_ID=:b133
AND
OBLIGATION_NUMBER=:b132
AND
PRODUCT_MONTH=:b131

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1   3089.37    3663.28    1683188   29056497      13865        7483
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2   3089.39    3663.29    1683188   29056497      13865        7483

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42     (recursive depth: 1)
********************************************************************************

select file#
from
 file$ where ts#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       11      0.00       0.00          0          0          0           0
Execute     11      0.00       0.00          0          0          0           0
Fetch       22      0.00       0.00          0         33          0          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       44      0.00       0.00          0         33          0          11

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID FILE$ (cr=3 r=0 w=0 time=72 us)
      1   INDEX RANGE SCAN I_FILE2 (cr=2 r=0 w=0 time=45 us)(object id 42)

********************************************************************************

SELECT DISTINCT
TO_CHAR(:b1,'YYYYMM'),
A.LOAN_PORT_ID,
D.PORT_FULL_NAME,
A.OBLIGATION_NO,
A.CA_NO,
C.CA_DSC_TEXT,
A.CA_CUST_ID,
F.CU_CUST_NAME_1_DISPLAY,
A.CA_CNCY_CD,
A.DEAL_PORT_ID,
A.DEAL_CUST_ID,
A.DEAL_CA_NO,
A.DEAL_CNCY_CD,
A.ACCT_STRUCTURE_CD,
A.IS_TIED_TO_CA,
A.ACCTING_METHOD,
A.LENDER_TYPE_CD_CUST_SHARE,
A.LENDER_TYPE_CD_OUR_SHARE,
A.INSTR_TYPE_CD,
L.PROD_TYPE_NAME_FULL,
A.LOAN_STATUS,
A.CLOSURE_TYPE,
B.PROD_GRP,
B.PROD_TYPE_CD,
P.FULL_DSC,
B.PURPOSE_CD,
B.OBLIGATION_BOOKING_UNIT,
M.UNIT_FULL_NAME,
B.COUNTRY_OF_BOOKING,
B.LOAN_FEDERAL_LOAN_CLASSIFICATI,
B.ADVANCE_TYPE_CD,
B.REGULATION_Z_FLAG,
B.REGULATION_U_FLAG,
B.ELIGIBLE_FOR_1098,
B.IND_CLASSIFICATION,
J.INDNAME,
B.CALLABLE_ON_DEMAND,
B.SECURED_CD,
B.PLEDGED_FOR_COLLATERAL,
B.BOOKING_TYPE_CD,
B.PREV_OBLIGATION_NO,
B.INTEREST_RATE_TYPE,
B.AGENT_BANK_CUST_NO,
ACBSDATE_TO_DATE(A.EFFECTIVE_DATE),
ACBSDATE_TO_DATE(A.LEGAL_MATURITY_DATE),
ACBSDATE_TO_DATE(A.INT_RATE_MATURITY_DATE),
ACBSDATE_TO_DATE(A.RATE_SETTING_DATE),
ACBSDATE_TO_DATE(A.STATUS_DATE),
A.CA_SECTION_BOOKED_UNDER,
A.PORT_BASE_CNCY_CD,
A.LOAN_CNCY_CD,
A.CL_SERVICING_USER_ID,
E.USER_NAME,
A.OBLIGATION_CURR_OFFICER,
I.UNIT_FULL_NAME,
A.GL_UNIT,
G.UNIT_FULL_NAME,
A.SERVICING_UNIT,
H.UNIT_FULL_NAME,
A.SERVICING_UNIT_SECTION,
K.UNIT_FULL_NAME,
B.RISK_RATING_OFFICER,
B.RISK_RATING_REGULATOR,
B.WATCH_MONITOR,
B.USER_CD_1,
B.USER_CD_2,
B.USER_CD_3,
B.USER_CD_4,
ACBSDATE_TO_DATE(B.USER_DATE_1),
ACBSDATE_TO_DATE(B.USER_DATE_2),
A.STANDARD_REFERENCE_ID,
T.FULL_DSC,
U.FULL_DSC,
V.FULL_DSC,
W.FULL_DSC,
A.OBLIGOR_NO,
D.PROC_GRP_CD,
N.COUNTRY_OF_EXPOSURE_RISK,
A.ADMINISTRATIVE_USER_ID,
O.USER_NAME,
ACBSDATE_TO_DATE(B.ORIG_MATURITY_DATE),
B.ORIG_GL_COND_CD,
ACBSDATE_TO_DATE(A.DATE_BOOKED),
B.INTERNAL_REFERENCE_NO,
B.EXTERNAL_REFERENCE_NO,
B.BENEFICIARY_CUST_NO,
X.CU_CUST_NAME_1_DISPLAY,
A.GL_COND_CD,
A.PORT_TYPE,
A.BANK_LIAB_TYPE_CD,
A.CUST_LIAB_TYPE_CD,
A.STATUS_CD,
A.STANDARD_REFERENCE_ID,
A.CAN_BE_TRADED,
A.MTM_ELIGIBLE,
A.STANDARD_REFERENCE_ID_TYPE,
B.OBLGTN_ORIG_RESPONSIBILITY_OFF,
B.OBLIGATION_ORIG_GL_UNIT,
Y.UNIT_FULL_NAME,
B.RISK_RATING_CRED_REVIEW,
ACBSDATE_TO_DATE(B.RISK_DATE_OFFICER),
ACBSDATE_TO_DATE(B.RISK_DATE_CRED_REVIEW),
ACBSDATE_TO_DATE(B.RISK_DATE_REGULATOR),
ACBSDATE_TO_DATE(B.NEXT_REVIEW_DATE),
B.REVIEW_FREQ,
ACBSDATE_TO_DATE(B.LAST_REVIEW_DATE),
B.NOTIFY_WHEN_RATE_CHANGES,
B.FIN_TRAN_DDA_ACCT_NO,
ACBSDATE_TO_DATE(B.LAST_RENEWAL_DATE),
B.REPORTING_ACCT_TYPE_CD,
B.INCOMING_PMT_INSTR_AGENT,
B.OUTGOING_PMT_INSTR_AGENT,
B.AGENT_BANK_CORRESPONDENCE_ID,
B.AGENT_BANK_ADDR_ID,
ACBSDATE_TO_DATE(B.SELF_LIQUIDATION_DATE),
A.PRICING_TMPLT_ID,
B.RISK_SCENARIO,
L.INSTR_PROC_CLASSIFICATION,
L.DISCOUNT_CALC_TYPE,
L.ACCT_TYPE_CD,
L.PROD_TYPE_CAN_BE_SOLD_OR_SYND,
L.PRI_BILLING_SCHED_REQUIREMENT,
L.ALLOW_NEGATIVE_PRIN_BALS,
ACBSDATE_TO_DATE(B.ACCTING_METHOD_DATE),
A.BID_REFERENCE_NO,
A.NO_OF_ACCT_RECORDS,
A.CA_SECTION_1,
A.CA_SECTION_2,
A.CA_SECTION_3,
A.CA_SECTION_4,
A.CA_SECTION_5,
R.FULL_DSC,
N.CA_TYPE_CD
FROM
DLS_STAGE.CL_MASTER_RECORD A,
DLS_STAGE.CL_MISC_CDS_VALUES B,
DLS_STAGE.CA_MASTER_RECORD C,
DLS_STAGE.PORT_DFN_TABLE D,
DLS_STAGE.SECURITY_USER_MASTER_RECORD E,
DLS_STAGE.CUST_MASTER_FILE F,
DLS_STAGE.BANK_ORG_UNIT_TABLE G,
DLS_STAGE.BANK_ORG_UNIT_TABLE H,
DLS_STAGE.BANK_ORG_UNIT_TABLE I,
DLS_STAGE.COMMON_TABLES_IND_DETAILS J,
DLS_STAGE.BANK_ORG_UNIT_TABLE K,
DLS_STAGE.ACCT_DFN_TABLE L,
DLS_STAGE.BANK_ORG_UNIT_TABLE M,
DLS_STAGE.CA_MISC_CDS_INFO N,
DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES P,
DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES R,
DLS_STAGE.SECURITY_USER_MASTER_RECORD O,
DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES T,
DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES U,
DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES V,
DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES W,
DLS_STAGE.CUST_MASTER_FILE X,
DLS_STAGE.BANK_ORG_UNIT_TABLE Y
WHERE
A.LOAN_PORT_ID =  B.LOAN_PORT_ID AND
A.OBLIGATION_NO =  B.OBLIGATION_NO AND
A.LOAN_PORT_ID =  C.PORT_ID(+) AND
A.CA_NO =  C.CA_NO(+) AND
A.LOAN_PORT_ID =  D.PORT_ID AND
A.CL_SERVICING_USER_ID =  E.USER_ID(+) AND
A.CA_CUST_ID =  F.CU_CUST_ID(+) AND
A.GL_UNIT =  G.UNIT_ID(+) AND
A.SERVICING_UNIT =  H.UNIT_ID(+) AND
A.OBLIGATION_CURR_OFFICER =  I.UNIT_ID(+) AND
B.IND_CLASSIFICATION =  J.IND_ID(+) AND
A.SERVICING_UNIT_SECTION =  K.UNIT_ID(+) AND
A.INSTR_TYPE_CD =  L.PROD_TYPE_CD(+) AND
B.OBLIGATION_BOOKING_UNIT =  M.UNIT_ID(+) AND
C.PORT_ID =  N.PORT_ID(+) AND
C.CA_NO =  N.CA_NO(+) AND
A.ADMINISTRATIVE_USER_ID =  O.USER_ID(+) AND
B.USER_CD_1 =  T.KEY_VALUE_1(+) AND
B.USER_CD_2 =  U.KEY_VALUE_1(+) AND
B.USER_CD_3 =  V.KEY_VALUE_1(+) AND
B.USER_CD_4 =  W.KEY_VALUE_1(+) AND
B.BENEFICIARY_CUST_NO =  X.CU_CUST_ID(+) AND
B.OBLIGATION_ORIG_GL_UNIT =  Y.UNIT_ID(+) AND
B.PROD_TYPE_CD =  P.KEY_VALUE_1(+) AND
B.PROD_GRP =  P.KEY_VALUE_2(+) AND
N.CA_TYPE_CD =  R.KEY_VALUE_1(+)
AND
G.UNIT_TYPE_CD(+) = '02' AND
H.UNIT_TYPE_CD (+) = '05' AND
I.UNIT_TYPE_CD (+) = '10' AND
K.UNIT_TYPE_CD (+) = '08' AND
M.UNIT_TYPE_CD(+) = '02' AND
T.TABLE_ID(+) = 'T2320' AND
U.TABLE_ID(+) = 'T2325' AND
V.TABLE_ID(+) = 'T2330' AND
W.TABLE_ID(+) = 'T2335' AND
Y.UNIT_TYPE_CD(+) = '02' AND
P.TABLE_ID(+) = 'T0335' AND
R.TABLE_ID(+) = 'T1095'
AND
NOT EXISTS (SELECT 1 FROM DLS_DM.LOAN_PRODUCT_DIMENSION WHERE
PORTFOLIO_ID = A.LOAN_PORT_ID
AND
OBLIGATION_NUMBER = A.OBLIGATION_NO
AND
PRODUCT_MONTH = TO_CHAR(:b1,'YYYYMM')
)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.05          0          0          0           0
Execute      1      0.23       0.23          0          0          0           0
Fetch        1    725.75     727.98        331   49343867          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    726.03     728.26        331   49343867          0           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42     (recursive depth: 1)
********************************************************************************

SELECT TO_CHAR(LAST_PROCESSING_DATE,'YYYYMM')
FROM
 ADM_ACBS_PERIODS


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42     (recursive depth: 1)
********************************************************************************

UPDATE /*+ PARALLEL (DLS_DM.LOAN_PRODUCT_DIMENSION,4)*/ DLS_DM.LOAN_PRODUCT_DIMENSION SET ACCOUNT_STRUCTURE_DESC =
  (SELECT  FULL_DSC FROM
   DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID(+) = 'T2285') AND (ACCOUNT_STRUCTURE_CODE =KEY_VALUE_1(+)) AND
   (ACCOUNT_STRUCTURE_CODE IS NOT NULL)),
  ACCOUNTING_METHOD_DESCRIPTION =
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T2280') AND (ACCOUNTING_METHOD_CODE=KEY_VALUE_1) AND
   (ACCOUNTING_METHOD_CODE IS NOT NULL)),
  LOAN_STATUS_DESCRIPTION =
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T2375') AND(LOAN_STATUS_CODE=KEY_VALUE_1) AND
   (LOAN_STATUS_CODE IS NOT NULL)),
  CLOSURE_TYPE_DESCRIPTION  =
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T2380') AND(CLOSURE_TYPE_CODE=KEY_VALUE_1) AND
   (CLOSURE_TYPE_CODE IS NOT NULL)),
  PRODUCT_GROUP_DESC   =
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T0330') AND (TRIM(PRODUCT_GROUP_CODE)=KEY_VALUE_1) AND
   (PRODUCT_GROUP_CODE IS NOT NULL)),
  PRODUCT_TYPE_DESCRIPTION  =
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T0335') AND(PRODUCT_TYPE_CODE=KEY_VALUE_1) AND
   (PRODUCT_TYPE_CODE IS NOT NULL)),
  PURPOSE_DESCRIPTION =
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T0340') AND (PURPOSE_CODE=KEY_VALUE_1) AND
   (PURPOSE_CODE IS NOT NULL)),
  COUNTRY_OF_BOOKING_DESC =
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T0185') AND (TRIM(COUNTRY_CODE_OF_BOOKING)=KEY_VALUE_1) AND
   (COUNTRY_CODE_OF_BOOKING IS NOT NULL)),
  FEDERAL_LOAN_CLASS_DESCRIPTION =
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T2015') AND(FEDERAL_LOAN_CLASS_CODE=KEY_VALUE_1) AND
   (FEDERAL_LOAN_CLASS_CODE IS NOT NULL)),
  ADVANCE_TYPE_DESCRIPTION=
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T2020') AND (ADVANCE_TYPE_CODE=KEY_VALUE_1) AND
   (ADVANCE_TYPE_CODE IS NOT NULL)),
  BOOKING_TYPE_DESCRIPTION =
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T2030') AND(BOOKING_TYPE_CODE=KEY_VALUE_1) AND
   (BOOKING_TYPE_CODE IS NOT NULL)),
  INTEREST_RATE_TYPE_DESC=
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T2060') AND(INTEREST_RATE_TYPE_CODE=KEY_VALUE_1) AND
   (INTEREST_RATE_TYPE_CODE IS NOT NULL)),
  INTERNAL_RISK_RATING_DESC =
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T2100') AND(INTERNAL_RISK_RATING_CODE=KEY_VALUE_1) AND
   (INTERNAL_RISK_RATING_CODE IS NOT NULL)),
  REGULAR_RISK_RATING_DESC=
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T1185') AND (REGULAR_RISK_RATING_CODE=KEY_VALUE_1) AND
   (REGULAR_RISK_RATING_CODE IS NOT NULL)),
  CA_CNTRY_OF_EXPOSURE_RISK_DESC=
  (SELECT  FULL_DSC FROM DLS_STAGE.COMMON_TABLE_DETAILED_ENTRIES
   WHERE (TABLE_ID = 'T0185') AND (TRIM(CA_COUNTRY_OF_EXPOSURE_RISK)=KEY_VALUE_1) AND
   (CA_COUNTRY_OF_EXPOSURE_RISK IS NOT NULL)),
  PORTFOLIO_BASE_CURRENCY_DESC  =
   (SELECT B.CRNCY_CNCY_FULL_NAME FROM DLS_STAGE.CNCY_DFN_TABLE B
   WHERE (PORTFOLIO_BASE_CURRENCY_CODE=B.CNCY_CD) AND (PORTFOLIO_BASE_CURRENCY_CODE IS NOT NULL)),
  LOAN_CURRENCY_DESCRIPTION =
   (SELECT B.CRNCY_CNCY_FULL_NAME FROM DLS_STAGE.CNCY_DFN_TABLE B
   WHERE (LOAN_CURRENCY_CODE=B.CNCY_CD) AND (LOAN_CURRENCY_CODE IS NOT NULL)),
  CA_CURRENCY_DESCRIPTION =
   (SELECT B.CRNCY_CNCY_FULL_NAME FROM DLS_STAGE.CNCY_DFN_TABLE B
   WHERE (CA_CURRENCY_CODE=B.CNCY_CD) AND (CA_CURRENCY_CODE IS NOT NULL)),
  DEAL_CURRENCY_DESCRIPTION =
   (SELECT B.CRNCY_CNCY_FULL_NAME FROM DLS_STAGE.CNCY_DFN_TABLE B
   WHERE (DEAL_CURRENCY_CODE=B.CNCY_CD) AND (DEAL_CURRENCY_CODE IS NOT NULL))
   WHERE PRODUCT_MONTH = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.03          0         33          0           0
Execute      1      0.90       0.97          4       4188      13207        7483
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.95       1.01          4       4221      13207        7483

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42     (recursive depth: 1)
********************************************************************************

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          0          0          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42  



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.10       0.21         18        180          0           0
Execute      3      0.14       0.04    1683559   78852373          0           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.25       0.25    1683577   78852553          0           2

Misses in library cache during parse: 2
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      203      0.26       0.30          0         81          0           0
Execute 127504   3097.39    3671.08    1683192   29060700      27078       14967
Fetch   128187    772.26     775.75        385   49791868          0       70287
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   255894   3869.92    4447.13    1683577   78852649      27078       85254

Misses in library cache during parse: 54

   19  user  SQL statements in session.
  187  internal SQL statements in session.
  206  SQL statements in session.
********************************************************************************
Trace file: D:\Oracle\admin\ACBSDM\udump\acbsdm_ora_3212.trc
Trace file compatibility: 9.00.01
Sort options: default

       1  session in tracefile.
      19  user  SQL statements in trace file.
     187  internal SQL statements in trace file.
     206  SQL statements in trace file.
      57  unique SQL statements in trace file.
  257759  lines in trace file.


0
 
LVL 6

Expert Comment

by:chedgey
ID: 12532836
Looks like it should be a relatively easy one.

A lot of the time is being eaten up by your update of DLS_DM.LOAN_PRODUCT_DIMENSION.

What are the indices on this table? Are the analysed? Have you run "Analyse Index <Index Name>;" for all of them?

Why not try going into Enterprise Manager and doing a full analyse of all objects in the relevant schemata. gather_schema_stats is not the most reliable of things.

Regards

Chedgey
0
 

Author Comment

by:metro2003
ID: 12533607
Chedgey,

Thank you for looking at the trace file.  Ok, here are the indices.  I create a script for the indices using TOAD.  There are only 2 for the LOAN_PRODUCT_DIMENSION table. Here they are:
Also, how would I go about doing a full analysis of all objects in the relevant schemas?


CREATE INDEX DLS_DM.LOAD_PROD_DIM_PORTFOLIO_ID ON DLS_DM.LOAN_PRODUCT_DIMENSION
(PORTFOLIO_ID)
NOLOGGING
TABLESPACE DM_INDEX1
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DLS_DM.SYS_C002084 ON DLS_DM.LOAN_PRODUCT_DIMENSION
(PRODUCT_KEY)
NOLOGGING
TABLESPACE DM_DATA1
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
0
 
LVL 6

Expert Comment

by:chedgey
ID: 12533995
OK, I set up a little test and your update statement picks up the index on PORTFOLIO_ID and uses it to do an index range scan. Just how selective is this index? I.e how many records are there in LOAN_PRODUCT_DIMENSION and how many disctint values are there in PORTFOLIO_ID? You can see this by doing "Select NUM_ROWS, DISTINCT_KEYS, LAST_ANALYZED From DBA_INDEXES Where INDEX_NAME='LOAD_PROD_DIM_PORTFOLIO_ID' and OWNER='DLS_DM'. Please post the results

For a full analysis I tend to use Enterprise Manager or you can just write a simple dynamic PL/SQL script that generates the statements for you. This will be something like:

Set Heading off
Set PAGESIZE 5000
Spool GatherStats.sql
Select 'begin dbms_stats.gather_index_stats(ownname=> '||OWNER||', indname=> '||INDEX_NAME||', partname=> NULL); end;'
  From DBA_INDEXES
 Where ...

Select 'begin dbms_stats.gather_index_stats(ownname=> '||OWNER||', tabname=> '||TABLE_NAME||', partname=> NULL); end;'
  From DBA_TABLES
 Where ...
Spool Off
@GatherStats


Regards

Chedgey
0
 

Author Comment

by:metro2003
ID: 12534198
Chedgey,

Looks like 5 distinct keys and over 70,000 rows.  I will try to do the PLSQL thing right now.

  NUM_ROWS DISTINCT_KEYS LAST_ANALYZED
---------- ------------- ---------
     72760             5 03-NOV-04
0
 
LVL 6

Expert Comment

by:chedgey
ID: 12534264
The low number of distinct keys is a part of your problem there although as the data volume is so low and it is only a single table update I am a little perplexed as to why it takes so long.

After you have analyzed the indices can you cut this problematic update statement into TOAD and do an explain plan of it to see what it comes up with for you. TOAD should do this automatically for you.

Regards

Chedgey
0
 

Author Comment

by:metro2003
ID: 12534895
I tried to create the SQL script as follows, but when I run it, nothing happens.  I just get a number: 163 and just stays there.

Select 'begin dbms_stats.gather_index_stats(ownname=> '||OWNER||', indname=> '||INDEX_NAME||', partname=> NULL); end;'
From DBA_INDEXES
Where OWNER='DLS_DM'

Is the script ok?  And in toad you mean I should copy the actual script that updates the tables (LOAN_PRODUCT_DIMENSION_MAP) and do an explain plan, right?
thank you.

0
 
LVL 6

Accepted Solution

by:
chedgey earned 800 total points
ID: 12535612
You will have to do some basic changes if you SQL*Plus environment has it's setting changed. Also, as I was typing from memory there were a couple of minor problems. Try:

Select 'begin dbms_stats.gather_index_stats(ownname=> '''||OWNER||''', indname=> '''||INDEX_NAME||''', partname=> NULL); end;'
From DBA_INDEXES
Where OWNER='DLS_DM'

notice the triple single quotation marks.

You may also have to try things like Set LINESIZE 200 prior to running it.

If you just have a line like "SQL> 163:" then SQL*Plus thinks you are writing line 163 of a PL/SQL or SQL block. Why not try starting SQL*Plus afresh to remove any unwanted settings and just past in:

Set Heading off
Set PAGESIZE 5000
Set LINESIZE 200
Spool GatherStats.sql
Select 'begin dbms_stats.gather_index_stats(ownname=> '''||OWNER||''', indname=> '''||INDEX_NAME||''', partname=> NULL); end;'
  From DBA_INDEXES
 Where ...

Select 'begin dbms_stats.gather_index_stats(ownname=> '''||OWNER||''', tabname=> '''||TABLE_NAME||''', partname=> NULL); end;'
  From DBA_TABLES
 Where ...
Spool Off
@GatherStats


Regards

Chedgey
0
 
LVL 2

Expert Comment

by:tpanar
ID: 12535645
Just as a side issue, since I didn't notice this brought up above, are you sure it is Oracle that has the performance problems, and not your OS in general? Is a virus scanner or some such other disk/cpu intensive application running?

Regards,
-Tammie
0
 

Author Comment

by:metro2003
ID: 12536089
ok,  I figured out the script needed the Exec command and not Begin. it was complaining about the Begin part.

Select 'exec dbms_stats.gather_index_stats(ownname=> '''||OWNER||''', indname=> '''||INDEX_NAME||''', partname=> NULL);'
From DBA_INDEXES
Where OWNER='DLS_DM'

and in the mean time I'm working on the Execute plan.  I have to install the Toad part on the dB first.  I'll post as soon as I have something.  Thank you for sticking with me so far. I'm almost there.

0
 

Author Comment

by:metro2003
ID: 12538166
Chedgey,

I am not able to create an Explain Plan in TOAD because it does not accept the script.  I copied the script out of the procedure and pasted it into another window.  I ran the script and TOAD stops half way with the following errors:

------------8<------------------------------
Error on line 683
BEGIN
UPDATE ADM_ACBS_PERIODS SET
LAST_PROCESSING_DATE = TO_DATE(GET_PROCESSIN

ORA-06550: line 8, column 6:
PLS-00201: identifier 'U_LOAN_PRODUCT_DIMENSION' must be declared
ORA-06550: line 8, column 1:
PL/SQL: SQL Statement ignored
------------8<------------------------------

There is nothing wrong with the script because if I run it as a procedure, it works, however, there is no Explain Plan created for procedures.  I don't know what else to try.  I even tried in SQLPLUS:

------------8<------------------------------
15:36:09 SQL> explain plan for
15:53:05   2  Exec d_loan_product_dimension_map;
Exec d_loan_product_dimension_map
*
ERROR at line 2:
ORA-00905: missing keyword
------------8<------------------------------


it doesn't like procedures.  It has to be a Select statement.  But this procedure does many updates, so I don't know how to run it by itself.  Any ideas? Thank you.

0
 
LVL 2

Expert Comment

by:KurtR
ID: 12541832
explain the 2 statements with the greatest cpu and elapsed time.
UPDATE DLS_DM.LOAN_PRODUCT_DIMENSION SET ...

SELECT DISTINCT
TO_CHAR(:b1,'YYYYMM'),
A.LOAN_PORT_ID,
...

in sqlplus the correct syntax is:
explain plan for
{your select/insert/update/delete statement}

example:
explain plan for
select * from emp;

explain plan can only be used on single sql-statements (no procedures, packages, ...)
to view the results use the following statement:
set pages 80 lines 140
select * from table(dbms_xplan.display());

post the explaind plan for these 2 statements here...

you need a table called plan_table in your schema (or the privileges to use it via synonym).
the scripte to generate the plan_table is supplied by oracle and has the name utlxplan.sql
(can be found under $ORACLE_HOME/rdbms/admin)

what is about U_LOAN_PRODUCT_DIMENSION? i cannot find it in your postings (neither source code nor tkprof.)? is it a table?
however as much as i see, it is not part of the 2 statements i mentioned above.

if you do not get this to work, try tkprof once more (as described above) and be sure to close the sqlplus session from which you started the trace, before you run tkprof.
you see some statements have (Rows     Row Source Operation) after their statatistics. if you close your sqlplus session before starting tkprof you will see these lines for all statements.
(Row Source Operation = real execution plan with much more details as you can get with explain plan)
0
 

Author Comment

by:metro2003
ID: 12729034
Hey All,

It took long enough, but I think I figured it out.  My problem was that I was analyzing the tables and indicies after the load.  For some reason, I analyzed everything right before the Loading process and the Time went down from 2hours and a half to about 8 minutes.  I think that's a bit of an improvement. :) That and I did the following:

hash_area_size  integer = 33 mb  
java_pool_size  big integer = 33 mb  
pga_aggregate_target = 700 mb
sort_area_size = 33 mb  

Thank you all for everything.

-Metro.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month20 days, 6 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question