Forum Stats

  • 3,780,464 Users
  • 2,254,398 Discussions
  • 7,879,339 Comments

Discussions

Explain Plan Better using Literals vs Bind Variables ... odd!

Duncs
Duncs Member Posts: 507 Bronze Badge
edited Jul 26, 2019 5:52AM in SQL & PL/SQL

Hi.

I need a little help with some off behaviour with what should be a very simple query.

DB Version: 11.2.0.4

ACTIVITY_ATTENDANCE Rowcount: 4,300,000

Stats: Gathered pre tuning exercise.

Index on column: ACCOUNTING_FY_LOV_ID

Query 1 (using bind variable)

SELECT aa.fraction_of_date_attended AS approved_va_days

             , aa.accounting_fy_lov_id as financial_year_lov_id

  FROM activity_attendance aa

  WHERE aa.ACCOUNTING_FY_LOV_ID = :P84_CURRENT_FY_LOV_ID;

Explain Plan

Plan hash value: 335757524
 
  -------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation            | Name                | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
  -------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT     |                     | 87062 |   425K|  2298   (1)| 00:01:07 |        |      |            |
  |   1 |  PX COORDINATOR      |                     |       |       |            |          |        |      |            |
  |   2 |   PX SEND QC (RANDOM)| :TQ10000            | 87062 |   425K|  2298   (1)| 00:01:07 |  Q1,00 | P->S | QC (RAND)  |
  |   3 |    PX BLOCK ITERATOR |                     | 87062 |   425K|  2298   (1)| 00:01:07 |  Q1,00 | PCWC |            |
  |*  4 |     TABLE ACCESS FULL| ACTIVITY_ATTENDANCE | 87062 |   425K|  2298   (1)| 00:01:07 |  Q1,00 | PCWP |            |
  -------------------------------------------------------------------------------------------------------------------------
 
  Predicate Information (identified by operation id):
  ---------------------------------------------------
 
     4 - filter("AA"."ACCOUNTING_FY_LOV_ID"=TO_NUMBER(:P84_CURRENT_FY_LOV_ID))

Query 1 (using literal in place of bind variable)

SELECT aa.fraction_of_date_attended AS approved_va_days

             , aa.accounting_fy_lov_id as financial_year_lov_id

  FROM activity_attendance aa

  WHERE aa.ACCOUNTING_FY_LOV_ID = 4306;

Explain Plan

Plan hash value: 909240005
 
  ---------------------------------------------------------------------------------------------------------
  | Id  | Operation                   | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |                           |  1302 |  6510 |   129   (0)| 00:00:04 |
  |   1 |  TABLE ACCESS BY INDEX ROWID| ACTIVITY_ATTENDANCE       |  1302 |  6510 |   129   (0)| 00:00:04 |
  |*  2 |   INDEX RANGE SCAN          | ACTIVITY_ATTENDANCE_IDX15 |  1302 |       |     5   (0)| 00:00:01 |
  ---------------------------------------------------------------------------------------------------------
 
  Predicate Information (identified by operation id):
  ---------------------------------------------------
 
     2 - access("AA"."ACCOUNTING_FY_LOV_ID"=4306)

Hopefully you can see from the 2 examples above, using a bind variable generates a less optimal explain plan than hard coding in a literal against the indexed column. This goes against everything I thought I knew about performance tuning and wondered if anyone can help explain why? This performance difference also manifests itself in our front end application as well. Using a bind variable takes about 3 seconds to run the report but using a hard coded literal takes 0.3 of a second.

The number of rows returned is about 1100 so the index should be being used given I have 4.3 million rows in the table.

Thanks in advance for any help.

Duncs

Tagged:
Mustafa_KALAYCIDuncsuser9294681_jummathguy
«1

Answers

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,695 Silver Crown
    edited Jul 25, 2019 7:04AM

    What is the output of:

    SELECT COUNT(DISTINCT aa.accounting_fy_lov_id)

    FROM activity_attendance aa;

    that will give you information to determine the selectivity of the column index...

  • KayK
    KayK Member Posts: 1,687 Bronze Crown
    edited Jul 25, 2019 7:07AM

    Hi Duncs,

    are there any histograms on this index_column ?

    Have a look at dba_tab_col_statistics with table- and colunm-name.

    regards

    Kay

    Mustafa_KALAYCI
  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,373 Bronze Crown
    edited Jul 25, 2019 7:07AM

    does your table have any parallelism?

    select degree from all_Tables where table_name = upper('activity_attendance');

    your first query run as parallel for some reason and that probably cause full table scan. add /*+ no_parallel */ hint to first query (after the select statement) and check the execution plan again. by the way instead of "explain plan" get execution plan using dbms_xplan.cursor! in that way you will be getting real running query's execution plan.

  • Duncs
    Duncs Member Posts: 507 Bronze Badge
    edited Jul 25, 2019 7:13AM

    SELECT COUNT(DISTINCT aa.accounting_fy_lov_id)

    FROM activity_attendance aa;

    Returns: 5 rows

  • Duncs
    Duncs Member Posts: 507 Bronze Badge
    edited Jul 25, 2019 7:15AM

    Hi Kay

    Yes, I see a record returned when querying that view. Historgram = FREQUENCY and last analyzed today

    Cheers

    Duncs

  • KayK
    KayK Member Posts: 1,687 Bronze Crown
    edited Jul 25, 2019 7:17AM

    then the full-table-scan is the best way for an unknown value of your index-column.

    You can expect 20% of the rows as the result. Do this via an index-scan it will last more than your 3 seconds.

    Have you tried to explain the statement with one the other real values ? A value that exists in more than 20% of your rows.

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,695 Silver Crown
    edited Jul 25, 2019 7:19AM

    That means that you have 5 values in those 4.3 millons rows. The optimizer will understand that you have more or less 860K rows per each distinct value, so it is not worth using an index to return 20% of the rows in the table, unless it has an histogram that explicitly tells it how many rows has each particular value...

    Since you have a non uniform data distribution (value 4306 has only 1100 rows) then you need histograms to allow Oracle to decide when to use the index and when not.

    user9294681
  • KayK
    KayK Member Posts: 1,687 Bronze Crown
    edited Jul 25, 2019 7:19AM

    Then the optimizer can "know" that your value 4306 will only get back a few rows, lets say some hundreds or thousands. But not millions.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Jul 25, 2019 7:44AM

    Which query is faster, elapsed time wise, have you timed them?

    Also note that :P84_CURRENT_FY_LOV_ID is not a NUMBER datatype and is CAST to NUMBER by TO_NUMBER function.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jul 25, 2019 8:27AM

    Explain plan NEVER peeks bind variables, this is why it is not to be trusted. The plan you want is the execution plan that you can grab from memory.


    When you use binds, you do so because you don't want Oracle to keep parsing the effectively-same query - it will try to use the last plan that was created for it. If you have a query which is sensitive to the value of that bind then this can cause quite the problem.

    In your case, when Oracle doesn't peek the bind it thinks that you will get 1/5 of the rows in the table by that filter - that would take forever to obtain using an index so a full tablescan is desired.

    When you executed the query with a bind variable, then it will use whatever plan it's already come up with, if the value that was peeked for the hard parse was one of the values that has the majority of your rows then Oracle is going to not want to use an index. Adaptive Cursor Sharing will kick in at some point but this is often too late (and requires a certain order of operations).

    Should the same code be used to query the table for the massively skewed values of ACCOUNTING_FY_LOV_ID? Would it be more dangerous if that execution used an index as opposed to every execution using a full table scan? Is there any reason for the column to be so massively skewed?

    You can see the peeked bind variables in the advanced execution plan. Find the sql_id for the query in v$sql and grab the advanced formatted plans with

    select * from table(dbms_xplan.display_cursor(sql_id=>'<SQL ID>', format=>'advanced'));

    -edit

    Have a read about Adaptive Cursor Sharing https://oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1  for ways to get it to help you here

    Duncs