Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Chris Saxon

Thanks for the question, Mandar.

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

Last updated: April 17, 2019 - 1:13 pm UTC

Version: 8.1.6

Viewed 1000+ times

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 Tom 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 asktom.oracle.com/magazine-archive.htm and search for statspack for a couple of articles on this utility.

Rating

  (4 ratings)

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

Comments

Full Table Scans

Jaideep, July 10, 2003 - 1:58 pm UTC

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

Cool

Khalid, February 07, 2004 - 11:09 am UTC

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

Tom Kyte
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?

John Keymer, April 16, 2019 - 7:23 am UTC

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
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

John Keymer, April 17, 2019 - 7:22 am UTC

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
April 17, 2019 - 1:13 pm UTC

They're totals - which will be since instance startup.
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.