Home » RDBMS Server » Performance Tuning » tuning a view (Oracle 10g, Windows)
tuning a view [message #353609] |
Tue, 14 October 2008 05:43 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |
![GETRITESH00%40YAHOO.COM](/forum/theme/orafaq/images/yahoo.png)
|
|
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
|
|
|
|
Re: tuning a view [message #353704 is a reply to message #353609] |
Tue, 14 October 2008 14:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |
![GETRITESH00%40YAHOO.COM](/forum/theme/orafaq/images/yahoo.png)
|
|
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
[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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |
![GETRITESH00%40YAHOO.COM](/forum/theme/orafaq/images/yahoo.png)
|
|
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
[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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |
![GETRITESH00%40YAHOO.COM](/forum/theme/orafaq/images/yahoo.png)
|
|
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
[Updated on: Sat, 18 October 2008 12:53] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:50:07 CDT 2024
|