Oracle 8 slacking...

From: kris p <kplasun_at_hotmail.com>
Date: 16 Apr 2004 09:25:58 -0700
Message-ID: <db13b49.0404160825.2c3edeb0@posting.google.com>

Hi

I have a performance problem with Oracle when printing a Crystal Report. The query when run by itself through sqlplus runs fast, the report when printed through Crystal Report designer runs fast as well but when I print the report through my application it takes 2-3 minutes to print. It's not my applications pre-report processing that takes up any time but the Crystal Raw_Export() method. I look at the processor and Oracle process stats through PERFMON in windows and Oracle barely does anything, shows a spike here, a blip there and that's it, slow like a drunk on an assembly line.

The SQL I'm running is below:

---

SELECT

    SYDMAIN."FSYSTEMKEY",
    SYDMAIN."FTITLE",
    SYDMAIN."FTITLENS",
    SYDMAIN."FISBN",
    SYDMAIN."FPUBLISHER",
    SYDMAIN."FTEXTYEAR",
    SYDMAIN."FEDITION",
    SYDMAIN."FPHYSDESC",
    SYDMAIN."FSECLEVEL",
    SYDMAIN."FACCESSKEY",
    SYDMAIN."FMULTIVOL",
    SYDMAIN."FISSN",
    SYDMAIN."FCALLLC",
    SYDMAIN."FCHECKED",
    SYDMAIN."FABSTRACTS",
    SYDMAIN."FAMENDMENTS",
    SYDMAIN."FANNOTATION",
    SYDMAIN."FCONTENTS",
    SYDMAIN."FNOTE",
    SYDMAIN."FSTD",

    TCABULLETIN."FCAPERIOD2",
    TCABULLETIN."FCAYEAR",
    TMATTYPE."FCODE",
    TMATTYPE."FCODEDESC",
    TDOCUMENTCD."FCODE",
    TDOCUMENTCD."FCODEDESC",
    TAQITEM."FTITLE",
    TAQITEM."FTITLENS",
    TCAPERIOD."FCODE",
    TCAPERIOD."FCODEDESC"
FROM
"TBOOKS" SYDMAIN,
"TBOOKS_AQITEM" SYDMAIN_AQITEM,
"TCABULLETIN" TCABULLETIN,
"TMATTYPE" TMATTYPE,
"TDOCUMENTCD" TDOCUMENTCD,
"TAQITEM" TAQITEM,
"TCAPERIOD" TCAPERIOD

WHERE
(

    SYDMAIN."FSYSTEMKEY" = SYDMAIN_AQITEM."FTEMPLATEKEY" (+)

    AND SYDMAIN."FCABULLETIN" = TCABULLETIN."FSYSTEMKEY" (+)  
    AND SYDMAIN."FMATERIAL" = TMATTYPE."FSYSTEMKEY" (+)  
    AND SYDMAIN."FDOCTYPE" = TDOCUMENTCD."FSYSTEMKEY" (+)  

    AND SYDMAIN_AQITEM."FAQITEM" = TAQITEM."FSYSTEMKEY" (+)     AND TCABULLETIN."FCAPERIOD2" = TCAPERIOD."FSYSTEMKEY" (+) )
AND
(

    SYDMAIN."FSYSTEMKEY" IN
    (

        SELECT FRECORDGUID  
        FROM TSYSSELECTEDRECORDS  
        WHERE FSESSIONGUID = '3292E1D9-F1AC-4E9E-9174-75FDB00533B7'

    )
)
AND
(

    SYDMAIN."FSYSTEMKEY" NOT IN
    (

        SELECT FOBJECTKEY  
        FROM TSYSITEMSECURTY  
        WHERE LOWER (FUSERSECKEY) = LOWER

('F84C2A27-6F78-4DC4-AEF8-6B73E29CB892') AND FVIEW = '2'     )
)
ORDER BY
    SYDMAIN."FACCESSKEY" ASC,
    SYDMAIN."FSYSTEMKEY" ASC;

---

The EXPLAIN PLAN look like this, pretty cheap i think:

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=855) 1 0 SORT (ORDER BY) (Cost=11 Card=1 Bytes=855) 2 1 NESTED LOOPS (OUTER) (Cost=10 Card=1 Bytes=855) 3 2 NESTED LOOPS (OUTER) (Cost=9 Card=1 Bytes=806)

4  3     NESTED LOOPS (OUTER) (Cost=8 Card=1 Bytes=730)  
5  4      NESTED LOOPS (OUTER) (Cost=7 Card=1 Bytes=681)  
6  5       NESTED LOOPS (OUTER) (Cost=6 Card=1 Bytes=629)  
7  6        NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=565) 
8  7         NESTED LOOPS (Cost=4 Card=1 Bytes=489)  
9  8          VIEW OF 'VW_NSO_1' (Cost=3 Card=1 Bytes=20 )
10  9           SORT (UNIQUE) (Cost=3 Card=1 Bytes=74) 
11  10            TABLE ACCESS (FULL) OF 'TSYSSELECTEDRE CORDS'
(Cost=1 Card=1 Bytes=74)
12  8          TABLE ACCESS (BY INDEX ROWID) OF 'TBOOKS' (Cost=1
Card=312 Bytes=146328)
13  12           INDEX (UNIQUE SCAN) OF 'IBOOKS_KEY' (UNIQUE)
14  13            TABLE ACCESS (BY INDEX ROWID) OF 'TSYSITEMSECURTY'
(Cost=3 Card=1 Bytes=89)
15  14             INDEX (RANGE SCAN) OF 'ISYSITEMSECURTY_KEY'
(UNIQUE) (Cost=2 Card=1)
16  7         TABLE ACCESS (FULL) OF 'TBOOKS_AQITEM' (Cost=1 Card=1
Bytes=76)
17  6        TABLE ACCESS (BY INDEX ROWID) OF 'TAQITEM' (Cost=1 Card=1
Bytes=64)
18  17         INDEX (UNIQUE SCAN) OF 'IAQITEM_KEY' (UNIQUE)
19  5       TABLE ACCESS (BY INDEX ROWID) OF 'TDOCUMENTCD' (Cost=1
Card=8 Bytes=416)
20  19        INDEX (UNIQUE SCAN) OF 'IDOCUMENTCD_KEY' (UNIQUE)
21  4      TABLE ACCESS (BY INDEX ROWID) OF 'TMATTYPE' (Cost=1 Card=16
Bytes=784)
22  21       INDEX (UNIQUE SCAN) OF 'IMATTYPE_KEY' (UNIQUE) 
23  3     TABLE ACCESS (BY INDEX ROWID) OF 'TCABULLETIN' (Cost=1
Card=6234 Bytes=473784)
24  23      INDEX (UNIQUE SCAN) OF 'ICABULLETIN_KEY' (UNIQUE) 

25 2 TABLE ACCESS (BY INDEX ROWID) OF 'TCAPERIOD' (Cost=1 Card=13 Bytes=637)
26 25 INDEX (UNIQUE SCAN) OF 'ICAPERIOD_KEY' (UNIQUE) Anybody have any ideas? Judging by the explain plan above the printing should not take so long, eh?

TIA
kristoff plasun Received on Fri Apr 16 2004 - 11:25:58 CDT