DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Long Running Querry

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222

    Unhappy Long Running Querry

    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.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I get a bunch of garbage in the download file.
    Jeff Hunter

  3. #3
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222
    Originally posted by marist89
    I get a bunch of garbage in the download file.
    Here is the attached file again. Thank you.
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I'd be interested in seeing a tkprof for:
    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
    ...and...

    Code:
    SELECT EMPLID
    , ROWSECCLASS
    , ACCESS_CD
    , EMPL_RCD
    , NAME
    , LAST_NAME_SRCH
    , SETID_DEPT
    , DEPTID
    , NAME_AC
    , PER_STATUS
    FROM PS_PERS_SRCH_FST
    Jeff Hunter

  5. #5
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222
    Originally posted by marist89
    I'd be interested in seeing a tkprof for:
    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
    ...and...

    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.

  6. #6
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222
    Don't know why your having problems opening this file. Here it is again. Thank you.
    Attached Files Attached Files

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Pl post the view definition.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width