| TKPROF - High scattered read [message #347535] |
Fri, 12 September 2008 01:01  |
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 #347553 is a reply to message #347535] |
Fri, 12 September 2008 02:02  |
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
