Re: Newbie help with explain plans

From: Roli <Roli.Meier_at_gmx.ch>
Date: 4 Sep 2003 09:39:45 -0700
Message-ID: <fe53b0b7.0309040839.2484f21@posting.google.com>

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<1062602922.443565_at_yasure>...
> Roli wrote:
>
> >Hi there,
> >
> >I have a problem with a huge select statement which i run against a
> >Oracle 9i 9.2.0.3 DB.
> >
> >We have one instance in house with the same data and db version as on
> >client side. If I run the statement in house, the statement is rather
> >fast, if i run it at client side it is very slow.
> >
> >Enclosed i send the explain plans. Could possibly someone tell where I
> >could tune the oracle instance at client side....
> >
> >Thanks in advance.
> >
> >****************Slow Explain Plan
> >*****************************************************
> >
> >
> >Operation Object Name Rows Bytes Cost
> >
> >SELECT STATEMENT Hint=CHOOSE 593 1217
> > HASH JOIN SEMI 593 70 K 1217
> > MERGE JOIN 593 66 K 1206
> > MERGE JOIN 1 K 101 K 1121
> > SORT JOIN 20 K 589 K 305
> > TABLE ACCESS FULL OS_A 20 K 589 K 35
> > FILTER
> > SORT JOIN
> > TABLE ACCESS FULL OS_P 21 K 1 M 51
> > SORT JOIN 12 K 154 K 85
> > INDEX FAST FULL SCAN OA_PK 12 K 154 K 15
> > VIEW VW_NSO_1 497 2 K 2
> > CONNECT BY WITH FILTERING
> > NESTED LOOPS
> > INDEX UNIQUE SCAN O_PK 1 5 1
> > TABLE ACCESS BY USER ROWID OS_O
> > HASH JOIN
> > CONNECT BY PUMP
> > TABLE ACCESS FULL OS_O 497 3 K 2
> >
> >
> >
> >***********************Fast Explain
> >Plan*************************************
> >
> >Operation Object Name Rows Bytes Cost
> >SELECT STATEMENT Hint=CHOOSE 6 477
> > TABLE ACCESS BY INDEX ROWID OS_P 20 K 1 M 2
> > NESTED LOOPS 6 678 477
> > NESTED LOOPS 156 6 K 165
> > NESTED LOOPS 156 2 K 9
> > VIEW VW_NSO_1 5 30 2
> > SORT UNIQUE 5 40
> > CONNECT BY WITH FILTERING
> > NESTED LOOPS
> > INDEX UNIQUE SCAN OS_PK 1 5 1
> > TABLE ACCESS BY USER ROWID OS_O
> > NESTED LOOPS
> > BUFFER SORT 5 40
> > CONNECT BY PUMP
> > TABLE ACCESS BY INDEX ROWID 5 40 2
> > INDEX RANGE SCAN RO_FK 5 1
> > INDEX RANGE SCAN OS_A_PK 31 403 1
> > TABLE ACCESS BY INDEX ROWID OS_A 1 26 1
> > INDEX UNIQUE SCAN A_PK 1
> > INDEX RANGE SCAN R_A_FK 1 1
> >
> >
> Post the SQL statement and verify that the constraints and indexes are
> the same between the two instances.

Thanks for your input, i forgot to say that i checked the indizes, constrainst and nearly all init ora parameters. I know that the statement could be improved but i would be very happy if this statement would be running faster on clients DB.

Thanks

The statement is the following:

SELECT OS_PORTFOLIO.CREVENUEGRDTP_CODE as

"CREVENUEGRDTP_CODE",OS_PORTFOLIO.CIDXCODE as
"CIDXCODE",OS_PORTFOLIO.CDEFACC_CODE as
"CDEFACC_CODE",OS_PORTFOLIO.NINVESTORRSKTYP_ID as
"NINVESTORRSKTYP_ID",OS_PORTFOLIO.CRISKGRDTYPE_CODE as
"CRISKGRDTYPE_CODE",OS_PORTFOLIO.NCREDITTYPE as
"NCREDITTYPE",OS_PORTFOLIO.NPERFORMANCEPERC1 as
"NPERFORMANCEPERC1",OS_ADDRESS.CADDRESSTYPE_CODE as
"CADDRESSTYPE_CODE",OS_PORTFOLIO.CPOWEROFATTOR_CODE as
"CPOWEROFATTOR_CODE",OS_PORTFOLIO.CIDXCODE as
"STANDARDKEY",OS_PORTFOLIO.NMORNINGMARGIN as
"NMORNINGMARGIN",OS_PORTFOLIO.NPORTFOLIOTYPE as
"TYPE",OS_PORTFOLIO.CADDRESS_CODE as
"CADDRESS_CODE",OS_PORTFOLIO.CQUICKACCESSKEY as
"CQUICKACCESSKEY",OS_PORTFOLIO.CBLOCKTYPE_CODE as
"CBLOCKTYPE_CODE",OS_PORTFOLIO.CADDINFO3 as
"CADDINFO3",OS_PORTFOLIO.CADDINFO2 as
"CADDINFO2",OS_PORTFOLIO.CADDINFO1

as "CADDINFO1",OS_PORTFOLIO.CCODE as
"CCODE",OS_PORTFOLIO.CCURRENCY_CODE
as "CCURRENCY_CODE",OS_PORTFOLIO.NCREDITLIMIT as

"NCREDITLIMIT",OS_PORTFOLIO.CIDENTIFICATION as
"CIDENTIFICATION",OS_PORTFOLIO.CCONDITION as
"CCONDITION",OS_ADDRESS.CSHORTDESCR as
"CSHORTDESCR",OS_PORTFOLIO.CDEFACCCCY_CODE as
"CDEFACCCCY_CODE",MM_ORG_ADDRESS.CORGANISATION_CODE as
"CORGANISATION_CODE"

FROM OS_Portfolio, OS_Address, MM_Org_Address WHERE OS_Portfolio.cAddress_Code = OS_Address.cCode

        AND MM_Org_Address.cAddress_Code = OS_Address.cCode
        AND MM_Org_address.cOrganisation_Code
        IN (select ccode from os_organisation start with ccode in
('-.')
         connect by cparentorg_code = prior ccode )
              AND MM_Org_Address.nOrgRelation_ID = 0
              AND ((SUBSTR(OS_ADDRESS.CADDRESSTYPE_CODE,1,1) IN
('4','5','7') OR OS_ADDRESS.CADDRESSTYPE_CODE NOT IN ('MP')))
              AND ((OS_PORTFOLIO.CIDXCODE LIKE 'B%') OR

(OS_ADDRESS.CSHORTDESCR LIKE 'B%')); Received on Thu Sep 04 2003 - 11:39:45 CDT