OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » tuning a view (Oracle 10g, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

tuning a view [message #353609]

Tue, 14 October 2008 05:43

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

Hello,

Below shown is View which is to be optizmed so that it uses appropriate execution plan

I have given details of tables, indexes as well explain plan and count of rows in tables, there is no primary key or
any other constraint on tables

CREATE OR REPLACE FORCE VIEW FCCI_PROD.ENTITY_DETAILS
(ED_POLICY_NO, ED_POLICY_RENEW_NO, ED_GROUP_CODE, ED_ENTY_CODE, ED_ENTY_NAME,
ED_ENTY_TAXIDNO, ED_SEQUENCE_NO)
AS
( SELECT preg_policy_no ed_policy_no,
preg_policy_renew_no ed_policy_renew_no,
enty_group_code ed_group_code,
enty_code ed_enty_code,
enty_name ed_enty_name,
enty_taxidno ed_enty_taxidno,
0 ed_sequence_no
FROM POLICY_REGISTER, ENTITY_ADDRESS_MASTER
WHERE ( (enty_group_code = 'BROKER' AND enty_code = preg_broker)
OR
(enty_group_code = 'ACCOUNT' AND enty_code = preg_insured_acc_no)
)
AND enty_status = 'Y'
UNION ALL
SELECT per_policy_no ed_policy_no,
per_policy_renew_no ed_policy_renew_no,
per_group_code ed_group_code,
per_entity_code ed_enty_code,
enty_name ed_enty_name,
enty_taxidno ed_enty_taxidno,
per_sequence_no ed_sequence_no
FROM POLICY_REGISTER, ENTITY_ADDRESS_MASTER, POLICY_ENTITY_REGISTER
WHERE preg_policy_no = per_policy_no
AND preg_policy_renew_no = per_policy_renew_no
AND enty_group_code = per_group_code
AND enty_code = per_entity_code
AND enty_status = 'Y'
);

Explain plan for View query

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 M 11645
UNION-ALL
CONCATENATION
HASH JOIN 93 K 7 M 4639
TABLE ACCESS FULL FCCI_PROD.POLICY_REGISTER 67 K 2 M 706
TABLE ACCESS BY INDEX ROWID FCCI_PROD.ENTITY_ADDRESS_MASTER 155 K 7 M 3330
INDEX SKIP SCAN FCCI_PROD.I_EAM_IND6 155 K 807
HASH JOIN 1 M 98 M 1695
TABLE ACCESS BY INDEX ROWID FCCI_PROD.ENTITY_ADDRESS_MASTER 10 K 514 K 981
INDEX SKIP SCAN FCCI_PROD.I_EAM_IND6 10 K 807
TABLE ACCESS FULL FCCI_PROD.POLICY_REGISTER 67 K 2 M 706
HASH JOIN 17 K 1 M 5310
TABLE ACCESS BY INDEX ROWID FCCI_PROD.POLICY_ENTITY_REGISTER 2 70 1
NESTED LOOPS 126 K 6 M 159
INDEX FULL SCAN FCCI_PROD.UK_PREG 67 K 1 M 57
INDEX RANGE SCAN FCCI_PROD.IND_PER_1 1 1
TABLE ACCESS BY INDEX ROWID FCCI_PROD.ENTITY_ADDRESS_MASTER 207 K 9 M 4171
INDEX SKIP SCAN FCCI_PROD.I_EAM_IND6 207 K 807

Select Count(*),Count(Distinct enty_group_code),Count(Distinct enty_code),
Count(Distinct enty_status)
From ENTITY_ADDRESS_MASTER

1518459 12 207491 2

Select Count(*),Count(Distinct preg_broker),Count(Distinct preg_insured_acc_no),
Count(Distinct preg_policy_renew_no),Count(Distinct preg_policy_no)
From POLICY_REGISTER

67866 593 13566 8 64834

Select Count(*),Count(Distinct per_policy_renew_no),Count(Distinct per_policy_no),
Count(Distinct per_group_code),Count(Distinct per_entity_code)
From POLICY_ENTITY_REGISTER

128238 8 64834 6 63762

Indexes

CREATE INDEX I_EAM_IND6 ON ENTITY_ADDRESS_MASTER
(ENTY_LAST_NAME_5_CHAR, ENTY_GROUP_CODE, ENTY_STATUS)
LOGGING
TABLESPACE FCCI_PROD_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE UNIQUE INDEX UK_PREG ON POLICY_REGISTER
(PREG_POLICY_NO, PREG_POLICY_RENEW_NO)
LOGGING
TABLESPACE FCCI_PROD_DATA
PCTFREE 20
INITRANS 8
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

Please tell me how tune it, whether i should change query structure or use indexes, please guide me

Thanks,
Ritesh

Report message to a moderator

Re: tuning a view [message #353620 is a reply to message #353609]

Tue, 14 October 2008 06:06

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Format!

Regards
Michel

Report message to a moderator

Re: tuning a view [message #353704 is a reply to message #353609]

Tue, 14 October 2008 14:19

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

Hello,

Below shown is View which is to be optizmed so that it uses appropriate execution plan

I have given details of tables, indexes as well explain plan and count of rows in tables, there is no primary key or
any other constraint on tables

CREATE OR REPLACE FORCE VIEW FCCI_PROD.ENTITY_DETAILS
(ED_POLICY_NO, ED_POLICY_RENEW_NO, ED_GROUP_CODE,
ED_ENTY_CODE, ED_ENTY_NAME,ED_ENTY_TAXIDNO, ED_SEQUENCE_NO)
AS
(SELECT preg_policy_no ed_policy_no,
preg_policy_renew_no ed_policy_renew_no,
enty_group_code ed_group_code,
enty_code ed_enty_code,
enty_name ed_enty_name,
enty_taxidno ed_enty_taxidno,
0 ed_sequence_no
FROM POLICY_REGISTER, ENTITY_ADDRESS_MASTER
WHERE ( (enty_group_code = 'BROKER'
AND
enty_code = preg_broker
)
OR
(enty_group_code = 'ACCOUNT'
AND
enty_code = preg_insured_acc_no
)
)
AND enty_status = 'Y'
UNION ALL
SELECT per_policy_no ed_policy_no,
per_policy_renew_no ed_policy_renew_no,
per_group_code ed_group_code,
per_entity_code ed_enty_code,
enty_name ed_enty_name,
enty_taxidno ed_enty_taxidno,
per_sequence_no ed_sequence_no
FROM POLICY_REGISTER, ENTITY_ADDRESS_MASTER,
POLICY_ENTITY_REGISTER
WHERE preg_policy_no = per_policy_no
AND preg_policy_renew_no = per_policy_renew_no
AND enty_group_code = per_group_code
AND enty_code = per_entity_code
AND enty_status = 'Y'
);

Select Count(*),Count(Distinct enty_group_code),Count(Distinct enty_code),
Count(Distinct enty_status)
From ENTITY_ADDRESS_MASTER

1518459 12 207491 2

Select Count(*),Count(Distinct preg_broker),Count(Distinct preg_insured_acc_no),
Count(Distinct preg_policy_renew_no),Count(Distinct preg_policy_no)
From POLICY_REGISTER

67866 593 13566 8 64834

Select Count(*),Count(Distinct per_policy_renew_no),Count(Distinct per_policy_no),
Count(Distinct per_group_code),Count(Distinct per_entity_code)
From POLICY_ENTITY_REGISTER

128238 8 64834 6 63762

Indexes

These indexes appear in Execution plan shown above

CREATE INDEX I_EAM_IND6 ON ENTITY_ADDRESS_MASTER
(ENTY_LAST_NAME_5_CHAR, ENTY_GROUP_CODE, ENTY_STATUS);

CREATE UNIQUE INDEX UK_PREG ON POLICY_REGISTER
(PREG_POLICY_NO, PREG_POLICY_RENEW_NO);

CREATE UNIQUE INDEX IND_PER_1 ON POLICY_ENTITY_REGISTER
(per_policy_no ,per_policy_renew_no );

Please tell me how tune it, whether i should change query structure or use indexes, please guide me

Thanks,
Ritesh

Attachment: Explain plan.txt
(Size: 1.45KB, Downloaded 1194 times)

[Updated on: Tue, 14 October 2008 22:06]

Report message to a moderator

Re: tuning a view [message #354409 is a reply to message #353704]

Sat, 18 October 2008 11:05

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

Attachment: Explain plan.txt
(Size: 1.45KB, Downloaded 1185 times)

[Updated on: Sat, 18 October 2008 12:40]

Report message to a moderator

Re: tuning a view [message #354412 is a reply to message #354409]

Sat, 18 October 2008 12:47

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

Hello,

See attached file for problem view query, tables used in query and index used in query execution plan, and explan plan of view query

There is no primary key or
any other constraint on tables

Please suggest me something to tune it so that execution plan cost gets reduced and query executes faster

Thanks,
Ritesh

Attachment: Explain plan.txt
(Size: 3.76KB, Downloaded 1248 times)

[Updated on: Sat, 18 October 2008 12:53]

Report message to a moderator

Previous Topic:	optimize query
Next Topic:	Slow cpu-bound hard parse in one environment, fast in another

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 11:39:20 CDT 2024