Solved: Jobs take Extremely long time to run. Please help.

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

metro2003

ASKER

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 ;
/

morphman

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.

KurtR

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.

MikeOM_DBA

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.

metro2003

ASKER

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.

metro2003

ASKER

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.

chedgey

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

metro2003

ASKER

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;

chedgey

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

metro2003

ASKER

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

chedgey

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

metro2003

ASKER

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.

tpanar

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

metro2003

ASKER

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.

metro2003

ASKER

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.

KurtR

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)

metro2003

ASKER

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.