Home » RDBMS Server » Performance Tuning » Tuning the sql query (10.2.0.4)
Tuning the sql query [message #571984] |
Wed, 05 December 2012 02:23 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I want to tune my sql query which is taking around 1 1/2 hour. I have tuned but it is not improving the performance much.
SELECT tvreschar.id as ID,
tereschar.creationdt AS CREATION_DATE,
tereschar.lastupdatedt AS LAST_MODIFIED_DATE_TIME,
tvreschar.versionindex AS VERSION_INDEX,
tvreschar.mainstatus AS MAIN_STATUS,
tvreschar.mainstatusdate AS MAIN_STATUS_DATE,
treschartype.NM AS NAME,
tvreschar.NM AS VALUE,
ttfiarscchar.fk_from_fia_aaa AS FK_AAA_ACCOUNT_ID,
null AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
decode (treschartype.CATEGORY, 'Secondary', 'N', 'Main', 'K', null) AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
FROM
tt_fia_aaa_has_rscchar ttfiarscchar,
tt_rscchar_has_rscchrt ttrscchart ,
t_reschar_type treschartype,
tv_res_char tvreschar,
te_res_char tereschar
WHERE
ttrscchart.FK_FROM_RSCCHAR = ttfiarscchar.FK_TO_RSCCHAR and
treschartype.id = ttrscchart.fk_to_rscchrt and
tvreschar.fk_entity = ttrscchart.fk_from_rscchar and
tvreschar.fk_entity = tereschar.id
AND tvreschar.mainstatus not in ('S','M')
UNION
select rc.id as ID,
rcE.Creationdt AS CREATION_DATE,
rcE.Lastupdatedt AS LAST_MODIFIED_DATE_TIME,
rc.versionindex AS VERSION_INDEX,
rc.mainstatus AS MAIN_STATUS,
rc.mainstatusdate AS MAIN_STATUS_DATE,
rct.nm AS NAME,
rc.nm AS VALUE,
null AS FK_AAA_ACCOUNT_ID,
hasspec.fk_from_pifcfg AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
null AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
from tt_pifcfg_has_rscchar has,
tv_res_char rc,
tt_rscchar_has_rscchrt hast,
t_reschar_type rct,
tt_pifcfg_hasspec_pifcfgs hasspec,
tt_pifcfgs_has_rscchrt spechas,
te_res_char rcE
where has.fk_to_rscchar = rc.fk_entity
and rc.fk_entity = hast.fk_from_rscchar
and hast.fk_to_rscchrt = rct.id
and hasspec.fk_from_pifcfg = has.fk_from_pifcfg(+)
and hasspec.fk_to_pifcfgs = spechas.fk_from_pifcfgs
and spechas.fk_to_rscchrt = hast.fk_to_rscchrt
and rcE.id = rc.fk_entity
and not exists (select 1 from wmv_free_profiles wfp
where wfp.fk_pifcfg = hasspec.fk_from_pifcfg)
and rc.mainstatus not in ('S','M')
UNION
SELECT srvcharV.Id AS ID,
srvcharE.Creationdt AS CREATION_DATE,
srvcharE.Lastupdatedt AS LAST_MODIFIED_DATE_TIME,
srvcharV.Versionindex AS VERSION_INDEX,
srvcharV.Mainstatus AS MAIN_STATUS,
srvcharV.Mainstatusdate AS MAIN_STATUS_DATE,
servicechartype.nm AS NAME,
srvcharV.nm AS VALUE,
null as FK_AAA_ACCOUNT_ID,
null as FK_PROV_LOG_IFC_CONFIG_ID,
cfshaschar.FK_FROM_CFS as FK_CFS_ID,
null as AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
FROM
tt_cfs_haschar_srvch cfshaschar,
tv_servicecharacteristic srvcharV,
tt_srvch_hastype_srvchty srvchhastype,
t_servicechartype servicechartype,
te_servicecharacteristic srvcharE
WHERE
cfshaschar.FK_TO_SRVCH = srvcharV.FK_ENTITY
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
AND srvchhastype.FK_FROM_SRVCH = cfshaschar.FK_TO_SRVCH
AND srvcharV.Fk_Entity = srvcharE.id
AND srvcharV.Mainstatus not in ('S','M')
UNION
SELECT srvcharV.Id AS ID,
srvcharE.Creationdt AS CREATION_DATE,
srvcharE.Lastupdatedt AS LAST_MODIFIED_DATE_TIME,
srvcharV.Versionindex AS VERSION_INDEX,
srvcharV.Mainstatus AS MAIN_STATUS,
srvcharV.Mainstatusdate AS MAIN_STATUS_DATE,
servicechartype.nm AS NAME,
srvcharV.nm AS VALUE,
null as FK_AAA_ACCOUNT_ID,
null as FK_PROV_LOG_IFC_CONFIG_ID,
null as FK_CFS_ID,
null as AAA_ATTRIBUTE_TYPE,
rprsrvhaschar.fk_from_rprsrv as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
FROM
tt_rprsrv_haschar_srvch rprsrvhaschar,
tv_servicecharacteristic srvcharV,
tt_srvch_hastype_srvchty srvchhastype,
t_servicechartype servicechartype,
te_servicecharacteristic srvcharE
WHERE
rprsrvhaschar.fk_to_srvch = srvcharV.FK_ENTITY
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
AND srvchhastype.FK_FROM_SRVCH = rprsrvhaschar.fk_to_srvch
AND srvcharV.Fk_Entity = srvcharE.id
AND srvcharV.Mainstatus not in('S','M')
UNION
SELECT tvreschar.id as ID,
tereschar.creationdt AS CREATION_DATE,
tereschar.lastupdatedt AS LAST_MODIFIED_DATE_TIME,
tvreschar.versionindex AS VERSION_INDEX,
tvreschar.mainstatus AS MAIN_STATUS,
tvreschar.mainstatusdate AS MAIN_STATUS_DATE,
treschartype.NM AS NAME,
tvreschar.NM AS VALUE,
ttdatarscchar.fk_from_dataaaa AS FK_AAA_ACCOUNT_ID,
null AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
decode (treschartype.CATEGORY, 'Secondary', 'N', 'Main', 'K', null) AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
FROM
TT_DATAAAA_HAS_RSCCHAR ttdatarscchar,
tt_rscchar_has_rscchrt ttrscchart ,
t_reschar_type treschartype,
tv_res_char tvreschar,
te_res_char tereschar
WHERE ttrscchart.FK_FROM_RSCCHAR = ttdatarscchar.FK_TO_RSCCHAR and
treschartype.id = ttrscchart.fk_to_rscchrt and
tvreschar.fk_entity = ttrscchart.fk_from_rscchar and
tvreschar.fk_entity = tereschar.id
AND tvreschar.mainstatus not in('S','M')
UNION
select v_srvch.id as ID,
e_srvch.creationdt as CREATION_DATE,
e_srvch.lastupdatedt as LAST_MODIFIED_DATE_TIME,
v_srvch.versionindex as VERSION_INDEX,
v_srvch.mainstatus as MAIN_STATUS,
v_srvch.mainstatusdate as MAIN_STATUS_DATE,
t_srvchtype.nm as NAME,
v_srvch.nm as VALUE,
null AS FK_AAA_ACCOUNT_ID,
null AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
null AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
tt_dlmsrv_srvch.fk_from_dlmserv as FK_DLM_SERVICE_ID
from tt_dlmserv_haschar_srvch tt_dlmsrv_srvch,
te_servicecharacteristic e_srvch,
tv_servicecharacteristic v_srvch,
tt_srvch_hastype_srvchty tt_servch_has_srvchtype,
t_servicechartype t_srvchtype
where tt_dlmsrv_srvch.fk_to_srvch = e_srvch.id
and e_srvch.id = v_srvch.fk_entity
and e_srvch.id = tt_servch_has_srvchtype.fk_from_srvch
and tt_servch_has_srvchtype.fk_to_srvchty = t_srvchtype.id
and v_srvch.mainstatus not in ('S','M');
Please suggest.
|
|
|
|
Re: Tuning the sql query [message #571986 is a reply to message #571985] |
Wed, 05 December 2012 02:50 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Sorry not to indented the SQL query.
Please find the indented query and explain plan as below.
SQL> SELECT tvreschar.id as ID,
tereschar.creationdt AS CREATION_DATE,
tereschar.lastupdatedt AS LAST_MODIFIED_DATE_TIME,
tvreschar.versionindex AS VERSION_INDEX,
tvreschar.mainstatus AS MAIN_STATUS,
tvreschar.mainstatusdate AS MAIN_STATUS_DATE,
treschartype.NM AS NAME,
tvreschar.NM AS VALUE,
ttfiarscchar.fk_from_fia_aaa AS FK_AAA_ACCOUNT_ID,
null AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
decode(treschartype.CATEGORY, 'Secondary', 'N', 'Main', 'K', null) AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
FROM tt_fia_aaa_has_rscchar ttfiarscchar,
tt_rscchar_has_rscchrt ttrscchart,
t_reschar_type treschartype,
tv_res_char tvreschar,
te_res_char tereschar
WHERE ttrscchart.FK_FROM_RSCCHAR = ttfiarscchar.FK_TO_RSCCHAR
and treschartype.id = ttrscchart.fk_to_rscchrt
and tvreschar.fk_entity = ttrscchart.fk_from_rscchar
and tvreschar.fk_entity = tereschar.id
AND tvreschar.mainstatus not in ('S', 'M')
UNION
select rc.id as ID,
rcE.Creationdt AS CREATION_DATE,
rcE.Lastupdatedt AS LAST_MODIFIED_DATE_TIME,
rc.versionindex AS VERSION_INDEX,
rc.mainstatus AS MAIN_STATUS,
rc.mainstatusdate AS MAIN_STATUS_DATE,
rct.nm AS NAME,
rc.nm AS VALUE,
null AS FK_AAA_ACCOUNT_ID,
hasspec.fk_from_pifcfg AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
null AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
from tt_pifcfg_has_rscchar has,
tv_res_char rc,
tt_rscchar_has_rscchrt hast,
t_reschar_type rct,
tt_pifcfg_hasspec_pifcfgs hasspec,
tt_pifcfgs_has_rscchrt spechas,
te_res_char rcE
where has.fk_to_rscchar = rc.fk_entity
and rc.fk_entity = hast.fk_from_rscchar
and hast.fk_to_rscchrt = rct.id
and hasspec.fk_from_pifcfg = has.fk_from_pifcfg(+)
and hasspec.fk_to_pifcfgs = spechas.fk_from_pifcfgs
and spechas.fk_to_rscchrt = hast.fk_to_rscchrt
and rcE.id = rc.fk_entity
and not exists (select 1
from wmv_free_profiles wfp
where wfp.fk_pifcfg = hasspec.fk_from_pifcfg)
and rc.mainstatus not in ('S', 'M')
UNION
SELECT srvcharV.Id AS ID,
srvcharE.Creationdt AS CREATION_DATE,
srvcharE.Lastupdatedt AS LAST_MODIFIED_DATE_TIME,
srvcharV.Versionindex AS VERSION_INDEX,
srvcharV.Mainstatus AS MAIN_STATUS,
srvcharV.Mainstatusdate AS MAIN_STATUS_DATE,
servicechartype.nm AS NAME,
srvcharV.nm AS VALUE,
null as FK_AAA_ACCOUNT_ID,
null as FK_PROV_LOG_IFC_CONFIG_ID,
cfshaschar.FK_FROM_CFS as FK_CFS_ID,
null as AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
FROM tt_cfs_haschar_srvch cfshaschar,
tv_servicecharacteristic srvcharV,
tt_srvch_hastype_srvchty srvchhastype,
t_servicechartype servicechartype,
te_servicecharacteristic srvcharE
WHERE cfshaschar.FK_TO_SRVCH = srvcharV.FK_ENTITY
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
AND srvchhastype.FK_FROM_SRVCH = cfshaschar.FK_TO_SRVCH
AND srvcharV.Fk_Entity = srvcharE.id
AND srvcharV.Mainstatus not in ('S', 'M')
UNION
SELECT srvcharV.Id AS ID,
srvcharE.Creationdt AS CREATION_DATE,
srvcharE.Lastupdatedt AS LAST_MODIFIED_DATE_TIME,
srvcharV.Versionindex AS VERSION_INDEX,
srvcharV.Mainstatus AS MAIN_STATUS,
srvcharV.Mainstatusdate AS MAIN_STATUS_DATE,
servicechartype.nm AS NAME,
srvcharV.nm AS VALUE,
null as FK_AAA_ACCOUNT_ID,
null as FK_PROV_LOG_IFC_CONFIG_ID,
null as FK_CFS_ID,
null as AAA_ATTRIBUTE_TYPE,
rprsrvhaschar.fk_from_rprsrv as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
FROM tt_rprsrv_haschar_srvch rprsrvhaschar,
tv_servicecharacteristic srvcharV,
tt_srvch_hastype_srvchty srvchhastype,
t_servicechartype servicechartype,
te_servicecharacteristic srvcharE
WHERE rprsrvhaschar.fk_to_srvch = srvcharV.FK_ENTITY
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
AND srvchhastype.FK_FROM_SRVCH = rprsrvhaschar.fk_to_srvch
AND srvcharV.Fk_Entity = srvcharE.id
AND srvcharV.Mainstatus not in ('S', 'M')
UNION
SELECT tvreschar.id as ID,
tereschar.creationdt AS CREATION_DATE,
tereschar.lastupdatedt AS LAST_MODIFIED_DATE_TIME,
tvreschar.versionindex AS VERSION_INDEX,
tvreschar.mainstatus AS MAIN_STATUS,
tvreschar.mainstatusdate AS MAIN_STATUS_DATE,
treschartype.NM AS NAME,
tvreschar.NM AS VALUE,
ttdatarscchar.fk_from_dataaaa AS FK_AAA_ACCOUNT_ID,
null AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
decode(treschartype.CATEGORY, 'Secondary', 'N', 'Main', 'K', null) AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
FROM TT_DATAAAA_HAS_RSCCHAR ttdatarscchar,
tt_rscchar_has_rscchrt ttrscchart,
t_reschar_type treschartype,
tv_res_char tvreschar,
te_res_char tereschar
WHERE ttrscchart.FK_FROM_RSCCHAR = ttdatarscchar.FK_TO_RSCCHAR
and treschartype.id = ttrscchart.fk_to_rscchrt
and tvreschar.fk_entity = ttrscchart.fk_from_rscchar
and tvreschar.fk_entity = tereschar.id
AND tvreschar.mainstatus not in ('S', 'M')
UNION
select v_srvch.id as ID,
e_srvch.creationdt as CREATION_DATE,
e_srvch.lastupdatedt as LAST_MODIFIED_DATE_TIME,
v_srvch.versionindex as VERSION_INDEX,
v_srvch.mainstatus as MAIN_STATUS,
v_srvch.mainstatusdate as MAIN_STATUS_DATE,
t_srvchtype.nm as NAME,
v_srvch.nm as VALUE,
null AS FK_AAA_ACCOUNT_ID,
null AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
null AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
tt_dlmsrv_srvch.fk_from_dlmserv as FK_DLM_SERVICE_ID
from tt_dlmserv_haschar_srvch tt_dlmsrv_srvch,
te_servicecharacteristic e_srvch,
tv_servicecharacteristic v_srvch,
tt_srvch_hastype_srvchty tt_servch_has_srvchtype,
t_servicechartype t_srvchtype
where tt_dlmsrv_srvch.fk_to_srvch = e_srvch.id
and e_srvch.id = v_srvch.fk_entity
and e_srvch.id = tt_servch_has_srvchtype.fk_from_srvch
and tt_servch_has_srvchtype.fk_to_srvchty = t_srvchtype.id
and v_srvch.mainstatus not in ('S', 'M');
SQL> select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 1041M| | 1888K(100)| 06:17:42 |
| 1 | SORT UNIQUE | | 10M| 1041M| 2304M| 1888K(100)| 06:17:42 |
| 2 | UNION-ALL | | | | | | |
| 3 | NESTED LOOPS | | 1 | 132 | | 5 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 100 | | 4 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 77 | | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 39 | | 2 (0)| 00:00:01 |
| 7 | INDEX FULL SCAN | SYS_C0054827 | 1 | 26 | | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | SYS_C0050999 | 1 | 13 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| TV_RES_CHAR | 1 | 38 | | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_ISI4533 | 1 | | | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | TE_RES_CHAR | 1 | 23 | | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | SYS_C0040129 | 1 | | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | T_RESCHAR_TYPE | 1 | 32 | | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | SYS_C0042350 | 1 | | | 1 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 202K| 26M| | 172K (7)| 00:34:30 |
|* 16 | HASH JOIN | | 202K| 22M| | 131K (9)| 00:26:21 |
| 17 | TABLE ACCESS FULL | T_RESCHAR_TYPE | 107 | 1926 | | 2 (0)| 00:00:01 |
|* 18 | HASH JOIN RIGHT ANTI | | 202K| 18M| 14M| 131K (9)| 00:26:21 |
| 19 | MAT_VIEW ACCESS FULL | WMV_FREE_PROFILES | 777K| 5316K| | 611 (11)| 00:00:08 |
| 20 | NESTED LOOPS | | 239K| 20M| | 129K (9)| 00:25:50 |
|* 21 | HASH JOIN | | 23M| 1698M| | 126K (7)| 00:25:24 |
| 22 | INDEX FULL SCAN | SYS_C0059288 | 96 | 1152 | | 1 (0)| 00:00:01 |
|* 23 | HASH JOIN | | 11M| 695M| 416M| 125K (6)| 00:25:11 |
|* 24 | HASH JOIN | | 11M| 288M| 121M| 45414 (7)| 00:09:05 |
| 25 | TABLE ACCESS FULL | TT_PIFCFG_HASSPEC_PIFCFGS | 5079K| 62M| | 7419 (7)| 00:01:30 |
| 26 | TABLE ACCESS FULL | TT_PIFCFG_HAS_RSCCHAR | 11M| 149M| | 16778 (7)| 00:03:22 |
|* 27 | TABLE ACCESS FULL | TV_RES_CHAR | 11M| 407M| | 31223 (8)| 00:06:15 |
|* 28 | INDEX UNIQUE SCAN | SYS_C0050999 | 1 | 13 | | 1 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | TE_RES_CHAR | 1 | 23 | | 1 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | SYS_C0040129 | 1 | | | 1 (0)| 00:00:01 |
|* 31 | HASH JOIN | | 8203K| 774M| | 553K (7)| 01:50:47 |
| 32 | TABLE ACCESS FULL | T_SERVICECHARTYPE | 159 | 2703 | | 3 (0)| 00:00:01 |
|* 33 | HASH JOIN | | 8203K| 641M| 555M| 553K (6)| 01:50:42 |
|* 34 | HASH JOIN | | 8203K| 461M| 305M| 335K (7)| 01:07:06 |
|* 35 | HASH JOIN | | 8203K| 211M| 203M| 136K (8)| 00:27:21 |
| 36 | TABLE ACCESS FULL | TT_CFS_HASCHAR_SRVCH | 8203K| 109M| | 14014 (7)| 00:02:49 |
| 37 | TABLE ACCESS FULL | TT_SRVCH_HASTYPE_SRVCHTY | 41M| 518M| | 58642 (8)| 00:11:44 |
|* 38 | TABLE ACCESS FULL | TV_SERVICECHARACTERISTIC | 42M| 1283M| | 91304 (9)| 00:18:16 |
| 39 | TABLE ACCESS FULL | TE_SERVICECHARACTERISTIC | 42M| 924M| | 115K (5)| 00:23:12 |
| 40 | NESTED LOOPS | | 732K| 69M| | 408K (3)| 01:21:42 |
| 41 | NESTED LOOPS | | 732K| 53M| | 261K (4)| 00:52:17 |
|* 42 | HASH JOIN | | 732K| 30M| | 114K (8)| 00:22:51 |
| 43 | TABLE ACCESS FULL | T_SERVICECHARTYPE | 159 | 2703 | | 3 (0)| 00:00:01 |
|* 44 | HASH JOIN | | 732K| 18M| 18M| 114K (8)| 00:22:51 |
| 45 | TABLE ACCESS FULL | TT_RPRSRV_HASCHAR_SRVCH | 732K| 9M| | 1177 (7)| 00:00:15 |
| 46 | TABLE ACCESS FULL | TT_SRVCH_HASTYPE_SRVCHTY | 41M| 518M| | 58642 (8)| 00:11:44 |
|* 47 | TABLE ACCESS BY INDEX ROWID | TV_SERVICECHARACTERISTIC | 1 | 32 | | 1 (0)| 00:00:01 |
|* 48 | INDEX RANGE SCAN | IDX_ISI4539 | 1 | | | 1 (0)| 00:00:01 |
| 49 | TABLE ACCESS BY INDEX ROWID | TE_SERVICECHARACTERISTIC | 1 | 23 | | 1 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | SYS_C0040135 | 1 | | | 1 (0)| 00:00:01 |
| 51 | NESTED LOOPS | | 1 | 132 | | 5 (0)| 00:00:01 |
| 52 | NESTED LOOPS | | 1 | 100 | | 4 (0)| 00:00:01 |
| 53 | NESTED LOOPS | | 1 | 77 | | 3 (0)| 00:00:01 |
| 54 | NESTED LOOPS | | 1 | 39 | | 2 (0)| 00:00:01 |
| 55 | INDEX FULL SCAN | SYS_C00405425 | 1 | 26 | | 1 (0)| 00:00:01 |
|* 56 | INDEX RANGE SCAN | SYS_C0050999 | 1 | 13 | | 1 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID| TV_RES_CHAR | 1 | 38 | | 1 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | IDX_ISI4533 | 1 | | | 1 (0)| 00:00:01 |
| 59 | TABLE ACCESS BY INDEX ROWID | TE_RES_CHAR | 1 | 23 | | 1 (0)| 00:00:01 |
|* 60 | INDEX UNIQUE SCAN | SYS_C0040129 | 1 | | | 1 (0)| 00:00:01 |
| 61 | TABLE ACCESS BY INDEX ROWID | T_RESCHAR_TYPE | 1 | 32 | | 1 (0)| 00:00:01 |
|* 62 | INDEX UNIQUE SCAN | SYS_C0042350 | 1 | | | 1 (0)| 00:00:01 |
|* 63 | HASH JOIN | | 1815K| 171M| | 499K (7)| 01:39:52 |
| 64 | TABLE ACCESS FULL | T_SERVICECHARTYPE | 159 | 2703 | | 3 (0)| 00:00:01 |
|* 65 | HASH JOIN | | 1815K| 142M| 107M| 499K (7)| 01:39:50 |
|* 66 | HASH JOIN | | 1815K| 86M| 84M| 310K (6)| 01:02:09 |
|* 67 | HASH JOIN | | 1815K| 64M| 45M| 194K (6)| 00:38:53 |
| 68 | TABLE ACCESS FULL | TT_DLMSERV_HASCHAR_SRVCH | 1815K| 24M| | 2139 (9)| 00:00:26 |
| 69 | TABLE ACCESS FULL | TE_SERVICECHARACTERISTIC | 42M| 924M| | 115K (5)| 00:23:12 |
| 70 | TABLE ACCESS FULL | TT_SRVCH_HASTYPE_SRVCHTY | 41M| 518M| | 58642 (8)| 00:11:44 |
|* 71 | TABLE ACCESS FULL | TV_SERVICECHARACTERISTIC | 42M| 1283M| | 91304 (9)| 00:18:16 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("TTRSCCHART"."FK_FROM_RSCCHAR"="TTFIARSCCHAR"."FK_TO_RSCCHAR")
9 - filter("TVRESCHAR"."MAINSTATUS"<>''S'' AND "TVRESCHAR"."MAINSTATUS"<>''M'')
10 - access("TVRESCHAR"."FK_ENTITY"="TTRSCCHART"."FK_FROM_RSCCHAR")
12 - access("TVRESCHAR"."FK_ENTITY"="TERESCHAR"."ID")
14 - access("TRESCHARTYPE"."ID"="TTRSCCHART"."FK_TO_RSCCHRT")
16 - access("HAST"."FK_TO_RSCCHRT"="RCT"."ID")
18 - access("WFP"."FK_PIFCFG"="HASSPEC"."FK_FROM_PIFCFG")
21 - access("HASSPEC"."FK_TO_PIFCFGS"="SPECHAS"."FK_FROM_PIFCFGS")
23 - access("HAS"."FK_TO_RSCCHAR"="RC"."FK_ENTITY")
24 - access("HASSPEC"."FK_FROM_PIFCFG"="HAS"."FK_FROM_PIFCFG")
27 - filter("RC"."MAINSTATUS"<>''S'' AND "RC"."MAINSTATUS"<>''M'')
28 - access("RC"."FK_ENTITY"="HAST"."FK_FROM_RSCCHAR" AND "SPECHAS"."FK_TO_RSCCHRT"="HAST"."FK_TO_RSCCHRT")
30 - access("RCE"."ID"="RC"."FK_ENTITY")
31 - access("SERVICECHARTYPE"."ID"="SRVCHHASTYPE"."FK_TO_SRVCHTY")
33 - access("SRVCHARV"."FK_ENTITY"="SRVCHARE"."ID")
34 - access("CFSHASCHAR"."FK_TO_SRVCH"="SRVCHARV"."FK_ENTITY")
35 - access("SRVCHHASTYPE"."FK_FROM_SRVCH"="CFSHASCHAR"."FK_TO_SRVCH")
38 - filter("SRVCHARV"."MAINSTATUS"<>''S'' AND "SRVCHARV"."MAINSTATUS"<>''M'')
42 - access("SERVICECHARTYPE"."ID"="SRVCHHASTYPE"."FK_TO_SRVCHTY")
44 - access("SRVCHHASTYPE"."FK_FROM_SRVCH"="RPRSRVHASCHAR"."FK_TO_SRVCH")
47 - filter("SRVCHARV"."MAINSTATUS"<>''S'' AND "SRVCHARV"."MAINSTATUS"<>''M'')
48 - access("RPRSRVHASCHAR"."FK_TO_SRVCH"="SRVCHARV"."FK_ENTITY")
50 - access("SRVCHARV"."FK_ENTITY"="SRVCHARE"."ID")
56 - access("TTRSCCHART"."FK_FROM_RSCCHAR"="TTDATARSCCHAR"."FK_TO_RSCCHAR")
57 - filter("TVRESCHAR"."MAINSTATUS"<>''S'' AND "TVRESCHAR"."MAINSTATUS"<>''M'')
58 - access("TVRESCHAR"."FK_ENTITY"="TTRSCCHART"."FK_FROM_RSCCHAR")
60 - access("TVRESCHAR"."FK_ENTITY"="TERESCHAR"."ID")
62 - access("TRESCHARTYPE"."ID"="TTRSCCHART"."FK_TO_RSCCHRT")
63 - access("TT_SERVCH_HAS_SRVCHTYPE"."FK_TO_SRVCHTY"="T_SRVCHTYPE"."ID")
65 - access("E_SRVCH"."ID"="V_SRVCH"."FK_ENTITY")
66 - access("E_SRVCH"."ID"="TT_SERVCH_HAS_SRVCHTYPE"."FK_FROM_SRVCH")
67 - access("TT_DLMSRV_SRVCH"."FK_TO_SRVCH"="E_SRVCH"."ID")
71 - filter("V_SRVCH"."MAINSTATUS"<>''S'' AND "V_SRVCH"."MAINSTATUS"<>''M'')
|
|
|
|
|
|
Re: Tuning the sql query [message #572178 is a reply to message #571984] |
Thu, 06 December 2012 19:45 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
start by using QUERY DECOMPOSITION. Run these statements and then get rowcounts from each table created. You are collecting seconds of elapsed time and getting a rowcount.
--
-- run this create, how long did it take?
--
create table work1
nologging
as
SELECT tvreschar.id as ID,
tereschar.creationdt AS CREATION_DATE,
tereschar.lastupdatedt AS LAST_MODIFIED_DATE_TIME,
tvreschar.versionindex AS VERSION_INDEX,
tvreschar.mainstatus AS MAIN_STATUS,
tvreschar.mainstatusdate AS MAIN_STATUS_DATE,
treschartype.NM AS NAME,
tvreschar.NM AS VALUE,
ttfiarscchar.fk_from_fia_aaa AS FK_AAA_ACCOUNT_ID,
null AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
decode (treschartype.CATEGORY, 'Secondary', 'N', 'Main', 'K', null) AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
FROM
tt_fia_aaa_has_rscchar ttfiarscchar,
tt_rscchar_has_rscchrt ttrscchart ,
t_reschar_type treschartype,
tv_res_char tvreschar,
te_res_char tereschar
WHERE
ttrscchart.FK_FROM_RSCCHAR = ttfiarscchar.FK_TO_RSCCHAR and
treschartype.id = ttrscchart.fk_to_rscchrt and
tvreschar.fk_entity = ttrscchart.fk_from_rscchar and
tvreschar.fk_entity = tereschar.id
AND tvreschar.mainstatus not in ('S','M')
/
--
-- run this create, how long did it take?
--
create table work2
nologging
as
select rc.id as ID,
rcE.Creationdt AS CREATION_DATE,
rcE.Lastupdatedt AS LAST_MODIFIED_DATE_TIME,
rc.versionindex AS VERSION_INDEX,
rc.mainstatus AS MAIN_STATUS,
rc.mainstatusdate AS MAIN_STATUS_DATE,
rct.nm AS NAME,
rc.nm AS VALUE,
null AS FK_AAA_ACCOUNT_ID,
hasspec.fk_from_pifcfg AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
null AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
from tt_pifcfg_has_rscchar has,
tv_res_char rc,
tt_rscchar_has_rscchrt hast,
t_reschar_type rct,
tt_pifcfg_hasspec_pifcfgs hasspec,
tt_pifcfgs_has_rscchrt spechas,
te_res_char rcE
where has.fk_to_rscchar = rc.fk_entity
and rc.fk_entity = hast.fk_from_rscchar
and hast.fk_to_rscchrt = rct.id
and hasspec.fk_from_pifcfg = has.fk_from_pifcfg(+)
and hasspec.fk_to_pifcfgs = spechas.fk_from_pifcfgs
and spechas.fk_to_rscchrt = hast.fk_to_rscchrt
and rcE.id = rc.fk_entity
and not exists (select 1 from wmv_free_profiles wfp
where wfp.fk_pifcfg = hasspec.fk_from_pifcfg)
and rc.mainstatus not in ('S','M')
/
--
-- run this create, how long did it take?
--
create table work3
nologging
as
SELECT srvcharV.Id AS ID,
srvcharE.Creationdt AS CREATION_DATE,
srvcharE.Lastupdatedt AS LAST_MODIFIED_DATE_TIME,
srvcharV.Versionindex AS VERSION_INDEX,
srvcharV.Mainstatus AS MAIN_STATUS,
srvcharV.Mainstatusdate AS MAIN_STATUS_DATE,
servicechartype.nm AS NAME,
srvcharV.nm AS VALUE,
null as FK_AAA_ACCOUNT_ID,
null as FK_PROV_LOG_IFC_CONFIG_ID,
cfshaschar.FK_FROM_CFS as FK_CFS_ID,
null as AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
FROM
tt_cfs_haschar_srvch cfshaschar,
tv_servicecharacteristic srvcharV,
tt_srvch_hastype_srvchty srvchhastype,
t_servicechartype servicechartype,
te_servicecharacteristic srvcharE
WHERE
cfshaschar.FK_TO_SRVCH = srvcharV.FK_ENTITY
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
AND srvchhastype.FK_FROM_SRVCH = cfshaschar.FK_TO_SRVCH
AND srvcharV.Fk_Entity = srvcharE.id
AND srvcharV.Mainstatus not in ('S','M')
/
--
-- run this create, how long did it take?
--
create table work4
nologging
as
SELECT srvcharV.Id AS ID,
srvcharE.Creationdt AS CREATION_DATE,
srvcharE.Lastupdatedt AS LAST_MODIFIED_DATE_TIME,
srvcharV.Versionindex AS VERSION_INDEX,
srvcharV.Mainstatus AS MAIN_STATUS,
srvcharV.Mainstatusdate AS MAIN_STATUS_DATE,
servicechartype.nm AS NAME,
srvcharV.nm AS VALUE,
null as FK_AAA_ACCOUNT_ID,
null as FK_PROV_LOG_IFC_CONFIG_ID,
null as FK_CFS_ID,
null as AAA_ATTRIBUTE_TYPE,
rprsrvhaschar.fk_from_rprsrv as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
FROM
tt_rprsrv_haschar_srvch rprsrvhaschar,
tv_servicecharacteristic srvcharV,
tt_srvch_hastype_srvchty srvchhastype,
t_servicechartype servicechartype,
te_servicecharacteristic srvcharE
WHERE
rprsrvhaschar.fk_to_srvch = srvcharV.FK_ENTITY
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
AND srvchhastype.FK_FROM_SRVCH = rprsrvhaschar.fk_to_srvch
AND srvcharV.Fk_Entity = srvcharE.id
AND srvcharV.Mainstatus not in('S','M')
/
--
-- run this create, how long did it take?
--
create table work5
nologging
as
SELECT tvreschar.id as ID,
tereschar.creationdt AS CREATION_DATE,
tereschar.lastupdatedt AS LAST_MODIFIED_DATE_TIME,
tvreschar.versionindex AS VERSION_INDEX,
tvreschar.mainstatus AS MAIN_STATUS,
tvreschar.mainstatusdate AS MAIN_STATUS_DATE,
treschartype.NM AS NAME,
tvreschar.NM AS VALUE,
ttdatarscchar.fk_from_dataaaa AS FK_AAA_ACCOUNT_ID,
null AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
decode (treschartype.CATEGORY, 'Secondary', 'N', 'Main', 'K', null) AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
null as FK_DLM_SERVICE_ID
FROM
TT_DATAAAA_HAS_RSCCHAR ttdatarscchar,
tt_rscchar_has_rscchrt ttrscchart ,
t_reschar_type treschartype,
tv_res_char tvreschar,
te_res_char tereschar
WHERE ttrscchart.FK_FROM_RSCCHAR = ttdatarscchar.FK_TO_RSCCHAR and
treschartype.id = ttrscchart.fk_to_rscchrt and
tvreschar.fk_entity = ttrscchart.fk_from_rscchar and
tvreschar.fk_entity = tereschar.id
AND tvreschar.mainstatus not in('S','M')
/
--
-- run this create, how long did it take?
--
create table work6
nologging
as
select v_srvch.id as ID,
e_srvch.creationdt as CREATION_DATE,
e_srvch.lastupdatedt as LAST_MODIFIED_DATE_TIME,
v_srvch.versionindex as VERSION_INDEX,
v_srvch.mainstatus as MAIN_STATUS,
v_srvch.mainstatusdate as MAIN_STATUS_DATE,
t_srvchtype.nm as NAME,
v_srvch.nm as VALUE,
null AS FK_AAA_ACCOUNT_ID,
null AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
null AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
tt_dlmsrv_srvch.fk_from_dlmserv as FK_DLM_SERVICE_ID
from tt_dlmserv_haschar_srvch tt_dlmsrv_srvch,
te_servicecharacteristic e_srvch,
tv_servicecharacteristic v_srvch,
tt_srvch_hastype_srvchty tt_servch_has_srvchtype,
t_servicechartype t_srvchtype
where tt_dlmsrv_srvch.fk_to_srvch = e_srvch.id
and e_srvch.id = v_srvch.fk_entity
and e_srvch.id = tt_servch_has_srvchtype.fk_from_srvch
and tt_servch_has_srvchtype.fk_to_srvchty = t_srvchtype.id
and v_srvch.mainstatus not in ('S','M')
/
select count(*) from work1;
select count(*) from work2;
select count(*) from work3;
select count(*) from work4;
select count(*) from work5;
select count(*) from work6;
[Updated on: Thu, 06 December 2012 20:16] Report message to a moderator
|
|
|
|
|
Re: Tuning the sql query [message #572181 is a reply to message #572180] |
Thu, 06 December 2012 20:05 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
After you have posted:
1) the runtime metrics (elapsed seconds in this case) of the six create table sql
2) the query diagrams for each create table sql
3) the count/filter results for tables in each create table sql (keep them seperate by each create table sql piece)
We will talk further. I figure this should take you between four and eight hours.
Kevin
[Updated on: Thu, 06 December 2012 20:12] Report message to a moderator
|
|
|
Re: Tuning the sql query [message #572292 is a reply to message #572181] |
Mon, 10 December 2012 03:11 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi,
Please find the output which I have taken from tables.
Timings:
SQL> set timing on
SQL> create table work1
Elapsed: 00:00:00.09
SQL> create table work2
Elapsed: 03:17:32.31
SQL> create table work3
Elapsed: 00:27:22.35
SQL> create table work4
Elapsed: 05:04:11.24
SQL> create table work5
Elapsed: 00:00:00.53
SQL> create table work6
Elapsed: 00:05:50.25
Record Count
SQL> select count(*) from work1;
COUNT(*)
----------
0
Elapsed: 00:00:00.08
SQL> select count(*) from work2;
COUNT(*)
----------
10541598
Elapsed: 00:00:10.78
SQL> select count(*) from wmv_free_profiles;
COUNT(*)
----------
779669
Elapsed: 00:00:00.47
SQL> select count(*) from work3;
COUNT(*)
----------
8251708
Elapsed: 00:00:08.49
SQL> select count(*) from work4;
COUNT(*)
----------
730076
Elapsed: 00:00:00.72
SQL> select count(*) from work5;
COUNT(*)
----------
0
Elapsed: 00:00:00.02
SQL> select count(*) from work6;
COUNT(*)
----------
1859412
Elapsed: 00:00:01.67
Diagrams:
Query: 1
FROM tt_fia_aaa_has_rscchar ttfiarscchar,
tt_rscchar_has_rscchrt ttrscchart,
t_reschar_type treschartype,
tv_res_char tvreschar,
te_res_char tereschar
WHERE ttrscchart.FK_FROM_RSCCHAR = ttfiarscchar.FK_TO_RSCCHAR
and treschartype.id = ttrscchart.fk_to_rscchrt
and tvreschar.fk_entity = ttrscchart.fk_from_rscchar
and tvreschar.fk_entity = tereschar.id
AND tvreschar.mainstatus not in ('S', 'M')
ttfiarscchar ----- ttrscchart ----- treschartype tvreschar ----- tereschar
| | |
| | |
| | |
+-------------------------------+ |
|
|
|
c
Query:2
from tt_pifcfg_has_rscchar has,
tv_res_char rc,
tt_rscchar_has_rscchrt hast,
t_reschar_type rct,
tt_pifcfg_hasspec_pifcfgs hasspec,
tt_pifcfgs_has_rscchrt spechas,
te_res_char rcE
where has.fk_to_rscchar = rc.fk_entity
and rc.fk_entity = hast.fk_from_rscchar
and hast.fk_to_rscchrt = rct.id
and hasspec.fk_from_pifcfg = has.fk_from_pifcfg(+)
and hasspec.fk_to_pifcfgs = spechas.fk_from_pifcfgs
and spechas.fk_to_rscchrt = hast.fk_to_rscchrt
and rcE.id = rc.fk_entity
and not exists (select 1
from wmv_free_profiles wfp
where wfp.fk_pifcfg = hasspec.fk_from_pifcfg)
and rc.mainstatus not in ('S', 'M')
has-----rc----------hast-------rct hasspec--------spechas rcE wmv_free_profiles
| || | | | | | |
| || | | | | | |
| || +------------------|--|------------+ | |
| || | | | |
| || | +--------------------|---------------+
| || | |
| |+------------------------------|-----------------------+
| | |
| | |
| | |
| c |
| |
+---------------------------------------+
Wmv_free_profiles is the Materialized View used in Query.2:
create materialized view WMV_FREE_PROFILES
build deferred
refresh force on demand
as
select eth_pif.fk_from_ethlifc as fk_ethlifc,
eth_pif.fk_to_pifcfg as fk_pifcfg
from tt_ethlifc_has_pifcfg eth_pif
where not exists
(select 1
from tt_rfs_uses_ethlifc rfs_use
where rfs_use.fk_to_ethlifc = eth_pif.fk_from_ethlifc);
SQL> select count(*) from tt_ethlifc_has_pifcfg;
COUNT(*)
----------
1999135
Elapsed: 00:00:18.75
SQL> select count(*) from tt_rfs_uses_ethlifc;
COUNT(*)
----------
1191559
Elapsed: 00:00:11.28
Query:3
FROM tt_cfs_haschar_srvch cfshaschar,
tv_servicecharacteristic srvcharV,
tt_srvch_hastype_srvchty srvchhastype,
t_servicechartype servicechartype,
te_servicecharacteristic srvcharE
WHERE cfshaschar.FK_TO_SRVCH = srvcharV.FK_ENTITY
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
AND srvchhastype.FK_FROM_SRVCH = cfshaschar.FK_TO_SRVCH
AND srvcharV.Fk_Entity = srvcharE.id
AND srvcharV.Mainstatus not in ('S', 'M');
cfshaschar---------srvcharV srvchhastype---------------servicechartype srvcharE
| | || | | |
| | || | | |
| | |+--------------|-------------------------------+ |
| | | | |
| | | | |
| | | | |
| | | | |
| | c | |
| +--------------------|-----------------------------------------------+
| |
+---------------------------------------+
Query:4
FROM tt_rprsrv_haschar_srvch rprsrvhaschar,
tv_servicecharacteristic srvcharV,
tt_srvch_hastype_srvchty srvchhastype,
t_servicechartype servicechartype,
te_servicecharacteristic srvcharE
WHERE rprsrvhaschar.fk_to_srvch = srvcharV.FK_ENTITY
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
AND srvchhastype.FK_FROM_SRVCH = rprsrvhaschar.fk_to_srvch
AND srvcharV.Fk_Entity = srvcharE.id
AND srvcharV.Mainstatus not in ('S', 'M');
rprsrvhaschar--------srvcharV srvchhastype---------servicechartype srvcharE
| || | |
| || | |
| || | |
| || | |
| |+--------------|-------------------------------+
| c |
| |
| |
+---------------------------------------+
Query:5
FROM TT_DATAAAA_HAS_RSCCHAR ttdatarscchar,
tt_rscchar_has_rscchrt ttrscchart,
t_reschar_type treschartype,
tv_res_char tvreschar,
te_res_char tereschar
WHERE ttrscchart.FK_FROM_RSCCHAR = ttdatarscchar.FK_TO_RSCCHAR
and treschartype.id = ttrscchart.fk_to_rscchrt
and tvreschar.fk_entity = ttrscchart.fk_from_rscchar
and tvreschar.fk_entity = tereschar.id
AND tvreschar.mainstatus not in ('S', 'M');
ttdatarscchar---------ttrscchart------treschartype tvreschar-------------tereschar
| ||
| ||
| ||
+---------------------------------------+|
|
|
c
Query:6
from tt_dlmserv_haschar_srvch tt_dlmsrv_srvch,
te_servicecharacteristic e_srvch,
tv_servicecharacteristic v_srvch,
tt_srvch_hastype_srvchty tt_servch_has_srvchtype,
t_servicechartype t_srvchtype
where tt_dlmsrv_srvch.fk_to_srvch = e_srvch.id
and e_srvch.id = v_srvch.fk_entity
and e_srvch.id = tt_servch_has_srvchtype.fk_from_srvch
and tt_servch_has_srvchtype.fk_to_srvchty = t_srvchtype.id
and v_srvch.mainstatus not in ('S', 'M');
tt_dlmsrv_srvch------------e_srvch---------v_srvch tt_servch_has_srvchtype t_srvchtype
| | | | |
| | | | |
| | | | |
| | | | |
+---------------|---------------+ +-----------------------+
|
|
c
Query:1
Count Queries
SQL> select count(*) from tt_fia_aaa_has_rscchar ttfiarscchar ;
COUNT(*)
----------
0
SQL> select count(*) from tt_rscchar_has_rscchrt ttrscchart ;
COUNT(*)
----------
11271522
SQL> select count(*) from t_reschar_type treschartype ;
COUNT(*)
----------
107
SQL> select count(*) from tv_res_char tvreschar ;
COUNT(*)
----------
11350737
SQL> select count(*) from te_res_char tereschar ;
COUNT(*)
----------
11271524
Filter Queries:
SQL> select count(*) from tv_res_char tvreschar where tvreschar.mainstatus not in ('S','M');
COUNT(*)
----------
11322823
Query:2
Count Queries
SQL> select count(*) from tt_pifcfg_has_rscchar has;
COUNT(*)
----------
11270011
SQL> select count(*) from tv_res_char rc;
COUNT(*)
----------
11350737
SQL> select count(*) from tt_rscchar_has_rscchrt hast;
COUNT(*)
----------
11271522
SQL> select count(*) from t_reschar_type rct;
COUNT(*)
----------
107
SQL> select count(*) from tt_pifcfg_hasspec_pifcfgs hasspec;
COUNT(*)
----------
5159404
SQL> select count(*) from tt_pifcfgs_has_rscchrt spechas;
COUNT(*)
----------
96
SQL> select count(*) from te_res_char rcE;
COUNT(*)
----------
11271524
SQL> select count(*) from wmv_free_profiles wfp;
COUNT(*)
----------
779669
Filter Queries:
SQL> select count(*) from tv_res_char tvreschar where tvreschar.mainstatus not in ('S','M');
COUNT(*)
----------
11322823
Query:3
SQL> select count(*) from tt_cfs_haschar_srvch cfshaschar;
COUNT(*)
----------
8243992
SQL> select count(*) from tv_servicecharacteristic srvcharV;
COUNT(*)
----------
42288864
SQL> select count(*) from tt_srvch_hastype_srvchty srvchhastype;
COUNT(*)
----------
42280766
SQL> select count(*) from t_servicechartype servicechartype;
COUNT(*)
----------
159
SQL> select count(*) from te_servicecharacteristic srvcharE;
COUNT(*)
----------
42280778
Filter Queries:
SQL> select count(*) from tv_servicecharacteristic srvcharV where srvcharV.mainstatus not in ('S','M');
COUNT(*)
----------
42284528
Query:4
SQL> select count(*) from tt_rprsrv_haschar_srvch rprsrvhaschar;
COUNT(*)
----------
730076
SQL> select count(*) from tv_servicecharacteristic srvcharV;
COUNT(*)
----------
42288864
SQL> select count(*) from tt_srvch_hastype_srvchty srvchhastype;
COUNT(*)
----------
42280766
SQL> select count(*) from t_servicechartype servicechartype;
COUNT(*)
----------
159
SQL> select count(*) from te_servicecharacteristic srvcharE;
COUNT(*)
----------
42280778
Filter Queries:
SQL> select count(*) from tv_servicecharacteristic srvcharV where srvcharV.mainstatus not in ('S','M');
COUNT(*)
----------
42284528
Query:5
SQL> select count(*) from TT_DATAAAA_HAS_RSCCHAR ttdatarscchar;
COUNT(*)
----------
0
SQL> select count(*) from tt_rscchar_has_rscchrt ttrscchart;
COUNT(*)
----------
11271522
SQL> select count(*) from t_reschar_type treschartype;
COUNT(*)
----------
107
SQL> select count(*) from tv_res_char tvreschar;
COUNT(*)
----------
11350737
SQL> select count(*) from te_res_char tereschar;
COUNT(*)
----------
11271524
Filter Queries:
SQL> select count(*) from tv_res_char tvreschar where tvreschar.mainstatus not in ('S','M');
COUNT(*)
----------
11322823
Query: 6
SQL> select count(*) from tt_dlmserv_haschar_srvch tt_dlmsrv_srvch;
COUNT(*)
----------
1859412
SQL> select count(*) from te_servicecharacteristic e_srvch;
COUNT(*)
----------
42280778
SQL> select count(*) from tv_servicecharacteristic v_srvch;
COUNT(*)
----------
42288864
SQL> select count(*) from tt_srvch_hastype_srvchty tt_servch_has_srvchtype;
COUNT(*)
----------
42280766
SQL> select count(*) from t_servicechartype t_srvchtype;
COUNT(*)
----------
159
Filter Queries:
SQL> select count(*) from tv_servicecharacteristic v_srvch where v_srvch.mainstatus not in ('S','M');
COUNT(*)
----------
42284528
|
|
|
Re: Tuning the sql query [message #572307 is a reply to message #572292] |
Mon, 10 December 2012 06:12 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
With a couple of those queries you are joining some really big (42M row) tables, but it looks like you are only getting a million or so rows back, and you don't have any significant filter criteria in the query. That suggests that at least ONE of the joins in those big tables has mostly non-matching rows. ie. You join 42M rows to 42M rows but get only 2M rows in return.
If you can identify the join that does this and make it run first (say, by reordering the tables in the FROM clause and adding the ORDERED hint), then you might find you can do all of the remaining joins in the query in-memory. Given these table sizes, it is likely that the big joins are spilling over memory limitation and paging to disk.
Ross Leishman
|
|
|
Re: Tuning the sql query [message #572316 is a reply to message #572307] |
Mon, 10 December 2012 09:10 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
BEAUTIFUL WORK grpatwari. Nice job on all of that.
Initially we wanted to see how many rows were filtered away by the (S/M) predicate but according to this, that it is none. From this we get two basic points:
1) the query does not appear to be an index oriented query so the query likely should be doing full table scans and hash joins everywhere (we will check this as we go, especially for the sub-queries that return no rows).
2) we need to next follow Ross' suggestion and note that:
a) there are still significant rows lost due to joins and so the proper order of joins will help reduce the intermediary row set sizes the query carriers around.
b) we should pay attention to our hash joins and try our best to make sure they are getting done in memory. I think we do this either by giving them more memory or employing parallel query. I will explain later, let us not rush into that yet.
Here is the data your provided in summary.
WORK TABLE TIMING seconds rows
work1 00:00.0 0 0
work2 17:32.0 1052 10541598
work3 27:22.0 1642 8251708
work4 04:11.0 251 730076
work5 00:01.0 1 0
work6 05:50.0 350 1859412
Let us start with the most expensive sub-query, Query:3. Using what you gave us from the COUNT and FILTERING queries, we can compute this data and see how well any filter predicates in this sub-query filter out data:
TABLE ROWS FILTERED ROWS filtered %
cfshaschar 8243992 8243992 0
srvcharV 42288864 42284528 0.01
srvchhastype 42280766 42280766 0
servicechartype 159 159 0
srvcharE 42280778 42280778 0
This data tells us that the filter predicate for Query:3 does not filter out any significant number of rows. We would have like to see it filter away 98% of the data or more but in this case it does almost nothing.
Now we have more work to do. We have to check the rowcounts after each join, with means we need to build reconstruction queries that incrementally rebuild our query one join at a time. I will do the for query:3. You can do them for rest. In this case, since there are few predicates this will be a little easier that ususal. Incidently, I believe your query diagram for this query has a small error in it. Please check it to make sure it is infact an error in the diagram and not a missing join in the query.
Please run the following. What we will learn is where joins drop rows from the query. Then we want the database to join in that order. We may find however that the query is already joining in the order we need. I suspect that since the query returns this manyh rows (8251708) and the table cfshaschar feeds this many rows into the query (8243992), that after we join to this table, we will have removed most of the data from the query. If so then this is the table we want to join to first so I have selected a valid join order based on your query diagram that puts this table as the first table we join, assuming we start with the only table that actually filters any data away.
Once you have these counts and timings, we can talk again. The point here is that we are trying to keep intermediary row set sizes as small as possible and since there are no explicit filters doing any real filtering, we have to rely on joins to do filtering and in the case of this query it seems that there is in fact one or more joins that remove large numbers of rows from the query (though this is not normal for most databases so if we are right we kind of got lucky).
select count(*),'srvcharV' new_table
from v_servicecharacteristic srvcharV
where srvcharV.Mainstatus not in ('S', 'M')
/
select count(*),'cfshaschar' new_table
from v_servicecharacteristic srvcharV
,tt_cfs_haschar_srvch cfshaschar
where srvcharV.Mainstatus not in ('S', 'M')'
and cfshaschar.FK_TO_SRVCH = srvcharV.FK_ENTITY
/
select count(*),'srvchhastype' new_table
from v_servicecharacteristic srvcharV
,tt_cfs_haschar_srvch cfshaschar
,tt_srvch_hastype_srvchty srvchhastype
where srvcharV.Mainstatus not in ('S', 'M')'
and cfshaschar.FK_TO_SRVCH = srvcharV.FK_ENTITY
AND srvchhastype.FK_FROM_SRVCH = cfshaschar.FK_TO_SRVCH
/
select count(*),'servicechartype' new_table
from v_servicecharacteristic srvcharV
,tt_cfs_haschar_srvch cfshaschar
,tt_srvch_hastype_srvchty srvchhastype
,t_servicechartype servicechartype
where srvcharV.Mainstatus not in ('S', 'M')'
and cfshaschar.FK_TO_SRVCH = srvcharV.FK_ENTITY
AND srvchhastype.FK_FROM_SRVCH = cfshaschar.FK_TO_SRVCH
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
/
select count(*),'srvcharE' new_table
from v_servicecharacteristic srvcharV
,tt_cfs_haschar_srvch cfshaschar
,tt_srvch_hastype_srvchty srvchhastype
,t_servicechartype servicechartype
,te_servicecharacteristic srvcharE
where srvcharV.Mainstatus not in ('S', 'M')'
and cfshaschar.FK_TO_SRVCH = srvcharV.FK_ENTITY
AND srvchhastype.FK_FROM_SRVCH = cfshaschar.FK_TO_SRVCH
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
AND srvcharV.Fk_Entity = srvcharE.id
/
Kevin
|
|
|
Re: Tuning the sql query [message #572608 is a reply to message #571984] |
Thu, 13 December 2012 23:23 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi,
Please find the output from all the queries with Query.2 incomplete output and do the needful.
QUERY.1:
select count(*), 'tvreschar' new_table
from tv_res_char tvreschar
where tvreschar.Mainstatus not in ('S', 'M')
/
COUNT(*) NEW_TABLE
---------- ---------
11322823 tvreschar
Elapsed: 00:00:49.45
select count(*), 'ttrscchart' new_table
from tv_res_char tvreschar, tt_rscchar_has_rscchrt ttrscchart
where tvreschar.Mainstatus not in ('S', 'M')
and tvreschar.fk_entity = ttrscchart.fk_from_rscchar
/
COUNT(*) NEW_TABLE
---------- ---------
11322821 ttrscchart
Elapsed: 00:08:28.99
select count(*), 'treschartype' new_table
from tv_res_char tvreschar,
tt_rscchar_has_rscchrt ttrscchart,
t_reschar_type treschartype
where tvreschar.Mainstatus not in ('S', 'M')
and tvreschar.fk_entity = ttrscchart.fk_from_rscchar
and treschartype.id = ttrscchart.fk_to_rscchrt
/
COUNT(*) NEW_TABLE
---------- ------------
11322821 treschartype
Elapsed: 00:27:55.38
select count(*), 'ttfiarscchar' new_table
from tv_res_char tvreschar,
tt_rscchar_has_rscchrt ttrscchart,
t_reschar_type treschartype,
tt_fia_aaa_has_rscchar ttfiarscchar
where tvreschar.Mainstatus not in ('S', 'M')
and tvreschar.fk_entity = ttrscchart.fk_from_rscchar
and treschartype.id = ttrscchart.fk_to_rscchrt
and ttrscchart.FK_FROM_RSCCHAR = ttfiarscchar.FK_TO_RSCCHAR
/
COUNT(*) NEW_TABLE
---------- ------------
0 ttfiarscchar
Elapsed: 00:00:00.04
select count(*), 'tereschar' new_table
from tv_res_char tvreschar,
tt_rscchar_has_rscchrt ttrscchart,
t_reschar_type treschartype,
tt_fia_aaa_has_rscchar ttfiarscchar,
te_res_char tereschar
where tvreschar.Mainstatus not in ('S', 'M')
and tvreschar.fk_entity = ttrscchart.fk_from_rscchar
and treschartype.id = ttrscchart.fk_to_rscchrt
and ttrscchart.FK_FROM_RSCCHAR = ttfiarscchar.FK_TO_RSCCHAR
and tvreschar.fk_entity = tereschar.id
/
COUNT(*) NEW_TABLE
---------- ---------
0 tereschar
Elapsed: 00:00:00.03
Query.2:
select count(*), 'rc' new_table
from tv_res_char rc
where rc.mainstatus not in ('S', 'M')
/
COUNT(*) NE
---------- --
11322823 rc
Elapsed: 00:00:40.58
select count(*), 'hast' new_table
from tv_res_char rc, tt_rscchar_has_rscchrt hast
where rc.mainstatus not in ('S', 'M')
and rc.fk_entity = hast.fk_from_rscchar
/
COUNT(*) NEW_
---------- ----
11322821 hast
Elapsed: 00:39:21.30
select count(*), 'rct' new_table
from tv_res_char rc, tt_rscchar_has_rscchrt hast, t_reschar_type rct
where rc.mainstatus not in ('S', 'M')
and rc.fk_entity = hast.fk_from_rscchar
and hast.fk_to_rscchrt = rct.id
/
COUNT(*) NEW
---------- ---
11322821 rct
Elapsed: 00:10:35.69
select count(*), 'spechas' new_table
from tv_res_char rc,
tt_rscchar_has_rscchrt hast,
t_reschar_type rct,
tt_pifcfgs_has_rscchrt spechas
where rc.mainstatus not in ('S', 'M')
and rc.fk_entity = hast.fk_from_rscchar
and hast.fk_to_rscchrt = rct.id
and spechas.fk_to_rscchrt = hast.fk_to_rscchrt
/
COUNT(*) NEW_TAB
---------- -------
11322821 spechas
Elapsed: 00:06:23.18
Query.3:
select count(*), 'srvcharV' new_table
from tv_servicecharacteristic srvcharV
where srvcharV.Mainstatus not in ('S', 'M')
/
COUNT(*) NEW_TABL
---------- --------
42284624 srvcharV
Elapsed: 00:01:03.87
select count(*), 'cfshaschar' new_table
from tv_servicecharacteristic srvcharV, tt_cfs_haschar_srvch cfshaschar
where srvcharV.Mainstatus not in ('S', 'M')
and cfshaschar.FK_TO_SRVCH = srvcharV.FK_ENTITY
/
COUNT(*) NEW_TABLE
---------- ----------
8251804 cfshaschar
Elapsed: 01:36:17.32
select count(*), 'srvchhastype' new_table
from tv_servicecharacteristic srvcharV,
tt_cfs_haschar_srvch cfshaschar,
tt_srvch_hastype_srvchty srvchhastype
where srvcharV.Mainstatus not in ('S', 'M')
and cfshaschar.FK_TO_SRVCH = srvcharV.FK_ENTITY
AND srvchhastype.FK_FROM_SRVCH = cfshaschar.FK_TO_SRVCH
/
COUNT(*) NEW_TABLE
---------- ------------
8251804 srvchhastype
Elapsed: 02:25:24.47
select count(*), 'servicechartype' new_table
from tv_servicecharacteristic srvcharV,
tt_cfs_haschar_srvch cfshaschar,
tt_srvch_hastype_srvchty srvchhastype,
t_servicechartype servicechartype
where srvcharV.Mainstatus not in ('S', 'M')
and cfshaschar.FK_TO_SRVCH = srvcharV.FK_ENTITY
AND srvchhastype.FK_FROM_SRVCH = cfshaschar.FK_TO_SRVCH
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
/
COUNT(*) NEW_TABLE
---------- ---------------
8251804 servicechartype
Elapsed: 01:16:58.67
select count(*), 'srvcharE' new_table
from tv_servicecharacteristic srvcharV,
tt_cfs_haschar_srvch cfshaschar,
tt_srvch_hastype_srvchty srvchhastype,
t_servicechartype servicechartype,
te_servicecharacteristic srvcharE
where srvcharV.Mainstatus not in ('S', 'M')
and cfshaschar.FK_TO_SRVCH = srvcharV.FK_ENTITY
AND srvchhastype.FK_FROM_SRVCH = cfshaschar.FK_TO_SRVCH
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
AND srvcharV.Fk_Entity = srvcharE.id
/
COUNT(*) NEW_TABL
---------- --------
8251808 srvcharE
Elapsed: 10:41:57.89
Query.4:
select count(*), 'srvcharV' new_table
from tv_servicecharacteristic srvcharV
where srvcharV.Mainstatus not in ('S', 'M')
/
COUNT(*) NEW_TABL
---------- --------
42284624 srvcharV
Elapsed: 00:02:06.82
select count(*), 'rprsrvhaschar' new_table
from tv_servicecharacteristic srvcharV,
tt_rprsrv_haschar_srvch rprsrvhaschar
where srvcharV.Mainstatus not in ('S', 'M')
and rprsrvhaschar.fk_to_srvch = srvcharV.FK_ENTITY
/
COUNT(*) NEW_TABLE
---------- -------------
730076 rprsrvhaschar
Elapsed: 00:20:10.26
select count(*), 'srvchhastype' new_table
from tv_servicecharacteristic srvcharV,
tt_rprsrv_haschar_srvch rprsrvhaschar,
tt_srvch_hastype_srvchty srvchhastype
where srvcharV.Mainstatus not in ('S', 'M')
and rprsrvhaschar.fk_to_srvch = srvcharV.FK_ENTITY
AND srvchhastype.FK_FROM_SRVCH = rprsrvhaschar.fk_to_srvch
/
COUNT(*) NEW_TABLE
---------- ------------
730076 srvchhastype
Elapsed: 00:59:19.97
select count(*), 'servicechartype' new_table
from tv_servicecharacteristic srvcharV,
tt_rprsrv_haschar_srvch rprsrvhaschar,
tt_srvch_hastype_srvchty srvchhastype,
t_servicechartype servicechartype
where srvcharV.Mainstatus not in ('S', 'M')
and rprsrvhaschar.fk_to_srvch = srvcharV.FK_ENTITY
AND srvchhastype.FK_FROM_SRVCH = rprsrvhaschar.fk_to_srvch
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
/
COUNT(*) NEW_TABLE
---------- ---------------
730076 servicechartype
Elapsed: 00:44:52.06
select count(*), 'srvcharE' new_table
from tv_servicecharacteristic srvcharV,
tt_rprsrv_haschar_srvch rprsrvhaschar,
tt_srvch_hastype_srvchty srvchhastype,
t_servicechartype servicechartype,
te_servicecharacteristic srvcharE
where srvcharV.Mainstatus not in ('S', 'M')
and rprsrvhaschar.fk_to_srvch = srvcharV.FK_ENTITY
AND srvchhastype.FK_FROM_SRVCH = rprsrvhaschar.fk_to_srvch
AND servicechartype.id = srvchhastype.FK_TO_SRVCHTY
AND srvcharV.Fk_Entity = srvcharE.id
/
COUNT(*) NEW_TABL
---------- --------
730076 srvcharE
Elapsed: 00:44:32.66
Query.5:
select count(*), 'tvreschar' new_table
from tv_res_char tvreschar
where tvreschar.mainstatus not in ('S', 'M')
/
COUNT(*) NEW_TABLE
---------- ---------
11322823 tvreschar
Elapsed: 00:00:38.60
select count(*), 'ttrscchart' new_table
from tv_res_char tvreschar, tt_rscchar_has_rscchrt ttrscchart
where tvreschar.mainstatus not in ('S', 'M')
and tvreschar.fk_entity = ttrscchart.fk_from_rscchar
/
COUNT(*) NEW_TABLE
---------- ----------
11322821 ttrscchart
Elapsed: 00:30:53.10
select count(*), 'treschartype' new_table
from tv_res_char tvreschar,
tt_rscchar_has_rscchrt ttrscchart,
t_reschar_type treschartype
where tvreschar.mainstatus not in ('S', 'M')
and tvreschar.fk_entity = ttrscchart.fk_from_rscchar
and treschartype.id = ttrscchart.fk_to_rscchrt
/
COUNT(*) NEW_TABLE
---------- ------------
11322821 treschartype
Elapsed: 00:53:34.10
select count(*), 'ttdatarscchar' new_table
from tv_res_char tvreschar,
tt_rscchar_has_rscchrt ttrscchart,
t_reschar_type treschartype,
TT_DATAAAA_HAS_RSCCHAR ttdatarscchar
where tvreschar.mainstatus not in ('S', 'M')
and tvreschar.fk_entity = ttrscchart.fk_from_rscchar
and treschartype.id = ttrscchart.fk_to_rscchrt
and ttrscchart.FK_FROM_RSCCHAR = ttdatarscchar.FK_TO_RSCCHAR
/
COUNT(*) NEW_TABLE
---------- -------------
0 ttdatarscchar
Elapsed: 00:00:00.03
select count(*), 'tereschar' new_table
from tv_res_char tvreschar,
tt_rscchar_has_rscchrt ttrscchart,
t_reschar_type treschartype,
TT_DATAAAA_HAS_RSCCHAR ttdatarscchar,
te_res_char tereschar
where tvreschar.mainstatus not in ('S', 'M')
and tvreschar.fk_entity = ttrscchart.fk_from_rscchar
and treschartype.id = ttrscchart.fk_to_rscchrt
and ttrscchart.FK_FROM_RSCCHAR = ttdatarscchar.FK_TO_RSCCHAR
and tvreschar.fk_entity = tereschar.id
/
COUNT(*) NEW_TABLE
---------- ---------
0 tereschar
Elapsed: 00:00:00.02
Query.6:
select count(*), 'v_srvch' NEW_TABLE
from tv_servicecharacteristic v_srvch
where v_srvch.mainstatus not in ('S', 'M')
/
COUNT(*) NEW_TAB
---------- -------
42284624 v_srvch
Elapsed: 00:02:10.85
select count(*), 'e_srvch' NEW_TABLE
from tv_servicecharacteristic v_srvch, te_servicecharacteristic e_srvch
where v_srvch.mainstatus not in ('S', 'M')
and e_srvch.id = v_srvch.fk_entity
/
COUNT(*) NEW_TAB
---------- -------
42284632 e_srvch
Elapsed: 02:47:59.05
select count(*), 'tt_dlmsrv_srvch' NEW_TABLE
from tv_servicecharacteristic v_srvch,
te_servicecharacteristic e_srvch,
tt_dlmserv_haschar_srvch tt_dlmsrv_srvch
where v_srvch.mainstatus not in ('S', 'M')
and e_srvch.id = v_srvch.fk_entity
and tt_dlmsrv_srvch.fk_to_srvch = e_srvch.id
/
COUNT(*) NEW_TABLE
---------- ---------------
1859412 tt_dlmsrv_srvch
Elapsed: 03:43:07.54
select count(*), 'tt_servch_has_srvchtype' NEW_TABLE
from tv_servicecharacteristic v_srvch,
te_servicecharacteristic e_srvch,
tt_dlmserv_haschar_srvch tt_dlmsrv_srvch,
tt_srvch_hastype_srvchty tt_servch_has_srvchtype
where v_srvch.mainstatus not in ('S', 'M')
and e_srvch.id = v_srvch.fk_entity
and tt_dlmsrv_srvch.fk_to_srvch = e_srvch.id
and e_srvch.id = tt_servch_has_srvchtype.fk_from_srvch
/
COUNT(*) NEW_TABLE
---------- -----------------------
1859412 tt_servch_has_srvchtype
Elapsed: 02:47:29.64
select count(*), 't_servicechartype' NEW_TABLE
from tv_servicecharacteristic v_srvch,
te_servicecharacteristic e_srvch,
tt_dlmserv_haschar_srvch tt_dlmsrv_srvch,
tt_srvch_hastype_srvchty tt_servch_has_srvchtype,
t_servicechartype t_srvchtype
where v_srvch.mainstatus not in ('S', 'M')
and e_srvch.id = v_srvch.fk_entity
and tt_dlmsrv_srvch.fk_to_srvch = e_srvch.id
and e_srvch.id = tt_servch_has_srvchtype.fk_from_srvch
and tt_servch_has_srvchtype.fk_to_srvchty = t_srvchtype.id
/
COUNT(*) NEW_TABLE
---------- -----------------
1859412 t_servicechartype
Elapsed: 02:03:06.89
|
|
|
Re: Tuning the sql query [message #572652 is a reply to message #572608] |
Fri, 14 December 2012 10:54 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Once again, Excellent work. Here is a summary table.
QUERY# FROM CLAUSE ORDER ALIAS SECONDS MINUTES ROWS
QUERY.1 1 tvreschar 49 1 11322823
QUERY.1 2 ttrscchart 509 8 11322821
QUERY.1 3 treschartype 1676 28 11322821
QUERY.1 4 ttfiarscchar 0 0 0
QUERY.1 5 tereschar 0 0 0
Query.2 1 rc 41 1 11322823
Query.2 2 hast 2362 39 11322821
Query.2 3 rct 636 11 11322821
Query.2 4 spechas 383 6 11322821
Query.3 1 srvcharV 604 10 42284624
Query.3 2 cfshaschar 5778 96 8251804
Query.3 3 srvchhastype 10324 172 8251804
Query.3 4 servicechartype 4619 77 8251804
Query.3 5 srvcharE 38518 642 8251808
Query.4 1 srvcharV 127 2 42284624
Query.4 2 rprsrvhaschar 1210 20 730076
Query.4 3 srvchhastype 3560 59 730076
Query.4 4 servicechartype 2692 45 730076
Query.4 5 srvcharE 2673 45 730076
Query.5 1 tvreschar 39 1 11322823
Query.5 2 ttrscchart 1853 31 11322821
Query.5 3 treschartype 3214 54 11322821
Query.5 4 ttdatarscchar 0 0 0
Query.5 5 tereschar 0 0 0
Query.6 1 v_srvch 131 2 42284624
Query.6 2 e_srvch 10079 168 42284632
Query.6 3 tt_dlmsrv_srvch 13388 223 1859412
Query.6 4 tt_servch_has_srvchtype 10030 167 1859412
Query.6 5 t_servicechartype 7387 123 1859412
From this data we learn some things of note:
1) as we add tables, in general the time goes up but sometimes it goes down. When it goes down this is generally because of:
a) caching of data. The data we fetched from the last query is still in memory so this query does not suffere the I/O.
b) the query plan may have changed to a better plan.
2) joins in these queries result in dropped rows because of the joins. So as much as possible we want to see if we can reorder the joins to take advantage of these dropped rows because the right join order in this case will allow us to drop rows earlier in the query process thus keeping out intermediary row set sizes smaller which is good for joins. This is what ROSS was talking about. We will to do a reordering in a moment.
3) joins take most of the time of these queries, not the table scans. Thus we want if possible to make sure our joins are working as efficiently as possible. We can test this by using MANUAL memory management. More on this later.
So let us take one of these sub-queries and see if there is a valid join order based on your query diagram for it, which moves dropped rows much earlier in the processs. Query#6 looks like a good candidate.
select count(*), 't_servicechartype' NEW_TABLE
from tv_servicecharacteristic v_srvch,
te_servicecharacteristic e_srvch,
tt_dlmserv_haschar_srvch tt_dlmsrv_srvch,
tt_srvch_hastype_srvchty tt_servch_has_srvchtype,
t_servicechartype t_srvchtype
where v_srvch.mainstatus not in ('S', 'M')
and e_srvch.id = v_srvch.fk_entity
and tt_dlmsrv_srvch.fk_to_srvch = e_srvch.id
and e_srvch.id = tt_servch_has_srvchtype.fk_from_srvch
and tt_servch_has_srvchtype.fk_to_srvchty = t_srvchtype.id
/
COUNT(*) NEW_TABLE
---------- -----------------
1859412 t_servicechartype
Elapsed: 02:03:06.89
QUERY# FROM CLAUSE ORDER ALIAS SECONDS MINUTES ROWS
Query.6 1 v_srvch 131 2 42284624
Query.6 2 e_srvch 10079 168 42284632
Query.6 3 tt_dlmsrv_srvch 13388 223 1859412
Query.6 4 tt_servch_has_srvchtype 10030 167 1859412
Query.6 5 t_servicechartype 7387 123 1859412
tt_dlmsrv_srvch------------e_srvch---------v_srvch tt_servch_has_srvchtype t_srvchtype
| | | | |
| | | | |
| | | | |
| | | | |
+---------------|---------------+ +-----------------------+
|
|
c
ALIAS FILTERED ROW COUNT
tt_dlmsrv_srvch 1859412
e_srvch 42280778
v_srvch 42284528
tt_servch_has_srvchtype 42280766
t_srvchtype 159
Making note of the filtered rowcount you provided, and the joins as seen in your reconstruction queries, we see that the join from v_srvch --> e_srvch drops no rows but that the join from e_srvch --> tt_dlmsrv_srvch drops a considerable number of rows.
So it MAY make sense to start with TT_DLMSRV_SRVCH and then do the join to E_SRVCH and then do the rest of the query so that we drop as many rows as we can right away. We want the query plan to look like this I believe:
PLAN_TABLE_OUTPUT
----------------------------------------------------------
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
| 2 | TABLE ACCESS FULL | T_SRVCHTYPE |
|* 3 | HASH JOIN | |
| 4 | TABLE ACCESS FULL | TT_SERVCH_HAS_SRVCHTYPE |
|* 5 | HASH JOIN | |
| 6 | TABLE ACCESS FULL | V_SRVCH |
|* 7 | HASH JOIN | |
| 8 | TABLE ACCESS FULL| TT_DLMSRV_SRVCH |
| 9 | TABLE ACCESS FULL| E_SRVCH |
----------------------------------------------------------
Which we can achieve with an order hint. The above query plan shows what looks like hash joins doin in memory and has a join order we believe MAY drop rows early). To test this idea, we can use the order hint to force a table join order. Please run this query and tells us the timing.
create table work6
nologging
as
select /*+ order */
v_srvch.id as ID,
e_srvch.creationdt as CREATION_DATE,
e_srvch.lastupdatedt as LAST_MODIFIED_DATE_TIME,
v_srvch.versionindex as VERSION_INDEX,
v_srvch.mainstatus as MAIN_STATUS,
v_srvch.mainstatusdate as MAIN_STATUS_DATE,
t_srvchtype.nm as NAME,
v_srvch.nm as VALUE,
null AS FK_AAA_ACCOUNT_ID,
null AS FK_PROV_LOG_IFC_CONFIG_ID,
null AS FK_CFS_ID,
null AS AAA_ATTRIBUTE_TYPE,
null as FK_REPAIRSERVICE_ID,
tt_dlmsrv_srvch.fk_from_dlmserv as FK_DLM_SERVICE_ID
from tt_dlmserv_haschar_srvch tt_dlmsrv_srvch,
te_servicecharacteristic e_srvch,
tv_servicecharacteristic v_srvch,
tt_srvch_hastype_srvchty tt_servch_has_srvchtype,
t_servicechartype t_srvchtype
where tt_dlmsrv_srvch.fk_to_srvch = e_srvch.id
and e_srvch.id = v_srvch.fk_entity
and e_srvch.id = tt_servch_has_srvchtype.fk_from_srvch
and tt_servch_has_srvchtype.fk_to_srvchty = t_srvchtype.id
and v_srvch.mainstatus not in ('S','M')
/
Couriously enough, these is the order listed in your original query which makes me thing someone might have been thinking about this before hand. In any event, the idea here is that the first join done will remove most of the rows from the query. If this is so, then this will create smaller intermediary rowsets for at least some of the steps and thus facilitate doing the hash joins.
You will recall I also noted that we want to see how getting the hash joins to run in memory would change things because it looks like (at least based on the query plan) that you may be using temp space which means either one-pass (OK) or multi-pass (BAD) hash joins. To affect this we can use the following commands:
show parameter area
alter session set workarea_size_policy=manual;
alter session set hash_area_size=2147483647;
show parameter area
These commands will allocate the maximum memory allowed to hash areas for a non-parallel query plan. So after you run the create table above, set these parameters and rerun the create table again to see if giving more memory to the joins helps. I am not suggesting manual memory management as a permanent solution, but we are trying to see what can be done to improve performance of the query and this is a fast way to learn if hash joins can be improved. I suggest you open up two SQL*Plus sessions so that you can set the parameters and do testing with them on one session while keeping the original values in your first session.
Kevin
|
|
|
|
Re: Tuning the sql query [message #572731 is a reply to message #572652] |
Mon, 17 December 2012 00:45 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Kevin,
Did you mean to put /*+ ORDERED */ or /*+ ORDER */
With a run time of around 4 minutes it seems to have gone OK, but I didn't know ORDER was a real hint.
Ross Leishman
|
|
|
|
Re: Tuning the sql query [message #572785 is a reply to message #572757] |
Mon, 17 December 2012 11:08 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
ROSS, I need you to keep me honest here.
grpatwari So... the timing of your query.6 went from 2 hours to 4 minutes is that correct?
Please for the benefit of everyone else, provide the plan for the query that takes four minutes so that we can compare it to the original plan. A simple explain plan will suffice. Please do this for both the original and the ORDERED version of query.6.
Next of course, which you may have already started doing, is to look at the other parts of your big query to see if there are places where any of them can be reordered in the same way. Look at the join order of the plan for each of these and see if a reordering makes sense. You should understand why this goes faster. There are two possible reasons.
1) caching of data. It may be your disk drives or Oracle has done a great job of caching blocks needed for this query (especially since you may have been running it several times in a row) such that you are getting data real fast because you don't need to actually do a disk read. If this is the reason for your better performance then we did not do anything good with the reordering or joins.
2) you have removed most of the data from the query early on because of the new join order. This in turn has reduced the number of joins you need to do in at least one subsequent step and made at least one of the hash joins you needed to do a lot smaller and thus likely turned it into an OPTIMAL hash join instead of a MULTI-PASS hash join.
The reordering was intended to achieve #2.
Additionally you may want to check to see if the joins were done in memory (OPTIMAL) or not. If not, try to increase the memory needed to do the hash joins (test this idea using the manual memory management steps I noted).
From here, our conversation can go a lot of places. For example: consider these two variations of the query plan for our query.
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
| 2 | TABLE ACCESS FULL | T_SRVCHTYPE |
|* 3 | HASH JOIN | |
| 4 | TABLE ACCESS FULL | TT_SERVCH_HAS_SRVCHTYPE |
|* 5 | HASH JOIN | |
| 6 | TABLE ACCESS FULL | V_SRVCH |
|* 7 | HASH JOIN | |
| 8 | TABLE ACCESS FULL| TT_DLMSRV_SRVCH |
| 9 | TABLE ACCESS FULL| E_SRVCH |
----------------------------------------------------------
vs.
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | HASH JOIN | |
|* 3 | HASH JOIN | |
|* 4 | HASH JOIN | |
| 5 | TABLE ACCESS FULL| TT_DLMSRV_SRVCH |
| 6 | TABLE ACCESS FULL| E_SRVCH |
| 7 | TABLE ACCESS FULL | V_SRVCH |
| 8 | TABLE ACCESS FULL | TT_SERVCH_HAS_SRVCHTYPE |
| 9 | TABLE ACCESS FULL | T_SRVCHTYPE |
----------------------------------------------------------
These two plans look like they do the same thing. For the same query and thus the same five tables, we are using the same join methods to join the same pairs of tables. By this I mean that both variations take the same row sets and join them together in the same pairings.
((((TT_DLMSRV_SRVCH,E_SRVCH), V_SRVCH), TT_SERVCH_HAS_SRVCHTYPE), T_SRVCHTYPE)
But there is a big difference between the two plan variations as the join pairings are done differently. Here is the join-pairing notation for each of the query plan variations.
1) hj(hj(hj(hj(E_SRVCH, TT_DLMSRV_SRVCH), V_SRVCH), TT_SERVCH_HAS_SRVCHTYPE), T_SRVCHTYPE) vs.
2) hj(T_SRVCHTYPE, hj(TT_SERVCH_HAS_SRVCHTYPE, hj(V_SRCH, hj(E_SRVCH,TT_DLMSRV_SRVCH))))
Yeah, this looks cryptic, but it shows the difference is what is the PROBING ROW-SET vs. what is the PROBED ROW-SET of each HASH JOIN.
Quote:The first thing to know about HASH JOINS is that one of the tables is in memory and the other is scanned. The table (or intermediary result set) that is going to be in memory is the table (or intermediary result set) that sits directly under the HASH JOIN line in the query plan.
Quote:The second thing to know about queries with mutliple hash joins, is that understanding the order of operations in the query follows the same rules as all other queries. A hash join does not invoke any additional or alernate rules in reading the query plan.
Putting these two pieces of information together we can read these two plans and see how they are very different.
Imagine if you please, the Optimizer and the Query Plan having a conversation about doing the query. The first plan reads like this.
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
| 2 | TABLE ACCESS FULL | T_SRVCHTYPE |
|* 3 | HASH JOIN | |
| 4 | TABLE ACCESS FULL | TT_SERVCH_HAS_SRVCHTYPE |
|* 5 | HASH JOIN | |
| 6 | TABLE ACCESS FULL | V_SRVCH |
|* 7 | HASH JOIN | |
| 8 | TABLE ACCESS FULL| TT_DLMSRV_SRVCH |
| 9 | TABLE ACCESS FULL| E_SRVCH |
----------------------------------------------------------
Quote:OPT: I am ready when you are.
QP: Then, lets get started.
OPT: can I do line 0 SELECT STATEMENT?
QP: no, first you have to do line 1 HASH JOIN.
OPT: then can I do line 1 HASH JOIN?
QP: no, first you have to do lines 2 TABLE ACCESS FULL T_SRVCHTYPE and 3 HASH JOIN.
OPT: then can I do line 2 TABLE ACCESS FULL T_SRVCHTYPE?
QP: yes.
OPT: I did line 2 T_SRVCHTYPE; I scaned table T_SRVCHTYPE and loaded it into memory as a hash table.
So now can I do line 3 HASH JOIN?
QP: no, first you have to do lines 4 TABLE ACCESS FULL TT_SERVCH_HAS_SRVCHTYPE and 5 HASH JOIN.
OPT: then can I do line 4 TABLE ACCESS FULL TT_SERVCH_HAS_SRVCHTYPE?
QP: yes.
OPT: I did line 4 TABLE ACCESS FULL TT_SERVCH_HAS_SRVCHTYPE; I scanned table TT_SERVCH_HAS_SRVCHTYPE and loaded it into memory as a hash table.
So now can I do line 5 HASH JOIN?
QP: no, first you have do lines 6 TABLE ACCESS FULL V_SRVCH and 7 HASH JOIN.
OPT: then can I do line 6 TABLE ACCESS FULL V_SRVCH?
QP: yes.
OPT: I did line 6 TABLE ACCESS FULL V_SRVCH; I scanned table V_SRVCH and loaded it into memory as a hash table.
So now can I do line 7 HASH JOIN.
QP: no, first you have to do lines 8 TABLE ACCESS FULL TT_DLMSRV_SRVCH and 9 TABLE ACCESS FULL E_SRVCH.
OPT: then can I do line 8 TABLE ACCESS FULL TT_DLMSRV_SRVCH?
QP: yes.
OPT: I did line 8 TABLE ACCESS FULL TT_DLMSRV_SRVCH; I scanned the table TT_DLMSRV_SRVCH and loaded into memory as a hash table.
So now can I do line 9 TABLE ACCESS FULL E_SRVCH?
QP: yes.
OPT: I did line 9 TABLE ACCESS FULL E_SRVCH, I scanned this table. And guess what. You are gonna be so proud of me Q. As I was scanning table E_SRVCH, I noted that all the other lines I had left to do:
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 3 | HASH JOIN | |
|* 5 | HASH JOIN | |
|* 7 | HASH JOIN | |
They had all their stuff sitting in memory waiting for me. So as I read a row off of E_SRVCH, I did the hash join lookup for 7 and then the hash join lookup for 5 and then the hash join lookup for 3 and then the hash join lookup for 1 and then sent the row I had back to the caller line 0 since that row was done. I did not have to write anything out to TEMP SPACE.
QP: nice one O. You are pretty smart.
OPT: thanks Q.
The nature and sequence of joins is dictated of course the by joins between tables and the DRIVING TABLE and JOIN ORDER selected for the query. We see in plan variation #1 that the query plan believes that the first four tables will fit in memory together thus giving the optimizer the opportunity to do a series of HASH JOINS back to back. Oracle says that it does not join more than two tables at a time, but I am not sure how to interpret that statement because in this plan it sure looks to me like it is doing four joins at the same time and I can verify this by looking at the TEMP SPACE usage via V$SQL_WORKAREA_ACTIVE. With plans like this, there is no TEMP SPACE usage and you never see a HASH JOIN step in V$SESSION_LONGOPS. I wont' get into that here as I am running out of time.
Lets just say that the imaginary conversation we might have beween the Optimizer and the Query Plan will be different for plan variation #2. Looking at the two variations of join pairings again.
1) hj(hj(hj(hj(E_SRVCH, TT_DLMSRV_SRVCH), V_SRVCH), TT_SERVCH_HAS_SRVCHTYPE), T_SRVCHTYPE) vs.
2) hj(T_SRVCHTYPE, hj(TT_SERVCH_HAS_SRVCHTYPE, hj(V_SRCH, hj(E_SRVCH,TT_DLMSRV_SRVCH))))
It should be a bit clearer now that the PROBING ROW-SETS and PROBED ROW-SETS are different, and they are:
variation #1probing row-set probed row-set
E_SRVCH TT_DLMSRV_SRVCH
(E_SRVCH, TT_DLMSRV_SRVCH) V_SRVCH
((E_SRVCH, TT_DLMSRV_SRVCH), V_SRVCH)) TT_SERVCH_HASH_SRVCHTYPE
(((E_SRVCH, TT_DLMSRV_SRVCH), V_SRVCH), TT_SERVCH_HAS_SRVCHTYPE))) T_SRVCHTYPE
Whereas the for query plan #2, the PROBING and PROBED ROW-SETS are:
variation #2probing row-set probed row-set
E_SRVCH TT_DLMSRV_SRVCH
V_SRCH TT_DLMSRV_SRVCH,E_SRVCH
TT_SERVCH_HAS_SRVCHTYPE TT_DLMSRV_SRVCH,E_SRVCH,V_SRCH
T_SRVCHTYPE TT_DLMSRV_SRVCH,E_SRVCH,V_SRCH,TT_SERVCH_HAS_SRVCHTYPE
IT IS THE PROBED ROW-SETS THAT SIT IN MEMORY AS HASH TABLES.
Keep in mind, that for both query plans, each HASH JOIN could be done as OPTIMAL. For an OPTIMAL HASH JOIN, we need the PROBED ROW-SET to fit into memory. But the difference between these two query plans is what is put in memory.
We see that in plan variation #1, our simple tables are sitting in memory, and that Oracle chose to put them in memory all at once because it thinks it can fit them all in memory at one time.
In our second query plan, Oracle is taking intermediary results and re-hashing them back into memory after each hash join completes, so that it can do the next hash join. I am going to stick my neck out a little bit here and say that this ususally happens when either a) JOIN ORDER requires intermediary joins to be hashed, or b) the Optimizer thinks that the intermediary row-set will not fit in memory.
The basic point though is that query variation #2 potentially has a lot more stuff being hashed into memory. Consider that:
query variation #2 does this join first first: E_SRVCH --> TT_DLMSRV_SRVCH.
query variation #1 does the same join first: E_SRVCH --> TT_DLMSRV_SRVCH.
But for query variation #2, as rows come out of the join, it must re-hash these rows and write them back into memory somewhere else as the HASH TABLE for the next join. There are two things to note about this:
1) this might make no difference what-so-ever. Each of the two variations has four HASH TABLES. Indeed, variation #1 require four hash tables to be in memory at the same time whereas variation #2 needs only two hash tables in memory at any given moment, one for the current join and one for the next join, so one might think that query variation #2 has an edge. If all joins are OPTIMAL for both variations then this variation is just as fast as the first one.
2) OR, it might make a lot of difference. PROBE tables in a HASH JOIN (those row-sets hashed in memory) include all the columns needed to satisfy the query, not just the join columns. The HASH TABLES being built in variation #2 are not based on simple tables, but on the results of previous joins and as such, each subsequence HASH TABLES will increase in size with each join as compared to the previous HASH TABLE, thus requiring more and more memory as the query progresses. In such a sequence of HASH JOINS as we see in variation #2, each subsequence HASH TABLE includes all columns needed from the prior HASH TABLE, plus any additional columns needed for the answer set. For wide rows in the final result set, this will eventually lead very large HASH TABLES which do not fit in memory and thus the need to write HASH TABLES out to TEMP SPACE. If this happens you get into ONE-PASS (these are OK) and MULTI-PASS (poor performing) HASH JOINS. MULT-PASS HASH JOINS are real slow.
Quote:This leads us to the third thing we want to know about HASH JOINS: OPTIMAL is great, ONE-PASS is pretty good, MULTI-PASS means much slower joins. We want to examine V$SQL_WORKAREA_ACTIVE or use some other check, to see how these hash joins are performing and if possible, do something about it when we see MULTI-PASS on a query that we need to go a lot faster.
I'll leave the rest of this conversation for my book, if I ever get it done and because this post is getting so long that no-one is likely to finish reading it all.
With your permission grpatwari, I might just use this example in the book I am writing.
ROSS, what say you. Did I mis-represent anything here? Do you wish to add additional detail?
Kevin
|
|
|
|
|
|
|
Re: Tuning the sql query [message #572827 is a reply to message #572812] |
Tue, 18 December 2012 01:02 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Please find the query.6 output.
Without Ordered hint Query.6 timings and explain plan:
SQL> create table work6
Elapsed: 00:05:50.25
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 3323635765
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1815K| 171M| | 499K (7)| 01:39:52 |
|* 1 | HASH JOIN | | 1815K| 171M| | 499K (7)| 01:39:52 |
| 2 | TABLE ACCESS FULL | T_SERVICECHARTYPE | 159 | 2703 | | 3 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 1815K| 142M| 107M| 499K (7)| 01:39:50 |
|* 4 | HASH JOIN | | 1815K| 86M| 84M| 310K (6)| 01:02:09 |
|* 5 | HASH JOIN | | 1815K| 64M| 45M| 194K (6)| 00:38:53 |
| 6 | TABLE ACCESS FULL| TT_DLMSERV_HASCHAR_SRVCH | 1815K| 24M| | 2139 (9)| 00:00:26 |
| 7 | TABLE ACCESS FULL| TE_SERVICECHARACTERISTIC | 42M| 924M| | 115K (5)| 00:23:12 |
| 8 | TABLE ACCESS FULL | TT_SRVCH_HASTYPE_SRVCHTY | 41M| 518M| | 58642 (8)| 00:11:44 |
|* 9 | TABLE ACCESS FULL | TV_SERVICECHARACTERISTIC | 42M| 1283M| | 91304 (9)| 00:18:16 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TT_SERVCH_HAS_SRVCHTYPE"."FK_TO_SRVCHTY"="T_SRVCHTYPE"."ID")
3 - access("E_SRVCH"."ID"="V_SRVCH"."FK_ENTITY")
4 - access("E_SRVCH"."ID"="TT_SERVCH_HAS_SRVCHTYPE"."FK_FROM_SRVCH")
5 - access("TT_DLMSRV_SRVCH"."FK_TO_SRVCH"="E_SRVCH"."ID")
9 - filter("V_SRVCH"."MAINSTATUS"<>'S' AND "V_SRVCH"."MAINSTATUS"<>'M')
25 rows selected.
Elapsed: 00:00:01.31
With Ordered Hint Query.6 timings and explain.
SQL> create table work6
2 nologging
3 as
4 select /*+ ordered */
5 v_srvch.id as ID,
6 e_srvch.creationdt as CREATION_DATE,
7 e_srvch.lastupdatedt as LAST_MODIFIED_DATE_TIME,
8 v_srvch.versionindex as VERSION_INDEX,
9 v_srvch.mainstatus as MAIN_STATUS,
10 v_srvch.mainstatusdate as MAIN_STATUS_DATE,
11 t_srvchtype.nm as NAME,
12 v_srvch.nm as VALUE,
13 cast(null AS number) FK_AAA_ACCOUNT_ID,
14 cast(null AS number) FK_PROV_LOG_IFC_CONFIG_ID,
15 cast(null AS number) FK_CFS_ID,
16 cast(null AS number) AAA_ATTRIBUTE_TYPE,
17 cast(null AS number) FK_REPAIRSERVICE_ID,
18 tt_dlmsrv_srvch.fk_from_dlmserv as FK_DLM_SERVICE_ID
19 from tt_dlmserv_haschar_srvch tt_dlmsrv_srvch,
20 te_servicecharacteristic e_srvch,
21 tv_servicecharacteristic v_srvch,
22 tt_srvch_hastype_srvchty tt_servch_has_srvchtype,
23 t_servicechartype t_srvchtype
24 where tt_dlmsrv_srvch.fk_to_srvch = e_srvch.id
25 and e_srvch.id = v_srvch.fk_entity
26 and e_srvch.id = tt_servch_has_srvchtype.fk_from_srvch
and tt_servch_has_srvchtype.fk_to_srvchty = t_srvchtype.id
27 28 and v_srvch.mainstatus not in ('S','M')
29 /
Table created.
Elapsed: 00:03:56.91
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 851351609
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1815K| 171M| | 500K (7)| 01:40:11 |
|* 1 | HASH JOIN | | 1815K| 171M| | 500K (7)| 01:40:11 |
| 2 | TABLE ACCESS FULL | T_SERVICECHARTYPE | 159 | 2703 | | 3 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 1815K| 142M| 140M| 500K (7)| 01:40:10 |
|* 4 | HASH JOIN | | 1815K| 119M| 84M| 381K (6)| 01:16:20 |
|* 5 | HASH JOIN | | 1815K| 64M| 45M| 194K (6)| 00:38:53 |
| 6 | TABLE ACCESS FULL| TT_DLMSERV_HASCHAR_SRVCH | 1815K| 24M| | 2139 (9)| 00:00:26 |
| 7 | TABLE ACCESS FULL| TE_SERVICECHARACTERISTIC | 42M| 924M| | 115K (5)| 00:23:12 |
|* 8 | TABLE ACCESS FULL | TV_SERVICECHARACTERISTIC | 42M| 1283M| | 91304 (9)| 00:18:16 |
| 9 | TABLE ACCESS FULL | TT_SRVCH_HASTYPE_SRVCHTY | 41M| 518M| | 58642 (8)| 00:11:44 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TT_SERVCH_HAS_SRVCHTYPE"."FK_TO_SRVCHTY"="T_SRVCHTYPE"."ID")
3 - access("E_SRVCH"."ID"="TT_SERVCH_HAS_SRVCHTYPE"."FK_FROM_SRVCH")
4 - access("E_SRVCH"."ID"="V_SRVCH"."FK_ENTITY")
5 - access("TT_DLMSRV_SRVCH"."FK_TO_SRVCH"="E_SRVCH"."ID")
8 - filter("V_SRVCH"."MAINSTATUS"<>'S' AND "V_SRVCH"."MAINSTATUS"<>'M')
25 rows selected.
Elapsed: 00:00:00.05
|
|
|
Re: Tuning the sql query [message #572887 is a reply to message #572827] |
Tue, 18 December 2012 07:22 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Well that is telling. These plans are almost identical and are the same or similar too the original query plan results you provided way back in the begining. So I make the following commentary:
1) so far we have had a good conversation and people are learning a lot... but also we have really not fixed anything.
2) though the ORDERED variation of the plan looks better by a few minutes, this could easily be explained by reduced I/O if you ran this version of query.6 second and thus had prefilled some block data in your disk drive cache. You can get some insight into this by runing both create table versions multiple times back to back.
3) you will notice that both plans show a combination of hased tables and re-hashed intermediary row-sets. As I indidicated, a re-hased intermediary row-set is not necessarily bad. The HASH JOINS at steps (3,4,5) all show use of TEMP STORAGE as expected by the Optimizer. But this could be a ONE-PASS scenario which is reasonable. It could also be that Oracle guessed wrong and did not in fact need any temp storage. We should monitor V$SQL_WORKAREA_ACTIVE to see if temp space is actually allocated to these workareas at runtime.
4) recall that this was the runtime summary for the six sub-queries.
WORK TABLE TIMING seconds rows
work1 00:00.0 0 0
work2 17:32.0 1052 10541598
work3 27:22.0 1642 8251708
work4 04:11.0 251 730076
work5 00:01.0 1 0
work6 05:50.0 350 1859412
Note that most of the time is being spent in query.2 and query.3. I left these for you to try to see if you could gain any advantage via join reordering.
5) MOST IMPORTANTLY, recall that the opening post said that this query was taking 1.5 hours or 90 miutes to run. If you add up these times you see that we can account for only 50 minutes of runtime here. To me this suggests the SORT UNIQUE step in the original query is taking half the time of this query so we need at some point to address sorting. Additionaly these plans all show that TEMP SPACE is expected to be used for the hash joins too. If we are going to address the sort by reducing or eliminating its need for TEMP SPACE, then we should do the same for HASH JOINS as well.
So what do we do next:
1) we recognize that Oracle is pretty good and figuring out for itself what we have been discussing and appears (at least for query.6) to have adopted a good join order already.
2) we need to check query.2 and query.3 to see if a join reordering makes sense for them.
3) we need to test the benefit of increased workarea sizes for hash join and sorts. Because this query does not filter away a lot of data initially, and because its join order may already be one which takes advantage early in the query of row loss due to joins, we may have to look somewhere else for time savings (? reduce temp space usage ?). Below I show how to set up the test for reducing TEMP SPACE usage of HASH JOINS and SORTS. I have allocated the maximum work area sizes for hash join and sorts using manual memory management. Just because this is a maximum, does not mean the max will be allocated. Oracle will only take what it needs. Do these steps in a SQL*Plus session and rerun the six mini-queries, and also rerun the big query (we want to see how this change affects the big SORT UNIQUE too).
08:09:00 SQL> alter session set workarea_size_policy=manual;
Session altered.
Elapsed: 00:00:00.15
08:09:09 SQL> select 1024*1024*1024*2-1 from dual;
1024*1024*1024*2-1
------------------
2147483647
1 row selected.
Elapsed: 00:00:00.12
08:09:17 SQL> alter session set hash_area_size=2147483647;
Session altered.
Elapsed: 00:00:00.06
08:09:29 SQL> alter session set sort_area_size=2147483647;
Session altered.
Elapsed: 00:00:00.06
08:09:41 SQL>
create table work1.
...
create table work_full_query...
We may not choose to run this query using manual memory management, but this test will give us some idea as to if increasing the memory used for HASH JOINS and SORTS will have any significant impact on our query runtimes.
Kevin
|
|
|
Re: Tuning the sql query [message #573342 is a reply to message #572887] |
Wed, 26 December 2012 23:34 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi,
Please find the timings.
SQL> create table work1
......
Table created.
Elapsed: 00:00:01.25
SQL> create table work_full_query
....
Table created.
Elapsed: 07:59:17.38
|
|
|
|
Re: Tuning the sql query [message #573545 is a reply to message #571984] |
Mon, 31 December 2012 02:47 |
grpatwari
Messages: 288 Registered: June 2008 Location: Hyderabad
|
Senior Member |
|
|
Hi,
Please find the query timings and plan for all the queries after changing work area size and has area size.
Query 1:
SQL> create table work1
.....
Table created.
Elapsed: 00:00:00.46
SQL> explain plan for --Query1
...........
Explained.
Elapsed: 00:00:01.08
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1600672636
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 5 (0) | 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 132 | 5 (0) | 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 100 | 4 (0) | 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 77 | 3 (0) | 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 39 | 2 (0) | 00:00:01 |
| 5 | INDEX FULL SCAN | SYS_C0054827 | 1 | 26 | 1 (0) | 00:00:01 |
|* 6 | INDEX RANGE SCAN | SYS_C0050999 | 1 | 13 | 1 (0) | 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | TV_RES_CHAR | 1 | 38 | 1 (0) | 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_ISI4533 | 1 | | 1 (0) | 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | TE_RES_CHAR | 1 | 23 | 1 (0) | 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | SYS_C0040129 | 1 | | 1 (0) | 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | T_RESCHAR_TYPE| 1 | 32 | 1 (0) | 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | SYS_C0042350 | 1 | | 1 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
6 - access("TTRSCCHART"."FK_FROM_RSCCHAR"="TTFIARSCCHAR"."FK_TO_RSCCHAR")
7 - filter("TVRESCHAR"."MAINSTATUS"<>'S' AND "TVRESCHAR"."MAINSTATUS"<>'M')
8 - access("TVRESCHAR"."FK_ENTITY"="TTRSCCHART"."FK_FROM_RSCCHAR")
10 - access("TVRESCHAR"."FK_ENTITY"="TERESCHAR"."ID")
12 - access("TRESCHARTYPE"."ID"="TTRSCCHART"."FK_TO_RSCCHRT")
28 rows selected.
Elapsed: 00:00:02.35
Query 2:
SQL> create table work2
............
Table created.
Elapsed: 00:23:45.21
SQL> explain plan for --Query2
...........
Explained.
Elapsed: 00:00:00.53
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1929446883
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 202K| 26M| 94061 (13)| 00:18:49 |
|* 1 | HASH JOIN | | 202K| 26M| 94061 (13)| 00:18:49 |
|* 2 | HASH JOIN | | 202K| 22M| 61345 (16)| 00:12:17 |
| 3 | TABLE ACCESS FULL | T_RESCHAR_TYPE | 107 | 1926 | 2 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 202K| 18M| 61333 (16)| 00:12:16 |
|* 5 | HASH JOIN | | 19M| 1565M| 59479 (13)| 00:11:54 |
| 6 | INDEX FULL SCAN | SYS_C0059288 | 96 | 1152 | 1 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 9475K| 650M| 58596 (12)| 00:11:44 |
|* 8 | HASH JOIN RIGHT ANTI | | 9468K| 307M| 26222 (12)| 00:05:15 |
| 9 | MAT_VIEW ACCESS FULL| WMV_FREE_PROFILES | 787K| 5380K| 616 (11)| 00:00:08 |
|* 10 | HASH JOIN | | 11M| 288M| 25047 (11)| 00:05:01 |
| 11 | TABLE ACCESS FULL | TT_PIFCFG_HASSPEC_PIFCFGS | 5079K| 62M| 7419 (7)| 00:01:30 |
| 12 | TABLE ACCESS FULL | TT_PIFCFG_HAS_RSCCHAR | 11M| 149M| 16778 (7)| 00:03:22 |
|* 13 | TABLE ACCESS FULL | TV_RES_CHAR | 11M| 407M| 31223 (8)| 00:06:15 |
|* 14 | INDEX UNIQUE SCAN | SYS_C0050999 | 1 | 13 | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | TE_RES_CHAR | 11M| 247M| 32193 (5)| 00:06:27 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
1 - access("RCE"."ID"="RC"."FK_ENTITY")
2 - access("HAST"."FK_TO_RSCCHRT"="RCT"."ID")
5 - access("HASSPEC"."FK_TO_PIFCFGS"="SPECHAS"."FK_FROM_PIFCFGS")
7 - access("HAS"."FK_TO_RSCCHAR"="RC"."FK_ENTITY")
8 - access("WFP"."FK_PIFCFG"="HASSPEC"."FK_FROM_PIFCFG")
10 - access("HASSPEC"."FK_FROM_PIFCFG"="HAS"."FK_FROM_PIFCFG")
13 - filter("RC"."MAINSTATUS"<>'S' AND "RC"."MAINSTATUS"<>'M')
14 - access("RC"."FK_ENTITY"="HAST"."FK_FROM_RSCCHAR" AND "SPECHAS"."FK_TO_RSCCHRT"="HAST"."FK_TO_RSCCHRT")
35 rows selected.
Elapsed: 00:00:00.04
Query 3:
SQL> create table work3
............
Table created.
Elapsed: 00:10:17.82
SQL> explain plan for --Query3
...........
Explained.
Elapsed: 00:00:01.95
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3075275129
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8203K| 774M| 287K (10)| 00:57:33 |
|* 1 | HASH JOIN | | 8203K| 774M| 287K (10)| 00:57:33 |
|* 2 | HASH JOIN | | 8203K| 594M| 169K (11)| 00:33:51 |
|* 3 | HASH JOIN | | 8203K| 344M| 75474 (11)| 00:15:06 |
| 4 | TABLE ACCESS FULL | T_SERVICECHARTYPE | 159 | 2703 | 3 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 8203K| 211M| 75100 (11)| 00:15:02 |
| 6 | TABLE ACCESS FULL| TT_CFS_HASCHAR_SRVCH | 8203K| 109M| 14014 (7)| 00:02:49 |
| 7 | TABLE ACCESS FULL| TT_SRVCH_HASTYPE_SRVCHTY | 41M| 518M| 58642 (8)| 00:11:44 |
|* 8 | TABLE ACCESS FULL | TV_SERVICECHARACTERISTIC | 42M| 1283M| 91304 (9)| 00:18:16 |
| 9 | TABLE ACCESS FULL | TE_SERVICECHARACTERISTIC | 42M| 924M| 115K (5)| 00:23:12 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SRVCHARV"."FK_ENTITY"="SRVCHARE"."ID")
2 - access("CFSHASCHAR"."FK_TO_SRVCH"="SRVCHARV"."FK_ENTITY")
3 - access("SERVICECHARTYPE"."ID"="SRVCHHASTYPE"."FK_TO_SRVCHTY")
5 - access("SRVCHHASTYPE"."FK_FROM_SRVCH"="CFSHASCHAR"."FK_TO_SRVCH")
8 - filter("SRVCHARV"."MAINSTATUS"<>'S' AND "SRVCHARV"."MAINSTATUS"<>'M')
25 rows selected.
Elapsed: 00:00:00.04
Query 4:
SQL> create table work4
............
Table created.
Elapsed: 00:04:27.36
SQL> explain plan for --Query4
...........
Explained.
Elapsed: 00:00:00.21
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 809886175
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 732K| 69M| 272K (9)| 00:54:36 |
|* 1 | HASH JOIN | | 732K| 69M| 272K (9)| 00:54:36 |
|* 2 | HASH JOIN | | 732K| 53M| 155K (11)| 00:31:01 |
|* 3 | HASH JOIN | | 732K| 30M| 61794 (11)| 00:12:22 |
| 4 | TABLE ACCESS FULL | T_SERVICECHARTYPE | 159 | 2703 | 3 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 732K| 18M| 61757 (11)| 00:12:22 |
| 6 | TABLE ACCESS FULL| TT_RPRSRV_HASCHAR_SRVCH | 732K| 9M| 1177 (7)| 00:00:15 |
| 7 | TABLE ACCESS FULL| TT_SRVCH_HASTYPE_SRVCHTY | 41M| 518M| 58642 (8)| 00:11:44 |
|* 8 | TABLE ACCESS FULL | TV_SERVICECHARACTERISTIC | 42M| 1283M| 91304 (9)| 00:18:16 |
| 9 | TABLE ACCESS FULL | TE_SERVICECHARACTERISTIC | 42M| 924M| 115K (5)| 00:23:12 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SRVCHARV"."FK_ENTITY"="SRVCHARE"."ID")
2 - access("RPRSRVHASCHAR"."FK_TO_SRVCH"="SRVCHARV"."FK_ENTITY")
3 - access("SERVICECHARTYPE"."ID"="SRVCHHASTYPE"."FK_TO_SRVCHTY")
5 - access("SRVCHHASTYPE"."FK_FROM_SRVCH"="RPRSRVHASCHAR"."FK_TO_SRVCH")
8 - filter("SRVCHARV"."MAINSTATUS"<>'S' AND "SRVCHARV"."MAINSTATUS"<>'M')
25 rows selected.
Elapsed: 00:00:00.06
Query 5:
SQL> create table work5
............
Table created.
Elapsed: 00:00:00.26
SQL> explain plan for --Query5
...........
Explained.
Elapsed: 00:00:00.09
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2190754947
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 132 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 132 | 5 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 100 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 77 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | SYS_C00405425 | 1 | 26 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | SYS_C0050999 | 1 | 13 | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| TV_RES_CHAR | 1 | 38 | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_ISI4533 | 1 | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | TE_RES_CHAR | 1 | 23 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | SYS_C0040129 | 1 | | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | T_RESCHAR_TYPE | 1 | 32 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | SYS_C0042350 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("TTRSCCHART"."FK_FROM_RSCCHAR"="TTDATARSCCHAR"."FK_TO_RSCCHAR")
7 - filter("TVRESCHAR"."MAINSTATUS"<>'S' AND "TVRESCHAR"."MAINSTATUS"<>'M')
8 - access("TVRESCHAR"."FK_ENTITY"="TTRSCCHART"."FK_FROM_RSCCHAR")
10 - access("TVRESCHAR"."FK_ENTITY"="TERESCHAR"."ID")
12 - access("TRESCHARTYPE"."ID"="TTRSCCHART"."FK_TO_RSCCHRT")
28 rows selected.
Elapsed: 00:00:00.04
Query 6:
SQL> create table work6
............
Table created.
Elapsed: 00:04:27.94
SQL> explain plan for --Query6
...........
Explained.
Elapsed: 00:00:00.41
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 287185723
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1815K| 171M| 274K (9)| 00:54:51 |
|* 1 | HASH JOIN | | 1815K| 171M| 274K (9)| 00:54:51 |
|* 2 | HASH JOIN | | 1815K| 116M| 180K (8)| 00:36:11 |
| 3 | TABLE ACCESS FULL | T_SERVICECHARTYPE | 159 | 2703 | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 1815K| 86M| 180K (8)| 00:36:10 |
|* 5 | HASH JOIN | | 1815K| 64M| 120K (7)| 00:24:02 |
| 6 | TABLE ACCESS FULL| TT_DLMSERV_HASCHAR_SRVCH | 1815K| 24M| 2139 (9)| 00:00:26 |
| 7 | TABLE ACCESS FULL| TE_SERVICECHARACTERISTIC | 42M| 924M| 115K (5)| 00:23:12 |
| 8 | TABLE ACCESS FULL | TT_SRVCH_HASTYPE_SRVCHTY | 41M| 518M| 58642 (8)| 00:11:44 |
|* 9 | TABLE ACCESS FULL | TV_SERVICECHARACTERISTIC | 42M| 1283M| 91304 (9)| 00:18:16 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E_SRVCH"."ID"="V_SRVCH"."FK_ENTITY")
2 - access("TT_SERVCH_HAS_SRVCHTYPE"."FK_TO_SRVCHTY"="T_SRVCHTYPE"."ID")
4 - access("E_SRVCH"."ID"="TT_SERVCH_HAS_SRVCHTYPE"."FK_FROM_SRVCH")
5 - access("TT_DLMSRV_SRVCH"."FK_TO_SRVCH"="E_SRVCH"."ID")
9 - filter("V_SRVCH"."MAINSTATUS"<>'S' AND "V_SRVCH"."MAINSTATUS"<>'M')
25 rows selected.
Elapsed: 00:00:00.03
Full Query:
SQL> create table work_full_query
..............
Table created.
Elapsed: 00:35:42.70
SQL> explain plan for --Full Query
...........
Explained.
Elapsed: 00:00:01.35
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2450839118
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 1041M| 933K(100)| 03:06:48 |
| 1 | SORT UNIQUE | | 10M| 1041M| 933K(100)| 03:06:48 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 1 | 132 | 5 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 100 | 4 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 77 | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
| 7 | INDEX FULL SCAN | SYS_C0054827 | 1 | 26 | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | SYS_C0050999 | 1 | 13 | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| TV_RES_CHAR | 1 | 38 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_ISI4533 | 1 | | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | TE_RES_CHAR | 1 | 23 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | SYS_C0040129 | 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | T_RESCHAR_TYPE | 1 | 32 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | SYS_C0042350 | 1 | | 1 (0)| 00:00:01 |
|* 15 | HASH JOIN | | 202K| 26M| 94061 (13)| 00:18:49 |
|* 16 | HASH JOIN | | 202K| 22M| 61345 (16)| 00:12:17 |
| 17 | TABLE ACCESS FULL | T_RESCHAR_TYPE | 107 | 1926 | 2 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 202K| 18M| 61333 (16)| 00:12:16 |
|* 19 | HASH JOIN | | 19M| 1565M| 59479 (13)| 00:11:54 |
| 20 | INDEX FULL SCAN | SYS_C0059288 | 96 | 1152 | 1 (0)| 00:00:01 |
|* 21 | HASH JOIN | | 9475K| 650M| 58596 (12)| 00:11:44 |
|* 22 | HASH JOIN RIGHT ANTI | | 9468K| 307M| 26222 (12)| 00:05:15 |
| 23 | MAT_VIEW ACCESS FULL | WMV_FREE_PROFILES | 787K| 5380K| 616 (11)| 00:00:08 |
|* 24 | HASH JOIN | | 11M| 288M| 25047 (11)| 00:05:01 |
| 25 | TABLE ACCESS FULL | TT_PIFCFG_HASSPEC_PIFCFGS | 5079K| 62M| 7419 (7)| 00:01:30 |
| 26 | TABLE ACCESS FULL | TT_PIFCFG_HAS_RSCCHAR | 11M| 149M| 16778 (7)| 00:03:22 |
|* 27 | TABLE ACCESS FULL | TV_RES_CHAR | 11M| 407M| 31223 (8)| 00:06:15 |
|* 28 | INDEX UNIQUE SCAN | SYS_C0050999 | 1 | 13 | 1 (0)| 00:00:01 |
| 29 | TABLE ACCESS FULL | TE_RES_CHAR | 11M| 247M| 32193 (5)| 00:06:27 |
|* 30 | HASH JOIN | | 8203K| 774M| 287K (10)| 00:57:33 |
|* 31 | HASH JOIN | | 8203K| 594M| 169K (11)| 00:33:51 |
|* 32 | HASH JOIN | | 8203K| 344M| 75474 (11)| 00:15:06 |
| 33 | TABLE ACCESS FULL | T_SERVICECHARTYPE | 159 | 2703 | 3 (0)| 00:00:01 |
|* 34 | HASH JOIN | | 8203K| 211M| 75100 (11)| 00:15:02 |
| 35 | TABLE ACCESS FULL | TT_CFS_HASCHAR_SRVCH | 8203K| 109M| 14014 (7)| 00:02:49 |
| 36 | TABLE ACCESS FULL | TT_SRVCH_HASTYPE_SRVCHTY | 41M| 518M| 58642 (8)| 00:11:44 |
|* 37 | TABLE ACCESS FULL | TV_SERVICECHARACTERISTIC | 42M| 1283M| 91304 (9)| 00:18:16 |
| 38 | TABLE ACCESS FULL | TE_SERVICECHARACTERISTIC | 42M| 924M| 115K (5)| 00:23:12 |
|* 39 | HASH JOIN | | 732K| 69M| 272K (9)| 00:54:36 |
|* 40 | HASH JOIN | | 732K| 53M| 155K (11)| 00:31:01 |
|* 41 | HASH JOIN | | 732K| 30M| 61794 (11)| 00:12:22 |
| 42 | TABLE ACCESS FULL | T_SERVICECHARTYPE | 159 | 2703 | 3 (0)| 00:00:01 |
|* 43 | HASH JOIN | | 732K| 18M| 61757 (11)| 00:12:22 |
| 44 | TABLE ACCESS FULL | TT_RPRSRV_HASCHAR_SRVCH | 732K| 9M| 1177 (7)| 00:00:15 |
| 45 | TABLE ACCESS FULL | TT_SRVCH_HASTYPE_SRVCHTY | 41M| 518M| 58642 (8)| 00:11:44 |
|* 46 | TABLE ACCESS FULL | TV_SERVICECHARACTERISTIC | 42M| 1283M| 91304 (9)| 00:18:16 |
| 47 | TABLE ACCESS FULL | TE_SERVICECHARACTERISTIC | 42M| 924M| 115K (5)| 00:23:12 |
| 48 | NESTED LOOPS | | 1 | 132 | 5 (0)| 00:00:01 |
| 49 | NESTED LOOPS | | 1 | 100 | 4 (0)| 00:00:01 |
| 50 | NESTED LOOPS | | 1 | 77 | 3 (0)| 00:00:01 |
| 51 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
| 52 | INDEX FULL SCAN | SYS_C00405425 | 1 | 26 | 1 (0)| 00:00:01 |
|* 53 | INDEX RANGE SCAN | SYS_C0050999 | 1 | 13 | 1 (0)| 00:00:01 |
|* 54 | TABLE ACCESS BY INDEX ROWID| TV_RES_CHAR | 1 | 38 | 1 (0)| 00:00:01 |
|* 55 | INDEX RANGE SCAN | IDX_ISI4533 | 1 | | 1 (0)| 00:00:01 |
| 56 | TABLE ACCESS BY INDEX ROWID | TE_RES_CHAR | 1 | 23 | 1 (0)| 00:00:01 |
|* 57 | INDEX UNIQUE SCAN | SYS_C0040129 | 1 | | 1 (0)| 00:00:01 |
| 58 | TABLE ACCESS BY INDEX ROWID | T_RESCHAR_TYPE | 1 | 32 | 1 (0)| 00:00:01 |
|* 59 | INDEX UNIQUE SCAN | SYS_C0042350 | 1 | | 1 (0)| 00:00:01 |
|* 60 | HASH JOIN | | 1815K| 171M| 274K (9)| 00:54:51 |
|* 61 | HASH JOIN | | 1815K| 116M| 180K (8)| 00:36:11 |
| 62 | TABLE ACCESS FULL | T_SERVICECHARTYPE | 159 | 2703 | 3 (0)| 00:00:01 |
|* 63 | HASH JOIN | | 1815K| 86M| 180K (8)| 00:36:10 |
|* 64 | HASH JOIN | | 1815K| 64M| 120K (7)| 00:24:02 |
| 65 | TABLE ACCESS FULL | TT_DLMSERV_HASCHAR_SRVCH | 1815K| 24M| 2139 (9)| 00:00:26 |
| 66 | TABLE ACCESS FULL | TE_SERVICECHARACTERISTIC | 42M| 924M| 115K (5)| 00:23:12 |
| 67 | TABLE ACCESS FULL | TT_SRVCH_HASTYPE_SRVCHTY | 41M| 518M| 58642 (8)| 00:11:44 |
|* 68 | TABLE ACCESS FULL | TV_SERVICECHARACTERISTIC | 42M| 1283M| 91304 (9)| 00:18:16 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("TTRSCCHART"."FK_FROM_RSCCHAR"="TTFIARSCCHAR"."FK_TO_RSCCHAR")
9 - filter("TVRESCHAR"."MAINSTATUS"<>'S' AND "TVRESCHAR"."MAINSTATUS"<>'M')
10 - access("TVRESCHAR"."FK_ENTITY"="TTRSCCHART"."FK_FROM_RSCCHAR")
12 - access("TVRESCHAR"."FK_ENTITY"="TERESCHAR"."ID")
14 - access("TRESCHARTYPE"."ID"="TTRSCCHART"."FK_TO_RSCCHRT")
15 - access("RCE"."ID"="RC"."FK_ENTITY")
16 - access("HAST"."FK_TO_RSCCHRT"="RCT"."ID")
19 - access("HASSPEC"."FK_TO_PIFCFGS"="SPECHAS"."FK_FROM_PIFCFGS")
21 - access("HAS"."FK_TO_RSCCHAR"="RC"."FK_ENTITY")
22 - access("WFP"."FK_PIFCFG"="HASSPEC"."FK_FROM_PIFCFG")
24 - access("HASSPEC"."FK_FROM_PIFCFG"="HAS"."FK_FROM_PIFCFG")
27 - filter("RC"."MAINSTATUS"<>'S' AND "RC"."MAINSTATUS"<>'M')
28 - access("RC"."FK_ENTITY"="HAST"."FK_FROM_RSCCHAR" AND
"SPECHAS"."FK_TO_RSCCHRT"="HAST"."FK_TO_RSCCHRT")
30 - access("SRVCHARV"."FK_ENTITY"="SRVCHARE"."ID")
31 - access("CFSHASCHAR"."FK_TO_SRVCH"="SRVCHARV"."FK_ENTITY")
32 - access("SERVICECHARTYPE"."ID"="SRVCHHASTYPE"."FK_TO_SRVCHTY")
34 - access("SRVCHHASTYPE"."FK_FROM_SRVCH"="CFSHASCHAR"."FK_TO_SRVCH")
37 - filter("SRVCHARV"."MAINSTATUS"<>'S' AND "SRVCHARV"."MAINSTATUS"<>'M')
39 - access("SRVCHARV"."FK_ENTITY"="SRVCHARE"."ID")
40 - access("RPRSRVHASCHAR"."FK_TO_SRVCH"="SRVCHARV"."FK_ENTITY")
41 - access("SERVICECHARTYPE"."ID"="SRVCHHASTYPE"."FK_TO_SRVCHTY")
43 - access("SRVCHHASTYPE"."FK_FROM_SRVCH"="RPRSRVHASCHAR"."FK_TO_SRVCH")
46 - filter("SRVCHARV"."MAINSTATUS"<>'S' AND "SRVCHARV"."MAINSTATUS"<>'M')
53 - access("TTRSCCHART"."FK_FROM_RSCCHAR"="TTDATARSCCHAR"."FK_TO_RSCCHAR")
54 - filter("TVRESCHAR"."MAINSTATUS"<>'S' AND "TVRESCHAR"."MAINSTATUS"<>'M')
55 - access("TVRESCHAR"."FK_ENTITY"="TTRSCCHART"."FK_FROM_RSCCHAR")
57 - access("TVRESCHAR"."FK_ENTITY"="TERESCHAR"."ID")
59 - access("TRESCHARTYPE"."ID"="TTRSCCHART"."FK_TO_RSCCHRT")
60 - access("E_SRVCH"."ID"="V_SRVCH"."FK_ENTITY")
61 - access("TT_SERVCH_HAS_SRVCHTYPE"."FK_TO_SRVCHTY"="T_SRVCHTYPE"."ID")
63 - access("E_SRVCH"."ID"="TT_SERVCH_HAS_SRVCHTYPE"."FK_FROM_SRVCH")
64 - access("TT_DLMSRV_SRVCH"."FK_TO_SRVCH"="E_SRVCH"."ID")
68 - filter("V_SRVCH"."MAINSTATUS"<>'S' AND "V_SRVCH"."MAINSTATUS"<>'M')
113 rows selected.
Elapsed: 00:00:00.47
[Updated on: Sat, 05 January 2013 19:07] by Moderator Report message to a moderator
|
|
|
Re: Tuning the sql query [message #573596 is a reply to message #571984] |
Mon, 31 December 2012 10:19 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
WORKAREAS Workareas
BEFORE After
WORK TABLE TIMING seconds rows seconds DIFF
work1 00:00.0 0 0 0 0
work2 17:32.0 1052 10541598 1425 373
work3 27:22.0 1642 8251708 617 -1025
work4 04:11.0 251 730076 267 16
work5 00:01.0 1 0 0 -1
work6 05:50.0 350 1859412 267 -83
3296 2576 -720
So looking at the before and after, we see that the time appears to have come down about 9 minutes with most of that from one of the sub-queries (the most expensive one). Additionaly we see that the FULL QUERY completed in 400 seconds less that the sum of the runtimes of the individual pieces. Let us take a moment to dicuss this.
Our goal was to tune this query and here is what we know and are trying to achieve:
1) several of the sub-queries fetch almost all the data in all the tables and thus don't need indexes
2) we want to tune out hash joins
3) data may have been cached which explains part of the improved runtime of the full query
4) the final sort cost seems to have gone away as well so I assume the OP used SORT_AREA_SIZE as well?
1) several of the sub-queries fetch almost all the data in all the tables. This means indexes are not of much use to these sub-queriers so we expect to make considerable use of HASH JOINS and FULL TABLE SCANS.
2) so our goal is to tune HASH JOINS at the moment. To do this we are using the following steps:
a) test to see if more memory will speed up the HASH JOINS. More memory could be valuable if it enables a HASH JOIN to UPGRADE its execution mechanism. This means if a HASH JOIN is able to reduce the number of passes required. We would see a HASH JOIN go from say multi-pass (5) to say multi-pass (3) and thus reduce the number of passes, or go from MULTI-PASS to ONE-PASS or go from ONE-PASS to OPTIMAL. Reducng the number of passes is what we want and we want to achieve either a ONE-PASS or OPTIMAL execution.
If adding more memory does not change the number of passes then the additional memory was of little value.
The simplest way to to test is to use MANUAL memory management though this does not mean our final solution will use MANUAL memory management.
Before testing with more memory, we should first look to see that more memory is actually avaialable from our HOST machine.
b) once we determine that more memory will help, and by how much, we decide if the benefit is enough and if we want to change anything to actually make the use of more memory for hash joins real. This will mean possibly lmodifying PGA_AGGREGATE_TARGET to increase total memory available and then hoping Oracle will allocate more to our hash joins, or going to MANUAL workarea management as we did in our test.
c) next we want to try PARALLEL QUERY. Parallel query allocates workareas for each set of parallel processes. So using a PQ DEGREE of 2 means twice as many workareas, of 4 means four times as many workareas. The goal of parallel query from a hash join perspective is to reduce the size of hash joins by doing many smaller joins instead of one big join. Smaller joins will require less passes. Parallel query will help the database to give more memory to your HASH JOINS, but for PQ to work you need to:
1. have unused MEMORY
2. have unused CPU
3. have unused DISK bandwidth
4. have unused NETWORK bandwith
Yes, that is right. Your box needs unused resources to use parallel query. So, if your box is not underutilized at the time you want to run a query with parallel processes then PQ might not be a good idea since it can make your system run slower instead of faster because of increased resource contention. Most boxes are not underutilized and as such can't make good use of PQ. Chew on that.
3) not much to say about possible caching of data. This could be either being done in ORacle or by the disk cache. Either way you cna see reduction in runtime.
4) OP, can you verify if you used SORT_AREA_SIZE setting as well.
If can help to monitor workareas while a query is running. To that end, starting up multiple SQL*Plus session and running this query in one of them can help:
/*
col sql_hash_value noprint
col sql_id noprint
col workarea_address noprint
col operation_type format a20
col policy format a10
col qcinst_id noprint
col qcsid noprint
*/
select * from v$sql_workarea_active where sid = &&1
/
Take note of what SID you are running from and use that sid in this query. Alternatively remove the sid predicate and view all workareas. You can watch workareas needed by a query and how they grow and when they start uing temp space.
---------------------------------------------------------------------
So at this point the OPs query has gone from 90 minutes to 35 minutes we think because of improved HASH JOINS and SORTING resulting from better memory management.
OP if you are still not satisified, your next step is to investigate PARALLEL QUERY. Learn the syntax for PQ for your specific version of Oracle, then find out how many CPUs you have, and then run PQ up to that many CPUs to see what happens. Start with PQ degree of 4 then go to 8 then 16 if it makes sense. Your goal is to see reductions in runtime. At some point the reduction will stop being worth while or you will saturate your system and slow everything down. As you can see PQ is not as easy as just putting ahint in the query. You have to do some research and know what your system is doing before you use it.
OP, you need also to decide if you are willing to use manual memory management for this query, and how you are going to enable/disable it for the corresponding sessions since you likely don't want to do it for all sessions. Additionally you will want to talk with other DBAs, possibly your boss as well to let him/her know what you are planning, to get some kind fo concensus if you are OK to move forward with this change. Take a summary of your research with you when you have these discussions so you can show people to potential benefit.
OP, lastly realize that we are not seeing order of magnitude improvements yet. It may be that your query will have a minumum runtime and we may be approaching it in which case it may become one of those "that is as fast as it will go unless we want to do something drastic" kind of queries. In which case someone will want to think about how to manage the query with its current runtime.
Kevin
|
|
|
|
Goto Forum:
Current Time: Sat Jan 18 00:39:37 CST 2025
|