OraFAQ Forum: Server Administration » Find Out Tablespace Least Busy Period

Home » RDBMS Server » Server Administration » Find Out Tablespace Least Busy Period (11.2.0.4 SE, Redhat 7.1)

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

Find Out Tablespace Least Busy Period [message #653424]

Tue, 05 July 2016 03:41

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear all,

from https://docs.oracle.com/cd/E11882_01/server.112/e25494/dfiles.htm#ADMIN11430

Procedure for Renaming Data Files in a Single Tablespace
Quote:

ALTER TABLESPACE users OFFLINE NORMAL;

Of course the first step can easily complete if there's no sessions or processes accessing the tablespace.

Unfortunately it can be easily completed if it there are many sessions or processes accessing the tablespace.

also I'm using SE, so DBA_HIS_ views will not be applicable. But statspack has been installed and snapshot taken at 20 minutes interval at 10, 30, 50 minute.

so how do I found the time of the day where the tablespace is accessed the least for a 7 day period?

thanks in advance

Report message to a moderator

Re: Find Out Tablespace Least Busy Period [message #653430 is a reply to message #653424]

Tue, 05 July 2016 05:13

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

STATS$SNAPSHOT gives you the time and STATS$FILESTATXS will give you statistics per file and so tablespace.

Report message to a moderator

Re: Find Out Tablespace Least Busy Period [message #653432 is a reply to message #653430]

Tue, 05 July 2016 06:18

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Code: [Select all] [Show/ hide]

SNAP_ID                                   NOT NULL NUMBER
DBID                                      NOT NULL NUMBER
INSTANCE_NUMBER                           NOT NULL NUMBER
TSNAME                                    NOT NULL VARCHAR2(30)
FILENAME                                  NOT NULL VARCHAR2(513)
PHYRDS                                             NUMBER
PHYWRTS                                            NUMBER
SINGLEBLKRDS                                       NUMBER
READTIM                                            NUMBER
WRITETIM                                           NUMBER
SINGLEBLKRDTIM                                     NUMBER
PHYBLKRD                                           NUMBER
PHYBLKWRT                                          NUMBER
WAIT_COUNT                                         NUMBER
TIME                                               NUMBER
FILE#                                              NUMBER

which parameter should I order by ? time?

Also I supposed to use a difference function right? what is term to googled for finding the difference between now and 1 hours ago?

thanks

Report message to a moderator

Re: Find Out Tablespace Least Busy Period [message #653433 is a reply to message #653432]

Tue, 05 July 2016 06:20

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why do you think I said?

Quote:

STATS$SNAPSHOT gives you the time

Report message to a moderator

Re: Find Out Tablespace Least Busy Period [message #653434 is a reply to message #653433]

Tue, 05 July 2016 07:15

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

actually what I mean is order by STATS$FILESTATXS.TIME.

https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_1141.htm#REFRN30087

since STATS$FILESTATXS is most likely a historical snapshot of v$file_stat,

isn't the duration of time taken to read and write the tablespace important in finding the least busy period.

desired output:

Code: [Select all] [Show/ hide]

time                         time taken
2016-07-01 14:00-15:00       30 minutes
2016-07-01 15:00-16:00       15 minutes
2016-07-01 16:00-17:00       45 minutes

of course the above means on this day 15:00-16:00 is the least busy right?

Report message to a moderator

Re: Find Out Tablespace Least Busy Period [message #653435 is a reply to message #653434]

Tue, 05 July 2016 07:44

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can take the statistics you want in STATS$FILESTATXS, TIME or something else (I prefer something else) but if you want when this happen (your "time" in "desired output") you have to join with the other table.

Report message to a moderator

Re: Find Out Tablespace Least Busy Period [message #653439 is a reply to message #653435]

Tue, 05 July 2016 10:24

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

More precisely, given you want to put offline the tablespace (I don't know why it is necessary) I'd choose the number of writes (PHYWRTS), access time is not relevant for the question.

Report message to a moderator

Previous Topic:	Getting OUI - 10150:runtime exception while setting s_dlgCfgNamingLabelSIDn
Next Topic:	AMM memory allocation with Huge amount of System Memory

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 22:06:30 CDT 2025