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.