Thanks for the question, Frank.

Asked: September 08, 2004 - 3:47 pm UTC

Last updated: November 12, 2005 - 4:30 pm UTC

Version: 9.2.0.1.0

Viewed 1000+ times

You Asked

Hi Tom,

We had a batch job written in perl. It runs in every 10 minutes. Following is the TKPROF output of one of the traces. Could you help me understand what are the possibilities causing so many execution and parsing due to one select statement using bind variables in perl? The current symptom of the problem is the job seems hang for long period of time randomly. Normally, the job only took less than 7 minutes to finish. Just recently, we noticed the job could took 7 to 16 hours.

I will really appreciate it if you could shed some lights.

By the way, I really enjoy your books and website. I learnt a lot from them.

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

TKPROF: Release 9.2.0.1.0 - Production on Tue Sep 7 15:33:53 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: peps_ora_29643.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7381 0.80 0.58 0 0 0 0
Execute 7381 0.50 0.46 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14762 1.30 1.04 0 0 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 13

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 7381 0.00 0.01
SQL*Net message from client 7381 0.27 12.08
********************************************************************************

SELECT
mi.MEDIA_TYPE_ID, mi.SYSTEM_NAME,
mi.PARTITION_KEY, mi.IMAGE_HEIGHT,
mi.IMAGE_WIDTH, mi.UPLOAD_METHOD,
mi.EXPIRATION_DATE, mi.CREATION_DATE,
mi.LAST_MODIFIED_DATE, mi.SERVER_NAME,
mi.DIRECTORY_NAME, mi.BASE_FILE_NAME,
mi.FILE_SIZE, mi.MOUNT_POINT
FROM msa_image_meta mi
WHERE mi.SERVER_NAME=:1
AND mi.MOUNT_POINT=:2
AND mi.SYSTEM_NAME=:3
AND mi.DIRECTORY_NAME=:4
AND mi.BASE_FILE_NAME=:5

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7381 0.40 0.53 0 0 0 0
Execute 7380 5.49 6.08 0 0 0 0
Fetch 7380 3.87 4.01 0 243998 0 21095
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 22141 9.76 10.62 0 243998 0 21095

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 13

Rows Row Source Operation
------- ---------------------------------------------------
3 PARTITION RANGE ALL PARTITION: 1 7 (cr=33 r=0 w=0 time=495 us)
3 TABLE ACCESS BY LOCAL INDEX ROWID MSA_IMAGE_META PARTITION: 1 7 (cr=33 r=0 w=0 time=429 us)
3 INDEX RANGE SCAN MSA_IMAGE_META_SMSDBMP_PK PARTITION: 1 7 (cr=30 r=0 w=0 time=369 us)(object id 5875)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 14760 0.00 0.02
SQL*Net message from client 14760 1.30 90.72

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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 14762 1.20 1.11 0 0 0 0
Execute 14761 5.99 6.54 0 0 0 0
Fetch 7380 3.87 4.01 0 243998 0 21095
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 36903 11.06 11.67 0 243998 0 21095

Misses in library cache during parse: 2

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 22141 0.00 0.03
SQL*Net message from client 22141 1.30 102.80

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

14762 user SQL statements in session.
0 internal SQL statements in session.
14762 SQL statements in session.
********************************************************************************
Trace file: peps_ora_29643.trc
Trace file compatibility: 9.00.01
Sort options: default

1 session in tracefile.
14762 user SQL statements in trace file.
0 internal SQL statements in trace file.
14762 SQL statements in trace file.
2 unique SQL statements in trace file.
295249 lines in trace file.

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

and Tom said...

that just means your perl program parsed and executed it that many times.

you have a loop or something in which you are doing something like:

for x in ( select * from t1 )
loop
for y in ( select * from t2 where t2.fk = t1.pk )
loop
process x/y

Your code would be INFINITELY more efficient if it just

for x in ( select * from t1, t2 where t2.fk = t1.pk )
loop

and let the database do the join. This looks on the face of it like a classic "we tried to out guess the database and do our own nested loops join"

There is no magic to "fix this", it requires a change in the underlying application code/algorithm

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How?

A reader, September 09, 2004 - 11:20 am UTC

Dont get it, how did you arrive at your response given his symptoms? All I see is a high parse=execute count which means that the app doesnt use bv's.

How did you get from that to the fact they might be trying to "outsmart" the database by doing their own NL joins?

Thanks

September 09, 2004 - 12:17 pm UTC

SELECT
mi.MEDIA_TYPE_ID, mi.SYSTEM_NAME,
mi.PARTITION_KEY, mi.IMAGE_HEIGHT,
mi.IMAGE_WIDTH, mi.UPLOAD_METHOD,
mi.EXPIRATION_DATE, mi.CREATION_DATE,
mi.LAST_MODIFIED_DATE, mi.SERVER_NAME,
mi.DIRECTORY_NAME, mi.BASE_FILE_NAME,
mi.FILE_SIZE, mi.MOUNT_POINT
FROM msa_image_meta mi
WHERE mi.SERVER_NAME=:1
AND mi.MOUNT_POINT=:2
AND mi.SYSTEM_NAME=:3
AND mi.DIRECTORY_NAME=:4
AND mi.BASE_FILE_NAME=:5

that shows it does in fact use binds -- :1, :2, :3....

This:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7381 0.40 0.53 0 0 0 0
Execute 7380 5.49 6.08 0 0 0 0
Fetch 7380 3.87 4.01 0 243998 0 21095
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 22141 9.76 10.62 0 243998 0 21095

shows the query was parsed 7,381 times -- it should have been done ONCE (so it does a bad there there.)

But given the simplicity of the query -- select .... from t where <key>=<value>, it looks like the classic "run a query to get keys, run another query with those keys as inputs"

(just *guessing* here)

change to perl code will help

Tony, September 09, 2004 - 1:29 pm UTC

There is a parameter in PERL, ora_check_sql, "whose default value of 1 motivates two parse calls per PERL prepare function call."

I would try to deactivate this by setting the value to 0 and see if your performance improves.

More on ora_check_sql

Tony, September 09, 2004 - 1:37 pm UTC

Sorry, I meant to include a small code snipet of this change

my $sth = $dbh->prepare ('select....',{ora_check_sql=>0});

or

my $sth = $dbh->prepare (<<'END OF SQL',{ora_check_sql=>0});
select....
from...
where...
END OF SQL

I hope that this helps you.

Good guess ...

Frank, September 09, 2004 - 1:39 pm UTC

Thanks Tom!

We do this in a bit SELECT to collect all primary key fields for another table, then insert them all into another table. What puzzled me is why oracle parse it and execute it so many times event it's the soft parse?

Thanks for your time!

September 09, 2004 - 2:15 pm UTC

Oracle didn't parse it - you did.

Oracle only does what it is told to do! Does perl have a "prepared statement" equivalent? like jdbc?

The execute is you executing it..... again, only doing what you told us to do.

However, this should just be:

insert into target_table
select ...
from primary_key_table, this_table
where join_condition;

a single statement, no procedural code whatsoever.

Thanks ...

Frank, September 09, 2004 - 2:37 pm UTC

Thanks a lot, Tom!

Question

PRS, September 09, 2004 - 4:04 pm UTC

I have standby database SID name "CRMSYSP" same as primary database. But the global database name "CRMDSS" which is different from SID. Which is OK for standby database. Also standby database has GLOBAL_NAMES=TRUE same as primary database. Standby database is open for read only mode for reports to run.
Now I have another database "EISQA" which has GLOBAL_NAMES=FALSE and I created a DB link pointing to CRMDSS in EISQA database with valid userid and password. 
I can select a table by using db link in EISQA databse.

Example 
EISQA@SQL> select * from sysadm.wsi_cti_vdn@CRMDSS

   This does return me rows. But as soon as I create a procedure in EISQA database which makes use of this SELECT statement, Procedure does not compile and it gives me following error.

PL/SQL: ORA-04063: table "SYSADM.WSI_CTI_VDN" has errors

   I am just puzzled when I can SELECT using the DB link, why does it not allow in procedure or package?

   Any reponse is appreciated.

Thanks,

September 09, 2004 - 4:19 pm UTC

what happens if you create a view of the remote table and use that?

(i'd really need an example -- datatypes and all)

My Query

Sulagna Mohanty, November 12, 2005 - 1:41 pm UTC

if i wanna fetch the entire table then select* wud be faster or select f1 f2 wud be faster? I'm into ABAP. I wanna know the answer in sql.Plz help me out.
Thanx & Regards,
Sulagna

November 12, 2005 - 4:30 pm UTC

wud?

selecting just the columns you need is the best practice - both for performance as well as maintainability.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

How?

change to perl code will help

More on ora_check_sql

Good guess ...

Thanks ...

Question

My Query

More to Explore

PL/SQL demos

PL/SQL docs

select statement with large execute count?!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

How?

change to perl code will help

More on ora_check_sql

Good guess ...

Thanks ...

Question

My Query

More to Explore

PL/SQL demos

PL/SQL docs