Results from TKPROF - how to tune CURSOR query

[slicendice]

Hi
I'm currently doing a data migration (via PL/SQL) and for each client that is migrated, I have to call a function in a pre-written package (not written by me).
The migration has been painfully slow running, which I've been puzzled by because it's not a particularly arduous migration.
With the help of a DBA, I've run TKPROF whilst running a test migration, and it seems that there is one statement that is doing all the damage and it's in the function that I have to call from the pre-written package. The small test we've just run took about 5 and a half minutes to run, and it seems that most of that time was taken up by this one statement. The results from TKPROF for this function are as follows:

SELECT H.ROWID RWID,H.*
FROM
PARIS.MPI_CH_VAC_HIST H WHERE (VID_ENTRY_ID = :b1 OR b2 IS NULL OR :b2 =
0 ))ORDER BY VID_DATE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 86 0.02 0.01 0 0 0 0
Fetch 1037 56.99 256.73 208467 342712 688 951
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1124 57.02 256.74 208467 342712 688 951

The statement that it highlights above is actually the query from a cursor defined in the function that I am calling. The section from that function is as follows:

Function FNC_CH_CREATEVACCBASEHIST(pEntryId In Integer) Return Number Is
Pragma Autonomous_Transaction;

Cursor c_VaccHistList(iEntryId Number) Is
Select h.Rowid As rwid , h.*
From paris.mpi_ch_vac_hist h
Where (vid_entry_id = iEntryId Or (pEntryId Is Null Or pEntryId=0))
Order By vid_date;

To me, the query doesn't look particularly heavy. The only things I'm wondering about are:
1) is the OR condition of the WHERE clause doing the damage?
2) the ORDER BY field (vid_date) is not indexed. Would that cause it to have such an impact on performance?

Sorry if this is a bit long-winded! Any help or suggestions would be very gratefully received...

Thanks very much

 

[slicendice]

I just noticed something the second I posted this! In the function definition the parameter passed in is called pEntryID. In the cursor definition, it takes a parameter iEntryID.
When the cursor is called within the function body, it is pEntryID that is passed as the cursor parameter, i.e.

For v_VaccHistRec In c_VaccHistList(pEntryId) Loop

However, I just noticed that in the cursor definition, the WHERE clause uses both variables (pEntry and iEntry), i.e.

Where (vid_entry_id = iEntryId Or (pEntryId Is Null Or pEntryId=0))

Would this cause a problem? Might it slow things down?

(Or am I grasping at straws?!?)

 

[taupirho]

Dont know if this will work, but it may be the ORs that are causing a problem. In a previous life I worked with the INGRES database and disjunctive normal forms in the where clause i.e x OR Y really slowed it down and needed to be changed to conjunctive normal form i.e NOT(NOT x AND NOT Y)

Worth a try anyway

 

[slicendice]

Thanks taupirho...

Yes it was the OR condition - after speaking with the developer who wrote the code last night, we were able to remove the OR clause completely. We did a before and after test (i.e. with and without the OR clause) and the TKPROF stats went from over 66 seconds to 0.01 seconds!! Quite a result...!