OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » full table scan

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

full table scan [message #147437]

Thu, 17 November 2005 10:31

alka205
Messages: 18
Registered: August 2005

Junior Member

Is there a script which can get me full table scan on my database

Thanks

Report message to a moderator

Re: full table scan [message #147442 is a reply to message #147437]

Thu, 17 November 2005 10:48

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

I'm sorry but ...... "What???" can you clarify/ give more detail?

Jim

Report message to a moderator

Re: full table scan [message #147446 is a reply to message #147442]

Thu, 17 November 2005 11:03

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

Report message to a moderator

Re: full table scan [message #147450 is a reply to message #147446]

Thu, 17 November 2005 11:31

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

Report message to a moderator

Re: full table scan [message #147452 is a reply to message #147437]

Thu, 17 November 2005 11:47

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.

Report message to a moderator

Re: full table scan [message #147454 is a reply to message #147452]

Thu, 17 November 2005 11:50

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

Report message to a moderator

Re: full table scan [message #147487 is a reply to message #147454]

Thu, 17 November 2005 18:00

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

Report message to a moderator

Re: full table scan [message #147562 is a reply to message #147487]

Fri, 18 November 2005 04:18

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

Report message to a moderator

Re: full table scan [message #147631 is a reply to message #147437]

Fri, 18 November 2005 11:26

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.

Report message to a moderator

Re: full table scan [message #147746 is a reply to message #147562]

Sun, 20 November 2005 16:12

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

Report message to a moderator

Re: full table scan [message #147805 is a reply to message #147746]

Mon, 21 November 2005 03:23

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

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

Report message to a moderator

Re: full table scan [message #147940 is a reply to message #147805]

Mon, 21 November 2005 18:06

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

Report message to a moderator

Re: full table scan [message #148007 is a reply to message #147940]

Tue, 22 November 2005 03:49

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

Report message to a moderator

Re: full table scan [message #149016 is a reply to message #147437]

Tue, 29 November 2005 12:40

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.

Report message to a moderator

Re: full table scan [message #149017 is a reply to message #147437]

Tue, 29 November 2005 12:46

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

Report message to a moderator

Re: full table scan [message #151609 is a reply to message #147437]

Thu, 15 December 2005 19:49

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 07:39:09 CST 2024