Printable View
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.
I get a bunch of garbage in the download file.
Here is the attached file again. Thank you.Quote:
Originally posted by marist89
I get a bunch of garbage in the download file.
I'd be interested in seeing a tkprof for:
...and...Code:SELECT DISTINCT EMPLID
, ROWSECCLASS
, ACCESS_CD
, EMPL_RCD
, NAME
, LAST_NAME_SRCH
, SETID_DEPT
, DEPTID
, NAME_AC
, PER_STATUS
FROM PS_PERS_SRCH_FST
Code:SELECT EMPLID
, ROWSECCLASS
, ACCESS_CD
, EMPL_RCD
, NAME
, LAST_NAME_SRCH
, SETID_DEPT
, DEPTID
, NAME_AC
, PER_STATUS
FROM PS_PERS_SRCH_FST
Quote:
Originally posted by marist89
I'd be interested in seeing a tkprof for:
...and...Code:SELECT DISTINCT EMPLID
, ROWSECCLASS
, ACCESS_CD
, EMPL_RCD
, NAME
, LAST_NAME_SRCH
, SETID_DEPT
, DEPTID
, NAME_AC
, PER_STATUS
FROM PS_PERS_SRCH_FST
Code:SELECT EMPLID
, ROWSECCLASS
, ACCESS_CD
, EMPL_RCD
, NAME
, LAST_NAME_SRCH
, SETID_DEPT
, DEPTID
, NAME_AC
, PER_STATUS
FROM PS_PERS_SRCH_FST
TKPROF: Release 8.1.7.2.0 - Production on Thu Oct 23 16:08:48 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Trace file: psprod_ora_24090.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.02 0.04 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.02 0.04 0 0 0 0
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
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.14 0.14 0 0 0 0
Execute 1 961.36 1042.97 1346 13139566 2103 59926
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 961.50 1043.11 1346 13139566 2103 59926
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 744 (SYSADM)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: HINT: RULE
0 SORT (UNIQUE)
0 FILTER
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PS_SCRTY_TBL_DEPT'
0 INDEX (RANGE SCAN) OF 'PSBSCRTY_TBL_DEPT'
(NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PSTREENODE'
0 INDEX (RANGE SCAN) OF 'PSFPSTREENODE'
(NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PS_JOB'
0 INDEX (RANGE SCAN) OF 'PS0JOB' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PS_PERSON'
0 INDEX (UNIQUE SCAN) OF 'PS_PERSON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PS_NAMES'
0 INDEX (RANGE SCAN) OF 'PS_NAMES' (UNIQUE)
0 INDEX (RANGE SCAN) OF 'PS_PERS_DATA_EFFDT' (UNIQUE)
0 SORT (AGGREGATE)
0 INDEX (RANGE SCAN) OF 'PSBJOB' (NON-UNIQUE)
0 SORT (AGGREGATE)
0 INDEX (RANGE SCAN) OF 'PSBJOB' (NON-UNIQUE)
0 SORT (AGGREGATE)
0 FILTER
0 INDEX (RANGE SCAN) OF 'PS_PERS_DATA_EFFDT' (UNIQUE)
0 SORT (AGGREGATE)
0 INDEX (RANGE SCAN) OF 'PS_PERS_DATA_EFFDT' (UNIQUE)
0 SORT (AGGREGATE)
0 FILTER
0 INDEX (RANGE SCAN) OF 'PS_NAMES' (UNIQUE)
0 SORT (AGGREGATE)
0 INDEX (RANGE SCAN) OF 'PS_NAMES' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PS_SCRTY_TBL_DEPT'
0 INDEX (RANGE SCAN) OF 'PS_SCRTY_TBL_DEPT' (UNIQUE)
********************************************************************************
alter session set sql_trace = false
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 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 2 0.00 0.02 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 744 (SYSADM)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.14 0.16 0 0 0 0
Execute 3 961.38 1043.01 1346 13139566 2103 59926
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 961.52 1043.17 1346 13139566 2103 59926
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 10 0.01 0.01 0 0 0 0
Execute 72 0.01 0.01 0 0 0 0
Fetch 214 0.02 0.05 0 355 0 145
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 296 0.04 0.07 0 355 0 145
Misses in library cache during parse: 0
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.
Don't know why your having problems opening this file. Here it is again. Thank you.
Pl post the view definition.
Tamil