Tkprof

[Subha]

Pls give your suggestions ..

select s.job_key 
  from jobs s 
 where s.JOB_key = :V001 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    6666      1.01       0.99          0         41          0           0
Execute  6666      9.85      10.22          0          0          0           0
Fetch    6666      0.81       0.87          4      33332          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    19998     11.68      12.09         4      33373          0           0

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  INDEX UNIQUE SCAN JOBS_KEY_IND (cr=2 r=2 w=0 time=3667 us)(object id 23179)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   49994        0.00          1.30
  SQL*Net message from client                 49994        0.12        133.67
  db file sequential read                         4        0.00          0.00
  latch free                                      7        0.00          0.00

[gandolf989]

My opinion is that you should always format column data, Its hard for someone else to know why 'SQL*Net message %% client' waits are so high, and a unique scan for a single row should be quick but any number of other things could cause the query to take longer to return.

[davey23uk]

parse once - execute many

[pando]

use bulk processing

[Bore]

My suggestion is that this statement works fine.
Maybe you could be so kind to tell us what exactly your concerns are, so that we do not have to presume that.
Presuming that you are shocked by the number of parses, to be the same as the number of executions, I would suggest to read metalink Note:32895.1
https://metalink.oracle.com/metalink...,0,1,helvetica
which says:
" 6. Both the soft and hard parse register as a parse in tkprof. Hashing the
current statement updates the parse count.

7. Soft parse avoids many of the steps taken during the parse phase for a
particular statement. Initial syntactic and semantic checks are made and
then the statement is hashed and compared with hashed statements in
the SQL area. If a match is found, then existing information is used
and relatively expensive steps (such as query optimization etc.) are avoided."

So as long as your parse CPU takes less than 10% of the total CPU and the elapsed parse time is less than 10% of the total elapsed time and you have only one library cache miss (what means one hard parse) you can be happy about that

See the note for more details about soft parse and hard parse

[malay_biswal]

Do you have any problem. If yes mention that.
However as it's already suggested the only thing you need to check is why the parse and execution count same.

[Bore]

I think I explained that. See above

[tomcat]

What I find interesting here is it's one of those "does this exist" queries - it never actually returns any rows...