|
| Monday, August 3, 2009 |
| Full Table Scans |
Full Table Scans
Here are some scripts related to Full Table Scans .
System Statistics (Table)SYSTEM STATISTICS (TABLE) NOTES:
Statistic Name - Name of the statistic
Bytes - Size
This query provides information on the full table scan activity. If your application is OLTP only, having long full table scans can be an indicator of having missing or incorrect indexes or untuned SQL.
Table fetch by rowid reflect the cumulative number of rows fetched from tables using a TABLE ACCESS BY ROWID operation.
Table fetch continued row reflect the cumulative number of continued rows fetched. This value is incremented when accessing a row that is longer than a block in length and when accessing "migrated" rows. Migrated rows are rows that were relocated from their original location to a new location because of an update that increased their size to the point where they could no longer be accommodated inside their original block. Access to migrated rows will cause this statistic's value to increment only if the access is performed by ROWID. Full table scans of tables that contain migrated rows do not cause this counter to increment.
Table scan blocks gotten reflect the cumulative number of blocks read for full table scans.
Table scans rows gotten reflect the cumulative number of rows read for full table scans.
Table scans (cache partitions) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.
Table scans (long scans) indicate a full scan of a table that has > 5 database blocks.
Table scans (rowid ranges) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.
Table scans (short scans) indicate a full scan of a table that has <= 5 database blocks.
select NAME,
VALUE
from v$sysstat
where NAME like '%table'
Process Table Scans
PROCESS TABLE SCAN NOTES:
User Process - Name of user process
Long Scans - Full scan of a table that has > 5 database blocks.
Short Scans - Full scan of a table that has <= 5 database blocks.
Row Retrieved - Cumulative number of rows read for full table scans.
select ss.username||'('||se.sid||') ' "User Process",
sum(decode(name,'table scans (short tables)',value)) "Short Scans",
sum(decode(name,'table scans (long tables)', value)) "Long Scans",
sum(decode(name,'table scan rows gotten',value)) "Rows Retreived"
from v$session ss,
v$sesstat se,
v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
or name like '%table scans (long tables)%'
or name like '%table scan rows gotten%')
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') '
Process Table Scans (Avg)
PROCESS TABLE SCAN (AVERAGE) NOTES:
User Process - Name of user process
Short Scans - Number of short scans (<= 5 blocks)
Long Scans - Number of long scans (> 5 blocks)
Rows Retrieved - Number of rows retrieved
Long Scans Length - Average long scan length (i.e. full table scan of > 5 blocks)
select ss.username||'('||se.sid||') ' "User Process",
sum(decode(name,'table scans (short tables)',value)) "Short Scans",
sum(decode(name,'table scans (long tables)', value)) "Long Scans",
sum(decode(name,'table scan rows gotten',value)) "Rows Retreived",
round((sum(decode(name,'table scan rows gotten',value)) - (sum(decode(name,'table scans (short tables)',value)) * 5)) / (sum(decode(name,'table scans (long tables)', value))),2) "Long Scans Length"
from v$session ss,
v$sesstat se,
v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
or name like '%table scans (long tables)%'
or name like '%table scan rows gotten%')
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') '
having sum(decode(name,'table scans (long tables)', value)) != 0
order by 3 descLabels: Full Table Scans |
posted by Srinivasan .R @ 11:15 PM  |
|
| 2 Comments: |
-
-
Hi! I understand this is kind of off-topic but I had to ask. Does managing a well-established blog like yours require a lot of work? I am completely new to operating a blog however I do write in my diary daily. I’d like to start a blog so I can share my personal experience and views online. Please let me know if you have any kind of suggestions or tips for new aspiring blog owners. Thankyou!
What your stating is absolutely genuine. I know that everyone ought to say the identical factor, but I just feel that you set it in a way that absolutely everyone can realize. I also adore the photographs you set in here. They fit so nicely with what youre hoping to say. Im guaranteed youll attain so numerous people today with what youve got to say.
These moles may possibly be irregular in size and color and that is what can make them this type of wellness danger. When you have been born with this particular problem you might also be more likely to develop Melanoma and so you might have to get the required precautions with regards to protecting your pores and skin and your well being.
This is such a great post, and was thinking much the same myself. It’s certainly an opinion I agree with.
Enjoyed this article. I believe that the writer took an rationale perspective and made some pivotale ideas.
Aw, it was an extremely good post. In notion I must place in writing such as this moreover – spending time and actual effort to make a really good article… but so what can I say… I procrastinate alot and no means apparently go completed.
You need to experience a tournament for just one of the most effective blogs on the web. I’ll recommend this page!
Thank you for yet another great informative article, I’m a loyal reader to this blog and I can’t stress enough how much valuable information I’ve learned from reading your content. I really appreciate all the hard work you put into this great site.
When I originally commented I clicked the -Notify me when new comments are added- checkbox and now when a comment is added I buy four emails concentrating on the same comment. Perhaps there is in any manner you can get rid of me from that service? Thanks!
|
| |
| << Home |
| |
|
|
|
|
soul knight mod apk
my talking angela mod apk
nova legacy mod apk
modern combat 5 mod apk
shadow fight 3 mod apk