Feed aggregator

ANSI Plans

Jonathan Lewis - Tue, 2019-10-22 12:59

Here’s a thought that falls somewhere between philosophical and pragmatic. It came up while I was playing around with a problem from the Oracle database forum that was asking about options for rewriting a query with a certain type of predicate. This note isn’t really about that question but the OP supplied a convenient script to demonstrate their requirement and I’ve hi-jacked most of the code for my own purposes so that I can ask the question:

Should the presence of an intermediate view name generated by the optimizer in the course of cost-based query transformation cause two plans, which are otherwise identical and do exactly the same thing, to have different plan hash values ?

To demonstrate the issue let’s start with a simple script to create some data and generate an execution plan.


rem
rem     Script:         or_expand_plans.sql
rem     Author:         Jonathan Lewis
rem     Dated           Oct 2019
rem
rem     Last tested
rem             19.3.0.0
rem             12.2.0.1
rem
rem     Notes:
rem     Data creation copied from ODC
rem     https://community.oracle.com/thread/4297365
rem

create table t as   
select 1 as id, to_date('2019-10-11', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual  
union all  
select 2 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual  
union all  
select 3 as id, to_date('2019-10-15', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual  
union all  
select 4 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual  
;  

create table t_others as  
select 1 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'Blue'  as color, 'Zone 7' as zoneid from dual  
union all  
select 2 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'Red'   as color, 'Zone 7' as zoneid from dual  
union all  
select 3 as id, to_date('2019-10-16', 'YYYY-MM-DD') as lastupdated, 'White' as color, 'Zone 7' as zoneid from dual  
union all  
select 4 as id, to_date('2019-10-17', 'YYYY-MM-DD') as lastupdated, 'Green' as color, 'Zone 7' as zoneid from dual  
;  

create table t_further_info as  
select 1 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'A' as typeinfo from dual  
union all   
select 2 as id, to_date('2019-10-14', 'YYYY-MM-DD') as lastupdated, 'C' as typeinfo from dual  
union all  
select 3 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'D' as typeinfo from dual  
union all  
select 4 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'E' as typeinfo from dual  
;  

prompt  ====================
prompt  "Traditional" syntax
prompt  ====================

explain plan for
select 
        /*+ or_expand */ 
        * 
from
        t,
        t_others        pt,
        t_further_info  fi  
/*
where   (
             t.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')   
         or pt.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')  
         or fi.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')  
        )  
*/
where   to_date('2019-10-21', 'YYYY-MM-DD') <= any(t.lastupdated, pt.lastupdated, fi.lastupdated)   
and     pt.id = t.id  
and     fi.id = t.id  
;

select * from table(dbms_xplan.display(null,null,'outline'));


You’ll see that I have a simple three-table join with the nasty little detail that I have a “non-join” predicates that may require Oracle to check across all three tables before it can decide whether or not a row should be discarded. I’ve shown two variants on a theme – they both have exactly the same effect but the ANY() presentation is just a little bit neater and more compact.

Essentially Oracle can use one of two strategies for this type of query/predicate; the first is to join all three tables and wait until the final join rowsource appears and then apply the check, or it can split the query into a union all of three separate queries where each query drives off a different table selecting only the rows from that table that match “its” part of the predicate.

In the latter case the second and third branches of the union all have to be modified to ensure that they discard any rows already returned by preceding parts of the union all; this can mean lots of new predicates appearing that use the lnnvl() function. (Of course there are variants between these two extremes, but the general principle is unchanged.)

The presence of the (incomplete) /*+ or_expand */ hint in my query is there to tell the optimizer that it should attempt to transform the query into the three-part union all. This, by the way, is a 12c feature, though older versions of Oracle could get similar effects in some cases from the /*+ use_concat */ hint. Here’s the plan, with outline,  I got from 12.2.0.1:


Plan hash value: 3181357500

----------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                    |     3 |   270 |    17   (6)| 00:00:01 |
|   1 |  VIEW                   | VW_ORE_67EF6547    |     3 |   270 |    17   (6)| 00:00:01 |
|   2 |   UNION-ALL             |                    |       |       |            |          |
|*  3 |    HASH JOIN            |                    |     1 |    55 |     6   (0)| 00:00:01 |
|   4 |     MERGE JOIN CARTESIAN|                    |     4 |   144 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL  | T_FURTHER_INFO     |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |      BUFFER SORT        |                    |     3 |    69 |     2   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL | T_OTHERS           |     3 |    69 |     2   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL   | T                  |     3 |    57 |     2   (0)| 00:00:01 |
|*  9 |    HASH JOIN            |                    |     2 |   182 |    11  (10)| 00:00:01 |
|  10 |     VIEW                | VW_JF_SET$FB5125FC |     2 |   156 |     9  (12)| 00:00:01 |
|  11 |      UNION-ALL          |                    |       |       |            |          |
|* 12 |       HASH JOIN         |                    |     1 |    42 |     4   (0)| 00:00:01 |
|* 13 |        TABLE ACCESS FULL| T_OTHERS           |     1 |    23 |     2   (0)| 00:00:01 |
|* 14 |        TABLE ACCESS FULL| T                  |     3 |    57 |     2   (0)| 00:00:01 |
|* 15 |       HASH JOIN         |                    |     1 |    42 |     4   (0)| 00:00:01 |
|* 16 |        TABLE ACCESS FULL| T                  |     1 |    19 |     2   (0)| 00:00:01 |
|  17 |        TABLE ACCESS FULL| T_OTHERS           |     4 |    92 |     2   (0)| 00:00:01 |
|  18 |     TABLE ACCESS FULL   | T_FURTHER_INFO     |     4 |    52 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$7C4216F7" "PT"@"SEL$1")
      LEADING(@"SEL$7C4216F7" "T"@"SEL$1" "PT"@"SEL$1")
      FULL(@"SEL$7C4216F7" "PT"@"SEL$1")
      FULL(@"SEL$7C4216F7" "T"@"SEL$1")
      USE_HASH(@"SEL$A4A33BE0" "T"@"SEL$1")
      LEADING(@"SEL$A4A33BE0" "PT"@"SEL$1" "T"@"SEL$1")
      FULL(@"SEL$A4A33BE0" "T"@"SEL$1")
      FULL(@"SEL$A4A33BE0" "PT"@"SEL$1")
      USE_HASH(@"SET$49E1C21B_3" "T"@"SEL$1")
      USE_MERGE_CARTESIAN(@"SET$49E1C21B_3" "PT"@"SEL$1")
      LEADING(@"SET$49E1C21B_3" "FI"@"SEL$1" "PT"@"SEL$1" "T"@"SEL$1")
      FULL(@"SET$49E1C21B_3" "T"@"SEL$1")
      FULL(@"SET$49E1C21B_3" "PT"@"SEL$1")
      FULL(@"SET$49E1C21B_3" "FI"@"SEL$1")
      USE_HASH(@"SEL$5FCD2D3C" "FI"@"SEL$1")
      LEADING(@"SEL$5FCD2D3C" "VW_JF_SET$FB5125FC"@"SEL$81DF0931" "FI"@"SEL$1")
      FULL(@"SEL$5FCD2D3C" "FI"@"SEL$1")
      NO_ACCESS(@"SEL$5FCD2D3C" "VW_JF_SET$FB5125FC"@"SEL$81DF0931")
      NO_ACCESS(@"SEL$67EF6547" "VW_ORE_67EF6547"@"SEL$67EF6547")
      OUTLINE(@"SET$49E1C21B_2")
      OUTLINE(@"SET$49E1C21B_1")
      OUTLINE(@"SEL$1")
      FACTORIZE_JOIN(@"SET$49E1C21B"("FI"@"SET$49E1C21B_2" "FI"@"SET$49E1C21B_1"))
      OUTLINE(@"SET$0E101D56")
      OUTLINE(@"SEL$81DF0931")
      OUTLINE(@"SEL$5AB42CD1")
      OR_EXPAND(@"SEL$1" (1) (2) (3))
      OUTLINE(@"SET$49E1C21B")
      OUTLINE_LEAF(@"SEL$67EF6547")
      FACTORIZE_JOIN(@"SET$49E1C21B"("FI"@"SET$49E1C21B_2" "FI"@"SET$49E1C21B_1"))
      OUTLINE_LEAF(@"SET$0E101D56")
      OUTLINE_LEAF(@"SEL$5FCD2D3C")
      OUTLINE_LEAF(@"SET$FB5125FC")
      OUTLINE_LEAF(@"SEL$A4A33BE0")
      OUTLINE_LEAF(@"SEL$7C4216F7")
      OUTLINE_LEAF(@"SET$49E1C21B_3")
      ALL_ROWS
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PT"."ID"="T"."ID" AND "FI"."ID"="T"."ID")
   5 - filter("FI"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   7 - filter(LNNVL("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
   8 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
   9 - access("FI"."ID"="ITEM_1")
  12 - access("PT"."ID"="T"."ID")
  13 - filter("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
  14 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
  15 - access("PT"."ID"="T"."ID")
  16 - filter("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))


This is a wonderful demonstration of how brilliant the optimizer can be.  The query has gone through several transformations and two of them have very high visibility. First, you can see the name VW_ORE_67EF6547 at operation 1. This is a view name that Oracle generates to express (cost-based) OR-Expansion” so the optimizer has clearly obeyed my hint. As a consequence of OR-expansion we can also see several examples of the lnnvl() function appearing in the Predicate Information section of the output.; we can also see the hint re-appearing in the completed form of OR_EXPAND(@”SEL$1″ (1) (2) (3)) in the Outline Data.

However, we don’t have the union all of three pieces that we might have expected; we have a union all of two pieces and the second piece is a hash join between the table t_further_info and a view called VW_JF_SET$FB5125FC. This view is the result of “join factorization”. The optimizer has taken the 2nd and 3rd sections of our union all view and decided that it would be cost-effective to “factor out” a common table, so this:

select from t_others, t, t_further_info ... where t_others.date_predicate ...
union all
select from t, t_others, t_further_info ... where t.date_predicate ...

changes to this:

select from 
        (
        select from t_others, t   ... where t_others.date_predicate ...
        union all
        select from t, t_others   ... where t.date_predicate ...
        ),
        t_further_info 
where   ...

Having said all that, I now have to change the code because the original query was written using “ANSI” style joins – like this:


explain plan for
select
        /*+ or_expand */
        * 
from
        t   
inner join 
        t_others       pt  
on      pt.id = t.id  
inner join 
        t_further_info fi  
on      fi.id = t.id  
where
        (
             t.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')   
         or pt.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')  
         or fi.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')  
        )
;

select  * from table(dbms_xplan.display(null,null,'outline'));


In the ANSI example I happen to have used the explicit “OR” list for the date predicates but that’s not really signficant . Here’s the plan produced by this query – and the first thing I’d like you to note is the Plan hash value:

Plan hash value: 3309788271

----------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                    |     3 |   270 |    17   (6)| 00:00:01 |
|   1 |  VIEW                   | VW_ORE_31069B60    |     3 |   270 |    17   (6)| 00:00:01 |
|   2 |   UNION-ALL             |                    |       |       |            |          |
|*  3 |    HASH JOIN            |                    |     1 |    55 |     6   (0)| 00:00:01 |
|   4 |     MERGE JOIN CARTESIAN|                    |     4 |   144 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL  | T_FURTHER_INFO     |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |      BUFFER SORT        |                    |     3 |    69 |     2   (0)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL | T_OTHERS           |     3 |    69 |     2   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL   | T                  |     3 |    57 |     2   (0)| 00:00:01 |
|*  9 |    HASH JOIN            |                    |     2 |   182 |    11  (10)| 00:00:01 |
|  10 |     VIEW                | VW_JF_SET$997549B1 |     2 |   156 |     9  (12)| 00:00:01 |
|  11 |      UNION-ALL          |                    |       |       |            |          |
|* 12 |       HASH JOIN         |                    |     1 |    42 |     4   (0)| 00:00:01 |
|* 13 |        TABLE ACCESS FULL| T_OTHERS           |     1 |    23 |     2   (0)| 00:00:01 |
|* 14 |        TABLE ACCESS FULL| T                  |     3 |    57 |     2   (0)| 00:00:01 |
|* 15 |       HASH JOIN         |                    |     1 |    42 |     4   (0)| 00:00:01 |
|* 16 |        TABLE ACCESS FULL| T                  |     1 |    19 |     2   (0)| 00:00:01 |
|  17 |        TABLE ACCESS FULL| T_OTHERS           |     4 |    92 |     2   (0)| 00:00:01 |
|  18 |     TABLE ACCESS FULL   | T_FURTHER_INFO     |     4 |    52 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$D12FC97A" "PT"@"SEL$1")
      LEADING(@"SEL$D12FC97A" "T"@"SEL$1" "PT"@"SEL$1")
      FULL(@"SEL$D12FC97A" "PT"@"SEL$1")
      FULL(@"SEL$D12FC97A" "T"@"SEL$1")
      USE_HASH(@"SEL$09C9729D" "T"@"SEL$1")
      LEADING(@"SEL$09C9729D" "PT"@"SEL$1" "T"@"SEL$1")
      FULL(@"SEL$09C9729D" "T"@"SEL$1")
      FULL(@"SEL$09C9729D" "PT"@"SEL$1")
      USE_HASH(@"SET$E8D85587_3" "T"@"SEL$1")
      USE_MERGE_CARTESIAN(@"SET$E8D85587_3" "PT"@"SEL$1")
      LEADING(@"SET$E8D85587_3" "FI"@"SEL$2" "PT"@"SEL$1" "T"@"SEL$1")
      FULL(@"SET$E8D85587_3" "T"@"SEL$1")
      FULL(@"SET$E8D85587_3" "PT"@"SEL$1")
      FULL(@"SET$E8D85587_3" "FI"@"SEL$2")
      USE_HASH(@"SEL$95B99BAF" "FI"@"SEL$2")
      LEADING(@"SEL$95B99BAF" "VW_JF_SET$997549B1"@"SEL$BB7F1ECF" "FI"@"SEL$2")
      FULL(@"SEL$95B99BAF" "FI"@"SEL$2")
      NO_ACCESS(@"SEL$95B99BAF" "VW_JF_SET$997549B1"@"SEL$BB7F1ECF")
      NO_ACCESS(@"SEL$31069B60" "VW_ORE_31069B60"@"SEL$31069B60")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$1" >"SEL$2")
      OUTLINE(@"SEL$58A6D7F6")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SET$E8D85587_2")
      OUTLINE(@"SET$E8D85587_1")
      MERGE(@"SEL$58A6D7F6" >"SEL$3")
      OUTLINE(@"SEL$9E43CB6E")
      FACTORIZE_JOIN(@"SET$E8D85587"("FI"@"SET$E8D85587_2" "FI"@"SET$E8D85587_1"))
      OUTLINE(@"SET$6117B24C")
      OUTLINE(@"SEL$BB7F1ECF")
      OUTLINE(@"SEL$344003E3")
      OR_EXPAND(@"SEL$9E43CB6E" (1) (2) (3))
      OUTLINE(@"SET$E8D85587")
      OUTLINE_LEAF(@"SEL$31069B60")
      FACTORIZE_JOIN(@"SET$E8D85587"("FI"@"SET$E8D85587_2" "FI"@"SET$E8D85587_1"))
      OUTLINE_LEAF(@"SET$6117B24C")
      OUTLINE_LEAF(@"SEL$95B99BAF")
      OUTLINE_LEAF(@"SET$997549B1")
      OUTLINE_LEAF(@"SEL$09C9729D")
      OUTLINE_LEAF(@"SEL$D12FC97A")
      OUTLINE_LEAF(@"SET$E8D85587_3")
      ALL_ROWS
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("FI"."ID"="T"."ID" AND "PT"."ID"="T"."ID")
   5 - filter("FI"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   7 - filter(LNNVL("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
   8 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
   9 - access("FI"."ID"="ITEM_1")
  12 - access("PT"."ID"="T"."ID")
  13 - filter("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
  14 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
  15 - access("PT"."ID"="T"."ID")
  16 - filter("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))


Is the plan for the “ANSI” version of the query the same as the plan for the “traditional” version? How carefully have you checked – after simply noting that the two Plan hash values were different.

The plans are the same – in that the mechanics are exactly the same and that ought to be the criterion on which we should judge them. But the hash values are different because of the change from traditional to ANSI syntax. The traditional form of the query starts life with a single query block while the ANSI form is considered to be two query blocks, so the initial ANSI query goes through three stages:


1) select from t1 join t2 join t3

2) select /*+ qb_name(sel$2) */ from (select /* qb_name(sel$1) */ from t1, t2), t3

3) select /*+ qb_name(sel$9E43CB6E) */ from t1, t2, t3

So the query is rapidly transformed to the equivalent traditional syntax but we  now have a query block name of SEL$9E43CB6E instead of SEL$1 that the traditional query (in the absence of a /*+ qb_name() */ hint would have had. This is why you see the difference in the two or_expand() hints in the Outline Data section. One reads: OR_EXPAND(@SEL$1 (1) (2) (3)), the other reads OR_EXPAND(@”SEL$9E43CB6E” (1) (2) (3)), and all the subseqent query block name differences follow on from this initial transformation. (Note: the value “sel$9e43cb6e” is derived from the input query block names of sel$1 and sel$2 that the new query block is derived from)

You may decide that this mismatch isn’t really terribly important. If you’ve modified the code to switch to ANSI style joins then you may be prepared to put in a little extra effort to check the plan in detail to see that it hasn’t changed; but it would be a lot nicer if the hash value wasn’t dependent on generated view names. You may recall that at one time Oracle had problems with plans that used materialized CTEs (“with” subqueries) because the plan hash value was affected by object names like sys_temp_0fd9d6791_dfc12da. The same principle ought, I think, to apply here.

If you don’t mind the ANSI/tradiational switch though, you might find that you’re less happy when you upgrade to 19c, because the same effect appears there too, only it’s worse. Not only do “identical” traditional and ANSI plans have different hash values, they don’t match the values from 12c because the generated name for the join factorization views (VW_JF) change in the upgrade. So if you’re depending on SQL Plan Baselines to reproduce 12c plans on 19c when you upgrade you may find cases where you know the stored baseline is giving you the same plan but Oracle thinks it isn’t and refuses to use it.

tl;dr

Plans which are functionally identical can have different plan hash values because the plans were reached through a different series of tranformations. In particular if you rewrite queries from “traditional” Oracle syntax to “ANSI” syntax you will find cases where the plan doesn’t change but the plan hash value does thanks to a change in the names of views generated by some transformations.

More significantly, if you upgrade from 12c to 19c there are case where the names of views generated by transformations may change, which could cause the optimizer to discard some of your carefully constructed SQL Plan Baselines as “not reproducible”.

Footnote

For reference, if I add the hint /*+ opt_param(‘_optimizer_join_factorization’ ‘false’) */ to the “traditional query then I get the following plan which shows more clearly the three branches that the original query has been split into – each section starting with a different choice for the driving table:


-------------------------------------------------------------------------------------------
| Id  | Operation               | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                 |     3 |   270 |    19   (6)| 00:00:01 |
|   1 |  VIEW                   | VW_ORE_67EF6547 |     3 |   270 |    19   (6)| 00:00:01 |
|   2 |   UNION-ALL             |                 |       |       |            |          |
|*  3 |    HASH JOIN            |                 |     1 |    55 |     7  (15)| 00:00:01 |
|*  4 |     HASH JOIN           |                 |     1 |    32 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL  | T               |     1 |    19 |     2   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL  | T_FURTHER_INFO  |     4 |    52 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL   | T_OTHERS        |     4 |    92 |     2   (0)| 00:00:01 |
|*  8 |    HASH JOIN            |                 |     1 |    55 |     6   (0)| 00:00:01 |
|   9 |     MERGE JOIN CARTESIAN|                 |     4 |   144 |     4   (0)| 00:00:01 |
|* 10 |      TABLE ACCESS FULL  | T_OTHERS        |     1 |    23 |     2   (0)| 00:00:01 |
|  11 |      BUFFER SORT        |                 |     4 |    52 |     2   (0)| 00:00:01 |
|  12 |       TABLE ACCESS FULL | T_FURTHER_INFO  |     4 |    52 |     2   (0)| 00:00:01 |
|* 13 |     TABLE ACCESS FULL   | T               |     3 |    57 |     2   (0)| 00:00:01 |
|* 14 |    HASH JOIN            |                 |     1 |    55 |     6   (0)| 00:00:01 |
|  15 |     MERGE JOIN CARTESIAN|                 |     4 |   144 |     4   (0)| 00:00:01 |
|* 16 |      TABLE ACCESS FULL  | T_FURTHER_INFO  |     1 |    13 |     2   (0)| 00:00:01 |
|  17 |      BUFFER SORT        |                 |     3 |    69 |     2   (0)| 00:00:01 |
|* 18 |       TABLE ACCESS FULL | T_OTHERS        |     3 |    69 |     2   (0)| 00:00:01 |
|* 19 |     TABLE ACCESS FULL   | T               |     3 |    57 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SET$BB614FD2_3" "T"@"SET$BB614FD2_3")
      USE_MERGE_CARTESIAN(@"SET$BB614FD2_3" "PT"@"SET$BB614FD2_3")
      LEADING(@"SET$BB614FD2_3" "FI"@"SET$BB614FD2_3" "PT"@"SET$BB614FD2_3"
              "T"@"SET$BB614FD2_3")
      FULL(@"SET$BB614FD2_3" "T"@"SET$BB614FD2_3")
      FULL(@"SET$BB614FD2_3" "PT"@"SET$BB614FD2_3")
      FULL(@"SET$BB614FD2_3" "FI"@"SET$BB614FD2_3")
      USE_HASH(@"SET$BB614FD2_2" "T"@"SET$BB614FD2_2")
      USE_MERGE_CARTESIAN(@"SET$BB614FD2_2" "FI"@"SET$BB614FD2_2")
      LEADING(@"SET$BB614FD2_2" "PT"@"SET$BB614FD2_2" "FI"@"SET$BB614FD2_2"
              "T"@"SET$BB614FD2_2")
      FULL(@"SET$BB614FD2_2" "T"@"SET$BB614FD2_2")
      FULL(@"SET$BB614FD2_2" "FI"@"SET$BB614FD2_2")
      FULL(@"SET$BB614FD2_2" "PT"@"SET$BB614FD2_2")
      USE_HASH(@"SET$BB614FD2_1" "PT"@"SET$BB614FD2_1")
      USE_HASH(@"SET$BB614FD2_1" "FI"@"SET$BB614FD2_1")
      LEADING(@"SET$BB614FD2_1" "T"@"SET$BB614FD2_1" "FI"@"SET$BB614FD2_1"
              "PT"@"SET$BB614FD2_1")
      FULL(@"SET$BB614FD2_1" "PT"@"SET$BB614FD2_1")
      FULL(@"SET$BB614FD2_1" "FI"@"SET$BB614FD2_1")
      FULL(@"SET$BB614FD2_1" "T"@"SET$BB614FD2_1")
      NO_ACCESS(@"SEL$49E1C21B" "VW_ORE_67EF6547"@"SEL$67EF6547")
      OUTLINE(@"SEL$1")
      OR_EXPAND(@"SEL$1" (1) (2) (3))
      OUTLINE_LEAF(@"SEL$49E1C21B")
      OUTLINE_LEAF(@"SET$BB614FD2")
      OUTLINE_LEAF(@"SET$BB614FD2_1")
      OUTLINE_LEAF(@"SET$BB614FD2_2")
      OUTLINE_LEAF(@"SET$BB614FD2_3")
      ALL_ROWS
      OPT_PARAM('_optimizer_join_factorization' 'false')
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PT"."ID"="T"."ID")
   4 - access("FI"."ID"="T"."ID")
   5 - filter("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   8 - access("PT"."ID"="T"."ID" AND "FI"."ID"="T"."ID")
  10 - filter("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
  13 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))
  14 - access("PT"."ID"="T"."ID" AND "FI"."ID"="T"."ID")
  16 - filter("FI"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
  18 - filter(LNNVL("PT"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))
  19 - filter(LNNVL("T"."LASTUPDATED">=TO_DATE(' 2019-10-21 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))

88 rows selected.

Although the “traditional” and “ANSI” plans still show a difference in their Plan hash values when join factorization is blocked, the absence of the join factorization view means that the plan hash values are now consistent between 12c to 19c (the output above came from 19.3.0.0 as you can see in the Outline information).

How to Convert BMP to JPG: 2 Easy Ways!

VitalSoftTech - Tue, 2019-10-22 08:45

Are you looking to convert BMP to JPG? There are plenty of ways you can do that, through software installed in your laptop or through online BMP to JPG converters. We’ll take a look at some of the way you can make a conversion in this article! But first, let’s take a look at what […]

The post How to Convert BMP to JPG: 2 Easy Ways! appeared first on VitalSoftTech.

Categories: DBA Blogs

srvctl config all

Michael Dinh - Tue, 2019-10-22 08:19

Learned something new today and not sure if it’s new feature.

Seems a lot easier to gather clusterware configuration using one command.

Works with srvctl version: 18.0.0.0.0 or higher.

19c

oracle@ol7-19-rac2 ~]$ echo $ORACLE_HOME
/u01/app/19.0.0/grid

[oracle@ol7-19-rac2 ~]$ srvctl -version
srvctl version: 19.0.0.0.0

[oracle@ol7-19-rac2 ~]$ srvctl config all

Oracle Clusterware configuration details
========================================

Oracle Clusterware basic information
------------------------------------
  Operating system          Linux
  Name                      ol7-19-cluster
  Class                     STANDALONE
  Cluster nodes             ol7-19-rac1, ol7-19-rac2
  Version                   19.0.0.0.0
  Groups                    SYSOPER: SYSASM:dba SYSRAC:dba SYSDBA:dba
  OCR locations             +DATA
  Voting disk locations     DATA
  Voting disk file paths    /dev/oracleasm/asm-disk3

Cluster network configuration details
-------------------------------------
  Interface name  Type  Subnet           Classification
  eth1            IPV4  192.168.56.0/24  PUBLIC
  eth2            IPV4  192.168.1.0/24   PRIVATE, ASM

SCAN configuration details
--------------------------

SCAN "ol7-19-scan" details
++++++++++++++++++++++++++
  Name                ol7-19-scan
  IPv4 subnet         192.168.56.0/24
  DHCP server type    static
  End points          TCP:1521

  SCAN listeners
  --------------
  Name              VIP address
  LISTENER_SCAN1    192.168.56.105
  LISTENER_SCAN2    192.168.56.106
  LISTENER_SCAN3    192.168.56.107


ASM configuration details
-------------------------
  Mode             remote
  Password file    +DATA
  SPFILE           +DATA

  ASM disk group details
  ++++++++++++++++++++++
  Name  Redundancy
  DATA  EXTERN

Database configuration details
==============================

Database "ora.cdbrac.db" details
--------------------------------
  Name                ora.cdbrac.db
  Type                RAC
  Version             19.0.0.0.0
  Role                PRIMARY
  Management          AUTOMATIC
  policy
  SPFILE              +DATA
  Password file       +DATA
  Groups              OSDBA:dba OSOPER:oper OSBACKUP:dba OSDG:dba OSKM:dba OSRAC:dba
  Oracle home         /u01/app/oracle/product/19.0.0/dbhome_1
[oracle@ol7-19-rac2 ~]$

18c

[oracle@rac1 Desktop]$ srvctl -version
srvctl version: 18.0.0.0.0

[oracle@rac1 Desktop]$ srvctl config all

Oracle Clusterware configuration details                                        
========================================                                        

Oracle Clusterware basic information                                            
------------------------------------                                            
  Operating system         Linux                                           
  Name                     scan                                            
  Class                    STANDALONE                                      
  Cluster nodes            rac1, rac2                                      
  Version                  18.0.0.0.0                                      
  Groups                   SYSOPER:dba SYSASM:dba SYSRAC:dba SYSDBA:dba    
  Cluster home             /u01/app/18.0.0/grid                            
  OCR locations            +CRS                                            
  Voting disk locations    /dev/asm-disk8, /dev/asm-disk9, /dev/asm-disk7  

Cluster network configuration details                                           
-------------------------------------                                           
  Interface name  Type  Subnet           Classification  
  eth1            IPV4  10.1.1.0/24      PRIVATE, ASM    
  eth0            IPV4  192.168.11.0/24  PUBLIC          

SCAN configuration details                                                      
--------------------------                                                      

SCAN "scan.localdomain" details                                                 
+++++++++++++++++++++++++++++++                                                 
  Name                scan.localdomain  
  IPv4 subnet         192.168.11.0/24   
  DHCP server type    static            
  End points          TCP:1521          

  SCAN listeners                                                                
  --------------                                                                
  Name        VIP address    
  LISTENER    192.168.11.60  


ASM configuration details                                                       
-------------------------                                                       
  Mode             remote  
  Password file    +RAC    
  SPFILE           +RAC    

  ASM disk group details                                                        
  ++++++++++++++++++++++                                                        
  Name  Redundancy  
  CRS   NORMAL      
  DATA  EXTERN      
  FRA   EXTERN      
  RAC   EXTERN      

Database configuration details                                                  
==============================                                                  

Database "ora.uptst.db" details                                                 
-------------------------------                                                 
  Name                ora.uptst.db                                                   
  Type                RAC                                                            
  Version             18.0.0.0.0                                                     
  Role                PRIMARY                                                        
  Management          AUTOMATIC                                                      
  policy                                                                             
  SPFILE              +DATA                                                          
  Password file       +DATA                                                          
  Groups              OSDBA:dba OSOPER:dba OSBACKUP:dba OSDG:dba OSKM:dba OSRAC:dba  
  Oracle home         /u01/app/oracle/product/18.0.0/db_home1                        

Database "ora.uptst2.db" details                                                
--------------------------------                                                
  Name                 ora.uptst2.db                                        
  Type                 RAC                                                  
  Version              12.1.0.2.0                                           
  Role                 PRIMARY                                              
  Management policy    AUTOMATIC                                            
  SPFILE               +DATA                                                
  Password file        +DATA                                                
  Groups               OSDBA:dba OSOPER:dba OSBACKUP:dba OSDG:dba OSKM:dba  
  Oracle home          /u01/app/oracle/product/12.1.0.2_1                   
[oracle@rac1 Desktop]$ 

The Juilliard School and Rutgers Tap Oracle Cloud to Manage Educational Back-office System

Oracle Press Releases - Tue, 2019-10-22 07:00
Press Release
The Juilliard School and Rutgers Tap Oracle Cloud to Manage Educational Back-office System Leading colleges/universities entrust complex global HR, finance and student operations to Oracle Cloud

Redwood Shores, Calif.—Oct 22, 2019

To manage complex environments globally, leading higher education institutions The Juilliard School and Rutgers University are turning to Oracle Cloud. Both institutions, looking to accelerate student success through more efficient operations across multiple schools and campuses, have chosen Oracle Enterprise Resource Planning (ERP) Cloud, Oracle Human Capital Management (HCM) Cloud, and Oracle Student Cloud.

Founded in 1905, The Juilliard School is a world-famous leader in performing arts education. The school’s mission is to provide the highest caliber of artistic education for gifted musicians, dancers, and actors from around the world. Located in New York City, Juilliard offers undergraduate and graduate degrees in dance, drama and music. Beyond its New York campus, Juilliard is now defining new directions in global performing arts education through its new branch campus in Tianjin, China, The Tianjin Juilliard School, and the global K-12 curricula.

Adopting a global operating model created challenges and complexity for Juilliard’s IT staff. Oracle Cloud provides a unified platform that will enable Juilliard to deliver an exceptional experience in both English and Chinese, and importantly, can be managed with existing IT resources. Once deployed, the Oracle Student Cloud solution will support approximately 2,000 Juilliard students across both campuses and 800 faculty and staff using the ERP, Student, and HCM Cloud solutions in New York.

“The opening of The Tianjin Juilliard School will broaden access to Juilliard’s world-class performing arts education across China and Asia,” said Carl Young, CIO of The Juilliard School. “With Oracle Cloud we will be able to provide our campuses in New York and Tianjin with unified, modern, multi-lingual systems that will have a significant impact on information management, which will ultimately help us better serve our students.”

Rutgers is a leading national research university and the state of New Jersey’s preeminent, comprehensive public institution of higher education. Established in 1766, the university is the eighth oldest higher education institution in the United States and home to more than 150 undergraduate majors, more than 400 graduate programs, and 300 research centers and institutes.

The daunting scale of Rutgers—with more than 70,000 students across 29 different schools and colleges at several campus locations—means it faced different, yet similarly formidable, issues of complexity. As part of a bold five-year plan to modernize and unify its key systems, Rutgers chose Oracle Cloud. 

“We strive to provide new and better experiences for our students,” said Michele Norin, senior vice president and chief information officer at Rutgers University. “The size and scale of Rutgers presents many difficulties as we work to manage and develop our systems. Oracle Cloud helps us meet our goals, stay on the forefront of higher education as it evolves, and enable our students to be more successful.”

Oracle Student Cloud is an integral component to an institution’s efforts to reduce educational costs while enhancing student outcomes and success. Oracle SFP Cloud streamlines the financial aid processes and delivers invaluable, data-backed insights into student successes, freeing administrators to focus more on supporting the academic needs of its students. Oracle ERP Cloud provides the financial solutions needed for institutions to enhance productivity, reduce costs and improve controls. Oracle HCM Cloud supports the changing business requirements around talent management and talent acquisition while furthering workforce productivity.

Contact Info
Katie Barron
Oracle
+1.202.904.1138
katie.barron@oracle.com
Kristin Reeves
Oracle
+1.925.787.6744
kris.reeves@oracle.com
About Oracle

The Oracle Cloud offers a complete suite of integrated applications for Sales, Service, Marketing, Human Resources, Finance, Supply Chain and Manufacturing, plus Highly Automated and Secure Generation 2 Infrastructure featuring the Oracle Autonomous Database. For more information about Oracle (NYSE: ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Katie Barron

  • +1.202.904.1138

Kristin Reeves

  • +1.925.787.6744

FAR SYNC FAILOVER

Tom Kyte - Tue, 2019-10-22 00:46
Dear Sir, In case of an outage on the primary database, the standard failover procedure applies, and after some time primary server available then how it going to sync all 3 server. please help me to understand this. Thanks Pradeep
Categories: DBA Blogs

Oracle query running slow

Tom Kyte - Tue, 2019-10-22 00:46
Hi Team, we have a SQL query which is a source query for the ETL load job, this take around 3 hours to run, could you please help us how we can make it run faster. The row count of the tables involved are as follows. D_PERSON 4618595 ...
Categories: DBA Blogs

Keep pooI

Tom Kyte - Tue, 2019-10-22 00:46
when I assign any of the segment to keep pool , It is nessesary to set appropriate DB_KEEP_CACHE_SIZE as per the sizes of the segment. or it will dynamically set DB_KEEP_CACHE_SIZE?
Categories: DBA Blogs

Limit parallelism

Tom Kyte - Tue, 2019-10-22 00:46
Hi ASKTOM team, I am not very good with parallelism, so have a question about DW database. These are my current settings: <code> parallel_adaptive_multi_user boolean TRUE parallel_automatic_tuning boolean FALSE parallel_degree_li...
Categories: DBA Blogs

Log Files in Oracle External Tables

Tom Kyte - Tue, 2019-10-22 00:46
My External Tables were working fine before i accidentally deleted all the .log and .bad files from the default location. Now I am getting below error ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-...
Categories: DBA Blogs

(Theoretical) Confusion with roles and public synonyms

Tom Kyte - Tue, 2019-10-22 00:46
Hi Tom, <b>Confusion1:- </b> Suppose I have three user accounts in my database: A, B and C. 'A' user has the privilege to create a role. Suppose there is a table named 'employee' in schema 'A' and 'A' issues:- 1. create role GiveAccess; 2...
Categories: DBA Blogs

Stat gather impact on production environment

Tom Kyte - Mon, 2019-10-21 12:45
On OLTP production environment, during huge transaction period, what is an impact if we run the stat gather of used schema for transaction???, It will missed any indexes, and other operation issues???
Categories: DBA Blogs

Best practice for "archiving" legacy tables and their data

Tom Kyte - Mon, 2019-10-21 12:45
Hi, I recently removed the last piece of front-end functionality that relied on a table, and am certain that that table and its data is no longer needed for the application to function. We'll have more similar tables in this situation in the near ...
Categories: DBA Blogs

Fetch across commit

Tom Kyte - Mon, 2019-10-21 12:45
what do you mean by 'Fetch across commit'
Categories: DBA Blogs

Is there a maximum number of schemas that can be included in a datapump par file?

Tom Kyte - Mon, 2019-10-21 12:45
I've been tasked with migrating a very large warehouse database (9TB) from hardware in one data center to new hardware in a different data center. For various reasons, the method I've selected for the migration is datapump. I'm breaking up the data...
Categories: DBA Blogs

Moat Wins Adweek “Reader’s Choice” Award for Best Brand Safety/Verification Solution

Oracle Press Releases - Mon, 2019-10-21 06:00
Blog
Moat Wins Adweek “Reader’s Choice” Award for Best Brand Safety/Verification Solution

By Kurt Kratchman, GVP, Product Development, Oracle Data Cloud—Oct 21, 2019

 

If you want to find the best product or service, you should ask the people who know that product or service best for their recommendations.

Want a reliable dishwasher? Check out the online reviews from appliance installers and repairmen. In the market for a new car?  Explore the consumer satisfaction surveys from thousands of current owners. Searching for the perfect pizza in Pittsburgh? Pick up a copy of the “Best of Pittsburgh” poll in the local City Paper.

The same holds true in digital advertising, which is why we are so honored and delighted to announce that Moat by Oracle Data Cloud is the winner of the 2019 Adweek “Readers’ Choice: Best of Tech” Award for Brand Safety/Verification.

The awards are based on an Adweek reader survey in which more than 50,000 votes were cast by executives from leading brands and agencies for their favorite technology providers and solutions. These experts know our industry the best, as they evaluate and make mission-critical decisions on a daily basis about which technologies, products, and solutions they should use to support their brands.

Equally important, these executives measure success by the real-world impact of their marketing investments. Therefore, they dedicate significant time and resources to finding technology partners who can help keep their brands safe, ensure their ads are viewable, and protect their campaigns from invalid traffic and fraud, which are the cornerstone of our Moat products.

Reinforcing Moat’s industry leadership, MediaPost editor-in-chief Joe Mandese noted in a column last week that Moat “has become the closest thing to a de facto currency for digital audience attention.”

Moat offers a range of products to help customers ensure their advertising dollars are well-spent, including our industry-leading Moat Analytics and Pre-Bid by Moat, which help identify brand-safe, fraud-safe, and viewable inventory before an ad is placed. Part of that solution is Oracle’s Contextual Intelligence, which provides real-time content review and classification across the majority of the addressable footprint of the open web. That functionality gives advertisers greater control over the safety and suitability of content near their ads.

Moat customers, along with their marketing peers, added their strong voice to the vote results. “To be effective, advertising needs to be viewable by real people in brand-safe environments, which is why rigorous standards for ad measurement, verified by third party independent providers is so important,” said Yale Cohen, EVP Digital Investment Ad Standards, Publicis Media. “As a long-term Moat customer, we congratulate the Oracle Data Cloud team on this award.”

“As a Publisher, it is critical we provide our clients with the confidence that their ad dollars are being spent wisely on our platforms,” added Nicole Lesko, SVP Ad Product & Revenue for Meredith Corporation. “As a customer of Moat, we have come to rely on their best-in-class measurement to ensure our inventory is bot-free, viewable and safe across channels.”

Beyond Moat’s win, Oracle Data Cloud’s recognition in the Adweek survey extended across its full product suite, with additional top-three finishes against scores of companies in the categories of Data Supplier (Oracle Data Cloud) and Measurement and Analytics: Digital and Mobile Display Advertising (Moat). In fact, Oracle Data Cloud secured a top-three finish in every one of its product categories from Audience to Measurement and Context.

As a data-driven company, we are delighted that Adweek’s analysis of votes by agency and brand executives supports what hundreds of their peers already know: If you want to protect your brand with the best viewability, fraud, and brand safety solutions, there’s only one Moat.

For more information about Moat by Oracle Data Cloud and the full range of solutions offered, including brand safety, viewability and IVT measurement, please visit Moat.com.

Index Rebuild and analyze

Tom Kyte - Mon, 2019-10-21 03:45
Hello Tom , I have a query regarding Index rebuild . what according to you should be time lag between index rebuilds. We are rebuilding indexes every week .but we found it is causing lot of fragmentation. is there any way we could find out whet...
Categories: DBA Blogs

Views of Views

Tom Kyte - Mon, 2019-10-21 03:45
I remember hearing some time ago that creating views based upon other existing views should be avoided as it can often confuse the optimiser and result in full table scans. I expect that this is just another urban myth however I would be intereste...
Categories: DBA Blogs

Inserts with APPEND Hint.

Tom Kyte - Mon, 2019-10-21 03:45
<code>insert /*+ append */ into t select rownum,mod(rownum,5) from all_objects where rownum <=1000 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- -----...
Categories: DBA Blogs

Kill a session from database procedure

Tom Kyte - Mon, 2019-10-21 03:45
How i can kill a session from a stored database procedure. There is some way to do this?
Categories: DBA Blogs

Solr Sharding – Concepts & Methods

Yann Neuhaus - Sun, 2019-10-20 03:06

A few weeks ago, I published a series of blog on the Alfresco Clustering, including Solr Sharding. At that time, I planned to first explain what is really the Solr Sharding, what are the different concepts and methods around it. Unfortunately, I didn’t get the time to write this blog so I had to post the one related to Solr even before explaining the basics. Today, I’m here to rights my wrong! Obviously, this blog has a focus on Alfresco related Solr Sharding since that’s what I do.

I. Solr Sharding – Concepts

The Sharding in general is the partitioning of a set of data in a specific way. There are several possibilities to do that, depending on the technology you are working on. In the scope of Solr, the Sharding is therefore the split of the Solr index into several smaller indices. You might be interested in the Solr Sharding because it improves the following points:

  • Fault Tolerance: with a single index, if you lose it, then… you lost it. If the index is split into several indices, then even if you are losing one part, you will still have all others that will continue working
  • High Availability: it provides more granularity than the single index. You might want for example to have a few small indices without HA and then have some others with HA because you configured them to contain some really important nodes of your repository
  • Automatic Failover: Alfresco knows automatically (with Dynamic Registration) which Shards are up-to-date and which ones are lagging behind so it will choose automatically the best Shards to handle the search queries so that you get the best results possible. In combination with the Fault Tolerance above, this gives the best possible HA solution with the less possible resources
  • Performance improvements: better performance in indexing since you will have several Shards indexing the same repository so you can have less work done by each Shards for example (depends on Sharding Method). Better performance in searches since the search query will be processes by all Shards in parallel on smaller parts of the index instead of being one single query on the full index

Based on benchmarks, Alfresco considers that a Solr Shard can contain up to 50 to 80 000 000 nodes. This is obviously not a hard limit, you can have a single Shard with 200 000 000 nodes but it is more of a best practice if you want to keep a fast and reliable index. With older versions of Alfresco (before the version 5.1), you couldn’t create Shards because Alfresco didn’t support it. So, at that time, there were no other solutions than having a single big index.

There is one additional thing that must be understood here: the 50 000 000 nodes soft limit is 50M nodes in the index, not in the repository. Let’s assume that you are using a DB_ID_RANGE method (see below for the explanation) with an assumed split of 65% live nodes, 20% archived nodes, 15% others (not indexed: renditions, other stores, …). So, if we are talking about the “workspace://SpacesStore” nodes (live ones), then if we want to fill a Shard with 50M nodes, we will have to use a DB_ID_RANGE of 100*50M/65 = 77M. Basically, the Shard should be more or less “full” once there are 77M IDs in the Database. For the “archive://SpacesStore” nodes (archived ones), it would be 100*50M/20 = 250M.

Alright so what are the main concepts in the Solr Sharding? There are several terms that need to be understood:

  • Node: It’s a Solr Server (a Solr installed using the Alfresco Search Services). Below, I will use “Solr Server” instead because I already use “nodes” (lowercase) for the Alfresco Documents so using “Node” (uppercase) for the Solr Server, it might be a little bit confusing…
  • Cluster: It’s a set of Solr Servers all working together to index the same repository
  • Shard: A part of the index. In other words, it’s a representation (virtual concept) of the index composed of a certain set of nodes (Alfresco Documents)
  • Shard Instance: It’s one Instance of a specific Shard. A Shard is like a virtual concept while the Instance is the implementation of that virtual concept for that piece of the index. Several Shard Instances of the same Shard will therefore contain the same set of Alfresco nodes
  • Shard Group: It’s a collection of Shards (several indices) that forms a complete index. Shards are part of the same index (same Shard Group) if they:
    • Track the same store (E.g.: workspace://SpacesStore)
    • Use the same template (E.g.: rerank)
    • Have the same number of Shards max (“numShards“)
    • Use the same configuration (Sharding methods, Solr settings, …)

Shard is often (wrongly) used in place of Shard Instance which might lead to some confusion… When you are reading “Shard”, sometimes it means the Shard itself (the virtual concept), sometimes it’s all its Shard Instances. This is these concepts can look like:
Solr Sharding - Concepts

II. Solr Sharding – Methods

Alfresco supports several methods for the Solr Sharding and they all have different attributes and different ways of working:

  • MOD_ACL_ID (ACL v1): Alfresco nodes and ACLs are grouped by their ACL ID and stored together in the same Shard. Different ACL IDs will be assigned randomly to different Shards (depending on the number of Shards you defined). Each Alfresco node using a specific ACL ID will be stored in the Shard already containing this ACL ID. This simplifies the search requests from Solr since ACLs and nodes are together, so permission checking is simple. If you have a lot of documents using the same ACL, then the distribution will not be even between Shards. Parameters:
    • shard.method=MOD_ACL_ID
    • shard.instance=<shard.instance>
    • shard.count=<shard.count>
  • ACL_ID (ACL v2): This is the same as the MOD_ACL_ID, the only difference is that it changes the method to assign to ACL to the Shards so it is more evenly distributed but if you still have a lot of documents using the same ACL then you still have the same issue. Parameters:
    • shard.method=ACL_ID
    • shard.instance=<shard.instance>
    • shard.count=<shard.count>
  • DB_ID: This is the default Sharding Method in Solr 6 which will evenly distribute the nodes in the different Shards based on their DB ID (“alf_node.id“). The ACLs are replicated on each of the Shards so that Solr is able to perform the permission checking. If you have a lot of ACLs, then this will obviously make the Shards a little bit bigger, but this is usually insignificant. Parameters:
    • shard.method=DB_ID
    • shard.instance=<shard.instance>
    • shard.count=<shard.count>
  • DB_ID_RANGE: Pretty much the same thing as the DB_ID but instead of looking into each DB ID one by one, it will just dispatch the DB IDs from the same range into the same Shard. The ranges are predefined at the Shard Instance creation and you cannot change them later, but this is also the only Sharding Method that allows you to add new Shards dynamically (auto-scaling) without the need to perform a full reindex. The lower value of the range is included and the upper value is excluded (for Math lovers: [begin-end[ ;)). Since DB IDs are incremental (increase over time), performing a search query with a date filter might end-up as simple as checking inside a single Shard. Parameters:
    • shard.method=DB_ID_RANGE
    • shard.range=<begin-end>
    • shard.instance=<shard.instance>
  • DATE: Months will be assigned to a specific Shard sequentially and then nodes are indexed into the Shard that was assigned the current month. Therefore, if you have 2 Shards, each one will contain 6 months (Shard 1 = Months 1,3,5,7,9,11 // Shard 2 = Months 2,4,6,8,10,12). It is possible to assign consecutive months to the same Shard using the “shard.date.grouping” parameter which defines how many months should be grouped together (a semester for example). If there is no date on a node, the fallback method is to use DB_ID instead. Parameters:
    • shard.method=DATE
    • shard.key=exif:dateTimeOriginal
    • shard.date.grouping=<1-12>
    • shard.instance=<shard.instance>
    • shard.count=<shard.count>
  • PROPERTY: A property is specified as the base for the Shard assignment. The first time that a node is indexed with a new value for this property, the node will be assigned randomly to a Shard. Each node coming in with the same value for this property will be assigned to the same Shard. Valid properties are either d:text (single line text), d:date (date only) or d:datetime (date+time). It is possible to use only a part of the property’s value using “shard.regex” (To keep only the first 4 digit of a date for example: shard.regex=^\d{4}). If this property doesn’t exist on a node or if the regex doesn’t match (if any is specified), the fallback method is to use DB_ID instead. Parameters:
    • shard.method=PROPERTY
    • shard.key=cm:creator
    • shard.instance=<shard.instance>
    • shard.count=<shard.count>
  • EXPLICIT_ID: Pretty much similar to the PROPERTY but instead of using the value of a “random” property, this method requires a specific property (d:text) to define explicitly on which Shard the node should be indexed. Therefore, this will require an update of the Data Model to have one property dedicated to the assignment of a node to a Shard. In case you are using several types of documents, then you will potentially want to do that for all. If this property doesn’t exist on a node or if an invalid Shard number is given, the fallback method is to use DB_ID instead. Parameters:
    • shard.method=EXPLICIT_ID
    • shard.key=<property> (E.g.: cm:targetShardInstance)
    • shard.instance=<shard.instance>
    • shard.count=<shard.count>

As you can see above, each Sharding Method has its own set of properties. You can define these properties in:

  • The template’s solrcore.properties file in which case it will apply to all Shard Instance creations
    • E.g.: $SOLR_HOME/solrhome/templates/rerank/conf/solrcore.properties
  • The URL/Command used to create the Shard Instance in which case it will only apply to the current Shard Instance creation
    • E.g.: curl -v “http://host:port/solr/admin/cores?action=newCore&…&property.shard.method=DB_ID_RANGE&property.shard.range=0-50000000&property.shard.instance=0

Summary of the benefits of each method:
Solr Sharding - Benefits

First supported versions for the Solr Sharding in Alfresco:
Solr Sharding - Availability

Hopefully, this is a good first look into the Solr Sharding. In a future blog, I will talk about the creation process and show some example of what is possible. If you want to read more on the subject, don’t hesitate to take a look at the Alfresco documentation, it doesn’t explain everything, but it is still a very good starting point.

Cet article Solr Sharding – Concepts & Methods est apparu en premier sur Blog dbi services.

Pages

Subscribe to Oracle FAQ aggregator