Home » RDBMS Server » Performance Tuning » full table scan
full table scan [message #147437] Thu, 17 November 2005 10:31 Go to next message
alka205
Messages: 18
Registered: August 2005
Junior Member
Is there a script which can get me full table scan on my database

Thanks
Re: full table scan [message #147442 is a reply to message #147437] Thu, 17 November 2005 10:48 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
I'm sorry but ...... "What???" can you clarify/ give more detail?

Jim
Re: full table scan [message #147446 is a reply to message #147442] Thu, 17 November 2005 11:03 Go to previous messageGo to next message
alka205
Messages: 18
Registered: August 2005
Junior Member
I want a script to run every 2 hrs and see if any sql query is doing a full table scan and creates a log file which has detail about table name,username and sql query doing full table scan.

Hope it helps
Thanks
Re: full table scan [message #147450 is a reply to message #147446] Thu, 17 November 2005 11:31 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Hmmm, don't really know. How many queries are getting run. There may very well be some utility to do it for you, but I have never come across it.

You can query v$sql_plan to get the plans of queries that are currently in the shared pool, but if you are running a lot of different queries, some may already be aged out.
What exactly is it you are trying to do (big picture Wink )

Jim
Re: full table scan [message #147452 is a reply to message #147437] Thu, 17 November 2005 11:47 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Sounds like the OP is of the mistaken opinion that Full Table Scans are always bad.
Re: full table scan [message #147454 is a reply to message #147452] Thu, 17 November 2005 11:50 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Yep, just wanted to double check OP's reasons, maybe it's just an academical q ..... maybe.
Could be time for the mantra
FTS not bad
FTS not bad
FTS not bad
ad infinitum
Re: full table scan [message #147487 is a reply to message #147454] Thu, 17 November 2005 18:00 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There is an extenstion to the "FTS not bad" corrolary.

On a well designed data model that is translated into a well-designed schema, FTS is rarely bad.

On a bad data model, on average, a FTS has a better-than-evens chance of being bad (despite the fact that Oracle may have no other options). In such a database, knowing the location and frequency of FTS can give you a hit-list for re-design.

v$wait_event can also tell you the full scans currently in progress.
_____________
Ross Leishman
Re: full table scan [message #147562 is a reply to message #147487] Fri, 18 November 2005 04:18 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
v$wait_event. I don't know that view, certainly doesn't seem to me in my schema, is it new to 10g? Do you have to run a utility to get it?
I was having a look and you can get some of the required info from :

select sid, value, statistic# from v$sesstat where statistic# in (183,184);

(183 and 184 are short and long table scans respectively)

short table scan are fts on tables of (I think) 6 blocks or less.
Hmmm, where would you take it from there?

Jim
Re: full table scan [message #147631 is a reply to message #147437] Fri, 18 November 2005 11:26 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I have seen scripts out there (I think) that look for FTS for the purpose of replacing them with indexes, can check the sql script archive collection type sites.

I'd think you could also turn on tracing for a day and then grep for it in the execution plans.
Re: full table scan [message #147746 is a reply to message #147562] Sun, 20 November 2005 16:12 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
JSI2001 wrote on Fri, 18 November 2005 21:18

v$wait_event. I don't know that view, ...


I really should read over my posts first.

v$session_wait


Sorry.

_____________
Ross Leishman
Re: full table scan [message #147805 is a reply to message #147746] Mon, 21 November 2005 03:23 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Laughing You had me panicking, I though I was missing a view Wink Not too sure how v$session_wait shows me FTS though. Any chance you can explain?
TIA
Jim
Re: full table scan [message #147940 is a reply to message #147805] Mon, 21 November 2005 18:06 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Look for a wait reason of db file scattered read

It pretty reliably shows FTSs
Re: full table scan [message #148007 is a reply to message #147940] Tue, 22 November 2005 03:49 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Hmm, I thought ... how to put it. An FTS does not necessarily mean that a db file scattered read event will occur it's just that if a db file scattered read event does occur, it is because of a FTS (I could be wrong of course)but that doesn't seem very reliable to me Wink . Also, in session_event we'd only see events for current sessions. Not what the OP was looking for (I think) The OP is looking for a history of all FTSs that have happened over the past 2hrs or so.

Jim
Re: full table scan [message #149016 is a reply to message #147437] Tue, 29 November 2005 12:40 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I'd need to double check, but wouldn't also an index fast full scan also generate a scattered read, operating under similar principles to the FTS except just with indexes.

I'm trying to remember where I saw a good explanation of this...perhaps in the oracle docs itself.
Re: full table scan [message #149017 is a reply to message #147437] Tue, 29 November 2005 12:46 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Ahh here we go, at least one place with discussion of both scattered and sequential. When I first heard the terms I thought they were backwards because I didn't understand how the terms were meant. Actually I think we've discussed this here on orafaq before.

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#i20526
Re: full table scan [message #151609 is a reply to message #147437] Thu, 15 December 2005 19:49 Go to previous message
humair77
Messages: 13
Registered: August 2005
Junior Member
Here is a great script to show SQL access patterns, grouped by full-table scans, index range scans and index unique scans.

--------------------------------------------------------------------------------

Full table scans and counts
Note that "K" indicates in the table is in the KEEP pool.


OWNER NAME NUM_ROWS C K BLOCKS NBR_FTS
-------------- ------------------------ --------- - - -------- --------
SYS DUAL N 2 97,237
SYSTEM SQLPLUS_PRODUCT_PROFILE N K 2 16,178
DONALD PAGE 3,450,209 N 932,120 9,999
DONALD RWU_PAGE 434 N 8 7,355
DONALD PAGE_IMAGE 18,067 N 1,104 5,368
DONALD SUBSCRIPTION 476 N K 192 2,087
DONALD PRINT_PAGE_RANGE 10 N K 32 874


--------------------------------------------------------------------------------



--**************************************************************
-- Object Access script report
--
-- © 2001 by Donald K. Burleson
--
-- No part of this SQL script may be copied. Sold or distributed
-- without the express consent of Donald K. Burleson
--**************************************************************

[Moderator edit: Removed copyrighted code]

[Updated on: Fri, 16 December 2005 00:36] by Moderator

Report message to a moderator

Previous Topic: when do I need to create an indexes.
Next Topic: Query using bind variable is slower??
Goto Forum:
  


Current Time: Fri Dec 09 13:43:11 CST 2022