Hi, I have a long running querry which returns 59k rows in 25 minutes. The querry is doing a select on a view and in the view I am joining 3 tables. I am using the RULE based optimizer and I tried analysing tables and using the CBO. It still takes 25 minutes. I tried using various hints without much luck. The TKPROF shows that no FULL table scans is being done. Please see attached TKPROF.
Couls someone please guide me in the right direction. Thanks.
********************************************************************************
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
********************************************************************************
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 744 (SYSADM)
********************************************************************************
INSERT INTO test ( EMPLID
, ROWSECCLASS
, ACCESS_CD
, EMPL_RCD
, NAME
, LAST_NAME_SRCH
, SETID_DEPT
, DEPTID
, NAME_AC
, PER_STATUS)
SELECT DISTINCT EMPLID
, ROWSECCLASS
, ACCESS_CD
, EMPL_RCD
, NAME
, LAST_NAME_SRCH
, SETID_DEPT
, DEPTID
, NAME_AC
, PER_STATUS
FROM PS_PERS_SRCH_FST
3 user SQL statements in session.
10 internal SQL statements in session.
13 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: psprod_ora_24090.trc
Trace file compatibility: 8.00.04
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
10 internal SQL statements in trace file.
13 SQL statements in trace file.
12 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
SYSADM.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
394 lines in trace file.