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 Go to next message
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 #571985 is a reply to message #571984] Wed, 05 December 2012 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Tuning the sql query [message #571986 is a reply to message #571985] Wed, 05 December 2012 02:50 Go to previous messageGo to next message
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 #572004 is a reply to message #571986] Wed, 05 December 2012 05:49 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
please advice this is very urgent.
Re: Tuning the sql query [message #572008 is a reply to message #572004] Wed, 05 December 2012 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing is urgent in a forum.
You give us nothing, we owe you no answer and especially no quick answer.
This is the principle.

Regards
Michel
Re: Tuning the sql query [message #572108 is a reply to message #572008] Thu, 06 December 2012 03:09 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Please any suggesstions or advice.
Re: Tuning the sql query [message #572178 is a reply to message #571984] Thu, 06 December 2012 19:45 Go to previous messageGo to next message
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.
set timing on


--
-- 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 #572179 is a reply to message #572178] Thu, 06 December 2012 19:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Then build a query diagram for each of those piecemeal queries. I will do the fist one for you.

          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
Re: Tuning the sql query [message #572180 is a reply to message #572179] Thu, 06 December 2012 20:02 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Then build and run count and filter queries for all the tables in your query. I will do the first one for you. Remember to record the results somewhere.

Count Queries

select count(*) from tt_fia_aaa_has_rscchar       ttfiarscchar ;
select count(*) from tt_rscchar_has_rscchrt       ttrscchart ;
select count(*) from t_reschar_type               treschartype ;
select count(*) from tv_res_char                  tvreschar ;
select count(*) from te_res_char                  tereschar ;


Filter Queries

select count(*) from tv_res_char                  tvreschar where tvreschar.mainstatus not in ('S','M');
Re: Tuning the sql query [message #572181 is a reply to message #572180] Thu, 06 December 2012 20:05 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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).

set timing on

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #572729 is a reply to message #572652] Mon, 17 December 2012 00:36 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Please find the Query.6 timings after adding order hint.

Elapsed: 00:04:15.51
Re: Tuning the sql query [message #572731 is a reply to message #572652] Mon, 17 December 2012 00:45 Go to previous messageGo to next message
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 #572757 is a reply to message #572731] Mon, 17 December 2012 07:55 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
my bad, documentation on hint is here

16.1.2.4 Hints for Join Orders
The following hints suggest join orders:

•LEADING

•ORDERED


grpatwari My appologies. Ross is correct. The official hin is ORDERED. You may wish to re-run the query using the official hint.

Please let me be clear, this was a mistake on my part. I should have said ORDERED, not order.

Now, check this out.

  1* explain plan for select /*+ order */ * from a,b
08:49:18 SQL> /

Explained.

Elapsed: 00:00:00.09
08:49:20 SQL> @showplan9i

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 26896 |   682K|   330 |
|   1 |  MERGE JOIN CARTESIAN|             | 26896 |   682K|   330 |
|   2 |   TABLE ACCESS FULL  | A           |   164 |  2132 |     2 |
|   3 |   BUFFER SORT        |             |   164 |  2132 |   328 |
|   4 |    TABLE ACCESS FULL | B           |   164 |  2132 |     2 |
--------------------------------------------------------------------

Note: cpu costing is off

12 rows selected.

Elapsed: 00:00:00.37
08:49:26 SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.06
08:49:30 SQL> explain plan for select /*+ order */ * from b,a;

Explained.

Elapsed: 00:00:00.06
08:49:45 SQL> @showplan9i

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             | 26896 |   682K|   330 |
|   1 |  MERGE JOIN CARTESIAN|             | 26896 |   682K|   330 |
|   2 |   TABLE ACCESS FULL  | B           |   164 |  2132 |     2 |
|   3 |   BUFFER SORT        |             |   164 |  2132 |   328 |
|   4 |    TABLE ACCESS FULL | A           |   164 |  2132 |     2 |
--------------------------------------------------------------------

Note: cpu costing is off

12 rows selected.

Elapsed: 00:00:00.14
08:49:48 SQL> 


You got me... Maybe it is a valid version of the hint. Seems to be.

Kevin
Re: Tuning the sql query [message #572785 is a reply to message #572757] Mon, 17 December 2012 11:08 Go to previous messageGo to next message
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 #1
probing 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 #2
probing 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 #572799 is a reply to message #572785] Mon, 17 December 2012 14:08 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I concur
Re: Tuning the sql query [message #572800 is a reply to message #572799] Mon, 17 December 2012 14:10 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks.
Re: Tuning the sql query [message #572803 is a reply to message #572785] Mon, 17 December 2012 14:55 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Thanks, man. That description of how the two exec plans work and the significance thereof was bloody brilliant. A good read, too.
As for the book, if you need help, I'm up for it. But I don't think I could match your level of technical content. I tried before to get a book going here http://www.orafaq.com/forum/mv/msg/167277/495359/148813/#msg_495359 but it didn't go anywhere.

Re: Tuning the sql query [message #572812 is a reply to message #572803] Mon, 17 December 2012 16:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
John... I was hoping you would pop in. Thanks. Between you and Ross I figure my description must be pretty on the money.
Re: Tuning the sql query [message #572827 is a reply to message #572812] Tue, 18 December 2012 01:02 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #573369 is a reply to message #571984] Thu, 27 December 2012 06:39 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks, but something seems wrong here. You missed the point too. We need runtimes for all the work tables after setting the manual workarea sizes, not just the first one.

Also, seems strange that the runtime should go up so high for the full query after this change. Please post the plan again after the manual workarea size changes for the full query.

I assume we area still trying to fix this right? You said the original was taking 1.5 hours and looking at this, it appears to be taking 8 hours? I applaud your willingness to wait out the runtime for us.

But please post the plan for this 8 hour query (the plan after setting the manual workarea settings) so that we can compare it to the original. Also, were there any data changes? Was this run with way more data?

Kevin
Re: Tuning the sql query [message #573545 is a reply to message #571984] Mon, 31 December 2012 02:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Tuning the sql query [message #573652 is a reply to message #573596] Tue, 01 January 2013 23:31 Go to previous message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you very much for your explanation.

I try to use Parallel Query..
Previous Topic: indexing
Next Topic: Sql Running Slowly
Goto Forum:
  


Current Time: Thu Mar 28 13:10:22 CDT 2024