tkprof Analysis

User_URPA8 · April 2020

Hi Experts,

Can someone please help me in understanding the below tkprof.Need some approximate analysis.The same is attached to notepad for better format.

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 2 0.00 0.00 0 0 2 0

Execute 842615 19.53 19.57 0 22 0 0

Fetch 842615 23333.33 23416.81 10 3469943694 0 842615

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 1685232 23352.86 23436.39 10 3469943716 2 842615

Misses in library cache during parse: 2

Misses in library cache during execute: 2

Optimizer mode: ALL_ROWS

Parsing user id: 173 (recursive depth: 1)

Number of plan statistics captured: 350

Rows (1st) Rows (avg) Rows (max) Row Source Operation

---------- ---------- ---------- ---------------------------------------------------

1 1 1 SORT AGGREGATE (cr=17522 pr=0 pw=0 time=33379 us)

0 0 1 VIEW (cr=17522 pr=0 pw=0 time=33376 us cost=607 size=13 card=1)

4 4 14 COUNT STOPKEY (cr=17522 pr=0 pw=0 time=33366 us)

4 4 14 VIEW (cr=17522 pr=0 pw=0 time=33364 us cost=607 size=13 card=1)

4 4 14 SORT ORDER BY STOPKEY (cr=17522 pr=0 pw=0 time=33363 us cost=607 size=188 card=1)

465 1135 1994 FILTER (cr=17522 pr=0 pw=0 time=38758 us)

467 1139 1998 NESTED LOOPS ANTI (cr=17508 pr=0 pw=0 time=35080 us cost=597 size=188 card=1)

468 1139 1998 NESTED LOOPS (cr=15219 pr=0 pw=0 time=33437 us cost=596 size=178 card=1)

468 1139 1998 NESTED LOOPS (cr=15214 pr=0 pw=0 time=31798 us cost=595 size=165 card=1)

468 1139 1998 NESTED LOOPS (cr=14071 pr=0 pw=0 time=30329 us cost=594 size=152 card=1)

468 1139 1998 NESTED LOOPS (cr=14067 pr=0 pw=0 time=29041 us cost=593 size=139 card=1)

468 1139 1998 NESTED LOOPS (cr=12923 pr=0 pw=0 time=27217 us cost=592 size=126 card=1)

608 1472 2381 NESTED LOOPS (cr=1639 pr=0 pw=0 time=5551 us cost=450 size=6578 card=143)

1 1 1 NESTED LOOPS (cr=4 pr=0 pw=0 time=8 us cost=2 size=19 card=1)

1 1 1 TABLE ACCESS BY INDEX ROWID XXABC_COLLEGE_MASTER_TBL (cr=2 pr=0 pw=0 time=4 us cost=1 size=6 card=1)

1 1 1 INDEX UNIQUE SCAN XXABC_COLLEGES_PK (cr=1 pr=0 pw=0 time=2 us cost=0 size=0 card=1)(object id 2313929)

1 1 1 TABLE ACCESS BY INDEX ROWID XXABC_LOV_MASTER_TBL (cr=2 pr=0 pw=0 time=3 us cost=1 size=13 card=1)

1 1 1 INDEX UNIQUE SCAN XXABC_LOVS_PK (cr=1 pr=0 pw=0 time=2 us cost=0 size=0 card=1)(object id 2313914)

608 1472 2381 TABLE ACCESS FULL XXABC_APPLICANT_COURSPREFS_TBL (cr=1635 pr=0 pw=0 time=5324 us cost=448 size=3861 card=143)

468 1139 1998 TABLE ACCESS BY INDEX ROWID XXABC_APPLICANT_DETAILS_TBL (cr=11284 pr=0 pw=0 time=18471 us cost=1 size=80 card=1)

579 1382 2339 INDEX UNIQUE SCAN XXABC_APPLICANTS_PK (cr=10419 pr=0 pw=0 time=16014 us cost=0 size=0 card=1)(object id 2316264)

0 1 3 NESTED LOOPS (cr=4420 pr=0 pw=0 time=3972 us cost=3 size=26 card=1)

2 2 13 TABLE ACCESS BY INDEX ROWID XXABC_APPLICANT_COURSPREFS_TBL (cr=4416 pr=0 pw=0 time=3594 us cost=2 size=14 card=1)

608 1472 2381 INDEX UNIQUE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=2944 pr=0 pw=0 time=2140 us cost=1 size=0 card=1)(object id 2316268)

0 1 3 TABLE ACCESS BY INDEX ROWID XXABC_CATEGORY_MASTER_TBL (cr=3 pr=0 pw=0 time=6 us cost=1 size=12 card=1)

2 2 13 INDEX UNIQUE SCAN XXABC_CATEGORY_PK (cr=2 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 2313948)

608 1472 2381 INDEX UNIQUE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=2944 pr=0 pw=0 time=2140 us cost=1 size=0 card=1)(object id 2316268)

0 1 3 TABLE ACCESS BY INDEX ROWID XXABC_CATEGORY_MASTER_TBL (cr=3 pr=0 pw=0 time=6 us cost=1 size=12 card=1)

2 2 13 INDEX UNIQUE SCAN XXABC_CATEGORY_PK (cr=2 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 2313948)

39 251 576 TABLE ACCESS BY INDEX ROWID BATCHED XXABC_APPLICANT_COURSPREFS_TBL (cr=2371 pr=0 pw=0 time=4787 us cost=3 size=10 card=1)

4693 8691 13009 INDEX RANGE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=1502 pr=0 pw=0 time=2613 us cost=2 size=0 card=4)(object id 2316268)

5 8 33 TABLE ACCESS BY INDEX ROWID BATCHED XXABC_APPLICANT_COURSPREFS_TBL (cr=2255 pr=0 pw=0 time=3760 us cost=3 size=10 card=1)

4513 8172 12952 INDEX RANGE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=1418 pr=0 pw=0 time=2105 us cost=2 size=0 card=4)(object id 2316268)

468 1139 1998 TABLE ACCESS BY INDEX ROWID XXABC_LOV_MASTER_TBL (cr=1143 pr=0 pw=0 time=1318 us cost=1 size=13 card=1)

468 1139 1998 INDEX UNIQUE SCAN XXABC_LOVS_PK (cr=4 pr=0 pw=0 time=515 us cost=0 size=0 card=1)(object id 2313914)

468 1139 1998 INDEX UNIQUE SCAN XXABC_LOVS_PK (cr=4 pr=0 pw=0 time=469 us cost=0 size=0 card=1)(object id 2313914)

468 1139 1998 TABLE ACCESS BY INDEX ROWID XXABC_LOV_MASTER_TBL (cr=1143 pr=0 pw=0 time=1077 us cost=1 size=13 card=1)

468 1139 1998 INDEX UNIQUE SCAN XXABC_LOVS_PK (cr=4 pr=0 pw=0 time=428 us cost=0 size=0 card=1)(object id 2313914)

468 1139 1998 TABLE ACCESS BY INDEX ROWID XXABC_LOV_MASTER_TBL (cr=5 pr=0 pw=0 time=1145 us cost=1 size=13 card=1)

468 1139 1998 INDEX UNIQUE SCAN XXABC_LOVS_PK (cr=4 pr=0 pw=0 time=441 us cost=0 size=0 card=1)(object id 2313914)

1 0 8 TABLE ACCESS BY INDEX ROWID XXABC_APPLICANT_COURSPREFS_TBL (cr=2289 pr=0 pw=0 time=1530 us cost=1 size=35510 card=3551)

468 1139 1998 INDEX UNIQUE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=1150 pr=0 pw=0 time=875 us cost=0 size=0 card=1)(object id 2316268)

0 0 0 VIEW index$_join$_014 (cr=13 pr=0 pw=0 time=287 us cost=0 size=12 card=1)

0 0 0 HASH JOIN (cr=13 pr=0 pw=0 time=285 us)

6 6 6 INDEX RANGE SCAN XXABC_CATEGORY_PK (cr=6 pr=0 pw=0 time=14 us cost=0 size=12 card=1)(object id 2313948)

12 11 12 INLIST ITERATOR (cr=8 pr=0 pw=0 time=23 us)

12 11 12 INDEX UNIQUE SCAN XXABC_CATEGORY_CODE_UK (cr=8 pr=0 pw=0 time=18 us cost=0 size=12 card=1)(object id 2313946)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

gc cr grant 2-way 9 0.00 0.00

db file sequential read 10 0.00 0.00

latch: row cache objects 10 0.00 0.00

latch: shared pool 17 0.00 0.00

resmgr:cpu quantum 21 0.00 0.00

********************************************************************************

Thank you,

satish

Jonathan Lewis · April 2020

3510875 wrote:
I would like to ask one more question.Is this index need to be unique or nonunique?
CREATE INDEX "XXADM"."XXADM_APPLCNT_APPLICANT_STATUS" ON "XXADM"."XXADM_APPLICANT_COURSPREFS_TBL"("APPLICANT_ID","STATUS_FLAG") COMPRESS 1;

There is no way to deduce whether or not the combination of (application_id, status_flag) should be constrained to be unique across the table. However if that was supposed to be the case there would already be a uniqueness constraint on the table to express that rule, and that would have created the index automatically. So there are no indications that the combination is supposed to be unique, so I wouldn't introduce one now. Create the index non-unique.

Regards

Jonathan Lewis

SureshMuddaveerappa · April 2020

Hi Satish,

I fee the question is too generic. It would be easier for the community feedback if you identify the specifics. Check out ID 32951.1, document ID 2556202.1, document ID 211498.1.

Hope this helps

Cheers

Suresh

User_URPA8 · April 2020

Sorry,Missed to add the sql statement.Below is the one.

SELECT COUNT(applicant_id)

FROM (SELECT applicant_id,

academic_year,

applicant_gender,

medium_of_study,

education_type,

college_id,

course_id,

medium_id,

hostel_required,

preference_order,

status_flag,

attribute7, -- Added on 7-mar-20

college_status_flag,

percentage,

-- group_percentage,

caste_category,

alloted_category,

NULL allotment_type

FROM (

SELECT adt.applicant_id,

lmt_gender.lov_code applicant_gender,

adt.medium_of_study,

act.college_id,

lmt_education_type.lov_code education_type,

act.course_id,

act.medium_id,

act.hostel_required,

act.preference_order,

act.status_flag,

act.attribute7, -- Added on 7-mar-20

adt.college_status_flag,

adt.academic_year,

adt.percentage,

adt.applicant_dob,

adt.legacy_appln_date,

adt.caste_category,

act.attribute1 alloted_category,

DECODE (lmt_pass.lov_code, 'ATTFIRST', 1, 'COMPARTL', 2, 3)

order_of_pass,

DECODE (late_entry_flag, 'N', 1, 'Y', 2, 3) order_of_entry,

DECODE (lmt_appearance.lov_code, 'REGULAR', 1, 'PRIVATE', 2, 3)

order_of_appearance,

DECODE (adt.is_ttd_employ_ward, 'Y', 1, 'N', 2, 3) order_of_ttd_emp,

DECODE (adt.is_balbhavan_studnt, 'Y', 1, 'N', 2, 3)

order_of_schooling

,act.attribute3 course_qe_priority

,adt.is_local_canditature_valid

,adt.is_ttd_emp_ward_info_valid

,adt.is_sv_bm_student_info_valid

,adt.is_social_ctgry_info_valid

-- ,DECODE(adt.college_status_flag,'B',1,'O',2,'N',3) order_of_status

-- ,(SELECT ROUND(((SUM(aqt.MARKS_OBTAINED)/sum(total_marks))*100),2)

-- FROM xxadm.xxadm_applcnt_qlfyxmmarks_tbl aqt,

-- xxadm.xxadm_subject_master_tbl smt

-- WHERE aqt.prereq_subject_id = smt.subject_id

-- AND aqt.applicant_id = adt.applicant_id

-- AND smt.subject_code IN ('MATHS','GS','SS')) group_percentage

FROM xxadm.xxadm_applicant_details_tbl adt,

xxadm.xxadm_applicant_coursprefs_tbl act,

xxadm.xxadm_college_master_tbl cmt,

xxadm.xxadm_course_master_tbl crmt,

xxadm.xxadm_medium_master_tbl mmt,

xxadm.xxadm_lov_master_tbl lmt_gender,

xxadm.xxadm_lov_master_tbl lmt_pass,

xxadm.xxadm_lov_master_tbl lmt_appearance,

xxadm.xxadm_lov_master_tbl lmt_religion,

xxadm.xxadm_lov_master_tbl lmt_education_type

WHERE adt.applicant_id = act.applicant_id

AND act.college_id = cmt.college_id

AND act.course_id = crmt.course_id

AND act.medium_id = mmt.medium_id

AND adt.applicant_gender = lmt_gender.lov_id

AND adt.pass_type = lmt_pass.lov_id

AND adt.appearance_type = lmt_appearance.lov_id

AND adt.religion = lmt_religion.lov_id

AND cmt.education_type = lmt_education_type.lov_id

AND adt.status = 'Active'

AND 1= (CASE WHEN act.hostel_required='Y'

THEN (CASE WHEN adt.distance_in_kms >20

AND lmt_religion.lov_code = 'HINDU'

AND adt.caste_category NOT IN

(SELECT category_id

FROM xxadm.xxadm_category_master_tbl

WHERE category_code IN ('BACKWRDC', 'BACKWRDE'))

THEN 1

ELSE 2 END)

ELSE

1 END)

AND 1= (CASE WHEN act.hostel_required ='Y'

THEN (CASE WHEN (lmt_education_type.lov_code='COEDUCOL' AND lmt_gender.lov_code='FEMALE')

THEN

2

ELSE

1 END )

ELSE

1 END) AND adt.course_applied_for = 'DEG' AND ( adt.college_status_flag IS NULL OR adt.college_status_flag IN ('N','T','C','B','O')) AND act.preference_order<=NVL((SELECT preference_order FROM xxadm.xxadm_applicant_coursprefs_tbl act1 WHERE act1.applicant_id=adt.applicant_id

AND status_flag IN('B','T','C','O')),act.preference_order) AND act.preference_order>=NVL((SELECT preference_order

FROM xxadm.xxadm_applicant_coursprefs_tbl act2 WHERE act2.applicant_id=adt.applicant_id

AND status_flag ='C'),act.preference_order)

AND act.preference_order NOT IN (SELECT act3.preference_order FROM xxadm.xxadm_applicant_coursprefs_tbl act3

WHERE act3.applicant_id = adt.applicant_id AND act3.status_flag ='O') AND act.preference_order NOT IN (SELECT act1.preference_order FROM xxadm.xxadm_applicant_coursprefs_tbl act1 WHERE act1.applicant_id = adt.applicant_id AND act1.status_flag IN ('C','B')

AND act1.attribute1 IN (SELECT category_id FROM xxadm.xxadm_category_master_tbl WHERE category_code IN ('OPENMERT')) AND NVL(act1.attribute7,'N')='N') AND cmt.college_id = :p_college_id

AND crmt.course_id = :p_course_id

AND mmt.medium_id = :p_medium_id

AND act.hostel_required = :p_hostel_required

ORDER BY

-- order_of_entry,

-- order_of_appearance,

-- order_of_status,

order_of_pass,

course_qe_priority,

percentage DESC,

-- group_percentage DESC,

applicant_dob,

legacy_appln_date) WHERE ROWNUM<=:p_seats) WHERE applicant_id=:p_applicant_id;

User_URPA8 · April 2020

This sql has complex login and so seeking your help.Please suggest

User_URPA8 · April 2020

Hi Experts,

Upto my understanding,the major elapsed time is taken by fetch operation.

Every fetch is visiting 4118 blocks to retrieve just 1 row(3469943694/842615).I need to understand which part of rowsource plan do i focus to reduce the blocks visited.

Many thanks

BPeaslandDBA · April 2020

Total cost of the SQL statement is 607. But this step:

TABLE ACCESS FULL XXABC_APPLICANT_COURSPREFS_TBL (cr=1635 pr=0 pw=0 time=5324 us cost=448 size=3861

that one contributes 448 to the overall cost. So I think this full table scan is causing the most performance pain. Make sure you have the appropriate indexes to help with the join to this table.

Cheers,

Brian

User_URPA8 · April 2020

This table already having a index

SQL> select INDEX_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name='XXABC_APPLICANT_COURSPREFS_TBL' and table_owner='XXABC';

INDEX_NAME |COLUMN_NAME |COLUMN_POSITION

------------------------------|----------------------------------------|---------------

XXABC_APPLCNT_PREFS_UK |APPLICANT_ID | 1

XXABC_APPLCNT_PREFS_UK |COLLEGE_ID | 2

XXABC_APPLCNT_PREFS_UK |COURSE_ID | 3

XXABC_APPLCNT_PREFS_UK |MEDIUM_ID | 4

XXABC_APPLCNT_PREFS_UK |HOSTEL_REQUIRED | 5

XXABC_APPLCNT_PREF_ORDER_UK |APPLICANT_ID | 1

XXABC_APPLCNT_PREF_ORDER_UK |PREFERENCE_ORDER | 2

7 rows selected.

SQL> select count(*) from XXABC.XXABC_APPLICANT_COURSPREFS_TBL;

COUNT(*)

----------

117302

JohnWatson2 · April 2020

Can you try

ALTER SESSION SET OPTIMIZER DYNAMIC SAMPLING=4;

and see what tkprof says then? I think that the optimizer is underestimating the number of rows coming out of some tables and therefore using indexes and loops when scans and hash joins might be better.

Jonathan Lewis · April 2020

The most significant feature of this output is that the query was executed 842,615 times.

The average execution time isn't a total disaster - though the plan shows some obvious inefficiencies - so the first strategy is probably is ASK if it would be feasible to find a way of executing the query much less frequently.

When trying to optimize, remember that the rowsource execution stats you get with the plan are just for the first execution that was dumped so (though the plan will stay the same) the execution work may vary significantly between executions.

The most resource intensive part of the query is the following:

TABLE ACCESS BY INDEX ROWID XXABC_APPLICANT_DETAILS_TBL (cr=11284 pr=0 pw=0 time=18471 us cost=1 size=80 card=1)

INDEX UNIQUE SCAN XXABC_APPLICANTS_PK (cr=10419 pr=0 pw=0 time=16014 us cost=0 size=0 card=1)(object id 2316264)

NESTED LOOPS (cr=4420 pr=0 pw=0 time=3972 us cost=3 size=26 card=1)

TABLE ACCESS BY INDEX ROWID XXABC_APPLICANT_COURSPREFS_TBL (cr=4416 pr=0 pw=0 time=3594 us cost=2 size=14 card=1)

INDEX UNIQUE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=2944 pr=0 pw=0 time=2140 us cost=1 size=0 card=1)(object id 2316268)

TABLE ACCESS BY INDEX ROWID XXABC_CATEGORY_MASTER_TBL (cr=3 pr=0 pw=0 time=6 us cost=1 size=12 card=1)

INDEX UNIQUE SCAN XXABC_CATEGORY_PK (cr=2 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 2313948)

INDEX UNIQUE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=2944 pr=0 pw=0 time=2140 us cost=1 size=0 card=1)(object id 2316268)

TABLE ACCESS BY INDEX ROWID XXABC_CATEGORY_MASTER_TBL (cr=3 pr=0 pw=0 time=6 us cost=1 size=12 card=1)

INDEX UNIQUE SCAN XXABC_CATEGORY_PK (cr=2 pr=0 pw=0 time=4 us cost=0 size=0 card=1)(object id 2313948)

TABLE ACCESS BY INDEX ROWID BATCHED XXABC_APPLICANT_COURSPREFS_TBL (cr=2371 pr=0 pw=0 time=4787 us cost=3 size=10 card=1)

INDEX RANGE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=1502 pr=0 pw=0 time=2613 us cost=2 size=0 card=4)(object id 2316268)

TABLE ACCESS BY INDEX ROWID BATCHED XXABC_APPLICANT_COURSPREFS_TBL (cr=2255 pr=0 pw=0 time=3760 us cost=3 size=10 card=1)

INDEX RANGE SCAN XXABC_APPLCNT_PREF_ORDER_UK (cr=1418 pr=0 pw=0 time=2105 us cost=2 size=0 card=4)(object id 2316268)

The "table access full" that precedes it is a red herring - ultimately this part of the query operates (a minimum of) 608 times reducing the 608 rows to 468 rows, and ultimately you get down to 465 rows, so there's no rearrangement of the basic join order that will reduce the data much faster before you run this messy bit. (Similarly the average and max show roughly 20% reduction due to this bit).

The plan corresponds to the string of NOT IN subqueries at the end of your query, and every single bit of the extract shows that Oracle is behaving very efficiently - the problem is the number of times all those NOT INs have to execute for each execution of the main query.

I've highlight a few numbers - the bold AND italic are the direct contributors of CR blocks to the 10,419 I've highlighted the same way in the "index unique scan". The bold but not italic are indirect contributors (the 2944 comes from a filter subquery to a subquery against an index access which is why it manages to appear twice and has an odd indent on the second occurrence)

You need to find a better way of expressing the messy condition - or need to precreate a single table that can be reference very efficiently just once to do all the tests.

Note, by the way that part of the "thousands of buffer gets to produce one row" is that you are first getting lots of rows then ordering them, then applying a ROWNUM < :N predicate which discards most of the rows you've found. Comparing the work down with the number of rows of ouput is not necessarily a meandingful test.

Regards

Jonathan Lewis

Jonathan Lewis · April 2020

I should have suggested you produce an execution plan that includes the predicate section so that we can get a better view of what Oracle has done to transform your query before optimising it. You have an SQL_ID in the tkprof output - try a call (with suitable SQL*Plus formatting) to:

select * from table(dbms_xplan.display_cursor('{your sql id}'));

and post the results here.

Regards

Jonathan Lewis

User_URPA8 · April 2020

My sincere thanks for helping me.

My bad,the plan is no longer in cursor.

SQL> select * from table(dbms_xplan.display_cursor('1n5q5gp58qzc9'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID: 1n5q5gp58qzc9, child number: 0 cannot be found

I think from AWR,we cannot get the predicate information.Anyways,team will run again within next 3 days and i will share it here.

Between,we have an index on the table XXABC_APPLICANT_COURSPREFS_TBL(as updated in previous updates), but it is not used and gone for full table scan.One more thing,Generally a unique scan should return a single row but the below operation is returning more than 1 row.Is this normal?

Rows (1st) Rows (avg) Rows (max) Row Source Operation

579 1382 2339 INDEX UNIQUE SCAN XXADM_APPLICANTS_PK (cr=10419 pr=0 pw=0 time=16014 us cost=0 size=0 card=1)(object id 2316264)

Thank you

Jonathan Lewis · April 2020

I'm trying to remember if the default value for child number in the display_cursor() is null or zero. You could give it another go and do an explicit null:

select * from table(dbms_xplan.display_cursor('1n5q5gp58qzc9',null));

Between,we have an index on the table XXABC_APPLICANT_COURSPREFS_TBL(as updated in previous updates), but it is not used and gone for full table scan

I've pointed out that the tablescan is not the significant part of the problem - but since the minimum access it 608 rows and the max is 2381 it might not be a good idea to use the index anyway. (The cardinality estimate for the tablscan is 143, with a cost of 448, which suggests that the index you've got is probably NOT a good candidate for the predicates that access the table - Oracle must think the index will identify far more than 143 table rows (at least 448) and require a lot of random I/Os to the table to discard the excess over 143).

As far as the "unique index / many rows" is concerned, I've pointed out:

the problem is the number of times all those NOT INs have to execute for each execution of the main query.

The line you've cited is the index to the 2nd table in a nested loop join. It's card is one (because it's unique), but the line will be executed 608 times because that's the number of rows coming from the first table. That's why the "Rows" figure is greater than one.

Regards

Jonathan Lewis

User_URPA8 · April 2020

Dear Jonathan

Thank you for the updates.

Unable to find the cursor in memory

SQL> select * from table(dbms_xplan.display_cursor('1n5q5gp58qzc9',null));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID: 1n5q5gp58qzc9 cannot be found

Jonathan Lewis · April 2020

3510875 wrote:Unable to find the cursor in memorySQL> select * from table(dbms_xplan.display_cursor('1n5q5gp58qzc9',null));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID: 1n5q5gp58qzc9 cannot be found

Okay,

Thanks for reporting back.

We'll just have to wait for the next run.

You could try using explain plan and dbms_xplan.display(), however you've got bind variables in the query so the plan might not be the same as the live plan. If it is though (as a guide check the plan_hash_value) the predicate section would be a good indicator of the live predicate usage - though not necessarily perfect.

Regards

Jonathan Lewis

User_URPA8 · April 2020

Dear Jonathan,

Thanks for the updates.Program is going to run in next couple of days and we will update the plan here.

User_URPA8 · April 2020

Dear Jonathan,

Overlooked your last update.I am sorry for that.

As requested,attached the explain plan output...one using select * from table(dbms_xplan.display); and other using SELECT * FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));

Eagerly waiting for your summary.

Thank you

Jonathan Lewis · April 2020

Thanks.

Unfortunately the 14 lines I extracted - which are the ones that really matter - show up as only 11 lines (20 - 30) in the plan produced by EXPLAIN PLAN, so either the stats have changed or a side effect of the bind variable inconsistency and lack of peeking for EXPLAIN PLAN mean we can't draw any sound conclusions about what's happening with the run-time plan.

Regards

Jonathan Lewis

User_URPA8 · April 2020

Dear Jonathan,

Thanks for the update.In our test instance,where the sql is running almost same time as in prod,we have enabled statistics=all and pulled the runtime statistics.

Attached for your reference.Actually this program will take 2 days to complete,once this is completed,i can provide the tkprof also.For now,i have attached the runtime statistics for last run of the same sql.Can you please provide your thoughts.

User_URPA8 · April 2020

Unable to attach...We have formatted to a good looking one.

SQL> sELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'1n5q5gp58qzc9',format=>'ALLSTATS LAST +COST +BYTES +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 1n5q5gp58qzc9, child number 0

-------------------------------------

SELECT COUNT(applicant_id) FROM (SELECT applicant_id,

academic_year, applicant_gender,

medium_of_study, education_type,

college_id, course_id,

medium_id, hostel_required,

preference_order, status_flag,

attribute7, -- Added on 7-mar-20

college_status_flag, percentage, --

group_percentage, caste_category,

alloted_category, NULL allotment_type

FROM ( SELECT adt.applicant_id,

lmt_gender.lov_code applicant_gender,

adt.medium_of_study, act.college_id,

lmt_education_type.lov_code education_type,

Plan hash value: 2129674217

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | | 470 (100)| 1 |00:00:00.01 | 3196 | | | |

| 1 | SORT AGGREGATE | | 1 | 1 | 13 | | 1 |00:00:00.01 | 3196 | | | |

|* 2 | VIEW | | 1 | 1 | 13 | 470 (1)| 0 |00:00:00.01 | 3196 | | | |

|* 3 | COUNT STOPKEY | | 1 | | | | 6 |00:00:00.01 | 3196 | | | |

| 4 | VIEW | | 1 | 1 | 13 | 470 (1)| 6 |00:00:00.01 | 3196 | | | |

|* 5 | SORT ORDER BY STOPKEY | | 1 | 1 | 188 | 470 (1)| 6 |00:00:00.01 | 3196 | 2048 | 2048 | 2048 (0)|

|* 6 | FILTER | | 1 | | | | 36 |00:00:00.01 | 3196 | | | |

| 7 | NESTED LOOPS ANTI | | 1 | 1 | 188 | 460 (1)| 36 |00:00:00.01 | 3196 | | | |

| 8 | NESTED LOOPS | | 1 | 1 | 178 | 459 (1)| 36 |00:00:00.01 | 3122 | | | |

| 9 | NESTED LOOPS | | 1 | 1 | 165 | 458 (1)| 36 |00:00:00.01 | 3117 | | | |

| 10 | NESTED LOOPS | | 1 | 1 | 152 | 457 (1)| 36 |00:00:00.01 | 3077 | | | |

| 11 | NESTED LOOPS | | 1 | 1 | 139 | 456 (1)| 36 |00:00:00.01 | 3073 | | | |

| 12 | NESTED LOOPS | | 1 | 1 | 126 | 455 (1)| 36 |00:00:00.01 | 3033 | | | |

| 13 | NESTED LOOPS | | 1 | 212 | 9752 | 243 (2)| 210 |00:00:00.01 | 883 | | | |

| 14 | NESTED LOOPS | | 1 | 1 | 19 | 2 (0)| 1 |00:00:00.01 | 4 | | | |

| 15 | TABLE ACCESS BY INDEX ROWID | XXADM_COLLEGE_MASTER_TBL | 1 | 1 | 6 | 1 (0)| 1 |00:00:00.01 | 2 | | | |

|* 16 | INDEX UNIQUE SCAN | XXADM_COLLEGES_PK | 1 | 1 | | 0 (0)| 1 |00:00:00.01 | 1 | | | |

| 17 | TABLE ACCESS BY INDEX ROWID | XXADM_LOV_MASTER_TBL | 1 | 1 | 13 | 1 (0)| 1 |00:00:00.01 | 2 | | | |

|* 18 | INDEX UNIQUE SCAN | XXADM_LOVS_PK | 1 | 1 | | 0 (0)| 1 |00:00:00.01 | 1 | | | |

|* 19 | TABLE ACCESS FULL | XXADM_APPLICANT_COURSPREFS_TBL | 1 | 212 | 5724 | 241 (2)| 210 |00:00:00.01 | 879 | | | |

|* 20 | TABLE ACCESS BY INDEX ROWID | XXADM_APPLICANT_DETAILS_TBL | 210 | 1 | 80 | 1 (0)| 36 |00:00:00.01 | 2150 | | | |

|* 21 | INDEX UNIQUE SCAN | XXADM_APPLICANTS_PK | 210 | 1 | | 0 (0)| 177 |00:00:00.01 | 1981 | | | |

| 22 | NESTED LOOPS | | 210 | 1 | 26 | 3 (0)| 0 |00:00:00.01 | 630 | | | |

|* 23 | TABLE ACCESS BY INDEX ROWID | XXADM_APPLICANT_COURSPREFS_TBL | 210 | 1 | 14 | 2 (0)| 0 |00:00:00.01 | 630 | | | |

|* 24 | INDEX UNIQUE SCAN | XXADM_APPLCNT_PREF_ORDER_UK | 210 | 1 | | 1 (0)| 210 |00:00:00.01 | 420 | | | |

|* 25 | TABLE ACCESS BY INDEX ROWID | XXADM_CATEGORY_MASTER_TBL | 0 | 1 | 12 | 1 (0)| 0 |00:00:00.01 | 0 | | | |

|* 26 | INDEX UNIQUE SCAN | XXADM_CATEGORY_PK | 0 | 1 | | 0 (0)| 0 |00:00:00.01 | 0 | | | |

|* 27 | TABLE ACCESS BY INDEX ROWID BATCHED| XXADM_APPLICANT_COURSPREFS_TBL | 210 | 1 | 10 | 3 (0)| 58 |00:00:00.01 | 615 | | | |

|* 28 | INDEX RANGE SCAN | XXADM_APPLCNT_PREFS_UK | 210 | 5 | | 2 (0)| 2038 |00:00:00.01 | 220 | | | |

|* 29 | TABLE ACCESS BY INDEX ROWID BATCHED| XXADM_APPLICANT_COURSPREFS_TBL | 179 | 1 | 10 | 3 (0)| 3 |00:00:00.01 | 528 | | | |

|* 30 | INDEX RANGE SCAN | XXADM_APPLCNT_PREFS_UK | 179 | 5 | | 2 (0)| 1756 |00:00:00.01 | 188 | | | |

| 31 | TABLE ACCESS BY INDEX ROWID | XXADM_LOV_MASTER_TBL | 36 | 1 | 13 | 1 (0)| 36 |00:00:00.01 | 40 | | | |

|* 32 | INDEX UNIQUE SCAN | XXADM_LOVS_PK | 36 | 1 | | 0 (0)| 36 |00:00:00.01 | 4 | | | |

|* 33 | INDEX UNIQUE SCAN | XXADM_LOVS_PK | 36 | 1 | | 0 (0)| 36 |00:00:00.01 | 4 | | | |

| 34 | TABLE ACCESS BY INDEX ROWID | XXADM_LOV_MASTER_TBL | 36 | 1 | 13 | 1 (0)| 36 |00:00:00.01 | 40 | | | |

|* 35 | INDEX UNIQUE SCAN | XXADM_LOVS_PK | 36 | 1 | | 0 (0)| 36 |00:00:00.01 | 4 | | | |

|* 36 | TABLE ACCESS BY INDEX ROWID | XXADM_LOV_MASTER_TBL | 36 | 1 | 13 | 1 (0)| 36 |00:00:00.01 | 5 | | | |

|* 37 | INDEX UNIQUE SCAN | XXADM_LOVS_PK | 36 | 1 | | 0 (0)| 36 |00:00:00.01 | 4 | | | |

|* 38 | TABLE ACCESS BY INDEX ROWID | XXADM_APPLICANT_COURSPREFS_TBL | 36 | 1 | 10 | 1 (0)| 0 |00:00:00.01 | 74 | | | |

|* 39 | INDEX UNIQUE SCAN | XXADM_APPLCNT_PREF_ORDER_UK | 36 | 1 | | 0 (0)| 36 |00:00:00.01 | 38 | | | |

|* 40 | VIEW | index$_join$_014 | 0 | 1 | 12 | 0 (0)| 0 |00:00:00.01 | 0 | | | |

|* 41 | HASH JOIN | | 0 | | | | 0 |00:00:00.01 | 0 | 1888K| 1888K| |

|* 42 | INDEX RANGE SCAN | XXADM_CATEGORY_PK | 0 | 1 | 12 | 0 (0)| 0 |00:00:00.01 | 0 | | | |

| 43 | INLIST ITERATOR | | 0 | | | | 0 |00:00:00.01 | 0 | | | |

|* 44 | INDEX UNIQUE SCAN | XXADM_CATEGORY_CODE_UK | 0 | 1 | 12 | 0 (0)| 0 |00:00:00.01 | 0 | | | |

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):

--------------------------------------

1 - :1 (NUMBER): 9

2 - :2 (NUMBER): 10

3 - (NUMBER): 17

4 - (VARCHAR2(30), CSID=873): 'N'

5 - (NUMBER): 6

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("APPLICANT_ID"=:P_APPLICANT_ID)

3 - filter(ROWNUM<=:P_SEATS)

5 - filter(ROWNUM<=:P_SEATS)

6 - filter(CASE "ACT"."HOSTEL_REQUIRED" WHEN 'Y' THEN CASE WHEN ("ADT"."DISTANCE_IN_KMS">20 AND "LMT_RELIGION"."LOV_CODE"='HINDU' AND IS NULL) THEN 1 ELSE 2 END ELSE 1 END =1)

16 - access("CMT"."COLLEGE_ID"=:P_COLLEGE_ID)

18 - access("CMT"."EDUCATION_TYPE"="LMT_EDUCATION_TYPE"."LOV_ID")

19 - filter(("ACT"."COURSE_ID"=:P_COURSE_ID AND "ACT"."COLLEGE_ID"=:P_COLLEGE_ID AND "ACT"."MEDIUM_ID"=:P_MEDIUM_ID AND "ACT"."HOSTEL_REQUIRED"=:P_HOSTEL_REQUIRED))

20 - filter(("ADT"."STATUS"='Active' AND (INTERNAL_FUNCTION("ADT"."COLLEGE_STATUS_FLAG") OR "ADT"."COLLEGE_STATUS_FLAG" IS NULL) AND "ADT"."COURSE_APPLIED_FOR"='DEG'))

21 - access("ADT"."APPLICANT_ID"="ACT"."APPLICANT_ID")

filter(( IS NULL AND "ACT"."PREFERENCE_ORDER"<=NVL(,"ACT"."PREFERENCE_ORDER") AND "ACT"."PREFERENCE_ORDER">=NVL(,"ACT"."PREFERENCE_ORDER")))

23 - filter((INTERNAL_FUNCTION("ACT1"."STATUS_FLAG") AND NVL("ACT1"."ATTRIBUTE7",'N')='N'))

24 - access("ACT1"."APPLICANT_ID"=:B1 AND "ACT1"."PREFERENCE_ORDER"=:B2)

25 - filter("CATEGORY_CODE"='OPENMERT')

26 - access("CATEGORY_ID"=TO_NUMBER("ACT1"."ATTRIBUTE1"))

27 - filter(("STATUS_FLAG"='B' OR "STATUS_FLAG"='C' OR "STATUS_FLAG"='O' OR "STATUS_FLAG"='T'))

28 - access("ACT1"."APPLICANT_ID"=:B1)

29 - filter("STATUS_FLAG"='C')

30 - access("ACT2"."APPLICANT_ID"=:B1)

32 - access("ADT"."RELIGION"="LMT_RELIGION"."LOV_ID")

33 - access("ADT"."APPEARANCE_TYPE"="LMT_APPEARANCE"."LOV_ID")

35 - access("ADT"."PASS_TYPE"="LMT_PASS"."LOV_ID")

36 - filter(CASE "ACT"."HOSTEL_REQUIRED" WHEN 'Y' THEN CASE WHEN ("LMT_EDUCATION_TYPE"."LOV_CODE"='COEDUCOL' AND "LMT_GENDER"."LOV_CODE"='FEMALE') THEN 2 ELSE 1 END ELSE 1 END

=1)

37 - access("ADT"."APPLICANT_GENDER"="LMT_GENDER"."LOV_ID")

38 - filter("ACT3"."STATUS_FLAG"='O')

39 - access("ACT3"."APPLICANT_ID"="ADT"."APPLICANT_ID" AND "ACT"."PREFERENCE_ORDER"="ACT3"."PREFERENCE_ORDER")

40 - filter(("CATEGORY_ID"=:B1 AND INTERNAL_FUNCTION("CATEGORY_CODE")))

41 - access(ROWID=ROWID)

42 - access("CATEGORY_ID"=:B1)

44 - access(("CATEGORY_CODE"='BACKWRDC' OR "CATEGORY_CODE"='BACKWRDE'))

Note

-----

- dynamic statistics used: dynamic sampling (level=2)

- statistics feedback used for this statement

- this is an adaptive plan

- 4 Sql Plan Directives used for this statement

119 rows selected.

SQL>

User_URPA8 · April 2020

Dear Jonathan,

Really,not sure why i am unable to attached the formatted plan to this thread,it is really tough to read with unformatted text.Hence,i created a new thread and attached the plan to it.

https://community.oracle.com/message/15595458#15595458

Thank you

Jonathan Lewis · April 2020

We still have the same problem.

The plan from your test instance is 44 lines long, the plan from the prod instance (omitting the SELECT line at the top) was 47 lines; so Oracle was doing something different in production. Again the 3 lines difference appears in the critical bit of the plan. (Is test on exactly the same version of Oracle with exactly the same set of patches ? - are the object definitions exactly the same, in particular are there any NOT NULL columns in test that aren't declared NOT NULL in prod, or any column constraints in test that don't exist in prod ?)

The other thing about test and prod that's a big unknown is the effect of feedback - test is reporting dynamic sampling, statistics feedback and plan directives - so you can't be certain about why it chose this plan.

However, the critical section still gives us some ideas. I've extracted a critical few lines from the plan and trimmed out redundant columns so that I can highlight one option:

------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

|* 20 | TABLE ACCESS BY INDEX ROWID | XXADM_APPLICANT_DETAILS_TBL | 210 | 36 | 2150 |

|* 21 | INDEX UNIQUE SCAN | XXADM_APPLICANTS_PK | 210 | 177 | 1981 |

| 22 | NESTED LOOPS | | 210 | 0 | 630 |

|* 23 | TABLE ACCESS BY INDEX ROWID | XXADM_APPLICANT_COURSPREFS_TBL | 210 | 0 | 630 |

|* 24 | INDEX UNIQUE SCAN | XXADM_APPLCNT_PREF_ORDER_UK | 210 | 210 | 420 |

|* 25 | TABLE ACCESS BY INDEX ROWID | XXADM_CATEGORY_MASTER_TBL | 0 | 0 | 0 |

|* 26 | INDEX UNIQUE SCAN | XXADM_CATEGORY_PK | 0 | 0 | 0 |

|* 27 | TABLE ACCESS BY INDEX ROWID BATCHED| XXADM_APPLICANT_COURSPREFS_TBL | 210 | 58 | 615 |

|* 28 | INDEX RANGE SCAN | XXADM_APPLCNT_PREFS_UK | 210 | 2038 | 220 |

|* 29 | TABLE ACCESS BY INDEX ROWID BATCHED| XXADM_APPLICANT_COURSPREFS_TBL | 179 | 3 | 528 |

|* 30 | INDEX RANGE SCAN | XXADM_APPLCNT_PREFS_UK | 179 | 1756 | 188 |

------------------------------------------------------------------------------------------------------------------------

20 - filter(("ADT"."STATUS"='Active' AND (INTERNAL_FUNCTION("ADT"."COLLEGE_STATUS_FLAG")

OR "ADT"."COLLEGE_STATUS_FLAG" IS NULL) AND "ADT"."COURSE_APPLIED_FOR"='DEG'))

21 - access("ADT"."APPLICANT_ID"="ACT"."APPLICANT_ID")

filter(( IS NULL AND "ACT"."PREFERENCE_ORDER"<=NVL(,"ACT"."PREFERENCE_ORDER")

AND "ACT"."PREFERENCE_ORDER">=NVL(,"ACT"."PREFERENCE_ORDER")))

23 - filter((INTERNAL_FUNCTION("ACT1"."STATUS_FLAG") AND NVL("ACT1"."ATTRIBUTE7",'N')='N'))

24 - access("ACT1"."APPLICANT_ID"=:B1 AND "ACT1"."PREFERENCE_ORDER"=:B2)

25 - filter("CATEGORY_CODE"='OPENMERT')

26 - access("CATEGORY_ID"=TO_NUMBER("ACT1"."ATTRIBUTE1"))

27 - filter(("STATUS_FLAG"='B' OR "STATUS_FLAG"='C' OR "STATUS_FLAG"='O' OR "STATUS_FLAG"='T'))

28 - access("ACT1"."APPLICANT_ID"=:B1)

29 - filter("STATUS_FLAG"='C')

30 - access("ACT2"."APPLICANT_ID"=:B1)

A very large fraction of your time spent was CPU time, which often correlates very closely with buffer gets, but could be due to the processing of rows and columns rather than buffers. This may be the case for at least part of your code.

If you look at operation 28 you start 210 times and acquire 2,038 rowids accessing 220 buffers. Using those rowids you then end up visiting the table 2,038 times, getting a further 395 buffers, to discard all but 48 rows. Similarly at operation 30 you start 179 times, acquire 1,756 rowids from 188 buffers, then visit the table getting a further 340 buffers and discarding all but 3 rows.

If you look at the table filter predicates (operations 27 and 29) you're discarding a huge fraction of the rows based on the status_flag. This suggests two options:

a) add the status_flag to the index you're using and avoid visiting the table thousands of times.

b) create a new index on (applicant_id, status_flag) to make the access as efficient as possible.

A refinement on (b) is to create two virtual columns and an index on those columns so that the index references only those rows that match you values of status flag you're interested in, and the modify the query to reference these virtual columns. (Let me know if you want me to expand on this idea if you're allowed to do something like that to the application). (The point of the extra complexity is that the new index would - on the basis of the stats from this run - be 1/40th of the size the simple 2-column index would have to be)

Regards

Jonathan Lewis

17

User_URPA8 · April 2020

Dear Jonathan,

You are absolutely right.We observed optimizer adaptive features parameter is true in test where as in prod,it is set to false.

Many thanks for the updates.We will go with first option as we dont have feasibility of changing the code.

Existing index and its definition:

CREATE UNIQUE INDEX "XXADM"."XXADM_APPLCNT_PREFS_UK" ON "

XXADM"."XXADM_APPLICANT_COURSPREFS_TBL"

("APPLICANT_ID", "COLLEGE_ID", "COURSE_ID", "MEDIUM_ID", "HOSTEL_REQUIRED")

SQL> select INDEX_NAME,COLUMN_NAME,COLUMN_POSITION,TABLE_NAME from dba_ind_columns where TABLE_NAME='XXADM_APPLICANT_COURSPREFS_TBL' and TABLE_OWNER='XXADM'

INDEX_NAME COLUMN_NAME COLUMN_POSITION TABLE_NAME

---------------------------------------- -------------------- --------------- ------------------------------

XXADM_APPLCNT_PREFS_UK APPLICANT_ID 1 XXADM_APPLICANT_COURSPREFS_TBL

XXADM_APPLCNT_PREFS_UK COLLEGE_ID 2 XXADM_APPLICANT_COURSPREFS_TBL

XXADM_APPLCNT_PREFS_UK COURSE_ID 3 XXADM_APPLICANT_COURSPREFS_TBL

XXADM_APPLCNT_PREFS_UK MEDIUM_ID 4 XXADM_APPLICANT_COURSPREFS_TBL

XXADM_APPLCNT_PREFS_UK HOSTEL_REQUIRED 5 XXADM_APPLICANT_COURSPREFS_TBL

XXADM_APPLCNT_PREF_ORDER_UK APPLICANT_ID 1 XXADM_APPLICANT_COURSPREFS_TBL

XXADM_APPLCNT_PREF_ORDER_UK PREFERENCE_ORDER 2 XXADM_APPLICANT_COURSPREFS_TBL

We will create the index with status_flag in column_postion 6 as below and we will test the performance.

CREATE UNIQUE INDEX "XXADM"."XXADM_APPLCNT_PREFS_UK" ON "

XXADM"."XXADM_APPLICANT_COURSPREFS_TBL"

("APPLICANT_ID", "COLLEGE_ID", "COURSE_ID", "MEDIUM_ID", "HOSTEL_REQUIRED","STATUS_FLAG");

Thanks again.

User_URPA8 · April 2020

Dear Jonathan

Want to ask one question like what made you to focus on steps 28 and 30.Just wanted to know as we have other 3 similar queries which needs to be tuned.It will be useful to further tune our queries.

The reason to ask this question was most of the team members who sees this plan are focussing on full table scan.They says,total cost of the statement is 607 and that full table scan alone took a cost of 448.(TABLE ACCESS FULL XXABC_APPLICANT_COURSPREFS_TBL (cr=1635 pr=0 pw=0 time=5324 us cost=448 size=3861) and says this is the problem.

Thanks a lot

Jonathan Lewis · April 2020

You can't just add a column to the current 5 and leave the index UNIQUE or you could get duplicates of the first 5. If you're going to add the column at position 6 then you need to create the index as non-unique but have a unique constraint on the table.

If you generally have multiple rows for each applicant (and the stats on the plan you provided suggested that you have several rows per applicant_id) then you can create the index with the option "compress 1" to save a little space. On the other hand, since you've only got two indexes on this table I'd consider adding a whole new index on just "(applicant_id, status) compress 1" (you could also rebuild both the other indexes with compress 1, of course).

Regards

Jonathan Lewis

Jonathan Lewis · April 2020

Remember that I picked operations 27-30 because (a) they were common to the two plans and I didn't want to make any comment about what was happening in the bit of the plan that changed between databases and (b) they showed an obvious easy example of things you might consider.

I picked the entire block from 20-30 and discounted the full tablescan because the initial plan you posted (a) reported a time of 33,379 microseconds and the full tablescan reported only 5,324 microseconds so most of the was in something other than the tablescan and (b) the tablescan reported between 608 and 2,381 rows returned so wasn't likely to get much faster regardless of the path used to get the rows and (c) that block of lines showed 4 places where the number of index entries acquired was large but the number of surviving table rows was very small by comparison, indicating a lot of wasted work.

Regards

Jonathan Lewis

User_URPA8 · April 2020

Dear Jonathan,

Thanks for the update.I searched for COMPRESS option and this is really wonderful.As suggsted,We are going to create a brand new index on applicant_id and status_flag.Because we are not getting proper support from developer and the one who wrote this code is not available now,I would like to ask one more question.Is this index need to be unique or nonunique?

CREATE INDEX "XXADM"."XXADM_APPLCNT_APPLICANT_STATUS" ON "XXADM"."XXADM_APPLICANT_COURSPREFS_TBL"("APPLICANT_ID","STATUS_FLAG") COMPRESS 1;

Thank you

Forum Stats

Howdy, Stranger!

Quick Links

Categories

tkprof Analysis

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Howdy, Stranger!

Categories

tkprof Analysis

Best Answer

Answers