Home » RDBMS Server » Performance Tuning » TKPROF - High scattered read (9.2.0.7)
TKPROF - High scattered read [message #347535] Fri, 12 September 2008 01:01 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Executing a single SQL statement and getting high "db file scattered read" and "db file sequential read". Basically its going full Table scan

What can be done.

Following is the TKPROF o/p ..

TKPROF: Release 9.2.0.7.0 - Production on Fri Sep 12 11:15:18 2008

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

Trace file: prod_ora_8404.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
********************************************************************************

The following statements encountered a error during parse:

alter session set events max_dump_file_size=unlimited ;
WAIT #6: nam='
Error encountered: ORA-02246
--------------------------------------------------------------------------------
alter session set events '10046 trace name context off';
WAIT #6: nam='
Error encountered: ORA-00911
********************************************************************************

alter session set events '10046 trace name context forever, level 12'


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

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

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  SQL*Net message from client                     4       38.85         68.09
  SQL*Net break/reset to client                   3        0.01          0.02
********************************************************************************

select * 
from
 jtf_ih_bulk_qtbl where msgid = '16B733FEB78015FFE044000F202BA462'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      1.64       1.60      22009      59867          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      1.64       1.60      22009      59867          0           1

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

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.01          0.02
  db file scattered read                       3365        0.00          0.33
  db file sequential read                      1252        0.00          0.02
********************************************************************************

alter session set events '10046 trace name context off'


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

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



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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        1      1.64       1.60      22009      59867          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      1.64       1.60      22009      59867          0           1

Misses in library cache during parse: 2
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       9        0.00          0.00
  SQL*Net message from client                     9       38.85         72.01
  SQL*Net break/reset to client                   3        0.01          0.02
  db file scattered read                       3365        0.00          0.33
  db file sequential read                      1252        0.00          0.02


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

    3  user  SQL statements in session.
    0  internal SQL statements in session.
    3  SQL statements in session.
********************************************************************************
Trace file: prod_ora_8404.trc
Trace file compatibility: 9.02.00
Sort options: default

       1  session in tracefile.
       3  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       3  SQL statements in trace file.
       3  unique SQL statements in trace file.
    4693  lines in trace file.


Re: TKPROF - High scattered read [message #347544 is a reply to message #347535] Fri, 12 September 2008 01:17 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Bryan, what is the query and execution plan? Is this a query
that previously was performing ok - what are the expectations?
Re: TKPROF - High scattered read [message #347553 is a reply to message #347535] Fri, 12 September 2008 02:02 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I have collected statistics using

dbms_stats.GATHER_TABLE_STATS(ownname=>'APPS',tabname=>'JTF_IH_BULK_QTBL', cascade=>TRUE, granularity => 'DEFAULT'); 


Table has around 477923 records and continuously increasing.

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------
| Id  | Operation            |  Name             | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |  4778 |   443K|  9063 |
|   1 |  FOR UPDATE          |                   |       |       |       |
|*  2 |   TABLE ACCESS FULL  | JTF_IH_BULK_QTBL  |  4778 |   443K|  9063 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   2 - filter(RAWTOHEX("JTF_IH_BULK_QTBL"."MSGID")=:Z)

Note: cpu costing is off

15 rows selected.


Earlier it was using the right index, I have gathered Schema Stats, after that it is going for Full tablescan.

Brayan
Re: TKPROF - High scattered read [message #347570 is a reply to message #347553] Fri, 12 September 2008 03:24 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Is msgid a RAW datatype?

Your SQL is currently casting MSGID to a string in order to compare it to your constant character string.

Change your SQL to be:

where msgid = HEXTORAW('16B733FEB78015FFE044000F202BA462')


This means that it will no longer need to cast and an index will be used on msgid if it exists.

Ross Leishman
Previous Topic: SUSE Oracle high CPU usgae problem
Next Topic: Index not being used in Oracle 10g
Goto Forum:
  


Current Time: Sat Dec 03 08:08:33 CST 2016

Total time taken to generate the page: 0.04554 seconds