Skip to Main Content
  • Questions
  • Unwarranted Direct Path Reads - how to avoid?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Khalid.

Asked: May 19, 2021 - 1:40 pm UTC

Last updated: May 27, 2021 - 9:42 am UTC

Version: 12.1.0.2

Viewed 100+ times

You Asked

There is a java batch program that runs at night, which issues several thousand queries in a loop (apparently), one such query I extracted from tkprof and pasted here.
The behavior of the batch job is as follows:
1) The query does a Full Table Scan (FTS), although a usable index exists for the 2 predicates shown below
2) The same type of query on other nights, also does an FTS, still ignores the index, but reads it out of the buffer cache (I can paste the tkprof snippet of that too, if you want it), and is much faster than 1
3) I ran a test using bind variables, it uses the index and is much faster, but the binds are not there in their Java code, I need to ask the developers to issue prepared statements,


One such query extracted from tkprof below takes two predicates passed literally as below:

-- One of those queries
SQL ID: 7a3h5nkztcdbm Plan Hash: 2536508599

 
select to_char(TRANSMITTAL_DATE,'DD-MON-YYYY') as transmittal_date , STARS_AGENCY_CODE , .........<several-other-columns>......
from phr.phr_sps_dbm_mast where employee_id = 115773158 and job_number = 605378;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      1.33      10.29      41815      41824          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      1.34      10.30      41815      41824          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 1230
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS FULL PHR_SPS_DBM_MAST (cr=41824 pr=41815 pw=0 time=10297064 us cost=6556 size=206 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  reliable message                                1        0.00          0.00
  enq: KO - fast object checkpoint                2        0.00          0.00
  KJC: Wait for msg sends to complete             1        0.00          0.00
  direct path read                              134        0.51          8.97
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

Cumulative result of all those reads is here:
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  direct path read                           259449        2.59      16104.95


Total tkprof elapsed seconds = 18708, out of which the Direct Path Read = 16104

Other details:
The above table segment size is only 328M with about 16000 rows in it.
Memory Parameters: Memory_TARGET=45G, SGA_TARGET=0, db_cache_size not set

Question:
How do I influence Oracle to avoid "Direct Path Reads" for the specific query? and preferably use the Index.
Can I cache the table? we have plenty of memory.

Other docs that are applicable to my case:
1) "Bug 18498878 - Small or Medium Size Tables are not Cached Consistently (Doc ID 18498878.8), but the suggested workaround is:
Workaround is either "set event 10949 level 1", OR serial_direct_read = never.
2) " https://fritshoogland.wordpress.com/2015/10/14/direct-path-and-buffered-reads-again/" - but the table in question should easily qualify as a small table

Thanks

and we said...

First thing to be aware of - using MEMORY_TARGET for a system of that size is definitely not recommended, and in fact, from 12.2 onwards, when you try to create a system larger than 4G memory using memory_target we'll explicitly pop up a warning about it.

So plan on moving to sga_target / pga_aggregate_target at some stage in the near future.

This is probably the definitive guide to understanding full table scans and the buffer cache:

https://blogs.oracle.com/smartscan-deep-dive/when-bloggers-get-it-wrong-part-1
https://blogs.oracle.com/smartscan-deep-dive/when-bloggers-get-it-wrong-part-2

but you mention an appropriate index. If you have an index on employee_id and job_number, and you're passing in "where employee_id = 115773158 and job_number = 605378" and an index is not being used, that is worth investigation.

Can you post the following as a review (run this from SQLPlus or similar)

select /*+ gather_plan_statistics */ to_char(TRANSMITTAL_DATE,'DD-MON-YYYY') as transmittal_date , STARS_AGENCY_CODE , .........several-other-columns......
from phr.phr_sps_dbm_mast where employee_id = 115773158 and job_number = 605378;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))

Rating

  (2 ratings)

Comments

Good suggestions; and here is my plan output

Khalid Rahim, May 25, 2021 - 11:52 pm UTC

1) You said: So plan on moving to sga_target / pga_aggregate_target at some stage in the near future.
-- Answer: We will;
2) You suggested; definitive guide to understanding full table scans and the buffer cache:
-- I plan to do that
3) Can you post the following as a review (run this from SQLPlus or similar)

19:37:37 oracle@ITAP10> set lines 200 time off timing off
oracle@ITAP10> set serveroutput off
oracle@ITAP10> select /*+ gather_plan_statistics */ to_char(TRANSMITTAL_DATE,'DD-MON-YYYY') as transmittal_date ,
  STARS_AGENCY_CODE ,SPS_ID ,SSN ,TO_CHAR(EMPLOYEE_DATE_OF_BIRTH,
  'DD-MON-YYYY') AS EMPLOYEE_DATE_OF_BIRTH ,LEGAL_FIRST_NAME ,
  LEGAL_MIDDLE_INITIAL ,LEGAL_LAST_NAME ,GENDER ,ADDRESS_LINE_1 ,
  ADDRESS_LINE_2 ,ADDRESS_CITY ,ADDRESS_STATE ,ADDRESS_REGION_COUNTY ,
  ADDRESS_ZIP ,ADDRESS_COUNTRY ,EMAIL_WORK ,EMAIL_PERSONAL ,TELEPHONE_HOME ,
  TELEPHONE_WORK ,WORK_EXTENSION ,TELEPHONE_MOBILE ,EMPLOYEE_TYPE ,
    2    3    4    5  EMPLOYEE_SUBTYPE ,FTE ,CHECK_DISTRIBUTION_CD ,TO_CHAR(LEAVE_OF_ABSENCE_DT,
  6    7    8    'DD-MON-YYYY') as LEAVE_OF_ABSENCE_DT, ABSENCE_REASON ,
  to_char(ESTIMATED_RETURN_FROM_LEAVE_DT,'DD-MON-YYYY') as
  ESTIMATED_RETURN_FROM_LEAVE_DT, multi_agency_employment, benefit_job_agency
from
 phr.phr_sps_dbm_mast where employee_id = 115773158 and job_number = 605378;
  9   10   11   12   13

TRANSMITTAL_DATE     STARS_ SPS_ID     SSN       EMPLOYEE_DATE_OF_BIR LEGAL_FIRST_NAME                         L LEGAL_LAST_NAME                          G
-------------------- ------ ---------- --------- -------------------- ---------------------------------------- - ---------------------------------------- -
ADDRESS_LINE_1                                          ADDRESS_LINE_2                                          ADDRESS_CITY                   AD ADDRESS_REGION_COUNTY                    ADDRESS_ZI
------------------------------------------------------- ------------------------------------------------------- ------------------------------ -- ---------------------------------------- ----------
ADD EMAIL_WORK                                                             EMAIL_PERSONAL                                                         TELEPHONE_HO TELEPHONE_WO WORK TELEPHONE_MO E EM FTE
--- ---------------------------------------------------------------------- ---------------------------------------------------------------------- ------------ ------------ ---- ------------ - -- ---
CHECK LEAVE_OF_ABSENCE_DT  AB ESTIMATED_RETURN_FRO M BENEFIT_JO
----- -------------------- -- -------------------- - ----------
19-MAY-2021          360222 W2069544   247814010 16-JUL-1987          Kevin                                    M Daniels                                  M
6878 Happy Heart Ln                                                                                             Columbia                       MD                                          21045
USA danielkm@g.umd.edu                                                                                                                            +18035536252 +13014058028                   R 02 050
12709                                              N 360222


1 row selected.

oracle@ITAP10> oracle@ITAP10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  99q9m00u6m0fq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */
to_char(TRANSMITTAL_DATE,'DD-MON-YYYY') as transmittal_date ,
STARS_AGENCY_CODE ,SPS_ID ,SSN ,TO_CHAR(EMPLOYEE_DATE_OF_BIRTH,
'DD-MON-YYYY') AS EMPLOYEE_DATE_OF_BIRTH ,LEGAL_FIRST_NAME ,
LEGAL_MIDDLE_INITIAL ,LEGAL_LAST_NAME ,GENDER ,ADDRESS_LINE_1 ,
ADDRESS_LINE_2 ,ADDRESS_CITY ,ADDRESS_STATE ,ADDRESS_REGION_COUNTY ,
ADDRESS_ZIP ,ADDRESS_COUNTRY ,EMAIL_WORK ,EMAIL_PERSONAL
,TELEPHONE_HOME ,   TELEPHONE_WORK ,WORK_EXTENSION ,TELEPHONE_MOBILE
,EMPLOYEE_TYPE ,   EMPLOYEE_SUBTYPE ,FTE ,CHECK_DISTRIBUTION_CD
,TO_CHAR(LEAVE_OF_ABSENCE_DT,   'DD-MON-YYYY') as LEAVE_OF_ABSENCE_DT,
ABSENCE_REASON ,   to_char(ESTIMATED_RETURN_FROM_LEAVE_DT,'DD-MON-YYYY')
 as   ESTIMATED_RETURN_FROM_LEAVE_DT, multi_agency_employment,
benefit_job_agency from  phr.phr_sps_dbm_mast where employee_id =
115773158 and job_number = 605378

Plan hash value: 2536508599

---------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |      1 |        |      1 |00:00:22.02 |   42315 |  42305 |
|*  1 |  TABLE ACCESS FULL| PHR_SPS_DBM_MAST |      1 |      1 |      1 |00:00:22.02 |   42315 |  42305 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((TO_NUMBER("JOB_NUMBER")=605378 AND TO_NUMBER("EMPLOYEE_ID")=115773158))


31 rows selected.

oracle@ITAP10> select index_name from dba_indexes where table_name='PHR_SPS_DBM_MAST' and owner='PHR';

INDEX_NAME
--------------------------------------------------------------------------------------------------------------------------------
PHR_SPS_DBM_MAST2
PHR_SPS_DBM_MAST_PK

2 rows selected.


Chris Saxon
May 26, 2021 - 1:16 pm UTC

Notice how the filter has

TO_NUMBER() 


for both of the columns?

That suggests you're storing numeric values as strings => implicit conversion of the text to a number => stops the optimizer using an index over ( job_number, employee_id ) or any combination of these

Either you need to:

- Create an index over ( to_number ( job_number ), to_number ( employee_id ) )
- Bind character values instead of numeric
- Change the columns to be numbers instead of varchar2

Follow-up, this is the new plan to avoid Implicit Conversion

Khalid Rahim, May 26, 2021 - 1:20 pm UTC

Thanks to you, I just noticed the to_number conversion and made a correction to avoid the To_Number(...), the Employee_ID, and Job_Number were actually varchars.
Therefore ... Now they use the index

oracle@ITAP10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3t6x3k1wgxhaj, child number 0
-------------------------------------
select /*+ gather_plan_statistics */
to_char(TRANSMITTAL_DATE,'DD-MON-YYYY') as transmittal_date ,
STARS_AGENCY_CODE ,SPS_ID ,SSN ,TO_CHAR(EMPLOYEE_DATE_OF_BIRTH,
'DD-MON-YYYY') AS EMPLOYEE_DATE_OF_BIRTH ,LEGAL_FIRST_NAME ,
LEGAL_MIDDLE_INITIAL ,LEGAL_LAST_NAME ,GENDER ,ADDRESS_LINE_1 ,
ADDRESS_LINE_2 ,ADDRESS_CITY ,ADDRESS_STATE ,ADDRESS_REGION_COUNTY ,
ADDRESS_ZIP ,ADDRESS_COUNTRY ,EMAIL_WORK ,EMAIL_PERSONAL
,TELEPHONE_HOME ,   TELEPHONE_WORK ,WORK_EXTENSION ,TELEPHONE_MOBILE
,EMPLOYEE_TYPE ,   EMPLOYEE_SUBTYPE ,FTE ,CHECK_DISTRIBUTION_CD
,TO_CHAR(LEAVE_OF_ABSENCE_DT,   'DD-MON-YYYY') as LEAVE_OF_ABSENCE_DT,
ABSENCE_REASON ,   to_char(ESTIMATED_RETURN_FROM_LEAVE_DT,'DD-MON-YYYY')
 as   ESTIMATED_RETURN_FROM_LEAVE_DT, multi_agency_employment,
benefit_job_agency  from phr.phr_sps_dbm_mast where employee_id =
'115773158' and job_number = '605378'

Plan hash value: 2656174162

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |      1 |        |      1 |00:00:00.02 |       5 |      3 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PHR_SPS_DBM_MAST  |      1 |      1 |      1 |00:00:00.02 |       5 |      3 |
|*  2 |   INDEX RANGE SCAN                  | PHR_SPS_DBM_MAST2 |      1 |      1 |      1 |00:00:00.01 |       3 |      2 |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"='115773158' AND "JOB_NUMBER"='605378')


Chris Saxon
May 27, 2021 - 9:42 am UTC

Great, glad you got it sorted!

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.