1 2 3 Previous Next 42 Replies Latest reply on May 9, 2015 11:20 AM by Stew Ashton Go to original post
      • 15. Re: Is there a more efficient way to do this conditional cross-join?
        Jonathan Lewis

        rwsBertman wrote:

         

        b) I'm not familiar with the "partition outer join" syntax, I'll look into it.

        .

         

        I've looked at the problem a little more closely - partition outer join isn't relevant.

         

        Regards

        Jonathan Lewis

        • 16. Re: Is there a more efficient way to do this conditional cross-join?
          Jonathan Lewis

          Saubhik wrote:

           

          I think Jonathan Lewis knows what sys_guid does . Cost-Based Oracle Fundamentals is one of my favorite book (although i do not understand all in that fully). I used to visit his blog  Oracle Scratchpad occasionally. He may want to point out something else!

           

          True, I do know what sys_op_guid does, but I hadn't figured out why it (or something like it) would have to be there.

           

          Regards

          Jonathan Lewis

          • 17. Re: Is there a more efficient way to do this conditional cross-join?
            Jonathan Lewis

            James Su wrote:

             

            Without that sys_guid it will raise this error:

            ORA-01436: CONNECT BY loop in user data

             

            This is because Oracle doesn't see any difference between the prior and current row.

             

            Thanks, I should have noticed that.


            The more I think about your solution, the more I like it - it's particularly nice that it doesn't generate more than the required number of rows at any point.

            I'd still like to know where the time goes in the OP's current plan, though.


            Regards

            Jonathan Lewis



            • 18. Re: Is there a more efficient way to do this conditional cross-join?
              rwsBertman

              Thanks so much for your input, James and Jonathan.

               

              CORRECTION: Unit 6395894 should actually have a latest_data value:

                  UPDATE TABLE_1 SET LATEST_DATA = TO_DATE('2-MAR-2015','DD-MON-YYYY') WHERE ID=6395894; COMMIT;

              As much as the latest_data values in the sample data are not realistic, the field is NOT NULL. My bad when creating the sample data, I should have confirmed this.

               

              Using live data for each of James' suggested query revisions, t1 in the first query returns a count(*) of a little over 8M rows, in 55 sec.

              I was unable to get a rowcount for t1 in the second query, as it ran for 4 hours without returning before I had to kill it.

              Apparently the EXPLAIN PLAN changes significantly with the significantly higher volume:

               

                explain plan for
                --------- option #1: cross join first then left join
                WITH t1 AS (
                    SELECT CR.*, cal.*
                    FROM table_1_LIVE CR
                    CROSS JOIN (
                        --create a calendar of days from 1-Jan-2012 to sysdate
                        select day, trunc(day,'month') as month
                        from (
                            select rownum-1 + to_date('1-Jan-2012','dd-Mon-yyyy') as day
                            from (select 1 from dual)
                            connect by level <= trunc(sysdate) - to_date('1-Jan-2012','dd-Mon-yyyy')
                            )
                        ) cal
                    WHERE cal.day >= CR.start_date
                        AND cal.day <= least(nvl(CR.end_date, CR.latest_data), CR.latest_data, sysdate)
                    )
                SELECT T1.ID, T1.start_date, T1.end_date, T1.day, T1.month,
                    MAX(R.rd) OVER(PARTITION BY t1.id ORDER BY T1.day) review_date, --get the most recent review date
                    LAST_VALUE(R.GROUP_ID IGNORE NULLS) OVER(PARTITION BY T1.id ORDER BY T1.day) group_ID --get the most recent review group
                FROM t1
                LEFT JOIN table_2_LIVE R
                    ON R.ID = t1.ID AND r.rd=t1.day
                ;
                SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
                
                
                explain plan for
                --------- option #2: use connect by to fill the missing dates
                WITH t1 AS (
                    SELECT t.*,start_date+LEVEL-1 day
                    FROM (
                        select c.*, least(nvl(c.end_date, c.latest_data), c.latest_data, TRUNC(sysdate)) AS dt2
                        from table_1_LIVE C
                        ) t
                    CONNECT BY id=PRIOR id
                        AND LEVEL<=dt2-start_date+1
                        AND PRIOR SYS_GUID() IS NOT NULL
                    )
                SELECT T1.ID, T1.start_date, T1.end_date, T1.day, TRUNC(T1.day,'MONTH') month,
                    MAX(R.rd) OVER(PARTITION BY t1.id ORDER BY t1.day) review_date, --get the most recent review date
                    LAST_VALUE(R.GROUP_ID IGNORE NULLS) OVER(PARTITION BY t1.id ORDER BY t1.day) group_ID --get the most recent review group
                FROM t1
                LEFT JOIN table_2_LIVE R
                    ON R.ID = t1.ID AND r.rd=t1.day
                ;
                SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());      
                
                
                Query 1:
                ------------------------------------------------------------------------------------------------------------
                | Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
                ------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT                    |                      |    43 |  2236 |   314   (1)| 00:00:04 |
                |   1 |  WINDOW SORT                        |                      |    43 |  2236 |   314   (1)| 00:00:04 |
                |   2 |   NESTED LOOPS OUTER                |                      |    43 |  2236 |   313   (0)| 00:00:04 |
                |   3 |    VIEW                             |                      |    43 |  1548 |   141   (0)| 00:00:02 |
                |*  4 |     MAT_VIEW ACCESS BY INDEX ROWID  | TABLE_1_LIVE         |    43 |  1075 |   139   (0)| 00:00:02 |
                |   5 |      NESTED LOOPS                   |                      |    43 |  1333 |   141   (0)| 00:00:02 |
                |   6 |       VIEW                          |                      |     1 |     6 |     2   (0)| 00:00:01 |
                |   7 |        COUNT                        |                      |       |       |            |          |
                |*  8 |         CONNECT BY WITHOUT FILTERING|                      |       |       |            |          |
                |   9 |          FAST DUAL                  |                      |     1 |       |     2   (0)| 00:00:01 |
                |* 10 |       INDEX RANGE SCAN              | I_TABLE_1_LIVE_START |   156 |       |     1   (0)| 00:00:01 |
                |* 11 |    MAT_VIEW ACCESS BY INDEX ROWID   | TABLE_2_LIVE         |     1 |    16 |     4   (0)| 00:00:01 |
                |* 12 |     INDEX RANGE SCAN                | I_TABLE_2_LIVE_ULI   |     3 |       |     2   (0)| 00:00:01 |
                ------------------------------------------------------------------------------------------------------------
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                   4 - filter(INTERNAL_FUNCTION("DAY")<=LEAST(NVL("END_DATE","LATEST_DATA"),"LATEST_DATA",SYSDATE@!))
                   8 - filter(LEVEL<=TRUNC(SYSDATE@!)-TO_DATE('2012-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
                  10 - access("START_DATE"<=INTERNAL_FUNCTION("DAY"))
                  11 - filter("RD"(+)=INTERNAL_FUNCTION("T1"."DAY"))
                  12 - access("ID"(+)="T1"."ID")
                ---------------------------------------------------
                
                
                Query 2:
                --------------------------------------------------------------------------------------------------------
                | Id  | Operation                       | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                --------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT                |              | 17299 |   895K|       |  1288   (2)| 00:00:16 |
                |   1 |  WINDOW SORT                    |              | 17299 |   895K|  2200K|  1288   (2)| 00:00:16 |
                |*  2 |   HASH JOIN OUTER               |              | 17299 |   895K|       |  1057   (2)| 00:00:13 |
                |   3 |    VIEW                         |              | 17299 |   625K|       |   141   (1)| 00:00:02 |
                |*  4 |     CONNECT BY WITHOUT FILTERING|              |       |       |       |            |          |
                |   5 |      MAT_VIEW ACCESS FULL       | TABLE_1_LIVE | 17299 |   422K|       |   141   (1)| 00:00:02 |
                |   6 |    MAT_VIEW ACCESS FULL         | TABLE_2_LIVE |   280K|  4378K|       |   913   (2)| 00:00:11 |
                --------------------------------------------------------------------------------------------------------
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                   2 - access("RD"(+)=INTERNAL_FUNCTION("T1"."DAY") AND "ULI"(+)="T1"."ID")
                   4 - access("ID"=PRIOR "ID")
                       filter(LEVEL<=LEAST(NVL("END_DATE","LATEST_DATA"),"LATEST_DATA",TRUNC(SYSDATE@!))-"START_DATE"+1 AND PRIOR SYS_GUID() IS NOT NULL)
                ---------------------------------------------------
              

               

              Jonathan, is there a way to get the

              table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))

              results without actually executing the query?

               

              I'm currently verifying the results of using the first query with live data, and it looks promising.

               

              Thank you both!

              • 19. Re: Is there a more efficient way to do this conditional cross-join?
                rwsBertman

                I just re-discovered the wrinkle in the logic that instigated the use of the inequality join in the original query

                    where R.rd <= C.day

                 

                The first review date for a unit may actually be earlier than its start date.

                A unit may be checked before it is registered, which means the first review_date for the unit will be recorded as earlier than its start_date. (e.g. Urgent call, unit needs attention NOW, deal with paperwork later. Bloody paperwork, what do they need that for, anyway? :-P )

                 

                Since I didn't account for this wrinkle in the sample data, add these few records in the sample data to reflect this possible condition:

                 

                  INSERT INTO TABLE_2 VALUES (6436679, to_date('04-AUG-2012','dd-MON-yyyy'), 'FN3');
                  INSERT INTO TABLE_2 VALUES (1061686, to_date('05-DEC-2012','dd-MON-yyyy'), 'FN3');
                  INSERT INTO TABLE_2 VALUES (5042598, to_date('24-JAN-2013','dd-MON-yyyy'), 'FN3');
                  INSERT INTO TABLE_2 VALUES (4679769, to_date('23-AUG-2014','dd-MON-yyyy'), 'FN3');
                  COMMIT;
                

                 

                These four units will need to display the review_date (and associated group_ID) that is earlier than their start_date, from day=start_date until their next review_date.

                 

                I suspect that this may actually be what is killing the performance of the original query...

                • 20. Re: Re: Is there a more efficient way to do this conditional cross-join?
                  Jonathan Lewis

                  You have to run the query to get the execution stats - however, if you are licensed for the performance and diagnostic packs (in effect if you are allowed to run the AWR, ADDM and ASH reports) then you can use the SQL Monitor feature. You can do this through the OEM screens, or by a call to a package:

                   

                  select

                          dbms_sqltune.report_sql_monitor(

                                  sql_id                  => '&m_sql_id',

                                  start_time_filter      => sysdate - 30/(24 * 60),

                                  type                    =>'TEXT'

                          ) text_line

                  from    dual

                  ;

                   

                  I've limited the call to reporting an SQL_ID (which you will have to supply) that started no more than 30 minutes ago.

                  The query doesn't have to complete for useful information to appear as the figures are updated in near real time (every 6 seconds the last time I checked, although the default display on the OEM screen refreshes every 10 seconds). The plans I showed earlier on give you an idea of the textual output.  (I'm currently write part 14 of a series on Execution plans for AllthingsOracle  Jonathan Lewis – All Things Oracle  that describes the feature - but it may be a couple of weeks before it's published)

                   

                   

                  Regards

                  Jonathan Lewis

                  • 21. Re: Is there a more efficient way to do this conditional cross-join?
                    James Su

                    You don't have table_1 data for these extra four units so I have to make up some:

                     

                     

                    insert into table_1 values(6436679, date '2015-1-1',NULL,to_date('04-AUG-2012','dd-MON-yyyy'));

                    insert into table_1 values(1061686, date '2015-1-1',NULL,to_date('05-DEC-2012','dd-MON-yyyy'));

                    insert into table_1 values(5042598, date '2015-1-1',NULL,to_date('24-JAN-2013','dd-MON-yyyy'));

                    insert into table_1 values(4679769, date '2015-1-1',NULL,to_date('23-AUG-2014','dd-MON-yyyy'));

                     

                     

                    The query can be changed to:

                     

                     

                    WITH

                    t1 AS (  ----- if both end_date and latest_data are null then this will return 1 row for the id anyway

                    SELECT t.*,start_date+LEVEL-1 day

                      FROM (select c.*

                                 ,least(nvl(c.end_date, c.latest_data), c.latest_data, TRUNC(sysdate)) AS dt2

                                 ,t2.rd

                                 ,t2.group_id

                             from table_1 C

                                  left join ( select C.ID, MAX(r.rd) rd,MAX(r.group_id) KEEP(DENSE_RANK LAST ORDER BY r.rd) group_id

                                                  from table_1 C INNER join table_2 R on R.ID = C.ID

                                                 WHERE r.rd<c.start_date

                                                  GROUP BY c.id

                                            ) t2

                                  ON c.id=t2.id             

                           ) t

                    CONNECT BY id=PRIOR id

                         AND LEVEL<=dt2-start_date+1

                         AND PRIOR SYS_GUID() IS NOT NULL

                    )

                    SELECT T1.ID, T1.start_date, T1.end_date,

                         T1.day, trunc(T1.day,'MM') month,

                         NVL(max(R.rd) OVER(PARTITION BY t1.id ORDER BY t1.day),t1.rd) review_date, --get the most recent review date

                         NVL(LAST_VALUE(R.GROUP_ID IGNORE NULLS) OVER(PARTITION BY t1.id ORDER BY t1.day),t1.group_id) group_ID --get the most recent review date

                    FROM t1

                          LEFT JOIN table_2 R

                             on R.ID = t1.ID AND r.rd=t1.day;

                    • 22. Re: Re: Is there a more efficient way to do this conditional cross-join?
                      chris227

                      In order to get some help by other people too it couldbe helpful if you provide some testdata a n d expected output(!!) all in one place.

                      My first attempt would be to do the group by and some filtering at an ealier stage and replace the cross join with an outer join:

                       

                      with joined_grp as (

                        select

                          id, start_date, end_date, ed,

                          max(CR.rd) as review_date, --get the most recent review date

                          min(CR.rd) min_rev_date, -- needed for the date range if this is before start_date

                          max(CR.group_ID) keep(dense_rank last order by rd nulls first) as group_ID --get the most recent group_ID

                        from (

                          select

                                C.ID, C.start_date, C.end_date,

                               -- in fact you want only retrieve the rows until review_date if that is not null and lower than ed

                                least(ed, nvl(r.rd, ed)) ed,

                                R.rd, R.group_ID

                          from (select C.ID, C.start_date, C.end_date, C.latest_data

                                        ,least(nvl(CR.end_date, CR.latest_data), CR.latest_data, sysdate) ed

                                  from table_1 C

                                  -- in fact you only need rows up from this date

                                  where CR.start_date >= date '2012-10-01'

                          ) c

                          left outer join table_2 R

                          on R.ID = C.ID

                        )

                        group by id, start_date, end_date, ed

                      )

                      , cal as (

                          --create a calendar of days from 1-Jan-2012 to sysdate

                          select

                                date '2012-10-01' + level as day

                          from dual

                          connect by level <= trunc(sysdate) - date '2012-10-01'

                      )

                      select

                          CR.ID, CR.start_date, CR.end_date,

                          cal.day, trunc(cal.day, 'month') month,

                          review_date, group_ID

                      from cal

                      left outer join

                      joined_grp cr

                      on cal.day between least(nvl(min_rev_date, start_date),start_date) and ed

                      order by id, day

                      • 23. Re: Is there a more efficient way to do this conditional cross-join?
                        rwsBertman

                        Chris, thanks, that's another option I will look at.

                        There is a snippet of sample data on the first page of this thread (reply 2) but apparently it's not really enough to do any useful analysis...

                        Here is a (hack job) script to generate some random sample data:

                         

                          --Unit registrations
                          create table table_1 (
                            ID number,
                            start_date date,
                            end_date date,
                            latest_data date
                            --other things that just come along for the ride
                            );
                          
                          --Unit checks
                          create table table_2 (
                            ID number,
                            rd date,
                            group_ID varchar2(3)
                            --other details that aren't used here
                            );
                          
                          --Group categories
                          create table table_3 (
                            gid number,
                            group_ID varchar(3)
                            );
                          
                          --create sample data for table_1
                          --select to_number(to_char(to_date('1-Jan-2012'),'J')) from dual; -- =2455928
                          --select to_number(to_char(to_date('31-Dec-2014'),'J')) from dual; -- =2457023
                          begin
                            for i in 1..100 loop -- set this loop to as many units as you want to create... my live data is about 17k
                              insert into table_1
                                select 
                                  trunc(dbms_random.value(1000000, 9999999)) as ID, -- maybe increase this to 8-digit or 9-digit ID number...?
                                  to_date(trunc(dbms_random.value(2455928, 2457023)),'J') as start_date,
                                  to_date(trunc(dbms_random.value(2455928, 2457023) + dbms_random.value(0, 2000)),'J') as end_date,
                                  to_date(trunc(dbms_random.value(2455928, 2457023) + dbms_random.value(0, 1000)),'J') as latest_data
                                from dual;
                            end loop;
                            --put three dates in order
                            update table_1
                              set start_date = least(start_date,end_date,latest_data),
                                  end_date = greatest(start_date,end_date,latest_data);
                            --if end_date is later than 1-Jan-2015 then null
                            update table_1
                              set end_date = null 
                              where end_date >= to_date('1-Jan-2015');
                            --if latest_data is later than end_date or 1-Jan-2015 then trunc(end_date,'month')
                            update table_1
                              set latest_data = trunc(nvl(end_date,start_date+31),'month') 
                              where latest_data >= end_date
                                 or latest_data >= to_date('1-Jan-2015');
                            --if latest data is earlier than start_date, then latest_date = start_date
                            update table_1
                              set latest_data = start_date
                              where latest_data < start_date;
                            commit;
                          end;
                          /
                          
                          --create sample data for table_3
                          begin
                            --create 25 group_ID codes
                            for i in 1..25 loop
                              insert into table_3
                                select
                                  i as gid,
                                  dbms_random.string('U',2)||to_char(trunc(dbms_random.value(1,9))) as group_ID
                                from dual;
                            end loop;
                            commit;
                          end;
                          /
                          
                          --create sample data for table_2
                          --(Caution: seriously filthy hack/slash code... :-P )
                          begin
                            --create review dates
                            for i in 1..2000 loop -- set this loop to about 20*number of units in table_1
                              insert into table_2
                                select 
                                  trunc(dbms_random.value(100, 999)) as ID, --prime the field with something
                                  to_date(trunc(dbms_random.value(2455928, 2457023)),'J') as rd,
                                  trunc(dbms_random.value(1,25)) as group_ID
                                from dual;
                            end loop;
                            --get the group_id from gid
                            update table_2 t2 set group_id = (select t3.group_id from table_3 t3 where t3.gid = to_number(t2.group_id));
                            --try to get some actual ID numbers from table_1 using the random 3 digits 
                            update table_2 t2 set ID = nvl((select min(t1.ID) from table_1 t1 where instr(to_char(t1.ID),to_char(t2.ID))<>0), t2.ID);
                            update table_2 t2 set ID = nvl((select min(t1.ID) from table_1 t1 where instr(to_char(t1.ID),reverse(to_char(t2.ID)))<>0), t2.ID);
                            --delete any records where rd is future
                            delete from table_2 where rd >= to_date('1-Jan-2015');
                            --delete records without valid ID
                            delete from table_2 where ID < 1000;
                            --trim multiple records for same ID on the same day
                            delete from table_2 t2 where group_id <> (select min(group_id) from table_2 tt where tt.id = t2.id and tt.rd = t2.rd group by id, rd);
                            commit;
                          end;
                          /
                          
                          select * from table_1 order by start_date desc;
                          select * from table_2 order by id desc;
                          select * from table_3 order by gid;
                        

                         

                        Required output would be like this:

                         

                          ID    start_date    end_date      latest_data   day         month       review_date   group_ID
                          --Unit with first review after start_date:
                          1     1-Jan-2014    1-Feb-2014    31-Jan-2014   1-Jan-2014  1-Jan-2014 
                          1     1-Jan-2014    1-Feb-2014    31-Jan-2014   2-Jan-2014  1-Jan-2014 
                          1     1-Jan-2014    1-Feb-2014    31-Jan-2014   3-Jan-2014  1-Jan-2014 
                          1     1-Jan-2014    1-Feb-2014    31-Jan-2014   4-Jan-2014  1-Jan-2014 
                          1     1-Jan-2014    1-Feb-2014    31-Jan-2014   5-Jan-2014  1-Jan-2014  5-Jan-2014    XY1
                          1     1-Jan-2014    1-Feb-2014    31-Jan-2014   6-Jan-2014  1-Jan-2014  5-Jan-2014    XY1
                          1     1-Jan-2014    1-Feb-2014    31-Jan-2014   7-Jan-2014  1-Jan-2014  5-Jan-2014    XY1
                          1     1-Jan-2014    1-Feb-2014    31-Jan-2014   8-Jan-2014  1-Jan-2014  5-Jan-2014    XY1
                                                                          ...
                          1     1-Jan-2014    1-Feb-2014    31-Jan-2014   31-Jan-2014 1-Jan-2014  5-Jan-2014    XY1
                          1     1-Jan-2014    1-Feb-2014    31-Jan-2014   1-Feb-2014  1-Feb-2014  5-Jan-2014    XY1
                          --Unit with first review before start_date:
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   25-Jan-2014 1-Jan-2014  15-Dec-2013   AB2
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   26-Jan-2014 1-Jan-2014  15-Dec-2013   AB2
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   27-Jan-2014 1-Jan-2014  15-Dec-2013   AB2
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   28-Jan-2014 1-Jan-2014  15-Dec-2013   AB2
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   29-Jan-2014 1-Jan-2014  15-Dec-2013   AB2
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   30-Jan-2014 1-Jan-2014  15-Dec-2013   AB2
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   31-Jan-2014 1-Jan-2014  15-Dec-2013   AB2
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   1-Feb-2014  1-Feb-2014  15-Dec-2013   AB2
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   2-Feb-2014  1-Feb-2014  15-Dec-2013   AB2
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   3-Feb-2014  1-Feb-2014  3-Feb-2014    JK9
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   4-Feb-2014  1-Feb-2014  3-Feb-2014    JK9
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   5-Feb-2014  1-Feb-2014  3-Feb-2014    JK9
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   6-Feb-2014  1-Feb-2014  3-Feb-2014    JK9
                          2     25-Jan-2014   7-Feb-2014    31-Jan-2014   7-Feb-2014  1-Feb-2014  3-Feb-2014    JK9
                          --Unit with no review data, or end_date:
                          3     3-Jan-2015                   3-Jan-2015   3-Jan-2015  1-Jan-2015
                          3     3-Jan-2015                   3-Jan-2015   4-Jan-2015  1-Jan-2015
                          3     3-Jan-2015                   3-Jan-2015   5-Jan-2015  1-Jan-2015
                          3     3-Jan-2015                   3-Jan-2015   6-Jan-2015  1-Jan-2015
                                                                          ...
                          3     3-Jan-2015                   3-Jan-2015   3-Mar-2015  1-Mar-2015
                        
                        • 24. Re: Is there a more efficient way to do this conditional cross-join?
                          rwsBertman

                          Thanks Jonathan.

                          Apparently I don't have the performance and diagnostics pack (or is DBMS_SQLTUNE not available on 10g?)

                          I get

                          ORA-00904: "DBMS_SQLTUNE"."REPORT_SQL_MONITOR": invalid identifier

                          .

                           

                          Upgrade to 11g is in the works, perhaps I will mention including this pack to the DBAs.

                          • 25. Re: Is there a more efficient way to do this conditional cross-join?
                            Jonathan Lewis

                            Sorry,

                             

                            I had forgotten you were running 10.2.0.3 - dbms_sqltune exists in that version but the report_sql_monitor functionality didn't appear until 11g.

                             

                            A couple of point to remember about your tests of James' ideas

                             

                            a) select count(*) from (big select statement) can often allow Oracle to take a completely different path to that needed for the basic select


                            b) The "connect by" for query 2 (particularly) may lead the optimizer to a bad patch because it can't get the right cardinality (which seems to be about 8M from your other comments) - so it's probably doing the hash join the wrong way round. ANSI syntax makes it harder to predict a correct set of hints, but if you hint the main select of query 2 with:  /*+ leading (t1 t2) use_hash(t2) swap_join_inputs(t2) */ the plan might change to use a HASH JOIN RIGHT OUTER showing t2 as the build table, which should allow the query to run more efficiently. It's possible, though, as I indicated earlier, that the scale of the connect by based on t1 is the key problem, requiring a huge amount of memory and a lot of work.

                             

                            Regarding (b) - one check you can do it to start a session for each query separately, then query v$sesstat (joined to v$statname), and v$session_event from another session, checking particularly for the memory used and the most significant wait time.



                            Regards

                            Jonathan Lewis

                            1 person found this helpful
                            • 26. Re: Is there a more efficient way to do this conditional cross-join?
                              James Su

                              In my last post I added a left join to address the "Unit with first review before start_date" did it work for you?

                              • 27. Re: Is there a more efficient way to do this conditional cross-join?
                                rwsBertman

                                Not yet, James, I will check when I have time. Thanks!

                                • 28. Re: Is there a more efficient way to do this conditional cross-join?
                                  Randolf Geist

                                  Jonathan Lewis wrote:

                                   

                                  You have to run the query to get the execution stats - however, if you are licensed for the performance and diagnostic packs (in effect if you are allowed to run the AWR, ADDM and ASH reports) then you can use the SQL Monitor feature.

                                  Jonathan,

                                   

                                  just a minor side note and not trying to split hairs here, but you are allowed to run AWR / ADDM and ASH with the Diagnostic Pack license, but for SQL Monitoring you need the additional Tuning Pack license. So being allowed to run AWR / ADDM / ASH doesn't imply you are allowed to use SQL Monitoring (not applicable to 10g anyway) - just for clarification.

                                   

                                  Randolf

                                  • 29. Re: Is there a more efficient way to do this conditional cross-join?
                                    rwsBertman

                                    Jonathan,

                                    Thanks, I'm learning more about query tuning in the last few days than I have in about 5 years of Oracle programming. (Why didn't i do this sooner??? :-D )

                                     

                                    Here is the exec plan for the original query:

                                         select * from table(dbms_xplan.display_cursor('&sqlID',null,'ALL'));
                                    
                                         ...
                                         
                                         Plan hash value: 277270850
                                    
                                         ----------------------------------------------------------------------------------------------------------------
                                         | Id  | Operation                          | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                                         ----------------------------------------------------------------------------------------------------------------
                                         |   0 | SELECT STATEMENT                   |                   |       |       |       |   316K(100)|          |
                                         |   1 |  SORT GROUP BY                     |                   |   110 |   222K|       |   316K  (1)| 01:03:20 |
                                         |   2 |   MERGE JOIN                       |                   |   110 |   222K|       |   316K  (1)| 01:03:20 |
                                         |   3 |    SORT JOIN                       |                   |   451K|   889M|  2354M|   316K  (1)| 01:03:20 |
                                         |*  4 |     HASH JOIN OUTER                |                   |   451K|   889M|       |   121K  (1)| 00:24:22 |
                                         |   5 |      MAT_VIEW ACCESS FULL          | TABLE_1           | 17299 |   422K|       |   141   (1)| 00:00:02 |
                                         |   6 |      VIEW                          |                   |   387K|   753M|       |   121K  (1)| 00:24:21 |
                                         |   7 |       SORT UNIQUE                  |                   |   387K|   545M|  1464M|   121K  (2)| 00:24:21 |
                                         |   8 |        UNION-ALL                   |                   |       |       |       |            |          |
                                         |   9 |         VIEW                       | TABLE_2_LIVE      |   280K|   544M|       |   913   (2)| 00:00:11 |
                                         |  10 |          MAT_VIEW ACCESS FULL      | TABLE_2           |   280K|  4378K|       |   913   (2)| 00:00:11 |
                                         |  11 |         VIEW                       | TABLE_2_LIVE      |   280K|  1642K|       |   913   (2)| 00:00:11 |
                                         |  12 |          MAT_VIEW ACCESS FULL      | TABLE_2           |   280K|  1642K|       |   913   (2)| 00:00:11 |
                                         |* 13 |    FILTER                          |                   |       |       |       |            |          |
                                         |* 14 |     SORT JOIN                      |                   |     1 |     6 |       |     3  (34)| 00:00:01 |
                                         |  15 |      VIEW                          |                   |     1 |     6 |       |     2   (0)| 00:00:01 |
                                         |  16 |       COUNT                        |                   |       |       |       |            |          |
                                         |  17 |        CONNECT BY WITHOUT FILTERING|                   |       |       |       |            |          |
                                         |  18 |         FAST DUAL                  |                   |     1 |       |       |     2   (0)| 00:00:01 |
                                         ----------------------------------------------------------------------------------------------------------------
                                    
                                         Query Block Name / Object Alias (identified by operation id):
                                         -------------------------------------------------------------
                                    
                                            1 - SEL$9037B4E2
                                            5 - SEL$9037B4E2 / TABLE_1@SEL$9
                                            6 - SET$1        / R@SEL$3
                                            7 - SET$1       
                                            9 - SEL$5        / TABLE_2_LIVE@SEL$4
                                           10 - SEL$5        / TABLE_2@SEL$5
                                           11 - SEL$7        / TABLE_2_LIVE@SEL$6
                                           12 - SEL$7        / TABLE_2@SEL$7
                                           15 - SEL$9385C8AB / from$_subquery$_009@SEL$11
                                           16 - SEL$9385C8AB
                                           18 - SEL$9385C8AB / DUAL@SEL$15
                                    
                                         Predicate Information (identified by operation id):
                                         ---------------------------------------------------
                                    
                                            4 - access("R"."ID"="ID")
                                           13 - filter((INTERNAL_FUNCTION("DAY")<=LEAST(NVL("END_DATE","LATEST_DATA"),"LATEST_DATA",SYSDATE@!)
                                                           AND ("R"."RD"<=INTERNAL_FUNCTION("DAY") OR "R"."RD" IS NULL)))
                                           14 - access("START_DATE"<=INTERNAL_FUNCTION("DAY"))
                                                 filter("START_DATE"<=INTERNAL_FUNCTION("DAY"))
                                    
                                         Column Projection Information (identified by operation id):
                                         -----------------------------------------------------------
                                    
                                            1 - (#keys=5) "START_DATE"[DATE,7], "ID"[NUMBER,22], "END_DATE"[DATE,7], "DAY"[DATE,8], 
                                                 TRUNC("DAY",'fmmonth')[8], MAX("R"."GROUP_ID") KEEP (DENSE_RANK FIRST  ORDER BY 
                                                 INTERNAL_FUNCTION("R"."RD") DESC  NULLS LAST)[4000], MAX("R"."RD")[21]
                                            2 - "START_DATE"[DATE,7], "ID"[NUMBER,22], "R"."RD"[DATE,21], "END_DATE"[DATE,7], 
                                                 "R"."GROUP_ID"[VARCHAR2,4000], "DAY"[DATE,8]
                                            3 - (#keys=1) "START_DATE"[DATE,7], "ID"[NUMBER,22], "LATEST_DATA"[DATE,7], "R"."RD"[DATE,21], 
                                                 "END_DATE"[DATE,7], "R"."GROUP_ID"[VARCHAR2,4000]
                                            4 - (#keys=1) "ID"[NUMBER,22], "LATEST_DATA"[DATE,7], "START_DATE"[DATE,7], 
                                                 "END_DATE"[DATE,7], "R"."GROUP_ID"[VARCHAR2,4000], "R"."RD"[DATE,21]
                                            5 - "ID"[NUMBER,22], "START_DATE"[DATE,7], "END_DATE"[DATE,7], "LATEST_DATA"[DATE,7]
                                            6 - "R"."ID"[NUMBER,22], "R"."RD"[DATE,21], "R"."GROUP_ID"[VARCHAR2,4000]
                                            7 - (#keys=3) STRDEF[22], STRDEF[21], STRDEF[4000]
                                            8 - STRDEF[22], STRDEF[21], STRDEF[4000]
                                            9 - "ID"[NUMBER,22], "RD"[DATE,21], "GROUP_ID"[VARCHAR2,4000]
                                           10 - "ID"[NUMBER,22], "GROUP_ID"[VARCHAR2,4000], "RD"[DATE,21]
                                           11 - "ID"[NUMBER,22]
                                           12 - "ID"[NUMBER,22]
                                           13 - "DAY"[DATE,8]
                                           14 - (#keys=1) INTERNAL_FUNCTION("DAY")[7], "DAY"[DATE,8]
                                           15 - "DAY"[DATE,8]
                                           16 - ROWNUM[4]
                                           17 - LEVEL[4]
                                    

                                     

                                    and the ALLSTATS table:

                                     

                                         select * from table(dbms_xplan.display_cursor('&sqlid',null,'ALLSTATS')); 
                                    
                                         Plan hash value: 277270850
                                    
                                         ----------------------------------------------------------------------------------------------------------------------------------------------------------
                                         | Id  | Operation                          | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem |  O/1/M   |
                                         ----------------------------------------------------------------------------------------------------------------------------------------------------------
                                         |   1 |  SORT GROUP BY                     |              |      1 |    110 |    250 |00:02:47.32 |    7003 |   1322 |    717K| 73728 | 73728 |          |
                                         |   2 |   MERGE JOIN                       |              |      1 |    110 |   3673K|00:00:27.61 |    6843 |      0 |      0 |       |       |          |
                                         |   3 |    SORT JOIN                       |              |      1 |    451K|  94043 |00:00:01.98 |    6843 |      0 |      0 |  6289K|  1015K|     1/0/0|
                                         |*  4 |     HASH JOIN OUTER                |              |      1 |    451K|  94043 |00:00:01.75 |    6843 |      0 |      0 |  1621K|  1235K|     1/0/0|
                                         |   5 |      MAT_VIEW ACCESS FULL          | TABLE_1      |      1 |  17299 |  17299 |00:00:00.01 |     347 |      0 |      0 |       |       |          |
                                         |   6 |      VIEW                          |              |      1 |    387K|    379K|00:00:01.84 |    6496 |      0 |      0 |       |       |          |
                                         |   7 |       SORT UNIQUE                  |              |      1 |    387K|    379K|00:00:01.46 |    6496 |      0 |      0 |    21M|  1992K|     1/0/0|
                                         |   8 |        UNION-ALL                   |              |      1 |        |    560K|00:00:00.56 |    6496 |      0 |      0 |       |       |          |
                                         |   9 |         VIEW                       | TABLE_2_LIVE |      1 |    280K|    280K|00:00:00.28 |    3248 |      0 |      0 |       |       |          |
                                         |  10 |          MAT_VIEW ACCESS FULL      | TABLE_2      |      1 |    280K|    280K|00:00:00.01 |    3248 |      0 |      0 |       |       |          |
                                         |  11 |         VIEW                       | TABLE_2_LIVE |      1 |    280K|    280K|00:00:00.28 |    3248 |      0 |      0 |       |       |          |
                                         |  12 |          MAT_VIEW ACCESS FULL      | TABLE_2      |      1 |    280K|    280K|00:00:00.01 |    3248 |      0 |      0 |       |       |          |
                                         |* 13 |    FILTER                          |              |  94043 |        |   3673K|00:00:17.38 |       0 |      0 |      0 |       |       |          |
                                         |* 14 |     SORT JOIN                      |              |  94043 |      1 |     14M|00:00:00.13 |       0 |      0 |      0 | 73728 | 73728 |          |
                                         |  15 |      VIEW                          |              |      1 |      1 |    153 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
                                         |  16 |       COUNT                        |              |      1 |        |    153 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
                                         |  17 |        CONNECT BY WITHOUT FILTERING|              |      1 |        |    153 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
                                         |  18 |         FAST DUAL                  |              |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
                                         ----------------------------------------------------------------------------------------------------------------------------------------------------------
                                    
                                         Predicate Information (identified by operation id):
                                         ---------------------------------------------------
                                    
                                            4 - access("R"."ID"="ID")
                                           13 - filter((INTERNAL_FUNCTION("DAY")<=LEAST(NVL("END_DATE","LATEST_DATA"),"LATEST_DATA",SYSDATE@!) AND ("R"."RD"<=INTERNAL_FUNCTION("DAY") OR 
                                                          "R"."RD" IS NULL)))
                                           14 - access("START_DATE"<=INTERNAL_FUNCTION("DAY"))
                                                 filter("START_DATE"<=INTERNAL_FUNCTION("DAY"))