How to use Views or Inline views ??

Can anyone please give me an idea how to optimize the below querry..Its working fine I just need to tune it...plz help

CREATE OR REPLACE PROCEDURE fmea_p_error_report( P_ECM_NAME IN FMEA_ECM.ECMNAME%TYPE,P_APPL_NAME IN FMEA_ECM.APPLICATION%TYPE,
P_CONFIG_ID IN NUMBER )
IS
BEGIN
DELETE fmea_report_error_table;
INSERT INTO fmea_report_error_table( CONNECTORNAME,PINNAME, PARAMETERNAME, COMPONENTNAME, COMPONENTVIEW , FAILUREMODE, SEVERITY, LIKELIHOOD, DVA, AFFECTEDERRORS, SYSTEMEFFECTTEXT )
( SELECT DISTINCT FMEA_Tables.connectorname,FMEA_Tables.pinname,FMEA _Tables.para_error ,FMEA_Tables.componentname,FMEA_Tables.componentvi ew,FMEA_Tables.failuremode,FMEA_Tables.severity,FM EA_Tables.likelihood,FMEA_Tables.dva, FMEA_Tables.AFFECTEDERRORS, FMEA_Tables.SYSTEMEFFECTTEXT SYSTEMEFFECTTEXTERR
FROM ( /*FMEA_Tables starts */
SELECT DISTINCT Param_and_errors.connectorname,Param_and_errors.pi nname,Param_and_errors.para_error,cmpnnt.component name,compv.componentview,fmode.failuremode,anadata .severity,anadata.likelihood,anadata.dva,aderraff. errorname AFFECTEDERRORS, aderraff.SYSTEMEFFECTTEXT
FROM FMEA.fmea_analyzedelement anaelmnt,
FMEA.fmea_componentversion compv,
FMEA.fmea_analysisdata anadata,
FMEA.fmea_failuremode fmode,
FMEA.fmea_component cmpnnt,
FMEA.fmea_analysisdataerraffected aderraff,( SELECT DISTINCT connectorname,PINNAME ,ERRORNAME AS PARA_ERROR,CONNECTORDETAILID /*Errortable starts*/
FROM ( SELECT TABLE1.connectorname,TABLE1.PINNAME,TABLE1.CONNECT ORDETAILID,PEAFF.ERRORNAME
FROM FMEA.FMEA_PINERRORAFFECTED PEAFF,( SELECT DISTINCT CON.connectorname,CONDTL.PINNAME,CONDTL.CONNECTORD ETAILID
FROM FMEA.FMEA_ECM ECM,FMEA.FMEA_ECMVERSION ECMV,
fmea.FMEA_CONNECTORS CON,FMEA.FMEA_CONNECTORDETAIL CONDTL
WHERE ECM.ECMNAME = P_ECM_NAME
AND ECM.APPLICATION = P_APPL_NAME
AND ECM.ECMID = ECMV.ECMID
AND ECMV.ECMVERSIONID = CON.ECMVERSIONID
AND CON.CONNECTORID = CONDTL.CONNECTORID ) TABLE1
WHERE TABLE1.CONNECTORDETAILID = PEAFF.CONNECTORDETAILID )Errortable /*Errortable ends*/

UNION ALL
SELECT connectorname,PINNAME ,PARAMETERNAME,CONNECTORDETAILID /*Parametertable starts*/
FROM ( SELECT TABLE1.connectorname, TABLE1.PINNAME,TABLE1.CONNECTORDETAILID ,PPAFF.PARAMETERNAME
FROM FMEA.FMEA_PINPARAMETERAFFECTED PPAFF,( SELECT DISTINCT CON.connectorname,CONDTL.PINNAME,CONDTL.CONNECTORD ETAILID
FROM FMEA.FMEA_ECM ECM,FMEA.FMEA_ECMVERSION ECMV,
FMEA.FMEA_CONNECTORS CON,FMEA.FMEA_CONNECTORDETAIL CONDTL
WHERE ECM.ECMNAME = P_ECM_NAME
AND ECM.APPLICATION = P_APPL_NAME
AND ECM.ECMID = ECMV.ECMID
AND ECMV.ECMVERSIONID = CON.ECMVERSIONID
AND CON.CONNECTORID = CONDTL.CONNECTORID ) TABLE1
WHERE TABLE1.CONNECTORDETAILID = PPAFF.CONNECTORDETAILID ) parametertable /*parametertable ends*/
) Param_and_errors
WHERE Param_and_errors.para_error = anaelmnt.elementname
AND anaelmnt.componentversionid = compv.componentversionid
AND compv.componentid = cmpnnt.componentid
AND anaelmnt.analyzedelementid = anadata.elementid
AND anadata.failuremodeid = fmode.failuremodeid
AND anadata.elementid= anaelmnt.analyzedelementid(+)
AND aderraff.analysisdataid(+)= anadata.analysisdataid ) FMEA_Tables , /*FMEA_Tables ends */
( SELECT c.CompName,cv.CompViewName /*C2ST_Tables table starts*/
FROM core2.C2_Component c, core2.C2_ComponentView cv,
core2.C2_MCompViewBuildConfig cvbc
WHERE c.CompId = cv.CompId
AND cv.CompId = cvbc.CompId
AND cv.CompVId = cvbc.CompVId
AND cvbc.ConfigId = P_CONFIG_ID )C2ST_Tables /*C2ST_Tables table ends */
WHERE FMEA_Tables.componentname = C2ST_Tables.compname
AND FMEA_Tables.componentview = C2ST_Tables.compviewname
) ;

END fmea_p_error_Report;
/

I'm not a DBA and I didn't sleep in a Holiday Inn. <grin>

So the only thing I'm sure of is in your where clauses I would do the various 'ID' comparisons first since numerical comparisons are faster than string comparisons.

If it was me I would create views since they get compiled and are generally faster.

I also thought Joins were faster than using Where clauses, but I may be wrong.

Hopefully this of some help and hopefully someone else answers who's an expert on query tuning!