cancel
Showing results for 
Search instead for 
Did you mean: 

tuning

dngaya
Advisor

tuning

hello,
I have a database of data oracle 8.1.7 installed of UNIX HP 11.
I have a request below of which I launched a tkprof of which here results.


select Ta1.o__num, Ta1.o__st, Ta1.o__edn, Ta1.o__kdn, Ta1.o__edx, Ta1.o__kdx,
Ta1.o__kdxa, Ta1.nm_st, Ta1.strt, Ta1.end_all, Ta2.o__num, Ta2.o__st
from
gtaia31d.cp_org Ta1, gtaia31d.cp_org_tk Ta2 where (((Ta2.token like :1) and
(Ta2.l_src=Ta1.o__num)) and ((Ta1.o__edn <= :2) and ((Ta1.o__kdn <= :3)
and (((Ta1.o__edx > :4) or (Ta1.o__kdx > :5)) and (Ta1.o__kdxa > :6)))))
order by Ta1.nm_sorting ASC


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.48 5.50 232 60128 5 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.48 5.50 232 60128 5 6

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 41 (JBU)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (ORDER BY)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CP_ORG'
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CP_ORG_TK'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'CP_ORG_TK_TOKEN_L_SRC' (NON-UNIQUE)


here structures of the tables:
table cp_org:
O__NUM CHAR(13) NOT NULL,
O__ST NUMBER(4) NOT NULL,
O__EDN NUMBER(6) NOT NULL,
O__KDN NUMBER(6) NOT NULL,
O__EDX NUMBER(6) NOT NULL,
O__KDX NUMBER(6) NOT NULL,
O__KDXA NUMBER(6) NOT NULL,
TYPE_FLD NUMBER(4) NULL,
TRADE_REGISTER_NMB VARCHAR2(34) NULL,
TRADE_NM VARCHAR2(50) NULL,
STRT NUMBER(6) NULL,
RGSTRTN_NMBR VARCHAR2(14) NULL,
PNONETIC_TRADE_NM VARCHAR2(80) NULL,
PHONETIC_NM VARCHAR2(80) NULL,
ORGNSTN_INITIALS VARCHAR2(20) NULL,
NM_ST VARCHAR2(50) NULL,
NM_SORTING VARCHAR2(100) NULL,
NATIONALITY VARCHAR2(20) NULL,
LUMP_SUM_IND NUMBER(11,2) NULL,
LANG0 NUMBER(2) NULL,
L_RELATION CHAR(13) NULL,
L_AREA_CRTN CHAR(13) NULL,
KEY_MANUAL_ST VARCHAR2(30) NULL,
IDNTFR VARCHAR2(10) NULL,
END_REASON NUMBER(4) NULL,
END_ALL NUMBER(6) NULL,
CREATE_CNTRY_CODE VARCHAR2(2) NULL,
CNVRSN_COLLECTVE_A VARCHAR2(30) NULL,
BUSINESS_CODE VARCHAR2(5) NULL,
ACTVTY_1 VARCHAR2(80) NULL,
TYPE VARCHAR2(1) NULL,
SLCTN_KEY VARCHAR2(2) NULL,
POSTAL_CODE VARCHAR2(10) NULL,
OCCPTN_TYPE NUMBER(2) NULL,
L_LIVE_COUNTRY CHAR(13) NULL,
L_RSDNT_AREA CHAR(13) NULL,
L_DSTRBTR CHAR(13) NULL,
IDNTFR_PERSON VARCHAR2(50) NULL,
GRP_NMBR VARCHAR2(6) NULL,
GRND_CMPTE NUMBER(1) NULL,
ENTRPRS_NMBR VARCHAR2(6) NULL,
ENTER_DT NUMBER(6) NULL,
CNTR_CNTRCT NUMBER(3) NULL,
BRNCH_NMBR VARCHAR2(6) NULL


table cp_org_tk:

O__NUM CHAR(13) NOT NULL,
O__ST NUMBER(4) NOT NULL,
TOKEN VARCHAR2(80) NULL,
L_SRC CHAR(13) NULL

here indiexes on table cp_org:
UNIQUE INDEX GTAIA31D.DI$CP_ORG
ON GTAIA31D.CP_ORG(O__NUM ASC,O__ST ASC,O__EDN ASC,O__KDN ASC,O__EDX ASC,O__KDX ASC,O__KDXA ASC)


here indiexes on table cp_org_tk:
INDEX GTAIA31D.CP_ORG_TK_TOKEN_L_SRC
ON GTAIA31D.CP_ORG_TK(TOKEN ASC,L_SRC ASC)

why the plan of execution shows a full scan on the table cp_org and yet the columns of the clause where of this table were indexed.
thank you for your assistance.
3 REPLIES
Volker Borowski
Honored Contributor

Re: tuning

Hi,

bad news! All "non-equal" comparisons can not be index supported.
So no real luck for accessing CP_ORG other than FTS.

(Ta2.l_src=Ta1.o__num) is the only one that might be worth something, but your index is on "token" first, so the RANGE-Scan given on this index will be for the
(Ta2.token like :1) expression only.
In addition, this will only support the join, not the select.

Try index L_SRC,TOKEN on CP_ORG_TK to give the join a better support. This may only help if CP_ORG_TK has a lot of rows.

May be for "<",">","<=" ... a function-based Index will help, but I never dealt with these yet.

Good hunting
Volker
dngaya
Advisor

Re: tuning

hello,
how can I make for resoudre this problem?. thank you for your assistance
Yogeeraj_1
Honored Contributor

Re: tuning

hi,

step 1:

make sure the statistics on tables cp_org and cp_org_tk are up to date.

Step 2:
Check if the status of the indexes are VALID.
(user_indexes.status)

step 3:
...

will spend some more time tomorrow at the office..and update this post...

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)