October 17th, 2005, 05:21 AM
|
 |
Daniel Schildsky
|
|
Join Date: Mar 2004
Location: KL, Malaysia.
|
|
|
No improvement after adding indices to the tables
Hi all,
Recently I have received complaints from my users that the queries involving a schema containing several tables are very slow. After inspecting the tables in the schema, I found out that there were no column being indexed except primary keys.
However, after indexing several columns in those tables, I have analysed the statistics using tkprof utility. The results were disappointing and varied from one run to another.
One of the queries I used is as follows:
Code:
CREATE OR REPLACE PROCEDURE "USP_CLD_ENG_CHK_BFD"
(
cd_in IN tbl_cld_evt_lvl.cd%TYPE,
dt_in IN tbl_cld_evt.dt_effective_from%TYPE,
highest_lvl IN tbl_cld_lvl_mt.cd%TYPE,
lowest_lvl IN tbl_cld_lvl_mt.cd%TYPE
-- result_cursor OUT TYPES.cursor_type
)
AS
trunc_dt_in_ DATE := NULL;
trunc_dt_in2_ DATE := NULL;
all_upp_lvl_code_ NVARCHAR2(500) := NULL;
tmp_date_ DATE := NULL;
is_bfd_ NVARCHAR2(1) := 'N';
BEGIN
BEGIN
all_upp_lvl_code_ := USF_CLD_ENG_RET_UPP_LVL_CD(cd_in, highest_lvl, lowest_lvl);
trunc_dt_in_ := TRUNC(dt_in) - 1; --Get yesterday
trunc_dt_in2_ := TRUNC(dt_in);
BEGIN
SELECT trunc_dt_in_ INTO tmp_date_
FROM DUAL
WHERE EXISTS
(
SELECT DISTINCT evtResult.dt_effective_from
, evtResult.dt_effective_to
FROM tbl_cld_evt evtResult, tbl_cld_evt_lvl evtLvlResult
WHERE evtResult.id = evtLvlResult.evt_id
AND (TRUNC(evtResult.dt_effective_from) <= trunc_dt_in_
AND TRUNC(evtResult.dt_effective_to) >= trunc_dt_in_)
AND evtLvlResult.is_associated = 'Y'
AND evtResult.id NOT IN (SELECT DISTINCT evtExpResult.id
FROM tbl_cld_evt evtExpResult, tbl_cld_evt_lvl evtExpLvlResult
WHERE evtExpResult.id = evtExpLvlResult.evt_id
AND (TRUNC(evtExpResult.dt_effective_from) <= trunc_dt_in_
AND TRUNC(evtExpResult.dt_effective_to) >= trunc_dt_in_)
AND evtExpLvlResult.is_associated = 'N'
AND (USF_CLD_ENG_INSTR(all_upp_lvl_code_, evtExpLvlResult.cd) = 1)
AND evtExpResult.evt_typ_cd = 'CLD_EVT_TYP_HOL'
)
AND (USF_CLD_ENG_INSTR(all_upp_lvl_code_, evtLvlResult.cd) = 1)
AND evtResult.evt_typ_cd = 'CLD_EVT_TYP_HOL'
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
tmp_date_ := NULL;
END;
IF(tmp_date_ IS NOT NULL) THEN
BEGIN
SELECT 'Y' INTO is_bfd_
FROM DUAL
WHERE NOT EXISTS(
SELECT DISTINCT evtWorkingDayResult.dt_effective_from
, evtWorkingDayResult.dt_effective_to
, evtWorkingDayResult.wk_no
, evtWorkingDayResult.dy_no
FROM (SELECT DISTINCT evtResult.dt_effective_from
, evtResult.dt_effective_to
, evtReptResult.wk_no
, evtReptResult.dy_no
FROM tbl_cld_evt evtResult, tbl_cld_evt_rept evtReptResult, tbl_cld_evt_lvl evtLvlResult
WHERE evtResult.evt_typ_cd = 'CLD_EVT_TYP_WD'
AND evtResult.id = evtLvlResult.evt_id
AND evtResult.id = evtReptResult.evt_id
AND (TRUNC(evtResult.dt_effective_from) <= trunc_dt_in_
AND TRUNC(evtResult.dt_effective_to) >= trunc_dt_in_)
AND evtLvlResult.is_associated = 'Y'
AND evtResult.id NOT IN (SELECT DISTINCT evtExpResult.id
FROM tbl_cld_evt evtExpResult, tbl_cld_evt_rept evtExpReptResult, tbl_cld_evt_lvl evtExpLvlResult
WHERE evtExpResult.id = evtExpLvlResult.evt_id
AND evtExpResult.id = evtExpReptResult.evt_id
AND (TRUNC(evtExpResult.dt_effective_from) <= trunc_dt_in2_
AND TRUNC(evtExpResult.dt_effective_to) >= trunc_dt_in2_)
AND evtExpResult.evt_typ_cd = 'CLD_EVT_TYP_WD'
AND evtExpLvlResult.is_associated = 'N'
AND (USF_CLD_ENG_INSTR(all_upp_lvl_code_, evtExpLvlResult.cd) = 1)
)
AND (USF_CLD_ENG_INSTR(all_upp_lvl_code_, evtLvlResult.cd) = 1)
) evtWorkingDayResult
WHERE USF_CLD_ENG_INSTR(USF_CLD_ENG_RET_REPT_DAY
(evtWorkingDayResult.wk_no, evtWorkingDayResult.dy_no,
evtWorkingDayResult.dt_effective_from, evtWorkingDayResult.dt_effective_to
),
TO_CHAR(tmp_date_, 'DD-MM-YYYY')
) = 1
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
is_bfd_ := 'N';
END;
END IF;
/*
OPEN result_cursor FOR
SELECT is_bfd_ AS is_bfd
FROM DUAL;*/
END;
END;
/
My questions are :
1. Why was the performance remain the same after I added the indices to several tables? I thought adding indices would speed up the queries..(if there is nothing there which causes the index to be suppressed).
2. The readings from tkprof report shows that the elapsed time is getting slower and slower each time when I execute the stored procedure. Anyone has any idea about this?
__________________
When the programming world turns decent, the real world will turn upside down. 
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
