Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Distributed Query in 8i->9i now slower 9i->9i

Re: Distributed Query in 8i->9i now slower 9i->9i

From: Lee Van Doren <leevd_at_adelphia.net>
Date: 2 Mar 2004 06:56:39 -0800
Message-ID: <afc7da8f.0403020656.1df9ed79@posting.google.com> 





"Sybrand Bakker" <postbus_at_sybrandb.nospam.demon.nl> wrote in message news:<1041j61iava6h67_at_corp.supernews.com>...


> Basically your post runs down to

> 'It doesn't work, why?'

> If you provide me your crystall ball, I will tell you.

> Your post doesn't contain anything but vague statements.




Sorry... here is more specific data... I apologize about the
formatting



Note the one line I mark with a !>  that is a big difference between
the two plans and seems to indicate taking advantage of memory
significantly more than the slower run??



6 seconds:  8i linked to SUBGL_LINK (9i)




Operation		Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop


SELECT STATEMENT Optimizer Mode=CHOOSE	1  	 	395
  SORT AGGREGATE			1  	565  	 	 	      	             	 
    HASH JOIN				1  	565  	395  	 	      	             	 
      REMOTE				1  	29  	2  	SUBGL_LINK.POWERSRC.COM	SERIAL
      NESTED LOOPS			63  	32 K	392  	 	      	             	 
        HASH JOIN			1  	492  	263  	 	      	             	 
          NESTED LOOPS			1  	484  	260  	 	      	             	 
            NESTED LOOPS		1  	414  	257  	 	      	             	 
              NESTED LOOPS		1  	344  	254  	 	      	             	 
                NESTED LOOPS		1  	290  	252  	 	      	             	 
                  HASH JOIN		1  	236  	250  	 	      	             	 
                    TABLE ACCESS FULL	PORTFOLIO	2  	26  	1
                    HASH JOIN		73  	15 K	248
                      NESTED LOOPS	2  	248  	5
                        REMOTE		2  	140  	3 
	SUBGL_LINK.POWERSRC.COM	SERIAL
                        REMOTE		23  	1 K	
	SUBGL_LINK.POWERSRC.COM	SERIAL
                      REMOTE		834  	80 K	242 
	SUBGL_LINK.POWERSRC.COM	SERIAL
                  REMOTE		23  	1 K	 	SUBGL_LINK.POWERSRC.COM	SERIAL
                REMOTE			23  	1 K	 	SUBGL_LINK.POWERSRC.COM	SERIAL
              REMOTE			1 K	111 K	 	SUBGL_LINK.POWERSRC.COM	SERIAL
            REMOTE			1 K	111 K	 	SUBGL_LINK.POWERSRC.COM	SERIAL
          TABLE ACCESS FULL	PNL_BOOK_SDS_MAPPING	1 K	10 K	2
!>       REMOTE				4 M	179 M	 	SUBGL_LINK.POWERSRC.COM	SERIAL	   

------------------




28 Seconds:  9i linked to SUBGL_LINK (9i)  same data on both sides,
same schema on local side, same database on link side



Operation		Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop


SELECT STATEMENT Optimizer Mode=CHOOSE	1  	 	393
  SORT AGGREGATE			1  	565  	 	 	      	             	 
    HASH JOIN				1  	565  	393  	 	      	             	 
      NESTED LOOPS			1  	557  	389  	 	      	             	 
        NESTED LOOPS			1  	487  	387  	 	      	             	 
          NESTED LOOPS			1  	417  	385  	 	      	             	 
            NESTED LOOPS		1  	363  	383  	 	      	             	 
              NESTED LOOPS		1  	334  	381  	 	      	             	 
                NESTED LOOPS		1  	290  	253  	 	      	             	 
                  HASH JOIN		1  	236  	251  	 	      	             	 
                    TABLE ACCESS FULL	PORTFOLIO	2  	26  	2
                    HASH JOIN		62  	13 K	248
                      NESTED LOOPS	1  	124  	4
                        REMOTE		1  	70  	3 
	SUBGL_LINK.POWERSRC.COM	SERIAL
                        REMOTE		1  	54  	1 
	SUBGL_LINK.POWERSRC.COM	SERIAL
                      REMOTE		1 K	108 K	243 
	SUBGL_LINK.POWERSRC.COM	SERIAL
                  REMOTE		1  	54  	2  	SUBGL_LINK.POWERSRC.COM	SERIAL
                REMOTE			63  	2 K	128  	SUBGL_LINK.POWERSRC.COM	SERIAL
              REMOTE			1  	29  	2  	SUBGL_LINK.POWERSRC.COM	SERIAL
            REMOTE			1  	54  	2  	SUBGL_LINK.POWERSRC.COM	SERIAL
          REMOTE			1  	70  	2  	SUBGL_LINK.POWERSRC.COM	SERIAL
        REMOTE				1  	70  	2  	SUBGL_LINK.POWERSRC.COM	SERIAL
      TABLE ACCESS FULL	PNL_BOOK_SDS_MAPPING	1 K	10 K	3







QUERY:


SELECT



count(*)


FROM


  SUBGL_LINK_PLR_FND_FLEX_VALUES  FND_FLEX_VALUES_BOOKID,
  SUBGL_LINK_PLR_FND_FX_VAL_TL  FND_FX_VAL_TL_BOOKID,
  SUBGL_LINK_PLR_GL_BALANCES  GL_BALANCES_REALGM_CURR,
  SUBGL_LINK_PLR_GL_PERIODS  GL_PERIODS_REALGM_CURR,
  SUBGL_LINK_PLR_GL_CODE_COMB,
  SUBGL_LINK_PLR_FND_FLEX_VALUES,
  SUBGL_LINK_PLR_FND_FX_VAL_TL,
  SUBGL_LINK_PLR_FND_FLEX_VALUES  FND_FLEX_VALUES_CONTEXT,
  SUBGL_LINK_PLR_FND_FX_VAL_TL  FND_FX_VAL_TL_CONTEXT,


  PLR.PNL_BOOK_SDS_MAPPING,



  PLR.PORTFOLIO



WHERE



  ( SUBGL_LINK_PLR_GL_CODE_COMB.SEGMENT3=PLR.PORTFOLIO.ORACLE_SEGMENT_CD

 )


  AND  ( SUBGL_LINK_PLR_FND_FX_VAL_TL.FLEX_VALUE_ID=SUBGL_LINK_PLR_FND_FLEX_VALUES.FLEX_VALUE_ID

 AND SUBGL_LINK_PLR_FND_FLEX_VALUES.FLEX_VALUE_SET_ID = 1006795

  )


  AND  ( SUBGL_LINK_PLR_FND_FLEX_VALUES.FLEX_VALUE=SUBGL_LINK_PLR_GL_CODE_COMB.SEGMENT14

 )


  AND  ( GL_BALANCES_REALGM_CURR.CODE_COMBINATION_ID=SUBGL_LINK_PLR_GL_CODE_COMB.CODE_COMBINATION_ID


AND SUBGL_LINK_PLR_GL_CODE_COMB.ENABLED_FLAG = 'Y'  AND  
GL_BALANCES_REALGM_CURR.SET_OF_BOOKS_ID = 1  AND
GL_BALANCES_REALGM_CURR.CURRENCY_CODE  = 'USD'  )


  AND  ( GL_PERIODS_REALGM_CURR.PERIOD_NAME=GL_BALANCES_REALGM_CURR.PERIOD_NAME

 )

  AND  ( FND_FX_VAL_TL_BOOKID.FLEX_VALUE_ID=FND_FLEX_VALUES_BOOKID.FLEX_VALUE_ID
AND FND_FLEX_VALUES_BOOKID.FLEX_VALUE_SET_ID = 1006793  )
  AND  ( FND_FLEX_VALUES_BOOKID.FLEX_VALUE=SUBGL_LINK_PLR_GL_CODE_COMB.SEGMENT12


 )


  AND  ( FND_FX_VAL_TL_CONTEXT.FLEX_VALUE_ID=FND_FLEX_VALUES_CONTEXT.FLEX_VALUE_ID

 AND FND_FLEX_VALUES_CONTEXT.FLEX_VALUE_SET_ID = 1006794  )

  AND  ( FND_FLEX_VALUES_CONTEXT.FLEX_VALUE=SUBGL_LINK_PLR_GL_CODE_COMB.SEGMENT13

 )


  AND  ( FND_FX_VAL_TL_BOOKID.DESCRIPTION=PLR.PNL_BOOK_SDS_MAPPING.SECDB_BOOK_ID

 )


  AND  ( GL_PERIODS_REALGM_CURR.START_DATE = '31-JAN-2004')  AND 

GL_PERIODS_REALGM_CURR.PERIOD_TYPE = '1'  AND

GL_PERIODS_REALGM_CURR.ADJUSTMENT_PERIOD_FLAG = 'N' AND 


( SUBGL_LINK_PLR_GL_CODE_COMB.SEGMENT2 )  = 'REALGM'   AND 
( SUBGL_LINK_PLR_FND_FX_VAL_TL.DESCRIPTION ) <> 'Prior Period' AND 
( FND_FX_VAL_TL_CONTEXT.DESCRIPTION ) = 'Trade'  AND 
( SUBGL_LINK_PLR_GL_CODE_COMB.SEGMENT1 ) IN
('027','028','033','101','118','120','123','124','125','127','128','130','000')


  AND  (



  ( ( PLR.PORTFOLIO.LONG_NAME ) IN ('Accrual','Mark to Market')  )
  )
Received on Tue Mar 02 2004 - 08:56:39 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US