Re: Optimization help
From: Sashi <smalladi_at_gmail.com>
Date: Mon, 21 Sep 2009 07:09:57 -0700 (PDT)
Message-ID: <faaffd4d-cb89-47ec-ab13-c9ccb8cd622a_at_v2g2000vbb.googlegroups.com>
> What is the purpose of "ltrim(a.TRUNK, '0') = trunk"? Is a.TRUNK a
> NUMBER or VARCHAR2 or CHAR? What about MAPPING_TABLE.TRUNK?
A.TRUNK sometimes has leading zeroes that need to be dropped. They're both varchar.
> Making a guess here, if you add an index to the MAPPING_TABLE on the
> columns: TRUNK, HOST, and VENDOR (with VENDOR last in the composite
> index), does performance improve?
I will try it out.
> Are you able to use DBMS_XPLAN to display the current plan for this
> DML statement from the library cache (you need to find the SQL_ID and
> CHILD_NUMBER by searching V$SQL)?
Date: Mon, 21 Sep 2009 07:09:57 -0700 (PDT)
Message-ID: <faaffd4d-cb89-47ec-ab13-c9ccb8cd622a_at_v2g2000vbb.googlegroups.com>
> What is the purpose of "ltrim(a.TRUNK, '0') = trunk"? Is a.TRUNK a
> NUMBER or VARCHAR2 or CHAR? What about MAPPING_TABLE.TRUNK?
A.TRUNK sometimes has leading zeroes that need to be dropped. They're both varchar.
> Making a guess here, if you add an index to the MAPPING_TABLE on the
> columns: TRUNK, HOST, and VENDOR (with VENDOR last in the composite
> index), does performance improve?
I will try it out.
> Are you able to use DBMS_XPLAN to display the current plan for this
> DML statement from the library cache (you need to find the SQL_ID and
> CHILD_NUMBER by searching V$SQL)?
I have no idea what you're talking about so this will be a good
reading experience.
I've never done an explain plan before to try and analyze what the
server is doing so this should be interesting.
> What Oracle release version are you using (8.1.7.4, 10.2.0.1,
> 11.2.0.1, etc.)?
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
Thanks for your help.
Sashi
Received on Mon Sep 21 2009 - 09:09:57 CDT