sql query tuning - Oracle Database

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_ind a1
WHERE a1.emplid = a.emplid
AND a1.effdt= (
SELECT MAX(a2.effdt)
FROM ps_pers_data_ind a2
WHERE a2.emplid = a1.emplid)) AS caste, (
SELECT RELIGION_CD
FROM PS_DIVERS_RELIGION a2
WHERE a2.emplid = a.emplid
AND a2.REG_REGION = a.REG_REGION) AS religion ,D.XLATLONGNAME, a.EMPL_STATUS ,a.LAST_DATE_WORKED,b.DIVISION ,c.DESCR ,e.name_prefix ,e.first_name ,e.middle_name ,e.last_name ,(
SELECT a1.ADDRESS1||a1.ADDRESS2||a1.ADDRESS3||a1.ADDRESS4 ||a1.POSTAL||' , '||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.ADDRESS3||a2.ADDRESS4 ||a2.POSTAL||' , '||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_ADDRESS ,(
SELECT a4.email_addr
FROM ps_email_addresses a4
WHERE a4.emplid(+) = a.emplid
AND a4.e_addr_type = 'HOME') AS PERSONAL_EMAIL ,(
SELECT a3.email_addr
FROM ps_email_addresses a3
WHERE a3.emplid(+) = a.emplid
AND a3.e_addr_type = 'BUSN') AS BUSINESS_EMAIL ,(
SELECT a5.phone
FROM PS_PERSONAL_PHONE a5
WHERE a5.emplid(+) = a.emplid
AND a5.phone_type = 'HOME') AS HOME_PHONE ,(
SELECT a6.phone
FROM PS_PERSONAL_PHONE a6
WHERE a6.emplid(+) = a.emplid
AND a6.phone_type = 'CELL') CELL_PHONE ,(
SELECT a7.extension
FROM PS_PERSONAL_PHONE a7
WHERE a7.emplid(+) = a.emplid
AND PREF_PHONE_FLAG = 'Y' ) AS EXTENSION , x.NATIONAL_ID_TYPE ,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_id,a.reports_to,a.position_nbr,a.TER MINATION_DT, 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_reason = b.action_reason
AND b1.eff_status = 'A')), a.jobcode,a.estabid, 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_data 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_LOCATION = c.setid
AND a.ESTABID = w.ESTABID(+)
AND a.BUSINESS_UNIT = v.BUSINESS_UNIT
AND b.DIVISION = u.DIVISION(+)
AND d.FIELDNAME = 'HIGHEST_EDUC_LVL'
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="SELECT STATEMENT" optimizer="ALL_ROWS" cost="641" cardinality="1" bytes="395"
- object_ID="0" id="1" operation="NESTED LOOPS" cost="3" cardinality="1" bytes="49"
-
- object_ID="1" id="2" operation="VIEW" object_owner="SYS" object_name="VW_SQ_1" object_type="VIEW" 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="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_JOBCODE_TBL" object_type="TABLE" object_instance="2" cost="2" cardinality="1" bytes="17"
-
object_ID="3" id="5" operation="INDEX" option="SKIP SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS1JOBCODE_TBL" object_type="INDEX" search_columns="1" cost="1" cardinality="1" /
object_ID="4" id="6" operation="INDEX" option="FULL SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS0JOBCODE_TBL" object_type="INDEX" search_columns="2" cost="1" cardinality="1" bytes="35" /
- object_ID="0" id="7" operation="NESTED LOOPS" cost="2" cardinality="1" bytes="34"
- object_ID="5" id="8" operation="VIEW" object_owner="SYS" object_name="VW_SQ_2" object_type="VIEW" 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="FIRST ROW" cost="1" cardinality="1" bytes="15"
-
object_ID="6" id="11" operation="INDEX" option="RANGE SCAN (MIN/MAX)" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PERS_DATA_IND" object_type="INDEX (UNIQUE)" search_columns="1" cost="1" cardinality="1" bytes="15" /
- object_ID="6" id="12" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PERS_DATA_IND" object_type="TABLE" object_instance="3" cost="1" cardinality="1" bytes="18"
object_ID="6" id="13" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PERS_DATA_IND" object_type="INDEX (UNIQUE)" search_columns="2" cost="0" cardinality="1" /

- object_ID="7" id="14" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_DIVERS_RELIGION" object_type="TABLE" object_instance="5" cost="1" cardinality="1" bytes="15"
object_ID="7" id="15" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_DIVERS_RELIGION" object_type="INDEX (UNIQUE)" search_columns="2" cost="0" cardinality="1" /
- object_ID="0" id="16" operation="NESTED LOOPS" cost="3" cardinality="1" bytes="121"
- - object_ID="8" id="17" operation="VIEW" object_owner="SYS" object_name="VW_SQ_3" object_type="VIEW" 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="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ADDRESSES" object_type="INDEX (UNIQUE)" search_columns="2" cost="2" cardinality="1" bytes="15" /
- object_ID="9" id="20" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ADDRESSES" object_type="TABLE" object_instance="6" cost="1" cardinality="1" bytes="105"
-
object_ID="9" id="21" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ADDRESSES" object_type="INDEX (UNIQUE)" search_columns="3" cost="0" cardinality="1" /
- object_ID="0" id="22" operation="NESTED LOOPS" cost="3" cardinality="1" bytes="121"
- object_ID="10" id="23" operation="VIEW" object_owner="SYS" object_name="VW_SQ_4" object_type="VIEW" 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="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ADDRESSES" object_type="INDEX (UNIQUE)" search_columns="2" cost="2" cardinality="1" bytes="15" /
- object_ID="9" id="26" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ADDRESSES" object_type="TABLE" object_instance="8" cost="1" cardinality="1" bytes="105"
object_ID="9" id="27" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ADDRESSES" object_type="INDEX (UNIQUE)" search_columns="3" cost="0" cardinality="1" /
- object_ID="11" id="28" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_EMAIL_ADDRESSES" object_type="TABLE" object_instance="10" cost="2" cardinality="1" bytes="38"
object_ID="11" id="29" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_EMAIL_ADDRESSES" object_type="INDEX (UNIQUE)" search_columns="2" cost="1" cardinality="1" /
- object_ID="11" id="30" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_EMAIL_ADDRESSES" object_type="TABLE" object_instance="11" cost="2" cardinality="1" bytes="38"
object_ID="11" id="31" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_EMAIL_ADDRESSES" object_type="INDEX (UNIQUE)" search_columns="2" cost="1" cardinality="1" /
- object_ID="12" id="32" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PERSONAL_PHONE" object_type="TABLE" object_instance="12" cost="2" cardinality="1" bytes="22"
object_ID="12" id="33" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PERSONAL_PHONE" object_type="INDEX (UNIQUE)" search_columns="2" cost="1" cardinality="1" /
- object_ID="12" id="34" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PERSONAL_PHONE" object_type="TABLE" object_instance="13" cost="2" cardinality="1" bytes="22"
object_ID="12" id="35" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PERSONAL_PHONE" object_type="INDEX (UNIQUE)" search_columns="2" cost="1" cardinality="1" /
- object_ID="12" id="36" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PERSONAL_PHONE" object_type="TABLE" object_instance="14" cost="3" cardinality="1" bytes="13"
object_ID="12" id="37" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PERSONAL_PHONE" object_type="INDEX (UNIQUE)" search_columns="1" cost="2" cardinality="1" /
- object_ID="0" id="38" operation="FILTER"
- object_ID="13" id="39" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_DEPT_TBL" object_type="TABLE" object_instance="15" cost="3" cardinality="1" bytes="44"
object_ID="13" id="40" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_DEPT_TBL" object_type="INDEX (UNIQUE)" search_columns="2" cost="2" cardinality="1" /
- object_ID="0" id="41" operation="SORT" option="AGGREGATE" cardinality="1" bytes="18"
- object_ID="0" id="42" operation="FILTER"
object_ID="14" id="43" operation="INDEX" option="SKIP SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS1DEPT_TBL" object_type="INDEX" search_columns="1" cost="3" cardinality="1" bytes="18" /
- object_ID="0" id="44" operation="NESTED LOOPS" cost="3" cardinality="1" bytes="43"
- object_ID="15" id="45" operation="VIEW" object_owner="SYS" object_name="VW_SQ_5" object_type="VIEW" 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="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ACTION_TBL" object_type="TABLE" object_instance="18" cost="2" cardinality="1" bytes="14"
-
object_ID="16" id="48" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ACTION_TBL" object_type="INDEX (UNIQUE)" search_columns="1" cost="1" cardinality="1" /
- object_ID="16" id="49" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ACTION_TBL" object_type="TABLE" object_instance="17" cost="1" cardinality="1" bytes="31"
object_ID="16" id="50" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ACTION_TBL" object_type="INDEX (UNIQUE)" search_columns="2" cost="0" cardinality="1" /
- object_ID="0" id="51" operation="NESTED LOOPS" cost="4" cardinality="1" bytes="48"
- object_ID="17" id="52" operation="VIEW" object_owner="SYS" object_name="VW_SQ_6" object_type="VIEW" 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="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ACTN_REASON_TBL" object_type="TABLE" object_instance="20" cost="3" cardinality="1" bytes="14" /
- object_ID="18" id="55" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ACTN_REASON_TBL" object_type="TABLE" object_instance="19" cost="1" cardinality="1" bytes="36"
object_ID="18" id="56" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_ACTN_REASON_TBL" object_type="INDEX (UNIQUE)" search_columns="3" cost="0" cardinality="1" /
- object_ID="0" id="57" operation="FILTER"
- object_ID="13" id="58" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_DEPT_TBL" object_type="TABLE" object_instance="21" cost="3" cardinality="1" bytes="36"
object_ID="13" id="59" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_DEPT_TBL" object_type="INDEX (UNIQUE)" search_columns="2" cost="2" cardinality="1" /
- object_ID="0" id="60" operation="SORT" option="AGGREGATE" cardinality="1" bytes="18"
- object_ID="0" id="61" operation="FILTER"
-
object_ID="14" id="62" operation="INDEX" option="SKIP SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS1DEPT_TBL" object_type="INDEX" 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="NESTED LOOPS" cost="639" cardinality="1" bytes="395"
-
- object_ID="0" id="65" operation="NESTED LOOPS" cost="638" cardinality="1" bytes="372"
-
- object_ID="0" id="66" operation="NESTED LOOPS" cost="636" cardinality="1" bytes="354"
-
- object_ID="0" id="67" operation="NESTED 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="NESTED 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="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_JOB" object_type="TABLE" object_instance="23" cost="3" cardinality="3" bytes="342"
-
- object_ID="0" id="73" operation="NESTED 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="NESTED LOOPS" cost="3" cardinality="1" bytes="70"
-
- object_ID="20" id="76" operation="VIEW" object_owner="SYS" object_name="VW_SQ_9" object_type="VIEW" object_instance="235" cost="2" cardinality="1" bytes="28"
-
- object_ID="0" id="77" operation="FILTER"
-
- object_ID="0" id="78" operation="HASH" option="GROUP BY" cost="2" cardinality="1" bytes="27"
-
object_ID="21" id="79" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PSXLATITEM" object_type="INDEX (UNIQUE)" search_columns="2" cost="2" cardinality="14" bytes="378" /
- object_ID="22" id="80" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PSXLATITEM" object_type="TABLE" object_instance="30" cost="1" cardinality="1" bytes="42"
-
object_ID="21" id="81" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PSXLATITEM" object_type="INDEX (UNIQUE)" search_columns="3" cost="0" cardinality="1" /
object_ID="23" id="82" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PERSONAL_DATA" object_type="TABLE" object_instance="31" cost="140" cardinality="10,968" bytes="416,784" /
</PlanElements
</PlanElement
object_ID="24" id="83" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PSAJOB" object_type="INDEX" search_columns="1" cost="1" cardinality="3" /
- object_ID="25" id="84" operation="VIEW" object_owner="SYS" object_name="VW_SQ_8" object_type="VIEW" object_instance="234" cost="268" cardinality="33,313" bytes="1,032,703"
-
- object_ID="0" id="85" operation="HASH" option="GROUP BY" cost="268" cardinality="33,313" bytes="699,573"
-
object_ID="19" id="86" operation="INDEX" option="FAST FULL SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_JOB" object_type="INDEX (UNIQUE)" cost="49" cardinality="33,313" bytes="699,573" /
- object_ID="26" id="87" operation="VIEW" object_owner="SYS" object_name="VW_SQ_7" object_type="VIEW" 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="INDEX" option="FAST FULL SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_JOB" object_type="INDEX (UNIQUE)" cost="49" cardinality="33,313" bytes="599,634" /

- object_ID="27" id="90" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_Z_PER_RATE" object_type="TABLE" object_instance="28" cost="2" cardinality="1" bytes="24"
-
object_ID="27" id="91" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_Z_PER_RATE" object_type="INDEX (UNIQUE)" search_columns="4" cost="1" cardinality="1" /
object_ID="28" id="92" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_Z_DIVISION" object_type="TABLE" object_instance="27" cost="3" cardinality="35" bytes="875" /

object_ID="29" id="93" operation="INDEX" option="FULL SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS0ESTAB_TBL" object_type="INDEX" search_columns="1" cost="1" cardinality="1" bytes="15" /
object_ID="30" id="94" operation="INDEX" option="FULL SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS0LOCATION_TBL" object_type="INDEX" search_columns="2" cost="1" cardinality="1" bytes="18" /
- object_ID="31" id="95" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PERS_NID" object_type="TABLE" object_instance="24" cost="2" cardinality="1" bytes="18"
object_ID="31" id="96" operation="INDEX" option="RANGE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_PERS_NID" object_type="INDEX (UNIQUE)" search_columns="1" cost="1" cardinality="1" /
- object_ID="32" id="97" operation="TABLE ACCESS" option="BY INDEX ROWID" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_BUS_UNIT_TBL_HR" object_type="TABLE" object_instance="26" cost="1" cardinality="1" bytes="23"
object_ID="32" id="98" operation="INDEX" option="UNIQUE SCAN" optimizer="ANALYZED" object_owner="SYSADM" object_name="PS_BUS_UNIT_TBL_HR" object_type="INDEX (UNIQUE)" search_columns="1" cost="0" cardinality="1" /

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

Apr 3 '08 #1

Subscribe Reply

3058

debasisdas

8,127

Recognized Expert Expert

Whom do you expect to read that code and tune it ?

Apr 3 '08 #2

Similar topics

4495

Single Complex Query Vs Temoprary Table

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...

Oracle Database

9271

Recursive Query

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...

Microsoft SQL Server

3396

Fine tuning this sql-query. help!

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...

Microsoft SQL Server

1885

bug in query planning?

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...

PostgreSQL Database

1978

Mysterious performance of query because of plsql function in where condition

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,...

PostgreSQL Database

2193

query tuning

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...

DB2 Database

2020

Tuning on Oracle 10g and SQL Server 2005

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...

Microsoft SQL Server

1705

Query Analyzer. What settings to see the data output again?

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...

Microsoft SQL Server

6213

A Roadmap To Query Tuning

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...

Oracle Database

7893

Self tuning bufferpool

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...

DB2 Database

7259

What is ONU?

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,...

General

7158

Changing the language in Windows 10

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...

Windows Server

7535

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

5085

Access Europe - Using VBA to create a class based on a table - Wed 1 May

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...

Microsoft Access / VBA

4745

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...

C# / C Sharp

3232

Trying to create a lan-to-lan vpn between two differents networks

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...

Networking - Hardware / Configuration

3221

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

798

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

455

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

General