Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to map object / device placement

3 posts in General Discussion Last posting was on 2012-05-03 15:29:59.0Z
datahouse Posted on 2012-05-02 15:04:12.0Z
Sender: 67a9.4fa149f8.1804289383@sybase.com
From: datahouse
Newsgroups: sybase.public.ase.general
Subject: How to map object / device placement
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fa14cec.6801.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 2 May 2012 08:04:12 -0700
X-Trace: forums-1-dub 1335971052 172.20.134.41 (2 May 2012 08:04:12 -0700)
X-Original-Trace: 2 May 2012 08:04:12 -0700, 172.20.134.41
Lines: 7
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31076
Article PK: 73966

I am testing round robin partitioning. Using 8 partitions I
create a global clustered index on the default segment. I
would like to know how the object gets distributed across
devices so want to map the object onto which device. I want
to use this to compare it with using user defined segments
across devices to see the benefit.
syspartitions-->sysusages-->sysdevices ??


Bret Halford Posted on 2012-05-03 15:28:37.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to map object / device placement
References: <4fa14cec.6801.1681692777@sybase.com>
In-Reply-To: <4fa14cec.6801.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fa2a425@forums-1-dub>
Date: 3 May 2012 08:28:37 -0700
X-Trace: forums-1-dub 1336058917 10.22.241.152 (3 May 2012 08:28:37 -0700)
X-Original-Trace: 3 May 2012 08:28:37 -0700, vip152.sybase.com
Lines: 78
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31079
Article PK: 73968


On 5/2/2012 9:04 AM, datahouse wrote:
> I am testing round robin partitioning. Using 8 partitions I
> create a global clustered index on the default segment. I
> would like to know how the object gets distributed across
> devices so want to map the object onto which device. I want
> to use this to compare it with using user defined segments
> across devices to see the benefit.
> syspartitions-->sysusages-->sysdevices ??

dbcc usedextents(dbname,0,0)

will dump information about each allocated extent in a record format
that looks like this

OAMPG: 328 Extent ID 2384 on allocation page 2304
Object ID is 5
Index ID is 0
Partition ID is 5
Allocation bitmap: 0xff ( 2384 2385 2386 2387 2388 2389 2390 2391 )
Dealloc bitmap: 0x00 ( )
Forward bitmap: 0x00 ( )
Reserve bitmap: 0x00 ( )
status: 0x00 (EX_DEALL_NOSTATUS )
Sort bit is off
Reference bit is off
Spacebits bitmap: 0x88888888
Page: 2384 (0x08 (Less than 100% occupied))
Page: 2385 (0x08 (Less than 100% occupied))
Page: 2386 (0x08 (Less than 100% occupied))
Page: 2387 (0x08 (Less than 100% occupied))
Page: 2388 (0x08 (Less than 100% occupied))
Page: 2389 (0x08 (Less than 100% occupied))
Page: 2390 (0x08 (Less than 100% occupied))
Page: 2391 (0x08 (Less than 100% occupied))
Buddy Page for extent (se_extbuddypage): 0


If you run dbcc usedextents and save the output in a file, you
can then filter out the extentid, object id, and indexid using a simple
awk script:

---------------------
isql -Usa -P -o usedextents.out << EOF
dbcc traceon(3604)
go
dbcc usedextents(qptunedb,0,0)
go
EOF

awk '$1=="OAMPG:" {printf ("%d\t", $5) } \
$1=="Partition" {printf (" %d \t", $4)} \
$1=="Index" {printf (" %d \n", $4)} ' usedextents.out > awk.out

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

This yields simple tabular data that looks like

174064 591338140 2
174096 591338140 2

i.e (partition 591338140 index 2 is allocated extent 174064)

You can then import that data into ASE and
use it in various queries to answer questions like yours.

-----------------------------------------------------
isql -Usa -P << EOF
use tempdb
go
create table usedextents (extent int, objectid int, indid tinyint)
go
!! bcp tempdb..usedextents in awk.out -Usa -P -c


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


Bret Halford Posted on 2012-05-03 15:29:59.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to map object / device placement
References: <4fa14cec.6801.1681692777@sybase.com> <4fa2a425@forums-1-dub>
In-Reply-To: <4fa2a425@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fa2a477$1@forums-1-dub>
Date: 3 May 2012 08:29:59 -0700
X-Trace: forums-1-dub 1336058999 10.22.241.152 (3 May 2012 08:29:59 -0700)
X-Original-Trace: 3 May 2012 08:29:59 -0700, vip152.sybase.com
Lines: 90
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31080
Article PK: 73970

Sorry - forgot a basic - first turn on traceflag 3604
so the output of dbcc usedextents will go to the client

dbcc traceon(3604)
go
dbcc usedextents(dbname,0,0)
go
...

On 5/3/2012 9:28 AM, Bret Halford wrote:
> On 5/2/2012 9:04 AM, datahouse wrote:
>> I am testing round robin partitioning. Using 8 partitions I
>> create a global clustered index on the default segment. I
>> would like to know how the object gets distributed across
>> devices so want to map the object onto which device. I want
>> to use this to compare it with using user defined segments
>> across devices to see the benefit.
>> syspartitions-->sysusages-->sysdevices ??
>
>
> dbcc usedextents(dbname,0,0)
>
> will dump information about each allocated extent in a record format
> that looks like this
>
> OAMPG: 328 Extent ID 2384 on allocation page 2304
> Object ID is 5
> Index ID is 0
> Partition ID is 5
> Allocation bitmap: 0xff ( 2384 2385 2386 2387 2388 2389 2390 2391 )
> Dealloc bitmap: 0x00 ( )
> Forward bitmap: 0x00 ( )
> Reserve bitmap: 0x00 ( )
> status: 0x00 (EX_DEALL_NOSTATUS )
> Sort bit is off
> Reference bit is off
> Spacebits bitmap: 0x88888888
> Page: 2384 (0x08 (Less than 100% occupied))
> Page: 2385 (0x08 (Less than 100% occupied))
> Page: 2386 (0x08 (Less than 100% occupied))
> Page: 2387 (0x08 (Less than 100% occupied))
> Page: 2388 (0x08 (Less than 100% occupied))
> Page: 2389 (0x08 (Less than 100% occupied))
> Page: 2390 (0x08 (Less than 100% occupied))
> Page: 2391 (0x08 (Less than 100% occupied))
> Buddy Page for extent (se_extbuddypage): 0
>
>
> If you run dbcc usedextents and save the output in a file, you
> can then filter out the extentid, object id, and indexid using a simple
> awk script:
>
> ---------------------
> isql -Usa -P -o usedextents.out << EOF
> dbcc traceon(3604)
> go
> dbcc usedextents(qptunedb,0,0)
> go
> EOF
>
> awk '$1=="OAMPG:" {printf ("%d\t", $5) } \
> $1=="Partition" {printf (" %d \t", $4)} \
> $1=="Index" {printf (" %d \n", $4)} ' usedextents.out > awk.out
>
> ------------------------
>
> This yields simple tabular data that looks like
>
> 174064 591338140 2
> 174096 591338140 2
>
> i.e (partition 591338140 index 2 is allocated extent 174064)
>
> You can then import that data into ASE and
> use it in various queries to answer questions like yours.
>
> -----------------------------------------------------
> isql -Usa -P << EOF
> use tempdb
> go
> create table usedextents (extent int, objectid int, indid tinyint)
> go
> !! bcp tempdb..usedextents in awk.out -Usa -P -c
>
>
> ---------------------------------------------------------------
>
>