Re: Oracle 8 slacking...

kris p wrote:

> 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

This is not, as you acknowledge, an Oracle issue. Take it up with Business Objects.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)