1 2 Previous Next 23 Replies Latest reply on Feb 12, 2007 9:33 AM by 464504

    SQL tunning

    515435
      Hi

      I have to tune a set of sql query that is used for sorting data. I created trace file and used tkprof to analyze it. Then, I got the bottleneck query. The report generated for the query is as follows.

      -------------------------------------------------------------------------------------------------------------------------------------------

      call count cpu elapsed disk query current rows
      ------- ------ -------- ---------- ---------- ---------- ---------- ----------
      Parse 1 0.84 0.87 5 3198 128 0
      Execute 1 0.00 0.00 0 0 0 0
      Fetch 1 65.19 72.97 3326 41938 107 1000
      ------- ------ -------- ---------- ---------- ---------- ---------- ----------
      total 3 66.04 73.84 3331 45136 235 1000

      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 400 (recursive depth: 1)
      ------------------------------------------------------------------------------------------------------------------------------------------------

      I an not including the query as it is so long and it is the OLAP DML query generated by OLAP API and is much difficult to understand.

      Can anyone tell me from above report, where is the bottleneck problem ( most likely in the database configuration) that i can change so as to make the query fast?

      Please help me.

      Thanks in advance.
      Subash
        • 1. Re: SQL tunning
          35041
          Can you at least post the explain plan, it would appear that fetching the data was the slowest. Do you need more indexes? Its hard to tune blind let alone when you have all the facts.
          • 2. Re: SQL tunning
            515435
            Hello kevin

            The query that was obtained from trace file is posted below. When I tried to create execution plan following error occurs:

            Describe Error: Failed to execute EXPLAIN plan: ORA-34492: Analytic workspace object __XML_GET_FULLTOAW_NAME does not exist.
            DMNS AW426_ET_COL_28 AS VARCHAR2(100) FROM AGE_GROUP WITH HRR AGE_GROUP_PARENTREL(AGE_GROUP_HIERLIST 'AGE_GROUP') INH AGE_GROUP_INHIER GID AW426_GID_COL_30 AS NUMBER FROM AGE_GROUP_GID LRL AW426_LEVEL_34 AS VARCHAR2(100), AW426_LEVEL_35 AS VARCHAR2(100) FROM AGE_GROUP_FAMILYREL(AGE_GROUP_LEVELLIST 'AGE_GRP'), AGE_GROUP_FAMILYREL(AGE_GROUP_LEVELLIST 'ALL_AGE_GRP') MSR OLAP_EXPRESSION_1 FROM AW_EXPR(CONVERT(ISSESSION(&(__XML_GET_FULLTOAW_NAME('AGE_GROUP.DIMENSION'))), INTEGER ))

            ---------------------------------------------------------------------------------------------------------------------------------
            ------------------ Query generated by OLAP API --------------------------------------------------------------
            ---------------------------------------------------------------------------------------------------------------------------------


            SELECT /*+ NOPARALLEL bypass_recursive_check */
            SP_ALIAS_190,
            ((CASE SP_ALIAS_191
            WHEN 1
            THEN 'PROVIDER::ALL_PROV::'
            WHEN 0
            THEN 'PROVIDER::PROV::'
            ELSE NULL END) || SP_ALIAS_190) ALIAS_3553,
            SP_ALIAS_194,
            SP_ALIAS_191,
            SP_ALIAS_192,
            SP_ALIAS_193,
            SP_ALIAS_205,
            D4_AGE_GROUP_ET,
            ((CASE D4_AGE_GROUP_GID
            WHEN 1
            THEN 'AGE_GROUP::ALL_AGE_GRP::'
            WHEN 0
            THEN 'AGE_GROUP::AGE_GRP::'
            ELSE NULL END) || D4_AGE_GROUP_ET) ALIAS_3554,
            D4_AGE_GROUP_HierarchyNumber,
            D4_AGE_GROUP_GID,
            D4_AGE_GROUP_ALL_AGE_GRP,
            D4_AGE_GROUP_AGE_GRP,
            T218.C2 D12_PROCEDURE_ET,
            ((CASE T218.C3
            WHEN 3
            THEN 'PROCEDURE::ALL_PROC::'
            WHEN 1
            THEN 'PROCEDURE::PROC_GRP::'
            WHEN 0
            THEN 'PROCEDURE::PROC::'
            ELSE NULL END) || T218.C2) ALIAS_3555,
            T218.C7 D12_PROCEDURE_HierarchyNumber,
            T218.C3 D12_PROCEDURE_GID,
            T218.C4 D12_PROCEDURE_ALL_PROC,
            T218.C5 D12_PROCEDURE_PROC_GRP,
            T218.C6 D12_PROCEDURE_PROC,
            T217.C2 D20_DIAGNOSIS_ET,
            ((CASE T217.C3
            WHEN 3
            THEN 'D2GROUP::ALL_D2GRP::'
            WHEN 1
            THEN 'D2GROUP::D2GRP::'
            WHEN 0
            THEN 'D2GROUP::ICD9::'
            ELSE NULL END) || T217.C2) ALIAS_3556,
            T217.C12 D20_DIAGNOSIS_HierarchyNumber,
            T217.C3 D20_DIAGNOSIS_GID,
            T217.C4 D20_DIAGNOSIS_ALL_D2GRP,
            T217.C5 D20_DIAGNOSIS_D2GRP,
            T217.C6 D20_DIAGNOSIS_ICD9,
            T217.C7 D20_DIAGNOSIS_ALL_ACC,
            T217.C8 D20_DIAGNOSIS_ACC,
            T217.C9 D20_DIAGNOSIS_CC,
            T217.C10 D20_DIAGNOSIS_DXGRP,
            T217.C11 D20_ALIAS5,
            D28_POS_ET,
            ((CASE D28_POS_GID
            WHEN 1
            THEN 'POS::ALL_POS::'
            WHEN 0
            THEN 'POS::POS::'
            ELSE NULL END) || D28_POS_ET) ALIAS_3557,
            D28_POS_HierarchyNumber,
            D28_POS_GID,
            D28_POS_ALL_POS,
            D28_POS_POS,
            T216.C2 D36_SPECIALTY_ET,
            ((CASE T216.C3
            WHEN 1
            THEN 'SPECIALTY::ALL_SPEC::'
            WHEN 0
            THEN 'SPECIALTY::SPEC::'
            ELSE NULL END) || T216.C2) ALIAS_3558,
            T216.C6 D36_SPECIALTY_HierarchyNumber,
            T216.C3 D36_SPECIALTY_GID,
            T216.C4 D36_SPECIALTY_ALL_SPEC,
            T216.C5 D36_SPECIALTY_SPEC,
            D44_INCR_PAID_ET,
            ((CASE D44_INCR_PAID_GID
            WHEN 0
            THEN 'INCR_PAID::INCR_PAID::'
            ELSE NULL END) || D44_INCR_PAID_ET) ALIAS_3559,
            D44_INCR_PAID_HierarchyNumber,
            D44_INCR_PAID_GID,
            D44_INCR_PAID_INCR_PAID,
            D52_BUSINESS_L_ET,
            ((CASE D52_BUSINESS_L_GID
            WHEN 15
            THEN 'BUSINESS_LEVEL::ALL_BL::'
            WHEN 7
            THEN 'BUSINESS_LEVEL::LVL1::'
            WHEN 3
            THEN 'BUSINESS_LEVEL::LVL2::'
            WHEN 1
            THEN 'BUSINESS_LEVEL::LVL3::'
            WHEN 0
            THEN 'BUSINESS_LEVEL::LVL4::'
            ELSE NULL END) || D52_BUSINESS_L_ET) ALIAS_3560,
            ALIAS_R68,
            D52_BUSINESS_L_GID,
            D52_BUSINESS_L_ALL_BL,
            D52_BUSINESS_L_LVL1,
            D52_BUSINESS_L_LVL2,
            D52_BUSINESS_L_LVL3,
            D52_BUSINESS_L_LVL4,
            COUNT(*) OVER () ALIAS_3561
            FROM
            (
            SELECT
            SP_D61_DIAGNOSIS_ET,
            SP_D61_DIAGNOSIS_GID,
            SP_D63_PROCEDURE_ET,
            SP_D63_PROCEDURE_GID,
            SP_D65_PROVIDER_ET,
            SP_D67_BUSINESS_L_ET,
            SP_D67_BUSINESS_L_GID,
            SP_D69_SPECIALTY_ET,
            SP_D69_SPECIALTY_GID,
            SP_D73_POS_ET,
            SP_D73_POS_GID,
            SP_D75_AGE_GROUP_ET,
            SP_D75_AGE_GROUP_GID,
            SP_D77_INCR_PAID_ET,
            SP_D77_INCR_PAID_GID,
            SP_ALIAS_190,
            SP_ALIAS_194,
            SP_ALIAS_191,
            SP_ALIAS_192,
            SP_ALIAS_193,
            SP_ALIAS_262,
            SP_ALIAS_256,
            SP_ALIAS_257,
            SP_ALIAS_205
            FROM
            (
            SELECT
            CAST (NULL AS NUMBER) ALIAS_256,
            CAST (NULL AS NUMBER) ALIAS_257,
            ALIAS_169,
            ALIAS_190,
            ALIAS_191,
            ALIAS_192,
            ALIAS_193,
            ALIAS_194,
            ALIAS_205,
            C59_M_TIME_AWMEDICA_MED_PAIDA,
            D61_DIAGNOSIS_ET,
            D61_DIAGNOSIS_HierarchyNumber,
            D61_DIAGNOSIS_GID,
            D63_PROCEDURE_ET,
            D63_PROCEDURE_HierarchyNumber,
            D63_PROCEDURE_GID,
            D65_PROVIDER_ET,
            D65_PROVIDER_GID,
            D67_BUSINESS_L_ET,
            ALIAS_R81,
            D67_BUSINESS_L_GID,
            D69_SPECIALTY_ET,
            D69_SPECIALTY_HierarchyNumber,
            D69_SPECIALTY_GID,
            D71_TIME_ET,
            D71_TIME_HierarchyNumber,
            D71_TIME_GID,
            D73_POS_ET,
            D73_POS_HierarchyNumber,
            D73_POS_GID,
            D75_AGE_GROUP_ET,
            D75_AGE_GROUP_HierarchyNumber,
            D75_AGE_GROUP_GID,
            D77_INCR_PAID_ET,
            D77_INCR_PAID_HierarchyNumber,
            D77_INCR_PAID_GID,
            ALIAS_262
            FROM
            (
            SELECT
            T139.C2 ALIAS_169,
            T139.C3 ALIAS_190,
            T139.C4 ALIAS_191,
            T139.C5 ALIAS_192,
            T139.C6 ALIAS_193,
            T139.C7 ALIAS_194,
            T139.C8 ALIAS_205,
            ALIAS_3589 C59_M_TIME_AWMEDICA_MED_PAIDA,
            D61_DIAGNOSIS_ET D61_DIAGNOSIS_ET,
            ALIAS_3588 D61_DIAGNOSIS_HierarchyNumber,
            D61_DIAGNOSIS_GID D61_DIAGNOSIS_GID,
            D63_PROCEDURE_ET D63_PROCEDURE_ET,
            ALIAS_3588 D63_PROCEDURE_HierarchyNumber,
            D63_PROCEDURE_GID D63_PROCEDURE_GID,
            D65_PROVIDER_ET D65_PROVIDER_ET,
            D65_PROVIDER_GID D65_PROVIDER_GID,
            D67_BUSINESS_L_ET D67_BUSINESS_L_ET,
            ALIAS_3588 ALIAS_R81,
            D67_BUSINESS_L_GID D67_BUSINESS_L_GID,
            D69_SPECIALTY_ET D69_SPECIALTY_ET,
            ALIAS_3588 D69_SPECIALTY_HierarchyNumber,
            D69_SPECIALTY_GID D69_SPECIALTY_GID,
            D71_TIME_ET D71_TIME_ET,
            ALIAS_3588 D71_TIME_HierarchyNumber,
            D71_TIME_GID D71_TIME_GID,
            D73_POS_ET D73_POS_ET,
            ALIAS_3588 D73_POS_HierarchyNumber,
            D73_POS_GID D73_POS_GID,
            D75_AGE_GROUP_ET D75_AGE_GROUP_ET,
            ALIAS_3588 D75_AGE_GROUP_HierarchyNumber,
            D75_AGE_GROUP_GID D75_AGE_GROUP_GID,
            D77_INCR_PAID_ET D77_INCR_PAID_ET,
            ALIAS_3588 D77_INCR_PAID_HierarchyNumber,
            D77_INCR_PAID_GID D77_INCR_PAID_GID,
            ROW_NUMBER() OVER (
            PARTITION BY
            D61_DIAGNOSIS_ET,
            D61_DIAGNOSIS_GID,
            ALIAS_3588,
            D63_PROCEDURE_ET,
            D63_PROCEDURE_GID,
            ALIAS_3588,
            D67_BUSINESS_L_ET,
            D67_BUSINESS_L_GID,
            ALIAS_3588,
            D69_SPECIALTY_ET,
            D69_SPECIALTY_GID,
            ALIAS_3588,
            D71_TIME_ET,
            D71_TIME_GID,
            ALIAS_3588,
            D73_POS_ET,
            D73_POS_GID,
            ALIAS_3588,
            D75_AGE_GROUP_ET,
            D75_AGE_GROUP_GID,
            ALIAS_3588,
            D77_INCR_PAID_ET,
            D77_INCR_PAID_GID,
            ALIAS_3588
            ORDER BY
            ALIAS_3589 DESC NULLS FIRST ,
            T139.C5 ASC NULLS FIRST ,
            T139.C6 ASC NULLS FIRST ) ALIAS_262
            FROM
            (SELECT ROW# R, C1, C2, C3, C4, C5, C6, C7, C8 FROM TABLE(OLAPRC_TABLE(1, '107:8104'))) T139,
            (
            SELECT
            D61_DIAGNOSIS_ET,
            0 ALIAS_3588,
            D61_DIAGNOSIS_GID,
            D63_PROCEDURE_ET,
            D63_PROCEDURE_GID,
            D65_PROVIDER_ET,
            D65_PROVIDER_GID,
            D67_BUSINESS_L_ET,
            D67_BUSINESS_L_GID,
            D69_SPECIALTY_ET,
            D69_SPECIALTY_GID,
            D71_TIME_ET,
            D71_TIME_GID,
            D73_POS_ET,
            D73_POS_GID,
            D75_AGE_GROUP_ET,
            D75_AGE_GROUP_GID,
            D77_INCR_PAID_ET,
            D77_INCR_PAID_GID,
            ALIAS_3589
            FROM
            (
            SELECT
            AW430.AW430_MEASURE_120 ALIAS_3589,
            AW430.AW430_ET_COL_107 D61_DIAGNOSIS_ET,
            AW430.AW430_GID_COL_109 D61_DIAGNOSIS_GID,
            AW430.AW430_ET_COL_82 D63_PROCEDURE_ET,
            AW430.AW430_GID_COL_84 D63_PROCEDURE_GID,
            AW430.AW430_ET_COL_74 D65_PROVIDER_ET,
            AW430.AW430_GID_COL_76 D65_PROVIDER_GID,
            AW430.AW430_ET_COL_17 D67_BUSINESS_L_ET,
            AW430.AW430_GID_COL_19 D67_BUSINESS_L_GID,
            AW430.AW430_ET_COL_99 D69_SPECIALTY_ET,
            AW430.AW430_GID_COL_101 D69_SPECIALTY_GID,
            AW430.AW430_ET_COL_1 D71_TIME_ET,
            AW430.AW430_GID_COL_3 D71_TIME_GID,
            AW430.AW430_ET_COL_91 D73_POS_ET,
            AW430.AW430_GID_COL_93 D73_POS_GID,
            AW430.AW430_ET_COL_28 D75_AGE_GROUP_ET,
            AW430.AW430_GID_COL_30 D75_AGE_GROUP_GID,
            AW430.AW430_ET_COL_36 D77_INCR_PAID_ET,
            AW430.AW430_GID_COL_38 D77_INCR_PAID_GID
            FROM
            (SELECT SYS$DV.* FROM ((SELECT * FROM TABLE(OLAP_TABLE('TIME_AW.D2HA_V1 duration session',
            NULL,
            NULL,
            ' MSR AW430_MEASURE_120 AS FLOAT FROM MEDICAL_MED_PAIDAMT DMNS AW430_ET_COL_107 AS VARCHAR2(100) FROM DIAGNOSIS WITH HRR DIAGNOSIS_PARENTREL(DIAGNOSIS_HIERLIST ''D2GROUP'') INH DIAGNOSIS_INHIER GID AW430_GID_COL_109 AS NUMBER FROM DIAGNOSIS_GID DMNS AW430_ET_COL_82 AS VARCHAR2(100) FROM PROCEDURE WITH HRR PROCEDURE_PARENTREL(PROCEDURE_HIERLIST ''PROCEDURE'') INH PROCEDURE_INHIER GID AW430_GID_COL_84 AS NUMBER FROM PROCEDURE_GID DMNS AW430_ET_COL_74 AS VARCHAR2(100) FROM PROVIDER WITH HRR PROVIDER_PARENTREL(PROVIDER_HIERLIST ''PROVIDER'') INH PROVIDER_INHIER GID AW430_GID_COL_76 AS NUMBER FROM PROVIDER_GID DMNS AW430_ET_COL_17 AS VARCHAR2(100) FROM BUSINESS_LEVEL WITH HRR BUSINESS_LEVEL_PARENTREL(BUSINESS_LEVEL_HIERLIST ''BUSINESS_LEVEL'') INH BUSINESS_LEVEL_INHIER GID AW430_GID_COL_19 AS NUMBER FROM BUSINESS_LEVEL_GID DMNS AW430_ET_COL_99 AS VARCHAR2(100) FROM SPECIALTY WITH HRR SPECIALTY_PARENTREL(SPECIALTY_HIERLIST ''SPECIALTY'') INH SPECIALTY_INHIER GID AW430_GID_COL_101 AS NUMBER FROM SPECIALTY_GID DMNS AW430_ET_COL_1 AS VARCHAR2(100) FROM TIME WITH HRR TIME_PARENTREL(TIME_HIERLIST ''CALENDER'') INH TIME_INHIER GID AW430_GID_COL_3 AS NUMBER FROM TIME_GID DMNS AW430_ET_COL_91 AS VARCHAR2(100) FROM POS WITH HRR POS_PARENTREL(POS_HIERLIST ''POS'') INH POS_INHIER GID AW430_GID_COL_93 AS NUMBER FROM POS_GID DMNS AW430_ET_COL_28 AS VARCHAR2(100) FROM AGE_GROUP WITH HRR AGE_GROUP_PARENTREL(AGE_GROUP_HIERLIST ''AGE_GROUP'') INH AGE_GROUP_INHIER GID AW430_GID_COL_30 AS NUMBER FROM AGE_GROUP_GID DMNS AW430_ET_COL_36 AS VARCHAR2(100) FROM INCR_PAID WITH HRR INCR_PAID_PARENTREL(INCR_PAID_HIERLIST ''INCR_PAID'') INH INCR_PAID_INHIER GID AW430_GID_COL_38 AS NUMBER FROM INCR_PAID_GID')) SQL MODEL DIMENSION BY(AW430_ET_COL_107,
            AW430_GID_COL_109,
            AW430_ET_COL_82,
            AW430_GID_COL_84,
            AW430_ET_COL_74,
            AW430_GID_COL_76,
            AW430_ET_COL_17,
            AW430_GID_COL_19,
            AW430_ET_COL_99,
            AW430_GID_COL_101,
            AW430_ET_COL_1,
            AW430_GID_COL_3,
            AW430_ET_COL_91,
            AW430_GID_COL_93,
            AW430_ET_COL_28,
            AW430_GID_COL_30,
            AW430_ET_COL_36,
            AW430_GID_COL_38) MEASURES(AW430_MEASURE_120) UNIQUE SINGLE REFERENCE RULES UPDATE SEQUENTIAL ORDER ()) SYS$DV)) AW430 )
            V234
            WHERE
            ((D61_DIAGNOSIS_GID = 3)
            AND (D63_PROCEDURE_GID = 3)
            AND (NOT ((D65_PROVIDER_ET) IN (('ALL_PROV_NOTAPP') , ('PROV_NOTAPP') ) ) )
            AND (D67_BUSINESS_L_GID = 15)
            AND (D69_SPECIALTY_GID = 1)
            AND ((D71_TIME_ET) = ('CAL_ALL_YR_1') )
            AND (D73_POS_GID = 1)
            AND (D75_AGE_GROUP_GID = 1)
            AND (1 = 1)) )
            C59
            WHERE
            ((NOT (((CASE
            WHEN (ALIAS_3589 > 0)
            THEN ALIAS_3589
            ELSE -1 END) ) = (-1.000000) ) )
            AND (NOT ((T139.C3) IN (('ALL_PROV_NOTAPP') , ('PROV_NOTAPP') ) ) )
            AND (SYS_OP_MAP_NONNULL(D65_PROVIDER_GID) = SYS_OP_MAP_NONNULL(T139.C4) )
            AND (SYS_OP_MAP_NONNULL(D65_PROVIDER_ET) = SYS_OP_MAP_NONNULL(T139.C3) )
            AND ((D65_PROVIDER_ET) IN (('ALL_PROV_1') , ('PROV_1000001') , ('PROV_1000002') , ('PROV_1000003') , ('PROV_1000004') , ('PROV_1000005') , ('PROV_1000006') , ('PROV_1000007') , ('PROV_1000008') , ('PROV_1000009') , ('PROV_1000010') , ('PROV_1000011') , ('PROV_1000012') , ('PROV_1000013') , ('PROV_1000014') , ('PROV_1000015') , ('PROV_1000016') , ('PROV_1000017') , ('PROV_1000018') , ('PROV_1000019') , ('PROV_1000020') , ('PROV_1000021') , ('PROV_1000022') , ('PROV_1000023') , ('PROV_1000024') ,
            ('PROV_1000025') , ('PROV_1000026') , ('PROV_1000027') , ('PROV_1000028') , ('PROV_1000029') , ('PROV_1000030') , ('PROV_1000031') , ('PROV_1000032') , ('PROV_1000033') , ('PROV_1000034') , ('PROV_1000035') , ('PROV_1000036') , ('PROV_1000037') , ('PROV_1000038') , ('PROV_1000039') , ('PROV_1000040') , ('PROV_1000041') , ('PROV_1000042') , ('PROV_1000043') , ('PROV_1000044') , ('PROV_1000045') , ('PROV_1000046') , ('PROV_1000047') , ('PROV_1000048') , ('PROV_1000049') ,
            ('PROV_1000050') , ('PROV_1000051') , ('PROV_1000052') , ('PROV_1000053') , ('PROV_1000054') , ('PROV_1000055') , ('PROV_1000056') , ('PROV_1000057') , ('PROV_1000058') , ('PROV_1000059') , ('PROV_1000060') , ('PROV_1000061') , ('PROV_1000062') , ('PROV_1000063') , ('PROV_1000064') , ('PROV_1000065') , ('PROV_1000066') , ('PROV_1000067') , ('PROV_1000068') , ('PROV_1000069') , ('PROV_1000070') , ('PROV_1000071') , ('PROV_1000072') , ('PROV_1000073') , ('PROV_1000074') ,
            ('PROV_1000075') , ('PROV_1000076') , ('PROV_1000077') , ('PROV_1000078') , ('PROV_1000079') , ('PROV_1000080') , ('PROV_1000081') , ('PROV_1000082') , ('PROV_1000083') , ('PROV_1000084') , ('PROV_1000085') , ('PROV_1000086') , ('PROV_1000087') , ('PROV_1000088') , ('PROV_1000089') , ('PROV_1000090') , ('PROV_1000091') , ('PROV_1000092') , ('PROV_1000093') , ('PROV_1000094') , ('PROV_1000095') , ('PROV_1000096') , ('PROV_1000097') , ('PROV_1000098') , ('PROV_1000099') ,
            ('PROV_1000100') , ('PROV_1000101') , ('PROV_1000102') , ('PROV_1000103') , ('PROV_1000104') , ('PROV_1000105') , ('PROV_1000106') , ('PROV_1000107') , ('PROV_1000108') , ('PROV_1000109') , ('PROV_1000110') , ('PROV_1000111') , ('PROV_1000112') , ('PROV_1000113') , ('PROV_1000114') , ('PROV_1000115') , ('PROV_1000116') , ('PROV_1000117') , ('PROV_1000118') , ('PROV_1000119') , ('PROV_1000120') , ('PROV_1000121') , ('PROV_1000122') , ('PROV_1000123') , ('PROV_1000124') ,
            ('PROV_1000125') , ('PROV_1000126') , ('PROV_1000127') , ('PROV_1000128') , ('PROV_1000129') , ('PROV_1000130') , ('PROV_1000131') , ('PROV_1000132') , ('PROV_1000133') , ('PROV_1000134') , ('PROV_1000135') , ('PROV_1000136') , ('PROV_1000137') , ('PROV_1000138') , ('PROV_1000139') , ('PROV_1000140') , ('PROV_1000141') , ('PROV_1000142') , ('PROV_1000143') , ('PROV_1000144') , ('PROV_1000145') , ('PROV_1000146') , ('PROV_1000147') , ('PROV_1000148') , ('PROV_1000149') ,
            ('PROV_1000150') , ('PROV_1000151') , ('PROV_1000152') , ('PROV_1000153') , ('PROV_1000154') , ('PROV_1000155') , ('PROV_1000156') , ('PROV_1000157') , ('PROV_1000158') , ('PROV_1000159') , ('PROV_1000160') , ('PROV_1000161') , ('PROV_1000162') , ('PROV_1000163') , ('PROV_1000164') , ('PROV_1000165') , ('PROV_1000166') , ('PROV_1000167') , ('PROV_1000168') , ('PROV_1000169') , ('PROV_1000170') , ('PROV_1000171') , ('PROV_1000172') , ('PROV_1000173') , ('PROV_1000174') ,
            ('PROV_1000175') , ('PROV_1000176') , ('PROV_1000177') , ('PROV_1000178') , ('PROV_1000179') , ('PROV_1000180') , ('PROV_1000181') , ('PROV_1000182') , ('PROV_1000183') , ('PROV_1000184') , ('PROV_1000185') , ('PROV_1000186') , ('PROV_1000187') , ('PROV_1000188') , ('PROV_1000189') , ('PROV_1000190') , ('PROV_1000191') , ('PROV_1000192') , ('PROV_1000193') , ('PROV_1000194') , ('PROV_1000195') , ('PROV_1000196') , ('PROV_1000197') , ('PROV_1000198') , ('PROV_1000199') ,
            ('PROV_1000200') , ('PROV_1000201') , ('PROV_1000202') , ('PROV_1000203') , ('PROV_1000204') , ('PROV_1000205') , ('PROV_1000206') , ('PROV_1000207') , ('PROV_1000208') , ('PROV_1000209') , ('PROV_1000210') , ('PROV_1000211') , ('PROV_1000212') , ('PROV_1000213') , ('PROV_1000214') , ('PROV_1000215') , ('PROV_1000216') , ('PROV_1000217') , ('PROV_1000218') , ('PROV_1000219') , ('PROV_1000220') , ('PROV_1000221') , ('PROV_1000222') , ('PROV_1000223') , ('PROV_1000224') ,
            ('PROV_1000225') , ('PROV_1000226') , ('PROV_1000227') , ('PROV_1000228') , ('PROV_1000229') , ('PROV_1000230') , ('PROV_1000231') , ('PROV_1000232') , ('PROV_1000233') , ('PROV_1000234') , ('PROV_1000235') , ('PROV_1000236') , ('PROV_1000237') , ('PROV_1000238') , ('PROV_1000239') , ('PROV_1000240') , ('PROV_1000241') , ('PROV_1000242') , ('PROV_1000243') , ('PROV_1000244') , ('PROV_1000245') , ('PROV_1000246') , ('PROV_1000247') , ('PROV_1000248') , ('PROV_1000249') ,
            ('PROV_1000250') , ('PROV_1000251') , ('PROV_1000252') , ('PROV_1000253') , ('PROV_1000254') , ('PROV_1000255') , ('PROV_1000256') , ('PROV_1000257') , ('PROV_1000258') , ('PROV_1000259') , ('PROV_1000260') , ('PROV_1000261') , ('PROV_1000262') , ('PROV_1000263') , ('PROV_1000264') , ('PROV_1000265') , ('PROV_1000266') , ('PROV_1000267') , ('PROV_1000268') , ('PROV_1000269') , ('PROV_1000270') , ('PROV_1000271') , ('PROV_1000272') , ('PROV_1000273') , ('PROV_1000274') ,
            ('PROV_1000275') , ('PROV_1000276') , ('PROV_1000277') , ('PROV_1000278') , ('PROV_1000279') , ('PROV_1000280') , ('PROV_1000281') , ('PROV_1000282') , ('PROV_1000283') , ('PROV_1000284') , ('PROV_1000285') , ('PROV_1000286') , ('PROV_1000287') , ('PROV_1000288') , ('PROV_1000289') , ('PROV_1000290') , ('PROV_1000291') , ('PROV_1000292') , ('PROV_1000293') , ('PROV_1000294') , ('PROV_1000295') , ('PROV_1000296') , ('PROV_1000297') , ('PROV_1000298') , ('PROV_1000299') ,
            ('PROV_1000300') , ('PROV_1000301') , ('PROV_1000302') , ('PROV_1000303') , ('PROV_1000304') , ('PROV_1000305') , ('PROV_1000306') , ('PROV_1000307') , ('PROV_1000308') , ('PROV_1000309') , ('PROV_1000310') , ('PROV_1000311') , ('PROV_1000312') , ('PROV_1000313') , ('PROV_1000314') , ('PROV_1000315') , ('PROV_1000316') , ('PROV_1000317') , ('PROV_1000318') , ('PROV_1000319') , ('PROV_1000320') , ('PROV_1000321') , ('PROV_1000322') , ('PROV_1000323') , ('PROV_1000324') ,
            ('PROV_1000325') , ('PROV_1000326') , ('PROV_1000327') , ('PROV_1000328') , ('PROV_1000329') , ('PROV_1000330') , ('PROV_1000331') , ('PROV_1000332') , ('PROV_1000333') , ('PROV_1000334') , ('PROV_1000335') , ('PROV_1000336') , ('PROV_1000337') , ('PROV_1000338') , ('PROV_1000339') , ('PROV_1000340') , ('PROV_1000341') , ('PROV_1000342') , ('PROV_1000343') , ('PROV_1000344') , ('PROV_1000345') , ('PROV_1000346') , ('PROV_1000347') , ('PROV_1000348') , ('PROV_1000349') ,
            ('PROV_1000350') , ('PROV_1000351') , ('PROV_1000352') , ('PROV_1000353') , ('PROV_1000354') , ('PROV_1000355') , ('PROV_1000356') , ('PROV_1000357') , ('PROV_1000358') , ('PROV_1000359') , ('PROV_1000360') , ('PROV_1000361') , ('PROV_1000362') , ('PROV_1000363') , ('PROV_1000364') , ('PROV_1000365') , ('PROV_1000366') , ('PROV_1000367') , ('PROV_1000368') , ('PROV_1000369') , ('PROV_1000370') , ('PROV_1000371') , ('PROV_1000372') , ('PROV_1000373') , ('PROV_1000374') ,
            ('PROV_1000375') , ('PROV_1000376') , ('PROV_1000377') , ('PROV_1000378') , ('PROV_1000379') , ('PROV_1000380') , ('PROV_1000381') , ('PROV_1000382') , ('PROV_1000383') , ('PROV_1000384') , ('PROV_1000385') , ('PROV_1000386') , ('PROV_1000387') , ('PROV_1000388') , ('PROV_1000389') , ('PROV_1000390') , ('PROV_1000391') , ('PROV_1000392') , ('PROV_1000393') , ('PROV_1000394') , ('PROV_1000395') , ('PROV_1000396') , ('PROV_1000397') , ('PROV_1000398') , ('PROV_1000399') ,
            ('PROV_1000400') , ('PROV_1000401') , ('PROV_1000402') , ('PROV_1000403') , ('PROV_1000404') , ('PROV_1000405') , ('PROV_1000406') , ('PROV_1000407') , ('PROV_1000408') , ('PROV_1000409') , ('PROV_1000410') , ('PROV_1000411') , ('PROV_1000412') , ('PROV_1000413') , ('PROV_1000414') , ('PROV_1000415') , ('PROV_1000416') , ('PROV_1000417') , ('PROV_1000418') , ('PROV_1000419') , ('PROV_1000420') , ('PROV_1000421') , ('PROV_1000422') , ('PROV_1000423') , ('PROV_1000424') ,
            ('PROV_1000425') , ('PROV_1000426') , ('PROV_1000427') , ('PROV_1000428') , ('PROV_1000429') , ('PROV_1000430') , ('PROV_1000431') , ('PROV_1000432') , ('PROV_1000433') , ('PROV_1000434') , ('PROV_1000435') , ('PROV_1000436') , ('PROV_1000437') , ('PROV_1000438') , ('PROV_1000439') , ('PROV_1000440') , ('PROV_1000441') , ('PROV_1000442') , ('PROV_1000443') , ('PROV_1000444') , ('PROV_1000445') , ('PROV_1000446') , ('PROV_1000447') , ('PROV_1000448') , ('PROV_1000449') ,
            ('PROV_1000450') , ('PROV_1000451') , ('PROV_1000452') , ('PROV_1000453') , ('PROV_1000454') , ('PROV_1000455') , ('PROV_1000456') , ('PROV_1000457') , ('PROV_1000458') , ('PROV_1000459') , ('PROV_1000460') , ('PROV_1000461') , ('PROV_1000462') , ('PROV_1000463') , ('PROV_1000464') , ('PROV_1000465') , ('PROV_1000466') , ('PROV_1000467') , ('PROV_1000468') , ('PROV_1000469') , ('PROV_1000470') , ('PROV_1000471') , ('PROV_1000472') , ('PROV_1000473') , ('PROV_1000474') ,
            ('PROV_1000475') , ('PROV_1000476') , ('PROV_1000477') , ('PROV_1000478') , ('PROV_1000479') , ('PROV_1000480') , ('PROV_1000481') , ('PROV_1000482') , ('PROV_1000483') , ('PROV_1000484') , ('PROV_1000485') , ('PROV_1000486') , ('PROV_1000487') , ('PROV_1000488') , ('PROV_1000489') , ('PROV_1000490') , ('PROV_1000491') , ('PROV_1000492') , ('PROV_1000493') , ('PROV_1000494') , ('PROV_1000495') , ('PROV_1000496') , ('PROV_1000497') , ('PROV_1000498') , ('PROV_1000499') ,
            ('PROV_1000500') , ('PROV_1000501') , ('PROV_1000502') , ('PROV_1000503') , ('PROV_1000504') , ('PROV_1000505') , ('PROV_1000506') , ('PROV_1000507') , ('PROV_1000508') , ('PROV_1000509') , ('PROV_1000510') , ('PROV_1000511') , ('PROV_1000512') , ('PROV_1000513') , ('PROV_1000514') , ('PROV_1000515') , ('PROV_1000516') , ('PROV_1000517') , ('PROV_1000518') , ('PROV_1000519') , ('PROV_1000520') , ('PROV_1000521') , ('PROV_1000522') , ('PROV_1000523') , ('PROV_1000524') ,
            ('PROV_1000525') , ('PROV_1000526') , ('PROV_1000527') , ('PROV_1000528') , ('PROV_1000529') , ('PROV_1000530') , ('PROV_1000531') , ('PROV_1000532') , ('PROV_1000533') , ('PROV_1000534') , ('PROV_1000535') , ('PROV_1000536') , ('PROV_1000537') , ('PROV_1000538') , ('PROV_1000539') , ('PROV_1000540') , ('PROV_1000541') , ('PROV_1000542') , ('PROV_1000543') , ('PROV_1000544') , ('PROV_1000545') , ('PROV_1000546') , ('PROV_1000547') , ('PROV_1000548') , ('PROV_1000549') ,
            ('PROV_1000550') , ('PROV_1000551') , ('PROV_1000552') , ('PROV_1000553') , ('PROV_1000554') , ('PROV_1000555') , ('PROV_1000556') , ('PROV_1000557') , ('PROV_1000558') , ('PROV_1000559') , ('PROV_1000560') , ('PROV_1000561') , ('PROV_1000562') , ('PROV_1000563') , ('PROV_1000564') , ('PROV_1000565') , ('PROV_1000566') , ('PROV_1000567') , ('PROV_1000568') , ('PROV_1000569') , ('PROV_1000570') , ('PROV_1000571') , ('PROV_1000572') , ('PROV_1000573') , ('PROV_1000574') ,
            ('PROV_1000575') , ('PROV_1000576') , ('PROV_1000577') , ('PROV_1000578') , ('PROV_1000579') , ('PROV_1000580') , ('PROV_1000581') , ('PROV_1000582') , ('PROV_1000583') , ('PROV_1000584') , ('PROV_1000585') , ('PROV_1000586') , ('PROV_1000587') , ('PROV_1000588') , ('PROV_1000589') , ('PROV_1000590') , ('PROV_1000591') , ('PROV_1000592') , ('PROV_1000593') , ('PROV_1000594') , ('PROV_1000595') , ('PROV_1000596') , ('PROV_1000597') , ('PROV_1000598') , ('PROV_1000599') ,
            ('PROV_1000600') , ('PROV_1000601') , ('PROV_1000602') , ('PROV_1000603') , ('PROV_1000604') , ('PROV_1000605') , ('PROV_1000606') , ('PROV_1000607') , ('PROV_1000608') , ('PROV_1000609') , ('PROV_1000610') , ('PROV_1000611') , ('PROV_1000612') , ('PROV_1000613') , ('PROV_1000614') , ('PROV_1000615') , ('PROV_1000616') , ('PROV_1000617') , ('PROV_1000618') , ('PROV_1000619') , ('PROV_1000620') , ('PROV_1000621') , ('PROV_1000622') , ('PROV_1000623') , ('PROV_1000624') ,
            ('PROV_1000625') , ('PROV_1000626') , ('PROV_1000627') , ('PROV_1000628') , ('PROV_1000629') , ('PROV_1000630') , ('PROV_1000631') , ('PROV_1000632') , ('PROV_1000633') , ('PROV_1000634') , ('PROV_1000635') , ('PROV_1000636') , ('PROV_1000637') , ('PROV_1000638') , ('PROV_1000639') , ('PROV_1000640') , ('PROV_1000641') , ('PROV_1000642') , ('PROV_1000643') , ('PROV_1000644') , ('PROV_1000645') , ('PROV_1000646') , ('PROV_1000647') , ('PROV_1000648') , ('PROV_1000649') ,
            ('PROV_1000650') , ('PROV_1000651') , ('PROV_1000652') , ('PROV_1000653') , ('PROV_1000654') , ('PROV_1000655') , ('PROV_1000656') , ('PROV_1000657') , ('PROV_1000658') , ('PROV_1000659') , ('PROV_1000660') , ('PROV_1000661') , ('PROV_1000662') , ('PROV_1000663') , ('PROV_1000664') , ('PROV_1000665') , ('PROV_1000666') , ('PROV_1000667') , ('PROV_1000668') , ('PROV_1000669') , ('PROV_1000670') , ('PROV_1000671') , ('PROV_1000672') , ('PROV_1000673') , ('PROV_1000674') ,
            ('PROV_1000675') , ('PROV_1000676') , ('PROV_1000677') , ('PROV_1000678') , ('PROV_1000679') , ('PROV_1000680') , ('PROV_1000681') , ('PROV_1000682') , ('PROV_1000683') , ('PROV_1000684') , ('PROV_1000685') , ('PROV_1000686') , ('PROV_1000687') , ('PROV_1000688') , ('PROV_1000689') , ('PROV_1000690') , ('PROV_1000691') , ('PROV_1000692') , ('PROV_1000693') , ('PROV_1000694') , ('PROV_1000695') , ('PROV_1000696') , ('PROV_1000697') , ('PROV_1000698') , ('PROV_1000699') ,
            ('PROV_1000700') , ('PROV_1000701') , ('PROV_1000702') , ('PROV_1000703') , ('PROV_1000704') , ('PROV_1000705') , ('PROV_1000706') , ('PROV_1000707') , ('PROV_1000708') , ('PROV_1000709') , ('PROV_1000710') , ('PROV_1000711') , ('PROV_1000712') , ('PROV_1000713') , ('PROV_1000714') , ('PROV_1000715') , ('PROV_1000716') , ('PROV_1000717') , ('PROV_1000718') , ('PROV_1000719') , ('PROV_1000720') , ('PROV_1000721') , ('PROV_1000722') , ('PROV_1000723') , ('PROV_1000724') ,
            ('PROV_1000725') , ('PROV_1000726') , ('PROV_1000727') , ('PROV_1000728') , ('PROV_1000729') , ('PROV_1000730') , ('PROV_1000731') , ('PROV_1000732') , ('PROV_1000733') , ('PROV_1000734') , ('PROV_1000735') , ('PROV_1000736') , ('PROV_1000737') , ('PROV_1000738') , ('PROV_1000739') , ('PROV_1000740') , ('PROV_1000741') , ('PROV_1000742') , ('PROV_1000743') , ('PROV_1000744') , ('PROV_1000745') , ('PROV_1000746') , ('PROV_1000747') , ('PROV_1000748') , ('PROV_1000749') ,
            ('PROV_1000750') , ('PROV_1000751') , ('PROV_1000752') , ('PROV_1000753') , ('PROV_1000754') , ('PROV_1000755') , ('PROV_1000756') , ('PROV_1000757') , ('PROV_1000758') , ('PROV_1000759') , ('PROV_1000760') , ('PROV_1000761') , ('PROV_1000762') , ('PROV_1000763') , ('PROV_1000764') , ('PROV_1000765') , ('PROV_1000766') , ('PROV_1000767') , ('PROV_1000768') , ('PROV_1000769') , ('PROV_1000770') , ('PROV_1000771') , ('PROV_1000772') , ('PROV_1000773') , ('PROV_1000774') ,
            ('PROV_1000775') , ('PROV_1000776') , ('PROV_1000777') , ('PROV_1000778') , ('PROV_1000779') , ('PROV_1000780') , ('PROV_1000781') , ('PROV_1000782') , ('PROV_1000783') , ('PROV_1000784') , ('PROV_1000785') , ('PROV_1000786') , ('PROV_1000787') , ('PROV_1000788') , ('PROV_1000789') , ('PROV_1000790') , ('PROV_1000791') , ('PROV_1000792') , ('PROV_1000793') , ('PROV_1000794') , ('PROV_1000795') , ('PROV_1000796') , ('PROV_1000797') , ('PROV_1000798') , ('PROV_1000799') ,
            ('PROV_1000800') , ('PROV_1000801') , ('PROV_1000802') , ('PROV_1000803') , ('PROV_1000804') , ('PROV_1000805') , ('PROV_1000806') , ('PROV_1000807') , ('PROV_1000808') , ('PROV_1000809') , ('PROV_1000810') , ('PROV_1000811') , ('PROV_1000812') , ('PROV_1000813') , ('PROV_1000814') , ('PROV_1000815') , ('PROV_1000816') , ('PROV_1000817') , ('PROV_1000818') , ('PROV_1000819') , ('PROV_1000820') , ('PROV_1000821') , ('PROV_1000822') , ('PROV_1000823') , ('PROV_1000824') ,
            ('PROV_1000825') , ('PROV_1000826') , ('PROV_1000827') , ('PROV_1000828') , ('PROV_1000829') , ('PROV_1000830') , ('PROV_1000831') , ('PROV_1000832') , ('PROV_1000833') , ('PROV_1000834') , ('PROV_1000835') , ('PROV_1000836') , ('PROV_1000837') , ('PROV_1000838') , ('PROV_1000839') , ('PROV_1000840') , ('PROV_1000841') , ('PROV_1000842') , ('PROV_1000843') , ('PROV_1000844') , ('PROV_1000845') , ('PROV_1000846') , ('PROV_1000847') , ('PROV_1000848') , ('PROV_1000849') ,
            ('PROV_1000850') , ('PROV_1000851') , ('PROV_1000852') , ('PROV_1000853') , ('PROV_1000854') , ('PROV_1000855') , ('PROV_1000856') , ('PROV_1000857') , ('PROV_1000858') , ('PROV_1000859') , ('PROV_1000860') , ('PROV_1000861') , ('PROV_1000862') , ('PROV_1000863') , ('PROV_1000864') , ('PROV_1000865') , ('PROV_1000866') , ('PROV_1000867') , ('PROV_1000868') , ('PROV_1000869') , ('PROV_1000870') , ('PROV_1000871') , ('PROV_1000872') , ('PROV_1000873') , ('PROV_1000874') ,
            ('PROV_1000875') , ('PROV_1000876') , ('PROV_1000877') , ('PROV_1000878') , ('PROV_1000879') , ('PROV_1000880') , ('PROV_1000881') , ('PROV_1000882') , ('PROV_1000883') , ('PROV_1000884') , ('PROV_1000885') , ('PROV_1000886') , ('PROV_1000887') , ('PROV_1000888') , ('PROV_1000889') , ('PROV_1000890') , ('PROV_1000891') , ('PROV_1000892') , ('PROV_1000893') , ('PROV_1000894') , ('PROV_1000895') , ('PROV_1000896') , ('PROV_1000897') , ('PROV_1000898') , ('PROV_1000899') ,
            ('PROV_1000900') , ('PROV_1000901') , ('PROV_1000902') , ('PROV_1000903') , ('PROV_1000904') , ('PROV_1000905') , ('PROV_1000906') , ('PROV_1000907') , ('PROV_1000908') , ('PROV_1000909') , ('PROV_1000910') , ('PROV_1000911') , ('PROV_1000912') , ('PROV_1000913') , ('PROV_1000914') , ('PROV_1000915') , ('PROV_1000916') , ('PROV_1000917') , ('PROV_1000918') , ('PROV_1000919') , ('PROV_1000920') , ('PROV_1000921') , ('PROV_1000922') , ('PROV_1000923') , ('PROV_1000924') ,
            ('PROV_1000925') , ('PROV_1000926') , ('PROV_1000927') , ('PROV_1000928') , ('PROV_1000929') , ('PROV_1000930') , ('PROV_1000931') , ('PROV_1000932') , ('PROV_1000933') , ('PROV_1000934') , ('PROV_1000935') , ('PROV_1000936') , ('PROV_1000937') , ('PROV_1000938') , ('PROV_1000939') , ('PROV_1000940') , ('PROV_1000941') , ('PROV_1000942') , ('PROV_1000943') , ('PROV_1000944') , ('PROV_1000945') , ('PROV_1000946') , ('PROV_1000947') , ('PROV_1000948') , ('PROV_1000949') ,
            ('PROV_1000950') , ('PROV_1000951') , ('PROV_1000952') , ('PROV_1000953') , ('PROV_1000954') , ('PROV_1000955') , ('PROV_1000956') , ('PROV_1000957') , ('PROV_1000958') , ('PROV_1000959') , ('PROV_1000960') , ('PROV_1000961') , ('PROV_1000962') , ('PROV_1000963') , ('PROV_1000964') , ('PROV_1000965') , ('PROV_1000966') , ('PROV_1000967') , ('PROV_1000968') , ('PROV_1000969') , ('PROV_1000970') , ('PROV_1000971') , ('PROV_1000972') , ('PROV_1000973') , ('PROV_1000974') ,
            ('PROV_1000975') , ('PROV_1000976') , ('PROV_1000977') , ('PROV_1000978') , ('PROV_1000979') , ('PROV_1000980') , ('PROV_1000981') , ('PROV_1000982') , ('PROV_1000983') , ('PROV_1000984') , ('PROV_1000985') , ('PROV_1000986') , ('PROV_1000987') , ('PROV_1000988') , ('PROV_1000989') , ('PROV_1000990') , ('PROV_1000991') , ('PROV_1000992') , ('PROV_1000993') , ('PROV_1000994') , ('PROV_1000995') , ('PROV_1000996') , ('PROV_1000997') , ('PROV_1000998') , ('PROV_1000999') )
            OR
            (D65_PROVIDER_ET) IN (('PROV_1001000') , ('PROV_1001001') , ('PROV_1001002') , ('PROV_1001003') , ('PROV_1001004') , ('PROV_1001005') , ('PROV_1001006') , ('PROV_1001007') , ('PROV_1001008') , ('PROV_1001009') , ('PROV_1001010') , ('PROV_1001011') , ('PROV_1001012') , ('PROV_1001013') , ('PROV_1001014') , ('PROV_1001015') , ('PROV_1001016') , ('PROV_1001017') , ('PROV_1001018') , ('PROV_1001019') , ('PROV_1001020') , ('PROV_1001021') , ('PROV_1001022') , ('PROV_1001023') , ('PROV_1001024') ,
            ('PROV_1001025') , ('PROV_1001026') , ('PROV_1001027') , ('PROV_1001028') , ('PROV_1001029') , ('PROV_1001030') , ('PROV_1001031') , ('PROV_1001032') , ('PROV_1001033') , ('PROV_1001034') , ('PROV_1001035') , ('PROV_1001036') , ('PROV_1001037') , ('PROV_1001038') , ('PROV_1001039') , ('PROV_1001040') , ('PROV_1001041') , ('PROV_1001042') , ('PROV_1001043') , ('PROV_1001044') , ('PROV_1001045') , ('PROV_1001046') , ('PROV_1001047') , ('PROV_1001048') , ('PROV_1001049') ,
            ('PROV_1001050') , ('PROV_1001051') , ('PROV_1001052') , ('PROV_1001053') , ('PROV_1001054') , ('PROV_1001055') , ('PROV_1001056') , ('PROV_1001057') , ('PROV_1001058') , ('PROV_1001059') , ('PROV_1001060') , ('PROV_1001061') , ('PROV_1001062') , ('PROV_1001063') , ('PROV_1001064') , ('PROV_1001065') , ('PROV_1001066') , ('PROV_1001067') , ('PROV_1001068') , ('PROV_1001069') , ('PROV_1001070') , ('PROV_1001071') , ('PROV_1001072') , ('PROV_1001073') , ('PROV_1001074') ,
            ('PROV_1001075') , ('PROV_1001076') , ('PROV_1001077') , ('PROV_1001078') , ('PROV_1001079') , ('PROV_1001080') , ('PROV_1001081') , ('PROV_1001082') , ('PROV_1001083') , ('PROV_1001084') , ('PROV_1001085') , ('PROV_1001086') , ('PROV_1001087') , ('PROV_1001088') , ('PROV_1001089') , ('PROV_1001090') , ('PROV_1001091') , ('PROV_1001092') , ('PROV_1001093') , ('PROV_1001094') , ('PROV_1001095') , ('PROV_1001096') , ('PROV_1001097') , ('PROV_1001098') , ('PROV_1001099') ,
            ('PROV_1001100') , ('PROV_1001101') , ('PROV_1001102') , ('PROV_1001103') , ('PROV_1001104') , ('PROV_1001105') , ('PROV_1001106') , ('PROV_1001107') , ('PROV_1001108') , ('PROV_1001109') , ('PROV_1001110') , ('PROV_1001111') , ('PROV_1001112') , ('PROV_1001113') , ('PROV_1001114') , ('PROV_1001115') , ('PROV_1001116') , ('PROV_1001117') , ('PROV_1001118') , ('PROV_1001119') , ('PROV_1001120') , ('PROV_1001121') , ('PROV_1001122') , ('PROV_1001123') , ('PROV_1001124') ,
            ('PROV_1001125') , ('PROV_1001126') , ('PROV_1001127') , ('PROV_1001128') , ('PROV_1001129') , ('PROV_1001130') , ('PROV_1001131') , ('PROV_1001132') , ('PROV_1001133') , ('PROV_1001134') , ('PROV_1001135') , ('PROV_1001136') , ('PROV_1001137') , ('PROV_1001138') , ('PROV_1001139') , ('PROV_1001140') , ('PROV_1001141') , ('PROV_1001142') , ('PROV_1001143') , ('PROV_1001144') , ('PROV_1001145') , ('PROV_1001146') , ('PROV_1001147') , ('PROV_1001148') , ('PROV_1001149') ,
            ('PROV_1001150') , ('PROV_1001151') , ('PROV_1001152') , ('PROV_1001153') , ('PROV_1001154') , ('PROV_1001155') , ('PROV_1001156') , ('PROV_1001157') , ('PROV_1001158') , ('PROV_1001159') , ('PROV_1001160') , ('PROV_1001161') , ('PROV_1001162') , ('PROV_1001163') , ('PROV_1001164') , ('PROV_1001165') , ('PROV_1001166') , ('PROV_1001167') , ('PROV_1001168') , ('PROV_1001169') , ('PROV_1001170') , ('PROV_1001171') , ('PROV_1001172') , ('PROV_1001173') , ('PROV_1001174') ,
            ('PROV_1001175') , ('PROV_1001176') , ('PROV_1001177') , ('PROV_1001178') , ('PROV_1001179') , ('PROV_1001180') , ('PROV_1001181') , ('PROV_1001182') , ('PROV_1001183') , ('PROV_1001184') , ('PROV_1001185') , ('PROV_1001186') , ('PROV_1001187') , ('PROV_1001188') , ('PROV_1001189') , ('PROV_1001190') , ('PROV_1001191') , ('PROV_1001192') , ('PROV_1001193') , ('PROV_1001194') , ('PROV_1001195') , ('PROV_1001196') , ('PROV_1001197') , ('PROV_1001198') , ('PROV_1001199') ,
            ('PROV_1001200') , ('PROV_1001201') , ('PROV_1001202') , ('PROV_1001203') , ('PROV_1001204') , ('PROV_1001205') , ('PROV_1001206') , ('PROV_1001207') , ('PROV_1001208') , ('PROV_1001209') , ('PROV_1001210') , ('PROV_1001211') , ('PROV_1001212') , ('PROV_1001213') , ('PROV_1001214') , ('PROV_1001215') , ('PROV_1001216') , ('PROV_1001217') , ('PROV_1001218') , ('PROV_1001219') , ('PROV_1001220') , ('PROV_1001221') , ('PROV_1001222') , ('PROV_1001223') , ('PROV_1001224') ,
            ('PROV_1001225') , ('PROV_1001226') , ('PROV_1001227') , ('PROV_1001228') , ('PROV_1001229') , ('PROV_1001230') , ('PROV_1001231') , ('PROV_1001232') , ('PROV_1001233') , ('PROV_1001234') , ('PROV_1001235') , ('PROV_1001236') , ('PROV_1001237') , ('PROV_1001238') , ('PROV_1001239') , ('PROV_1001240') , ('PROV_1001241') , ('PROV_1001242') , ('PROV_1001243') , ('PROV_1001244') , ('PROV_1001245') , ('PROV_1001246') , ('PROV_1001247') , ('PROV_1001248') , ('PROV_1001249') ,
            ('PROV_1001250') , ('PROV_1001251') , ('PROV_1001252') , ('PROV_1001253') , ('PROV_1001254') , ('PROV_1001255') , ('PROV_1001256') , ('PROV_1001257') , ('PROV_1001258') , ('PROV_1001259') , ('PROV_1001260') , ('PROV_1001261') , ('PROV_1001262') , ('PROV_1001263') , ('PROV_1001264') , ('PROV_1001265') , ('PROV_1001266') , ('PROV_1001267') , ('PROV_1001268') , ('PROV_1001269') , ('PROV_1001270') , ('PROV_1001271') , ('PROV_1001272') , ('PROV_1001273') , ('PROV_1001274') ,
            ('PROV_1001275') , ('PROV_1001276') , ('PROV_1001277') , ('PROV_1001278') , ('PROV_1001279') , ('PROV_1001280') , ('PROV_1001281') , ('PROV_1001282') , ('PROV_1001283') , ('PROV_1001284') , ('PROV_1001285') , ('PROV_1001286') , ('PROV_1001287') , ('PROV_1001288') , ('PROV_1001289') , ('PROV_1001290') , ('PROV_1001291') , ('PROV_1001292') , ('PROV_1001293') , ('PROV_1001294') , ('PROV_1001295') , ('PROV_1001296') , ('PROV_1001297') , ('PROV_1001298') , ('PROV_1001299') ,
            ('PROV_1001300') , ('PROV_1001301') , ('PROV_1001302') , ('PROV_1001303') , ('PROV_1001304') , ('PROV_1001305') , ('PROV_1001306') , ('PROV_1001307') , ('PROV_1001308') , ('PROV_1001309') , ('PROV_1001310') , ('PROV_1001311') , ('PROV_1001312') , ('PROV_1001313') , ('PROV_1001314') , ('PROV_1001315') , ('PROV_1001316') , ('PROV_1001317') , ('PROV_1001318') , ('PROV_1001319') , ('PROV_1001320') , ('PROV_1001321') , ('PROV_1001322') , ('PROV_1001323') , ('PROV_1001324') ,
            ('PROV_1001325') , ('PROV_1001326') , ('PROV_1001327') , ('PROV_1001328') , ('PROV_1001329') , ('PROV_1001330') , ('PROV_1001331') , ('PROV_1001332') , ('PROV_1001333') , ('PROV_1001334') , ('PROV_1001335') , ('PROV_1001336') , ('PROV_1001337') , ('PROV_1001338') , ('PROV_1001339') , ('PROV_1001340') , ('PROV_1001341') , ('PROV_1001342') , ('PROV_1001343') , ('PROV_1001344') , ('PROV_1001345') , ('PROV_1001346') , ('PROV_1001347') , ('PROV_1001348') , ('PROV_1001349') ,
            ('PROV_1001350') , ('PROV_1001351') , ('PROV_1001352') , ('PROV_1001353') , ('PROV_1001354') , ('PROV_1001355') , ('PROV_1001356') , ('PROV_1001357') , ('PROV_1001358') , ('PROV_1001359') , ('PROV_1001360') , ('PROV_1001361') , ('PROV_1001362') , ('PROV_1001363') , ('PROV_1001364') , ('PROV_1001365') , ('PROV_1001366') , ('PROV_1001367') , ('PROV_1001368') , ('PROV_1001369') , ('PROV_1001370') , ('PROV_1001371') , ('PROV_1001372') , ('PROV_1001373') , ('PROV_1001374') ,
            ('PROV_1001375') , ('PROV_1001376') , ('PROV_1001377') , ('PROV_1001378') , ('PROV_1001379') , ('PROV_1001380') , ('PROV_1001381') , ('PROV_1001382') , ('PROV_1001383') , ('PROV_1001384') , ('PROV_1001385') , ('PROV_1001386') , ('PROV_1001387') , ('PROV_1001388') , ('PROV_1001389') , ('PROV_1001390') , ('PROV_1001391') , ('PROV_1001392') , ('PROV_1100001') , ('PROV_1100002') , ('PROV_1100003') , ('PROV_1100004') , ('PROV_1100005') , ('PROV_1100006') , ('PROV_1100007') ,
            ('PROV_1100008') , ('PROV_1100009') , ('PROV_1100010') , ('PROV_1100011') , ('PROV_1100012') , ('PROV_1100013') , ('PROV_1100014') , ('PROV_1100015') , ('PROV_1100016') , ('PROV_1100017') , ('PROV_1100018') , ('PROV_1100019') , ('PROV_1100020') , ('PROV_1100021') , ('PROV_1100022') , ('PROV_1100023') , ('PROV_1100024') , ('PROV_1100025') , ('PROV_1100026') , ('PROV_1100027') , ('PROV_1100028') , ('PROV_1100029') , ('PROV_1100030') , ('PROV_1100031') , ('PROV_1100032') ,
            ('PROV_1100033') , ('PROV_1100034') , ('PROV_1100035') , ('PROV_1100036') , ('PROV_1100037') , ('PROV_1100038') , ('PROV_1100039') , ('PROV_1100040') , ('PROV_1100041') , ('PROV_1100042') , ('PROV_1100043') , ('PROV_1100044') , ('PROV_1100045') , ('PROV_1100046') , ('PROV_1100047') , ('PROV_1100048') , ('PROV_1100049') , ('PROV_1100050') , ('PROV_1100051') , ('PROV_1100052') , ('PROV_1100053') , ('PROV_1100054') , ('PROV_1100055') , ('PROV_1100056') , ('PROV_1100057') ,
            ('PROV_1100058') , ('PROV_1100059') , ('PROV_1100060') , ('PROV_1100061') , ('PROV_1100062') , ('PROV_1100063') , ('PROV_1100064') , ('PROV_1100065') , ('PROV_1100066') , ('PROV_1100067') , ('PROV_1100068') , ('PROV_1100069') , ('PROV_1100070') , ('PROV_1100071') , ('PROV_1100072') , ('PROV_1100073') , ('PROV_1100074') , ('PROV_1100075') , ('PROV_1100076') , ('PROV_1100077') , ('PROV_1100078') , ('PROV_1100079') , ('PROV_1100080') , ('PROV_1100081') ) )
            AND (NOT ((D65_PROVIDER_ET) IN (('ALL_PROV_NOTAPP') , ('PROV_NOTAPP') ) ) )
            AND ((D69_SPECIALTY_ET) = ('ALL_SPEC_1') )
            AND ((D61_DIAGNOSIS_ET) = ('ALL_D2GRP_1') )
            AND ((D63_PROCEDURE_ET) = ('ALL_PROC_1') )
            AND (D67_BUSINESS_L_GID = 15)
            AND (D73_POS_GID = 1)
            AND (D75_AGE_GROUP_GID = 1)
            AND (D67_BUSINESS_L_GID = 15)
            AND (D69_SPECIALTY_GID = 1)
            AND (D73_POS_GID = 1)
            AND (D61_DIAGNOSIS_GID = 3)
            AND (D63_PROCEDURE_GID = 3)
            AND (D75_AGE_GROUP_GID = 1) ) )
            V142 )
            V143
            MODEL
            PARTITION BY (
            D61_DIAGNOSIS_GID SP_D61_DIAGNOSIS_GID,
            D61_DIAGNOSIS_ET SP_D61_DIAGNOSIS_ET,
            D61_DIAGNOSIS_HierarchyNumber SP_ALIAS_3591,
            D63_PROCEDURE_GID SP_D63_PROCEDURE_GID,
            D63_PROCEDURE_ET SP_D63_PROCEDURE_ET,
            D63_PROCEDURE_HierarchyNumber SP_ALIAS_3592,
            D67_BUSINESS_L_GID SP_D67_BUSINESS_L_GID,
            D67_BUSINESS_L_ET SP_D67_BUSINESS_L_ET,
            ALIAS_R81 SP_ALIAS_R81,
            D69_SPECIALTY_GID SP_D69_SPECIALTY_GID,
            D69_SPECIALTY_ET SP_D69_SPECIALTY_ET,
            D69_SPECIALTY_HierarchyNumber SP_ALIAS_3593,
            D71_TIME_GID SP_D71_TIME_GID,
            D71_TIME_ET SP_D71_TIME_ET,
            D71_TIME_HierarchyNumber SP_D71_TIME_HierarchyNumber,
            D73_POS_GID SP_D73_POS_GID,
            D73_POS_ET SP_D73_POS_ET,
            D73_POS_HierarchyNumber SP_D73_POS_HierarchyNumber,
            D75_AGE_GROUP_GID SP_D75_AGE_GROUP_GID,
            D75_AGE_GROUP_ET SP_D75_AGE_GROUP_ET,
            D75_AGE_GROUP_HierarchyNumber SP_ALIAS_3594,
            D77_INCR_PAID_GID SP_D77_INCR_PAID_GID,
            D77_INCR_PAID_ET SP_D77_INCR_PAID_ET,
            D77_INCR_PAID_HierarchyNumber SP_ALIAS_3595)
            DIMENSION BY (
            ALIAS_190 SP_ALIAS_190,
            ALIAS_191 SP_ALIAS_191,
            ALIAS_169 SP_ALIAS_169,
            D65_PROVIDER_ET SP_D65_PROVIDER_ET,
            D65_PROVIDER_GID SP_D65_PROVIDER_GID)
            MEASURES (
            ALIAS_194 SP_ALIAS_194,
            ALIAS_192 SP_ALIAS_192,
            ALIAS_193 SP_ALIAS_193,
            C59_M_TIME_AWMEDICA_MED_PAIDA SP_ALIAS_3590,
            ALIAS_262 SP_ALIAS_262,
            ALIAS_256 SP_ALIAS_256,
            ALIAS_257 SP_ALIAS_257,
            ALIAS_205 SP_ALIAS_205)
            RULES UPDATE
            (
            SP_ALIAS_256[ANY , ANY , ANY , ANY , ANY ] = MAX(SP_ALIAS_3590) [
            SP_ALIAS_192[CV(SP_ALIAS_190) , CV(SP_ALIAS_191) , CV(SP_ALIAS_169) , CV(SP_D65_PROVIDER_ET) , CV(SP_D65_PROVIDER_GID) ] ,
            1,
            ANY ,
            ANY ,
            ANY ] ,
            SP_ALIAS_257[ANY , ANY , ANY , ANY , ANY ] = MAX(SP_ALIAS_3590) [
            SP_ALIAS_193[CV(SP_ALIAS_190) , CV(SP_ALIAS_191) , CV(SP_ALIAS_169) , CV(SP_D65_PROVIDER_ET) , CV(SP_D65_PROVIDER_GID) ] ,
            0,
            ANY ,
            ANY ,
            ANY ]
            ) )
            V144,
            (
            SELECT
            D52_BUSINESS_L_ET,
            D52_BUSINESS_L_GID,
            D52_BUSINESS_L_ALL_BL,
            D52_BUSINESS_L_LVL1,
            D52_BUSINESS_L_LVL2,
            D52_BUSINESS_L_LVL3,
            D52_BUSINESS_L_LVL4,
            ALIAS_R68
            FROM
            (
            SELECT
            D52_BUSINESS_L_ET,
            D52_BUSINESS_L_GID,
            D52_BUSINESS_L_ALL_BL,
            D52_BUSINESS_L_LVL1,
            D52_BUSINESS_L_LVL2,
            D52_BUSINESS_L_LVL3,
            D52_BUSINESS_L_LVL4,
            ALIAS_R68,
            ROW_NUMBER() OVER (
            ORDER BY
            D52_BUSINESS_L_ET ASC NULLS FIRST ) ALIAS_72
            FROM
            (
            SELECT /*+ no_expand_gset_to_union index_combine(*) */
            AW429.AW429_ET_COL_17 D52_BUSINESS_L_ET,
            AW429.AW429_GID_COL_19 D52_BUSINESS_L_GID,
            AW429.AW429_LEVEL_27 D52_BUSINESS_L_ALL_BL,
            AW429.AW429_LEVEL_26 D52_BUSINESS_L_LVL1,
            AW429.AW429_LEVEL_25 D52_BUSINESS_L_LVL2,
            AW429.AW429_LEVEL_24 D52_BUSINESS_L_LVL3,
            AW429.AW429_LEVEL_23 D52_BUSINESS_L_LVL4,
            0 ALIAS_R68
            FROM
            (SELECT SYS$DV.* FROM ((SELECT * FROM TABLE(OLAP_TABLE('TIME_AW.D2HA_V1 duration session',
            NULL,
            NULL,
            ' DMNS AW429_ET_COL_17 AS VARCHAR2(100) FROM BUSINESS_LEVEL WITH HRR BUSINESS_LEVEL_PARENTREL(BUSINESS_LEVEL_HIERLIST ''BUSINESS_LEVEL'') INH BUSINESS_LEVEL_INHIER GID AW429_GID_COL_19 AS NUMBER FROM BUSINESS_LEVEL_GID LRL AW429_LEVEL_23 AS VARCHAR2(100), AW429_LEVEL_24 AS VARCHAR2(100), AW429_LEVEL_25 AS VARCHAR2(100), AW429_LEVEL_26 AS VARCHAR2(100), AW429_LEVEL_27 AS VARCHAR2(100) FROM BUSINESS_LEVEL_FAMILYREL(BUSINESS_LEVEL_LEVELLIST ''LVL4''), BUSINESS_LEVEL_FAMILYREL(BUSINESS_LEVEL_LEVELLIST ''LVL3''), BUSINESS_LEVEL_FAMILYREL(BUSINESS_LEVEL_LEVELLIST ''LVL2''), BUSINESS_LEVEL_FAMILYREL(BUSINESS_LEVEL_LEVELLIST ''LVL1''), BUSINESS_LEVEL_FAMILYREL(BUSINESS_LEVEL_LEVELLIST ''ALL_BL'') MSR OLAP_EXPRESSION_14 FROM AW_EXPR(CONVERT(ISSESSION(&(__XML_GET_FULLTOAW_NAME(''BUSINESS_LEVEL.DIMENSION''))), INTEGER ))')) SQL MODEL DIMENSION BY(AW429_ET_COL_17,
            AW429_GID_COL_19) MEASURES(AW429_LEVEL_23,
            AW429_LEVEL_24,
            AW429_LEVEL_25,
            AW429_LEVEL_26,
            AW429_LEVEL_27,
            OLAP_EXPRESSION_14) UNIQUE SINGLE REFERENCE RULES UPDATE SEQUENTIAL ORDER ()) SYS$DV)) AW429 )
            D52
            WHERE
            (D52_BUSINESS_L_GID = 15) )
            V53
            WHERE
            (((ALIAS_72) = (1) )
            AND (D52_BUSINESS_L_GID = 15)
            AND (D52_BUSINESS_L_GID = 15) ) )
            V54,
            (
            SELECT
            D44_INCR_PAID_ET,
            D44_INCR_PAID_GID,
            D44_INCR_PAID_INCR_PAID,
            D44_INCR_PAID_HierarchyNumber
            FROM
            (
            SELECT
            D44_INCR_PAID_ET,
            D44_INCR_PAID_GID,
            D44_INCR_PAID_INCR_PAID,
            D44_INCR_PAID_HierarchyNumber,
            ROW_NUMBER() OVER (
            ORDER BY
            D44_INCR_PAID_ET ASC NULLS FIRST ) ALIAS_60
            FROM
            (
            SELECT /*+ no_expand_gset_to_union index_combine(*) */
            AW428.AW428_ET_COL_36 D44_INCR_PAID_ET,
            AW428.AW428_GID_COL_38 D44_INCR_PAID_GID,
            AW428.AW428_LEVEL_42 D44_INCR_PAID_INCR_PAID,
            0 D44_INCR_PAID_HierarchyNumber
            FROM
            (SELECT SYS$DV.* FROM ((SELECT * FROM TABLE(OLAP_TABLE('TIME_AW.D2HA_V1 duration session',
            NULL,
            NULL,
            ' DMNS AW428_ET_COL_36 AS VARCHAR2(100) FROM INCR_PAID WITH HRR INCR_PAID_PARENTREL(INCR_PAID_HIERLIST ''INCR_PAID'') INH INCR_PAID_INHIER GID AW428_GID_COL_38 AS NUMBER FROM INCR_PAID_GID LRL AW428_LEVEL_42 AS VARCHAR2(100) FROM INCR_PAID_FAMILYREL(INCR_PAID_LEVELLIST ''INCR_PAID'') MSR OLAP_EXPRESSION_12 FROM AW_EXPR(CONVERT(ISSESSION(&(__XML_GET_FULLTOAW_NAME(''INCR_PAID.DIMENSION''))), INTEGER ))')) SQL MODEL DIMENSION BY(AW428_ET_COL_36,
            AW428_GID_COL_38) MEASURES(AW428_LEVEL_42,
            OLAP_EXPRESSION_12) UNIQUE SINGLE REFERENCE RULES UPDATE SEQUENTIAL ORDER ()) SYS$DV)) AW428 )
            D44 )
            V45
            WHERE
            ((ALIAS_60) = (1) ) )
            V46,
            (SELECT 1 R, 1 C1, 'ALL_SPEC_1' C2, 1 C3, 'ALL_SPEC_1' C4, CAST(NULL AS VARCHAR2(30)) C5, 0 C6 FROM DUAL ) T216,
            (
            SELECT
            D28_POS_ET,
            D28_POS_GID,
            D28_POS_ALL_POS,
            D28_POS_POS,
            D28_POS_HierarchyNumber
            FROM
            (
            SELECT
            D28_POS_ET,
            D28_POS_GID,
            D28_POS_ALL_POS,
            D28_POS_POS,
            D28_POS_HierarchyNumber,
            ROW_NUMBER() OVER (
            ORDER BY
            D28_POS_ET ASC NULLS FIRST ) ALIAS_40
            FROM
            (
            SELECT /*+ no_expand_gset_to_union index_combine(*) */
            AW427.AW427_ET_COL_91 D28_POS_ET,
            AW427.AW427_GID_COL_93 D28_POS_GID,
            AW427.AW427_LEVEL_98 D28_POS_ALL_POS,
            AW427.AW427_LEVEL_97 D28_POS_POS,
            0 D28_POS_HierarchyNumber
            FROM
            (SELECT SYS$DV.* FROM ((SELECT * FROM TABLE(OLAP_TABLE('TIME_AW.D2HA_V1 duration session',
            NULL,
            NULL,
            ' DMNS AW427_ET_COL_91 AS VARCHAR2(100) FROM POS WITH HRR POS_PARENTREL(POS_HIERLIST ''POS'') INH POS_INHIER GID AW427_GID_COL_93 AS NUMBER FROM POS_GID LRL AW427_LEVEL_97 AS VARCHAR2(100), AW427_LEVEL_98 AS VARCHAR2(100) FROM POS_FAMILYREL(POS_LEVELLIST ''POS''), POS_FAMILYREL(POS_LEVELLIST ''ALL_POS'') MSR OLAP_EXPRESSION_9 FROM AW_EXPR(CONVERT(ISSESSION(&(__XML_GET_FULLTOAW_NAME(''POS.DIMENSION''))), INTEGER ))')) SQL MODEL DIMENSION BY(AW427_ET_COL_91,
            AW427_GID_COL_93) MEASURES(AW427_LEVEL_97,
            AW427_LEVEL_98,
            OLAP_EXPRESSION_9) UNIQUE SINGLE REFERENCE RULES UPDATE SEQUENTIAL ORDER ()) SYS$DV)) AW427 )
            D28
            WHERE
            (D28_POS_GID = 1) )
            V29
            WHERE
            (((ALIAS_40) = (1) )
            AND (D28_POS_GID = 1)
            AND (D28_POS_GID = 1) ) )
            V30,
            (SELECT 1 R, 1 C1, 'ALL_D2GRP_1' C2, 3 C3, 'ALL_D2GRP_1' C4, CAST(NULL AS VARCHAR2(30)) C5, CAST(NULL AS VARCHAR2(30)) C6, CAST(NULL AS VARCHAR2(30)) C7, CAST(NULL AS VARCHAR2(30)) C8, CAST(NULL AS VARCHAR2(30)) C9, CAST(NULL AS VARCHAR2(30)) C10, CAST(NULL AS VARCHAR2(30)) C11, 0 C12 FROM DUAL ) T217,
            (SELECT 1 R, 1 C1, 'ALL_PROC_1' C2, 3 C3, 'ALL_PROC_1' C4, CAST(NULL AS VARCHAR2(30)) C5, CAST(NULL AS VARCHAR2(30)) C6, 0 C7 FROM DUAL ) T218,
            (
            SELECT
            D4_AGE_GROUP_ET,
            D4_AGE_GROUP_GID,
            D4_AGE_GROUP_ALL_AGE_GRP,
            D4_AGE_GROUP_AGE_GRP,
            D4_AGE_GROUP_HierarchyNumber
            FROM
            (
            SELECT
            D4_AGE_GROUP_ET,
            D4_AGE_GROUP_GID,
            D4_AGE_GROUP_ALL_AGE_GRP,
            D4_AGE_GROUP_AGE_GRP,
            D4_AGE_GROUP_HierarchyNumber,
            ROW_NUMBER() OVER (
            ORDER BY
            D4_AGE_GROUP_ET ASC NULLS FIRST ) ALIAS_9
            FROM
            (
            SELECT /*+ no_expand_gset_to_union index_combine(*) */
            AW426.AW426_ET_COL_28 D4_AGE_GROUP_ET,
            AW426.AW426_GID_COL_30 D4_AGE_GROUP_GID,
            AW426.AW426_LEVEL_35 D4_AGE_GROUP_ALL_AGE_GRP,
            AW426.AW426_LEVEL_34 D4_AGE_GROUP_AGE_GRP,
            0 D4_AGE_GROUP_HierarchyNumber
            FROM
            (SELECT SYS$DV.* FROM ((SELECT * FROM TABLE(OLAP_TABLE('TIME_AW.D2HA_V1 duration session',
            NULL,
            NULL,
            ' DMNS AW426_ET_COL_28 AS VARCHAR2(100) FROM AGE_GROUP WITH HRR AGE_GROUP_PARENTREL(AGE_GROUP_HIERLIST ''AGE_GROUP'') INH AGE_GROUP_INHIER GID AW426_GID_COL_30 AS NUMBER FROM AGE_GROUP_GID LRL AW426_LEVEL_34 AS VARCHAR2(100), AW426_LEVEL_35 AS VARCHAR2(100) FROM AGE_GROUP_FAMILYREL(AGE_GROUP_LEVELLIST ''AGE_GRP''), AGE_GROUP_FAMILYREL(AGE_GROUP_LEVELLIST ''ALL_AGE_GRP'') MSR OLAP_EXPRESSION_1 FROM AW_EXPR(CONVERT(ISSESSION(&(__XML_GET_FULLTOAW_NAME(''AGE_GROUP.DIMENSION''))), INTEGER ))')) SQL MODEL DIMENSION BY(AW426_ET_COL_28,
            AW426_GID_COL_30) MEASURES(AW426_LEVEL_34,
            AW426_LEVEL_35,
            OLAP_EXPRESSION_1) UNIQUE SINGLE REFERENCE RULES UPDATE SEQUENTIAL ORDER ()) SYS$DV)) AW426 )
            D4
            WHERE
            (D4_AGE_GROUP_GID = 1) )
            V5
            WHERE
            (((ALIAS_9) = (1) )
            AND (D4_AGE_GROUP_GID = 1)
            AND (D4_AGE_GROUP_GID = 1) ) )
            V6
            WHERE
            ((NOT ((SP_ALIAS_190) IN (('ALL_PROV_NOTAPP') , ('PROV_NOTAPP') ) ) )
            AND (T216.C3 = 1)
            AND ((T217.C3 = 3)
            AND (T217.C12 = 0) )
            AND (T218.C3 = 3)
            AND (SYS_OP_MAP_NONNULL(SP_D67_BUSINESS_L_GID) = SYS_OP_MAP_NONNULL(D52_BUSINESS_L_GID) )
            AND (SYS_OP_MAP_NONNULL(SP_D67_BUSINESS_L_ET) = SYS_OP_MAP_NONNULL(D52_BUSINESS_L_ET) )
            AND (SYS_OP_MAP_NONNULL(SP_D77_INCR_PAID_GID) = SYS_OP_MAP_NONNULL(D44_INCR_PAID_GID) )
            AND (SYS_OP_MAP_NONNULL(SP_D77_INCR_PAID_ET) = SYS_OP_MAP_NONNULL(D44_INCR_PAID_ET) )
            AND (SYS_OP_MAP_NONNULL(T216.C3) = SYS_OP_MAP_NONNULL(SP_D69_SPECIALTY_GID) )
            AND (SYS_OP_MAP_NONNULL(T216.C2) = SYS_OP_MAP_NONNULL(SP_D69_SPECIALTY_ET) )
            AND (SYS_OP_MAP_NONNULL(SP_D73_POS_GID) = SYS_OP_MAP_NONNULL(D28_POS_GID) )
            AND (SYS_OP_MAP_NONNULL(SP_D73_POS_ET) = SYS_OP_MAP_NONNULL(D28_POS_ET) )
            AND (SYS_OP_MAP_NONNULL(T217.C3) = SYS_OP_MAP_NONNULL(SP_D61_DIAGNOSIS_GID) )
            AND (SYS_OP_MAP_NONNULL(T217.C2) = SYS_OP_MAP_NONNULL(SP_D61_DIAGNOSIS_ET) )
            AND (SYS_OP_MAP_NONNULL(T218.C3) = SYS_OP_MAP_NONNULL(SP_D63_PROCEDURE_GID) )
            AND (SYS_OP_MAP_NONNULL(T218.C2) = SYS_OP_MAP_NONNULL(SP_D63_PROCEDURE_ET) )
            AND (SYS_OP_MAP_NONNULL(SP_D75_AGE_GROUP_GID) = SYS_OP_MAP_NONNULL(D4_AGE_GROUP_GID) )
            AND (SYS_OP_MAP_NONNULL(SP_D75_AGE_GROUP_ET) = SYS_OP_MAP_NONNULL(D4_AGE_GROUP_ET) )
            AND (NOT ((SP_D65_PROVIDER_ET) IN (('ALL_PROV_NOTAPP') , ('PROV_NOTAPP') ) ) ) )
            ORDER BY
            D4_AGE_GROUP_ALL_AGE_GRP ASC NULLS FIRST ,
            D4_AGE_GROUP_AGE_GRP ASC NULLS FIRST ,
            T218.C4 ASC NULLS FIRST ,
            T218.C5 ASC NULLS FIRST ,
            T218.C6 ASC NULLS FIRST ,
            T217.C4 ASC NULLS FIRST ,
            T217.C5 ASC NULLS FIRST ,
            T217.C6 ASC NULLS FIRST ,
            D28_POS_ALL_POS ASC NULLS FIRST ,
            D28_POS_POS ASC NULLS FIRST ,
            T216.C4 ASC NULLS FIRST ,
            T216.C5 ASC NULLS FIRST ,
            D44_INCR_PAID_ET ASC NULLS FIRST ,
            D52_BUSINESS_L_ALL_BL ASC NULLS FIRST ,
            D52_BUSINESS_L_LVL1 ASC NULLS FIRST ,
            D52_BUSINESS_L_LVL2 ASC NULLS FIRST ,
            D52_BUSINESS_L_LVL3 ASC NULLS FIRST ,
            D52_BUSINESS_L_LVL4 ASC NULLS FIRST ,
            SP_ALIAS_256 DESC NULLS FIRST ,
            SP_ALIAS_192 ASC NULLS FIRST ,
            SP_ALIAS_257 DESC NULLS FIRST ,
            SP_ALIAS_193 ASC NULLS FIRST ,
            SP_ALIAS_262 ASC NULLS FIRST

            --------------------------------------------------------------------------------------------------------------------------------
            Thanks
            Subash
            • 3. Re: SQL tunning
              Billy~Verreynne
              Sheez.. and I thought I had seen it all as far as crappy SQL goes... :-)

              It's unlikely that a mere 'database configuration' is the cause of the slow performance.

              This query uses a lot of nested SQLs. It uses pipe line table functions. It is extremely large and thus very likely as complex. It uses massive IN clauses. It attempts to force the CBO execution plan via hardcoded execution plan hints.

              Throw all this together, stir, and you have an excellent concoction to set fire to your Oracle server.

              If anything, I would say the problem is with this OLAP API. Unfortunately, one often get the situation where developers develop software in an ideal environment without any clue as the real world environment the software will run in and the data volumes it will process. I would not be surprised if this is the case here.

              To start to tune this bohemeth, you most definitely would need to get an execution plan for it to determine just which parts the CBO considers the most expensive.
              • 4. Re: SQL tunning
                516501
                You tried FIRST_ROWS or RULE hint? If you want to use, remove all hints inside your query.
                I have found best performane using these hints.

                If possible, post on-line plan of the query using
                SELECT id , lpad (' ', depth) || operation operation , options, object_owner , object_name, cost, bytes FROM V$SQL_PLAN WHERE (hash_value,address)=(select sql_hash_value, sql_address from v$session where sid=&sid) START WITH id = 0 CONNECT BY ( prior id = parent_id AND prior hash_value = hash_value AND prior child_number = child_number ) ORDER SIBLINGS BY id, position ;
                • 5. Re: SQL tunning
                  32685
                  You tried FIRST_ROWS or RULE hint? If you want to use, remove all hints inside
                  your query.
                  I have found best performane using these hints.
                  For which queries? They are not silver bullets and can make things much worse. And the RULE hint is not very wise, especially seeing as the RBO is on it's way out.

                  You can only choose the correct course of action when you know what the problem is. Throwing hints at a query is hardly a methodical approach to tuning and in all likelihood will cost time and effort that could have been better focussed on the real core of the problem.
                  • 6. Re: SQL tunning
                    APC
                    I know this does look like something that ought to be an Oracle WTF posting but apparently it is ...
                    ------------------ Query generated by OLAP API --------------------------------------------------------------
                    Does the OP want us to rewrite the query - or make suggestions at least - or do they want to tweak the sysetm so the code runs as generated?

                    Cheers, APC
                    • 7. Re: SQL tunning
                      6363
                      Does anyone know if this is Oracle's OLAP API, or is it a third party implementation of an OLAP API?
                      • 8. Re: SQL tunning
                        William Robertson
                        That is just beautiful.

                        I liked the bit that went
                        WHERE
                        ((NOT (((CASE
                        WHEN (ALIAS_3589 > 0)
                        THEN ALIAS_3589
                        ELSE -1 END) ) = (-1.000000) ) )
                        AND (NOT ((...
                        but then there are so many other bits of it to like. Can I borrow it?
                        • 9. Re: SQL tunning
                          Billy~Verreynne
                          > You tried FIRST_ROWS or RULE hint? If you want to use, remove all hints
                          inside your query. I have found best performane using these hints.

                          Wow.. and I almost stepped into this. And sheez.. what a big and smelly piece it is too.


                          --
                          Billy
                          • 10. Re: SQL tunning
                            Billy~Verreynne
                            > Does anyone know if this is Oracle's OLAP API, or is it a third party implementation
                            of an OLAP API?

                            Who cares!?

                            <humming>
                            The sequal, the sequal,
                            the sequal is on fire
                            The sequal, the sequal,
                            the sequal is on fire
                            we don't need no water let the .. burn
                            burn .. burn


                            Listening to NY's Bloodhound Gang makes it more relaxing watching SQL fires like this, without getting upset about what it being burned in Oracle.. ;-)
                            • 11. Re: SQL tunning
                              APC
                              Can I borrow it?
                              Sure William, why not? It's not like it's mine or anything.

                              Cheers, APC
                              • 12. Re: SQL tunning
                                6363
                                Does anyone know if this is Oracle's OLAP API, or is it a third party implementation
                                of an OLAP API?
                                Who cares!?
                                Someone who may be considering using Oracle OLAP but would avoid something that produces this kind of thing like the plague.
                                • 13. Re: SQL tunning
                                  245482
                                  I almost missed the MODEL clause. It's not every query that's large enough to hide one of those.
                                  MODEL
                                  PARTITION BY (
                                  D61_DIAGNOSIS_GID SP_D61_DIAGNOSIS_GID,
                                  D61_DIAGNOSIS_ET SP_D61_DIAGNOSIS_ET,
                                  D61_DIAGNOSIS_HierarchyNumber SP_ALIAS_3591,
                                  D63_PROCEDURE_GID SP_D63_PROCEDURE_GID,
                                  D63_PROCEDURE_ET SP_D63_PROCEDURE_ET,
                                  D63_PROCEDURE_HierarchyNumber SP_ALIAS_3592,
                                  D67_BUSINESS_L_GID SP_D67_BUSINESS_L_GID,
                                  D67_BUSINESS_L_ET SP_D67_BUSINESS_L_ET,
                                  ALIAS_R81 SP_ALIAS_R81,
                                  D69_SPECIALTY_GID SP_D69_SPECIALTY_GID,
                                  D69_SPECIALTY_ET SP_D69_SPECIALTY_ET,
                                  D69_SPECIALTY_HierarchyNumber SP_ALIAS_3593,
                                  D71_TIME_GID SP_D71_TIME_GID,
                                  D71_TIME_ET SP_D71_TIME_ET,
                                  D71_TIME_HierarchyNumber SP_D71_TIME_HierarchyNumber,
                                  D73_POS_GID SP_D73_POS_GID,
                                  D73_POS_ET SP_D73_POS_ET,
                                  D73_POS_HierarchyNumber SP_D73_POS_HierarchyNumber,
                                  D75_AGE_GROUP_GID SP_D75_AGE_GROUP_GID,
                                  D75_AGE_GROUP_ET SP_D75_AGE_GROUP_ET,
                                  D75_AGE_GROUP_HierarchyNumber SP_ALIAS_3594,
                                  D77_INCR_PAID_GID SP_D77_INCR_PAID_GID,
                                  D77_INCR_PAID_ET SP_D77_INCR_PAID_ET,
                                  D77_INCR_PAID_HierarchyNumber SP_ALIAS_3595)
                                  DIMENSION BY (
                                  ALIAS_190 SP_ALIAS_190,
                                  ALIAS_191 SP_ALIAS_191,
                                  ALIAS_169 SP_ALIAS_169,
                                  D65_PROVIDER_ET SP_D65_PROVIDER_ET,
                                  D65_PROVIDER_GID SP_D65_PROVIDER_GID)
                                  MEASURES (
                                  ALIAS_194 SP_ALIAS_194,
                                  ALIAS_192 SP_ALIAS_192,
                                  ALIAS_193 SP_ALIAS_193,
                                  C59_M_TIME_AWMEDICA_MED_PAIDA SP_ALIAS_3590,
                                  ALIAS_262 SP_ALIAS_262,
                                  ALIAS_256 SP_ALIAS_256,
                                  ALIAS_257 SP_ALIAS_257,
                                  ALIAS_205 SP_ALIAS_205)
                                  RULES UPDATE
                                  (
                                  SP_ALIAS_256[ANY , ANY , ANY , ANY , ANY ] = MAX(SP_ALIAS_3590) [
                                  SP_ALIAS_192[CV(SP_ALIAS_190) , CV(SP_ALIAS_191) , CV(SP_ALIAS_169) , CV(SP_D65_PROVIDER_ET) , CV(SP_D65_PROVIDER_GID) ] ,
                                  1,
                                  ANY ,
                                  ANY ,
                                  ANY ] ,
                                  SP_ALIAS_257[ANY , ANY , ANY , ANY , ANY ] = MAX(SP_ALIAS_3590) [
                                  SP_ALIAS_193[CV(SP_ALIAS_190) , CV(SP_ALIAS_191) , CV(SP_ALIAS_169) , CV(SP_D65_PROVIDER_ET) , CV(SP_D65_PROVIDER_GID) ] ,
                                  0,
                                  ANY ,
                                  ANY ,
                                  ANY ]
                                  ) )
                                  • 14. Re: SQL tunning
                                    Billy~Verreynne
                                    The "who cares!?" comment was tic (tongue in cheek)- if you listen to "The Burn" from the Bloodhound Gang, you'll know what I meant. Let 'em burn. ;-)
                                    1 2 Previous Next