Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Mandar.

Asked: July 22, 2001 - 8:21 pm UTC

Answered by: Tom Kyte - Last updated: April 17, 2019 - 1:13 pm UTC

Category: Database - Version: 8.1.6

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: The SQL Loader log file - as data

You Asked

Tom,

Can you please provide info on how can I find the
full table scan and index table scan activities
in the database using v$statname and v$sysstat?

Do I need to set TIMED_STATISTICS=TRUE before running
queries against v$sysstat?

Also, I look at v$sqlarea for most offensive sqls, is
there a better way to find the most offensive SQLs?


Thanks in advance,

-Mandar.



and we said...

1* select * from v$sysstat where lower(name) like '%table%'
ops$tkyte@ORA8I.WORLD> /

STATISTIC# NAME CLASS VALUE
---------- ------------------------------ ---------- ----------
48 DBWR transaction table writes 8 614
144 transaction tables consistent 128 0
reads - undo records applied

145 transaction tables consistent 128 0
read rollbacks

158 table scans (short tables) 64 1676475
159 table scans (long tables) 64 3942
160 table scans (rowid ranges) 64 0
161 table scans (cache partitions) 64 7153
162 table scans (direct read) 64 0
163 table scan rows gotten 64 1512068438
164 table scan blocks gotten 64 24283078
165 table fetch by rowid 64 22813564
166 table fetch continued row 64 309293


1* select * from v$sysstat where lower(name) like '%full%'
ops$tkyte@ORA8I.WORLD> /

STATISTIC# NAME CLASS VALUE
---------- ------------------------------ ---------- ----------
76 commit cleanouts successfully 8 268320
completed

174 index fast full scans (full) 64 10981
175 index fast full scans (rowid r 64 0
anges)

176 index fast full scans (direct 64 0
read)


will get that. You do not need timed_stats however, i would recommend you just leave it on, it is so useful.


I use statspack to find the "top offenders" as far as SQL goes. Goto www.oracle.com/oramag and search for statspack for a couple of articles on this utility.

and you rated our response

  (4 ratings)

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

Reviews

Full Table Scans

July 10, 2003 - 1:58 pm UTC

Reviewer: Jaideep from Grand Rapids, MI , USA

Wonderful answer. I was not looking for anything more than this !

Cool

February 07, 2004 - 11:09 am UTC

Reviewer: Khalid from Yemen

I want to now what is the sql statements that do a full table scan in the database

Tom Kyte

Followup  

February 07, 2004 - 3:12 pm UTC

if you have 9i, easy. just look at v$sql_plan

if you do not, then, start thinking about all of the other things you might be missing in the older releases :)

Time Period?

April 16, 2019 - 7:23 am UTC

Reviewer: John Keymer

Sorry for dragging up an old thread... we have an instance which is running a huge amount of queries (COTS ERP with a lot of customisations). I suspect poor (dynamic) code is knocking other plans out of the SGA quite frequently and thought I might be able to use the above to prove this. One thing though - what time period are the stats recorded over? Is it since the instance was started? Is there anything which gives daily counts? I.e. so I can say "today we had XXX queries parsed by Oracle, of which YYY were hard parsed"? We have diagnostics and tuning pack.


Thanks
Chris Saxon

Followup  

April 16, 2019 - 3:23 pm UTC

It depends on the stat. For example, you have:

logons cumulative - Total number of logons since the instance started. Useful only in V$SYSSTAT. It gives an instance overview of all processes that logged on.

logons current - Total number of current logons. Useful only in V$SYSSTAT.


Check them out at: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/statistics-descriptions-2.html#GUID-2FBC1B7E-9123-41DD-8178-96176260A639

If you're looking for changes in a given time period you can look at your AWR reports which report parse ratios etc. for that period.

Parse Stats

April 17, 2019 - 7:22 am UTC

Reviewer: John Keymer

Sorry, I meant the parse stats:

select * from v$sysstat where name like '%parse count%';


This gives me counts - but over what time period are they?
Chris Saxon

Followup  

April 17, 2019 - 1:13 pm UTC

They're totals - which will be since instance startup.