473,597 Members | 2,437 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

sql query tuning

1 New Member
Hi All

I have the following query to be tuned..



SELECT distinct a.EMPLID
,a.PER_ORG
,a.HIRE_DT
,a.grade
,(
SELECT j.descr
FROM PS_JOBCODE_TBL j
WHERE j.effdt = (
SELECT MAX(effdt)
FROM PS_JOBCODE_TBL j1
WHERE j.JOBCODE = j1.JOBCODE
AND j1.EFF_STATUS = 'A')
AND a.JOBCODE = j.JOBCODE) AS Job_Title ,e.BIRTHDATE ,e.SEX ,e.mar_status ,(
SELECT gpin_caste
FROM ps_pers_data_in d a1
WHERE a1.emplid = a.emplid
AND a1.effdt= (
SELECT MAX(a2.effdt)
FROM ps_pers_data_in d a2
WHERE a2.emplid = a1.emplid)) AS caste, (
SELECT RELIGION_CD
FROM PS_DIVERS_RELIG ION a2
WHERE a2.emplid = a.emplid
AND a2.REG_REGION = a.REG_REGION) AS religion ,D.XLATLONGNAME , a.EMPL_STATUS ,a.LAST_DATE_WO RKED,b.DIVISION ,c.DESCR ,e.name_prefix ,e.first_name ,e.middle_name ,e.last_name ,(
SELECT a1.ADDRESS1||a1 .ADDRESS2||a1.A DDRESS3||a1.ADD RESS4||a1.POSTA L||' , '||a1.city||' , '||a1.COUNTRY
FROM ps_addresses a1
WHERE a1.EFFDT = (
SELECT MAX(y.EFFDT)
FROM PS_ADDRESSES y
WHERE a1.EMPLID = y.EMPLID
AND y.EFFDT <=SYSDATE)
AND a.emplid = a1.emplid(+)
AND a1.ADDRESS_TYPE = 'PRES') AS PRESENT_ADDRESS ,(
SELECT a2.ADDRESS1||a2 .ADDRESS2||a2.A DDRESS3||a2.ADD RESS4||a2.POSTA L||' , '||a2.city||' , '||a2.COUNTRY
FROM ps_addresses a2
WHERE a2.EFFDT = (
SELECT MAX(y.EFFDT)
FROM PS_ADDRESSES y
WHERE a2.EMPLID = y.EMPLID
AND y.EFFDT <=SYSDATE)
AND a.emplid = a2.emplid(+)
AND a2.ADDRESS_TYPE = 'PRNT') AS PERMANENT_ADDRE SS ,(
SELECT a4.email_addr
FROM ps_email_addres ses a4
WHERE a4.emplid(+) = a.emplid
AND a4.e_addr_type = 'HOME') AS PERSONAL_EMAIL ,(
SELECT a3.email_addr
FROM ps_email_addres ses a3
WHERE a3.emplid(+) = a.emplid
AND a3.e_addr_type = 'BUSN') AS BUSINESS_EMAIL ,(
SELECT a5.phone
FROM PS_PERSONAL_PHO NE a5
WHERE a5.emplid(+) = a.emplid
AND a5.phone_type = 'HOME') AS HOME_PHONE ,(
SELECT a6.phone
FROM PS_PERSONAL_PHO NE a6
WHERE a6.emplid(+) = a.emplid
AND a6.phone_type = 'CELL') CELL_PHONE ,(
SELECT a7.extension
FROM PS_PERSONAL_PHO NE a7
WHERE a7.emplid(+) = a.emplid
AND PREF_PHONE_FLAG = 'Y' ) AS EXTENSION , x.NATIONAL_ID_T YPE ,x.NATIONAL_ID ,a.DEPTID ,(
SELECT y.descr
FROM PS_DEPT_TBL y
WHERE y.deptid = a.deptid
AND y.setid = a.setid_dept
AND y.effdt = (
SELECT MAX(effdt)
FROM PS_DEPT_TBL y1
WHERE y.DEPTID = y1.deptid
AND y.eff_status = 'A')) , w.descr, v.DESCR, u.DESCR ,a.supervisor_i d,a.reports_to, a.position_nbr, a.TERMINATION_D T, a.ACTION, a.action_dt, (
SELECT a1.ACTION_DESCR
FROM PS_ACTION_TBL a1
WHERE a.ACTION = a1.ACTION
AND a1.EFFDT = (
SELECT MAX(effdt)
FROM PS_ACTION_TBL a2
WHERE a1.ACTION = a2.ACTION
AND a2.EFF_STATUS = 'A')), a.ACTION_REASON ,(
SELECT b.descr
FROM PS_ACTN_REASON_ TBL b
WHERE a.action = b.action
AND a.action_reason = b.action_reason
AND b.effdt = (
SELECT MAX(effdt)
FROM PS_ACTN_REASON_ TBL b1
WHERE b1.action_reaso n = b.action_reason
AND b1.eff_status = 'A')), a.jobcode,a.est abid, a.business_unit , a.effdt ,(
SELECT y.descrshort
FROM PS_DEPT_TBL y
WHERE y.deptid = a.deptid
AND y.setid = a.setid_dept
AND y.effdt = (
SELECT MAX(effdt)
FROM PS_DEPT_TBL y1
WHERE y.DEPTID = y1.deptid
AND y.eff_status = 'A'))
FROM PS_JOB a, PS_PERS_NID x, PS_ESTAB_TBL w, PS_BUS_UNIT_TBL _HR v, PS_Z_DIVISION u, ps_z_per_rate b , ps_location_tbl c, psxlatitem D, ps_personal_dat a e
WHERE a.EFFDT = (
SELECT MAX(y.EFFDT)
FROM PS_JOB y
WHERE a.EMPLID = y.EMPLID
AND a.EMPL_RCD = y.EMPL_RCD
AND y.EFFDT <=SYSDATE)
AND a.EFFSEQ = (
SELECT MAX(z.EFFSEQ)
FROM PS_JOB z
WHERE a.EMPLID = z.EMPLID
AND a.EMPL_RCD = z.EMPL_RCD
AND a.EFFDT = z.EFFDT)
AND a.EMPLID = x.emplid
AND a.emplid = b.emplid(+)
AND a.effdt = b.effdt(+)
AND a.effseq = b.effseq(+)
AND a.empl_rcd = b.empl_rcd(+)
AND a.LOCATION = c.LOCATION
AND a.SETID_LOCATIO N = c.setid
AND a.ESTABID = w.ESTABID(+)
AND a.BUSINESS_UNIT = v.BUSINESS_UNIT
AND b.DIVISION = u.DIVISION(+)
AND d.FIELDNAME = 'HIGHEST_EDUC_L VL'
AND d.FIELDVALUE = e.HIGHEST_EDUC_ LVL
AND d.EFFDT = (
SELECT MAX(EFFDT)
FROM PSXLATITEM d1
WHERE d1.FIELDNAME = d.FIELDNAME
AND d1.fieldvalue = d.fieldvalue
AND d1.EFFDT <= SYSDATE)
AND a.emplid = e.emplid
AND x.PRIMARY_NID = 'Y'
order by a.emplid
_______________ _______________ _______________ _______________ _________

with the plan as

*************** *************** *************** **************

- object_ID="0" id="0" operation="SELE CT STATEMENT" optimizer="ALL_ ROWS" cost="641" cardinality="1" bytes="395"
- object_ID="0" id="1" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="49"
-
- object_ID="1" id="2" operation="VIEW " object_owner="S YS" object_name="VW _SQ_1" object_type="VI EW" object_instance ="227" cost="2" cardinality="1" bytes="14"
-
- object_ID="0" id="3" operation="SORT " option="GROUP BY" cost="2" cardinality="1" bytes="17"
-
- object_ID="2" id="4" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _JOBCODE_TBL" object_type="TA BLE" object_instance ="2" cost="2" cardinality="1" bytes="17"
-
object_ID="3" id="5" operation="INDE X" option="SKIP SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 1JOBCODE_TBL" object_type="IN DEX" search_columns= "1" cost="1" cardinality="1" /
object_ID="4" id="6" operation="INDE X" option="FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 0JOBCODE_TBL" object_type="IN DEX" search_columns= "2" cost="1" cardinality="1" bytes="35" /
- object_ID="0" id="7" operation="NEST ED LOOPS" cost="2" cardinality="1" bytes="34"
- object_ID="5" id="8" operation="VIEW " object_owner="S YS" object_name="VW _SQ_2" object_type="VI EW" object_instance ="228" cost="1" cardinality="1" bytes="16"
-
- object_ID="0" id="9" operation="SORT " option="GROUP BY" cost="1" cardinality="1" bytes="15"
-
- object_ID="0" id="10" operation="FIRS T ROW" cost="1" cardinality="1" bytes="15"
-
object_ID="6" id="11" operation="INDE X" option="RANGE SCAN (MIN/MAX)" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_DATA_IND" object_type="IN DEX (UNIQUE)" search_columns= "1" cost="1" cardinality="1" bytes="15" /
- object_ID="6" id="12" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_DATA_IND" object_type="TA BLE" object_instance ="3" cost="1" cardinality="1" bytes="18"
object_ID="6" id="13" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_DATA_IND" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="0" cardinality="1" /


- object_ID="7" id="14" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DIVERS_RELIGIO N" object_type="TA BLE" object_instance ="5" cost="1" cardinality="1" bytes="15"
object_ID="7" id="15" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DIVERS_RELIGIO N" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="0" cardinality="1" /
- object_ID="0" id="16" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="121"
- - object_ID="8" id="17" operation="VIEW " object_owner="S YS" object_name="VW _SQ_3" object_type="VI EW" object_instance ="229" cost="2" cardinality="1" bytes="16"
- - object_ID="0" id="18" operation="SORT " option="GROUP BY" cost="2" cardinality="1" bytes="15"
- object_ID="9" id="19" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="1" bytes="15" /
- object_ID="9" id="20" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="TA BLE" object_instance ="6" cost="1" cardinality="1" bytes="105"
-
object_ID="9" id="21" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="IN DEX (UNIQUE)" search_columns= "3" cost="0" cardinality="1" /
- object_ID="0" id="22" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="121"
- object_ID="10" id="23" operation="VIEW " object_owner="S YS" object_name="VW _SQ_4" object_type="VI EW" object_instance ="230" cost="2" cardinality="1" bytes="16"
- object_ID="0" id="24" operation="SORT " option="GROUP BY" cost="2" cardinality="1" bytes="15"
- object_ID="9" id="25" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="1" bytes="15" /
- object_ID="9" id="26" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="TA BLE" object_instance ="8" cost="1" cardinality="1" bytes="105"
object_ID="9" id="27" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="IN DEX (UNIQUE)" search_columns= "3" cost="0" cardinality="1" /
- object_ID="11" id="28" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _EMAIL_ADDRESSE S" object_type="TA BLE" object_instance ="10" cost="2" cardinality="1" bytes="38"
object_ID="11" id="29" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _EMAIL_ADDRESSE S" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="1" cardinality="1" /
- object_ID="11" id="30" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _EMAIL_ADDRESSE S" object_type="TA BLE" object_instance ="11" cost="2" cardinality="1" bytes="38"
object_ID="11" id="31" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _EMAIL_ADDRESSE S" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="1" cardinality="1" /
- object_ID="12" id="32" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="TA BLE" object_instance ="12" cost="2" cardinality="1" bytes="22"
object_ID="12" id="33" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="IN DEX (UNIQUE)" search_columns= "2" cost="1" cardinality="1" /
- object_ID="12" id="34" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="TA BLE" object_instance ="13" cost="2" cardinality="1" bytes="22"
object_ID="12" id="35" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="IN DEX (UNIQUE)" search_columns= "2" cost="1" cardinality="1" /
- object_ID="12" id="36" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="TA BLE" object_instance ="14" cost="3" cardinality="1" bytes="13"
object_ID="12" id="37" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="IN DEX (UNIQUE)" search_columns= "1" cost="2" cardinality="1" /
- object_ID="0" id="38" operation="FILT ER"
- object_ID="13" id="39" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DEPT_TBL" object_type="TA BLE" object_instance ="15" cost="3" cardinality="1" bytes="44"
object_ID="13" id="40" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DEPT_TBL" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="1" /
- object_ID="0" id="41" operation="SORT " option="AGGREGA TE" cardinality="1" bytes="18"
- object_ID="0" id="42" operation="FILT ER"
object_ID="14" id="43" operation="INDE X" option="SKIP SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 1DEPT_TBL" object_type="IN DEX" search_columns= "1" cost="3" cardinality="1" bytes="18" /
- object_ID="0" id="44" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="43"
- object_ID="15" id="45" operation="VIEW " object_owner="S YS" object_name="VW _SQ_5" object_type="VI EW" object_instance ="231" cost="2" cardinality="1" bytes="12"
- object_ID="0" id="46" operation="SORT " option="GROUP BY" cost="2" cardinality="1" bytes="14"
- object_ID="16" id="47" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTION_TBL" object_type="TA BLE" object_instance ="18" cost="2" cardinality="1" bytes="14"
-
object_ID="16" id="48" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTION_TBL" object_type="IN DEX (UNIQUE)" search_columns= "1" cost="1" cardinality="1" /
- object_ID="16" id="49" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTION_TBL" object_type="TA BLE" object_instance ="17" cost="1" cardinality="1" bytes="31"
object_ID="16" id="50" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTION_TBL" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="0" cardinality="1" /
- object_ID="0" id="51" operation="NEST ED LOOPS" cost="4" cardinality="1" bytes="48"
- object_ID="17" id="52" operation="VIEW " object_owner="S YS" object_name="VW _SQ_6" object_type="VI EW" object_instance ="232" cost="3" cardinality="1" bytes="12"
- object_ID="0" id="53" operation="SORT " option="GROUP BY" cost="3" cardinality="1" bytes="14"
object_ID="18" id="54" operation="TABL E ACCESS" option="FULL" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTN_REASON_TB L" object_type="TA BLE" object_instance ="20" cost="3" cardinality="1" bytes="14" /
- object_ID="18" id="55" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTN_REASON_TB L" object_type="TA BLE" object_instance ="19" cost="1" cardinality="1" bytes="36"
object_ID="18" id="56" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTN_REASON_TB L" object_type="IN DEX (UNIQUE)" search_columns= "3" cost="0" cardinality="1" /
- object_ID="0" id="57" operation="FILT ER"
- object_ID="13" id="58" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DEPT_TBL" object_type="TA BLE" object_instance ="21" cost="3" cardinality="1" bytes="36"
object_ID="13" id="59" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DEPT_TBL" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="1" /
- object_ID="0" id="60" operation="SORT " option="AGGREGA TE" cardinality="1" bytes="18"
- object_ID="0" id="61" operation="FILT ER"
-
object_ID="14" id="62" operation="INDE X" option="SKIP SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 1DEPT_TBL" object_type="IN DEX" search_columns= "1" cost="3" cardinality="1" bytes="18" /
- object_ID="0" id="63" operation="SORT " option="UNIQUE" cost="640" cardinality="1" bytes="395"
-
- object_ID="0" id="64" operation="NEST ED LOOPS" cost="639" cardinality="1" bytes="395"
-
- object_ID="0" id="65" operation="NEST ED LOOPS" cost="638" cardinality="1" bytes="372"
-
- object_ID="0" id="66" operation="NEST ED LOOPS" cost="636" cardinality="1" bytes="354"
-
- object_ID="0" id="67" operation="NEST ED LOOPS" option="OUTER" cost="635" cardinality="1" bytes="336"
-
- object_ID="0" id="68" operation="HASH JOIN" option="OUTER" cost="634" cardinality="1" bytes="321"
-
- object_ID="0" id="69" operation="NEST ED LOOPS" option="OUTER" cost="630" cardinality="1" bytes="296"
-
- object_ID="0" id="70" operation="HASH JOIN" cost="628" cardinality="1" bytes="272"
-
- object_ID="0" id="71" operation="HASH JOIN" cost="416" cardinality="1" bytes="253"
- object_ID="19" id="72" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _JOB" object_type="TA BLE" object_instance ="23" cost="3" cardinality="3" bytes="342"
-
- object_ID="0" id="73" operation="NEST ED LOOPS" cost="147" cardinality="1" bytes="222"
-
- object_ID="0" id="74" operation="HASH JOIN" cost="144" cardinality="1" bytes="108"
-
- object_ID="0" id="75" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="70"
-
- object_ID="20" id="76" operation="VIEW " object_owner="S YS" object_name="VW _SQ_9" object_type="VI EW" object_instance ="235" cost="2" cardinality="1" bytes="28"
-
- object_ID="0" id="77" operation="FILT ER"
-
- object_ID="0" id="78" operation="HASH " option="GROUP BY" cost="2" cardinality="1" bytes="27"
-
object_ID="21" id="79" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PSXLATITEM" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="14 " bytes="378" /
- object_ID="22" id="80" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS XLATITEM" object_type="TA BLE" object_instance ="30" cost="1" cardinality="1" bytes="42"
-
object_ID="21" id="81" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PSXLATITEM" object_type="IN DEX (UNIQUE)" search_columns= "3" cost="0" cardinality="1" /
object_ID="23" id="82" operation="TABL E ACCESS" option="FULL" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_DATA" object_type="TA BLE" object_instance ="31" cost="140" cardinality="10 ,968" bytes="416,784" /
</PlanElements
</PlanElement
object_ID="24" id="83" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS AJOB" object_type="IN DEX" search_columns= "1" cost="1" cardinality="3" /
- object_ID="25" id="84" operation="VIEW " object_owner="S YS" object_name="VW _SQ_8" object_type="VI EW" object_instance ="234" cost="268" cardinality="33 ,313" bytes="1,032,70 3"
-
- object_ID="0" id="85" operation="HASH " option="GROUP BY" cost="268" cardinality="33 ,313" bytes="699,573"
-
object_ID="19" id="86" operation="INDE X" option="FAST FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _JOB" object_type="IN DEX (UNIQUE)" cost="49" cardinality="33 ,313" bytes="699,573" /
- object_ID="26" id="87" operation="VIEW " object_owner="S YS" object_name="VW _SQ_7" object_type="VI EW" object_instance ="233" cost="211" cardinality="24 ,243" bytes="460,617"
-
- object_ID="0" id="88" operation="HASH " option="GROUP BY" cost="211" cardinality="24 ,243" bytes="436,374"
-
object_ID="19" id="89" operation="INDE X" option="FAST FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _JOB" object_type="IN DEX (UNIQUE)" cost="49" cardinality="33 ,313" bytes="599,634" /

- object_ID="27" id="90" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _Z_PER_RATE" object_type="TA BLE" object_instance ="28" cost="2" cardinality="1" bytes="24"
-
object_ID="27" id="91" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _Z_PER_RATE" object_type="IN DEX (UNIQUE)" search_columns= "4" cost="1" cardinality="1" /
object_ID="28" id="92" operation="TABL E ACCESS" option="FULL" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _Z_DIVISION" object_type="TA BLE" object_instance ="27" cost="3" cardinality="35 " bytes="875" /

object_ID="29" id="93" operation="INDE X" option="FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 0ESTAB_TBL" object_type="IN DEX" search_columns= "1" cost="1" cardinality="1" bytes="15" /
object_ID="30" id="94" operation="INDE X" option="FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 0LOCATION_TBL" object_type="IN DEX" search_columns= "2" cost="1" cardinality="1" bytes="18" /
- object_ID="31" id="95" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_NID" object_type="TA BLE" object_instance ="24" cost="2" cardinality="1" bytes="18"
object_ID="31" id="96" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_NID" object_type="IN DEX (UNIQUE)" search_columns= "1" cost="1" cardinality="1" /
- object_ID="32" id="97" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _BUS_UNIT_TBL_H R" object_type="TA BLE" object_instance ="26" cost="1" cardinality="1" bytes="23"
object_ID="32" id="98" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _BUS_UNIT_TBL_H R" object_type="IN DEX (UNIQUE)" search_columns= "1" cost="0" cardinality="1" /



*************** *************** **********
Apr 3 '08 #1
1 3072
debasisdas
8,127 Recognized Expert Expert
Whom do you expect to read that code and tune it ?
Apr 3 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

6
4506
by: Mahesh Hardikar | last post by:
Hi , Oracle 8.1.7.0.0 on HP-UX 11.0 I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL T-SQL background. We have a report which uses a select statement . This select statement joins 15 tables . SOme of the tables are outer joined. It runs much slow when parameters (From & To Date) are for a month.
14
9282
by: Bob | last post by:
Hi there, Need a little help with a certain query that's causing a lot of acid in my stomach... Have a table that stores sales measures for a given client. The sales measures are stored per year and there could be multiple sales measures every year per client. There is another field called last update date. If there are multiple sales measures then need to select the one that's been entered last based on this field. Also, if there
9
3400
by: majsen | last post by:
Hi, I have problem running this query. It will time out for me... My database are small just about 200 members. I have a site for swaping appartments (rental). my query should look for match in a triangle. Like this member A -> B->C A give his appartment to B. B gives his appartment to C and finally C gives his appartment to A Soo my query looks for matching parameters like rooms, location, size
6
1899
by: Steven D.Arnold | last post by:
I have a query which does not use column indexes that it should use. I have discovered some interesting behaviors of Postgres which may indicate a bug in the database's query planning. Take a look at the query below. There is a btree index on both m.account_id and a.account_id. Query (1) does not use the index on the messages table, instead opting for a full table scan, thus killing performance. The messages table can contain...
1
2000
by: Peter Alberer | last post by:
Hi there, i have a problem with a query that uses the result of a plsql function In the where clause: SELECT assignments.assignment_id, assignments.package_id AS package_id, assignments.title AS title, COUNT(*) AS Count
4
2202
by: Raj | last post by:
Hi all, I have couple of questions some one plzz help 1.I have a query which run for 2 hours on my production machine, it returns 1.5 millon rows, i looked at the explain plan it is picking up the indexes on big table. The query joins 5 tables ( 1 has 30 million records 4 other tables have less than 100000) i see the cost at each step is less than 1000 so i guess it shouldn't take that long to return the result set??? it does a sort...
3
2023
by: mleal | last post by:
Does anyone have some more detailed information about how Oracle and MS implement / allow Tuning on Oracle 10g and SQL Server 2005 and the differences between them? Which of them, In a deep comparison about it, allow better tuning and why. Regards, Marcio Evangelista
3
1716
by: dba_222 | last post by:
Dear experts, I hate to ask such a seemingly dumb question. But I have spent some time trying to solve this already to no avail. When I was using my query analyzer last, a few months ago, I was tuning some strange code. I changed some settings to see what was going on.
1
6222
by: Vinod Sadanandan | last post by:
A Roadmap To Query Tuning ============================ For each SQL statement, there are different approaches that could be used to retrieve the required data. Optimization is the process of choosing the most efficient way to retrieve this data based upon the evaluation of a number of different criteria. The CBO bases optimization choices on pre-gathered table and index statistics while the RBO makes it's decisions based on a set of ...
3
7901
by: dunleav1 | last post by:
In 9.1 and 9.5 (Linux 64 bit) when a buffer pool is set to self- tuning, how are blocks configured in respect to blocked vs non-blocked when self-tuning is set to on? (ie) I have one bufferpool that is 16k that the IBMDEFAULTBP is shared between all tablespaces. The data tablespace has prefetch automatic set to on.
0
7959
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7883
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8021
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6677
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5842
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
3876
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3917
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2393
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.