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.

memory navigation

8 posts in General Discussion Last posting was on 2012-10-15 20:22:18.0Z
vtpcnk Posted on 2012-10-02 14:20:18.0Z
Sender: 144a.506af72b.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: memory navigation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <506af822.147c.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 2 Oct 2012 07:20:18 -0700
X-Trace: forums-1-dub 1349187618 172.20.134.41 (2 Oct 2012 07:20:18 -0700)
X-Original-Trace: 2 Oct 2012 07:20:18 -0700, 172.20.134.41
Lines: 9
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31403
Article PK: 74292

when data/index pages are brought into the cache, when they
need to be accessed again, how does the optimizer know where
to find them in the memory?

if we only have the default data cache of say 4 gb, then how
does the optimizer track down the related data/index pages
of an object within the cache?

appreciate the insights.


Rob V Posted on 2012-10-05 11:28:10.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: memory navigation
References: <506af822.147c.1681692777@sybase.com>
In-Reply-To: <506af822.147c.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: <506ec44a@forums-1-dub>
Date: 5 Oct 2012 04:28:10 -0700
X-Trace: forums-1-dub 1349436490 172.20.134.152 (5 Oct 2012 04:28:10 -0700)
X-Original-Trace: 5 Oct 2012 04:28:10 -0700, vip152.sybase.com
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31413
Article PK: 74301


On 02-Oct-2012 16:20, vtpcnk wrote:
> when data/index pages are brought into the cache, when they
> need to be accessed again, how does the optimizer know where
> to find them in the memory?
>
> if we only have the default data cache of say 4 gb, then how
> does the optimizer track down the related data/index pages
> of an object within the cache?
>
> appreciate the insights.
>

Pages are always accessed by page number. For all pages in cache, the
page numbers are in a hash table. This is a standard technique in
computing.

When a query needs to access pages for table X, it will first get read
syspartitions to get either the first data/index page or the first OAM
page for table X (depending on the table's lock scheme); other pages are
accessed based on the information stored there.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


vtpcnk Posted on 2012-10-11 04:12:34.0Z
Sender: 1cfc.50763f25.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: Re: memory navigation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50764732.1fa5.1681692777@sybase.com>
References: <506ec44a@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 10 Oct 2012 21:12:34 -0700
X-Trace: forums-1-dub 1349928754 172.20.134.41 (10 Oct 2012 21:12:34 -0700)
X-Original-Trace: 10 Oct 2012 21:12:34 -0700, 172.20.134.41
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31421
Article PK: 74310


> Pages are always accessed by page number. For all pages in
> cache, the page numbers are in a hash table. This is a
> standard technique in computing.

page numbers in memory/cache are the same as the page
numbers on disk? or in other words : don't cache/memory use
its own page numbers?

> When a query needs to access pages for table X, it will
> first get read syspartitions to get either the first
> data/index page or the first OAM page for table X
> (depending on the table's lock scheme); other pages are
> accessed based on the information stored there.

in the above are you talking about the way pages are
accessed in cache?

so even to access pages in cache, there should be disk
access first (syspartitions)?

or i guess syspartitions can itself be cached and used.


btw on a different note, what's 'hth' rob?


Rob V Posted on 2012-10-11 18:31:17.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:16.0) Gecko/20121005 Thunderbird/16.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: memory navigation
References: <506ec44a@forums-1-dub> <50764732.1fa5.1681692777@sybase.com>
In-Reply-To: <50764732.1fa5.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: <50771075@forums-1-dub>
Date: 11 Oct 2012 11:31:17 -0700
X-Trace: forums-1-dub 1349980277 172.20.134.152 (11 Oct 2012 11:31:17 -0700)
X-Original-Trace: 11 Oct 2012 11:31:17 -0700, vip152.sybase.com
Lines: 64
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31423
Article PK: 74312


On 11-Oct-2012 06:12, vtpcnk wrote:
>> Pages are always accessed by page number. For all pages in
>> cache, the page numbers are in a hash table. This is a
>> standard technique in computing.
>
> page numbers in memory/cache are the same as the page
> numbers on disk? or in other words : don't cache/memory use
> its own page numbers?

Yes. Page 12345 is the same on disk as in cache. This page has the
number '12345' in its page header.

>
>> When a query needs to access pages for table X, it will
>> first get read syspartitions to get either the first
>> data/index page or the first OAM page for table X
>> (depending on the table's lock scheme); other pages are
>> accessed based on the information stored there.
>
> in the above are you talking about the way pages are
> accessed in cache?
Yes


> so even to access pages in cache, there should be disk
> access first (syspartitions)?

Yes, after ASE has been started there must always be some initial disk
reads. For tables like syspartitions, sysobjects etc, these are used to
populate in-memory structures with all the info about the table or
object; pages for these system tables themselves do not necessarily need
to remain in cache after that.

>
> or i guess syspartitions can itself be cached and used.

Indeed, see my previous remark.

>
>
> btw on a different note, what's 'hth' rob?
>

Hope This Helps


--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


vtpcnk Posted on 2012-10-13 03:39:07.0Z
Sender: 6929.5078dcb7.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: Re: memory navigation
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <5078e25b.69b5.1681692777@sybase.com>
References: <50771075@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 12 Oct 2012 20:39:07 -0700
X-Trace: forums-1-dub 1350099547 172.20.134.41 (12 Oct 2012 20:39:07 -0700)
X-Original-Trace: 12 Oct 2012 20:39:07 -0700, 172.20.134.41
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31431
Article PK: 74324

rob, i am find it difficult to understand this. let me
explain.

maybe i should have raised the question first about how data
is tracked on disk.
- space for a database is split up into allocation units
(256 pages each).
- each db has a Global Allocation Map which gives the
details of all allocation units for a db.
- the allocation page (the first page of each allocation
unit) has information about the pages/extents within an
allocation unit which are allocated to various objects.
- the OAM contains information regarding pages allocated to
an object which lie across various allocation units.

so i guess when you search for a particular data set in a
particular table in a particular db, the ASE goes from : GAM
-> allocation page -> OAM and so on.

i am not sure how page numbers are allocated. is it
sequential from disk 1 to disk n on the ASE - the first page
on the first disk is page 1 and the last page on the last
disk is the last page? or is it for each db (page number 1
in the first allocation unit to ...)? or does every
allocation unit have its own page numbers (1 to 256 and so
on)?

but however the page numbers are allocated, i think they map
to the physical location of the pages on the disks. so when
you look for page 118, the ASE knows where to go.

so when pages are read into memory and often contain partial
datasets of objects, how can these same page numbers (as
they are on disk) be used in memory to locate pages - what
physical corelation do the page numbers on disks have to
page numbers in memory?

and do pages in memory have their own page numbers, which
are then mapped to page numbers from disk?

or when searching for a particular page in cache, is each
page in the whole memory scanned for that particular page
number?

HIANTC - hope i am not too confusing :-)


Rob V Posted on 2012-10-13 09:36:34.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:16.0) Gecko/20121005 Thunderbird/16.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: memory navigation
References: <50771075@forums-1-dub> <5078e25b.69b5.1681692777@sybase.com>
In-Reply-To: <5078e25b.69b5.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: <50793622$1@forums-1-dub>
Date: 13 Oct 2012 02:36:34 -0700
X-Trace: forums-1-dub 1350120994 172.20.134.152 (13 Oct 2012 02:36:34 -0700)
X-Original-Trace: 13 Oct 2012 02:36:34 -0700, vip152.sybase.com
Lines: 99
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31432
Article PK: 74318


On 13-Oct-2012 05:39, vtpcnk wrote:
> rob, i am find it difficult to understand this. let me
> explain.
>
> maybe i should have raised the question first about how data
> is tracked on disk.
> - space for a database is split up into allocation units
> (256 pages each).
> - each db has a Global Allocation Map which gives the
> details of all allocation units for a db.
> - the allocation page (the first page of each allocation
> unit) has information about the pages/extents within an
> allocation unit which are allocated to various objects.
> - the OAM contains information regarding pages allocated to
> an object which lie across various allocation units.
>

That's all correct.

> so i guess when you search for a particular data set in a
> particular table in a particular db, the ASE goes from : GAM
> -> allocation page -> OAM and so on.

No. When I need to read rows from table X, first you must read
syspartitions. From here, you get the number of the first OAM page so
you can read that page. Then on that page you find the allocation pages
where the extents are located, so you can read that allocation page and
then proceed to the actual extents for the table.

> i am not sure how page numbers are allocated. is it
> sequential from disk 1 to disk n on the ASE - the first page
> on the first disk is page 1 and the last page on the last
> disk is the last page? or is it for each db (page number 1
> in the first allocation unit to ...)? or does every
> allocation unit have its own page numbers (1 to 256 and so
> on)?
>
> but however the page numbers are allocated, i think they map
> to the physical location of the pages on the disks. so when
> you look for page 118, the ASE knows where to go.

The page nubmers inside a database start at 0 and run to the highest
page number. These are the page numbers we usually talk about when we
say 'page 12345'.
The physical page number (or virtual page numbers) are those recorded in
sysdevices and they depend on the vdevno that was chosen (explicitly or
implicitly) when 'disk init' was executed. Physical page numbers are not
stored anywhere in the pages themselves, they are derived from their
relative position on the database device as created by disk init.
The sysusages table maps physical page numbers to logical page numbers
for every database.


>
> so when pages are read into memory and often contain partial
> datasets of objects, how can these same page numbers (as
> they are on disk) be used in memory to locate pages - what
> physical corelation do the page numbers on disks have to
> page numbers in memory?
>
> and do pages in memory have their own page numbers, which
> are then mapped to page numbers from disk?

There is no difference between a page in memory and a page on disk --
they are byte-for-byte identical. When ASE needs to read page 45678
because it is not in cache, sysusages and sysdevices are used to
translate this to an I/O to the correspondign disk location.


>
> or when searching for a particular page in cache, is each
> page in the whole memory scanned for that particular page
> number?
No, as I mentioned earlier, the page numbers are in a hash table and
that's how they can be accessed quickly. The hash is on the page number
plus database ID.

>
> HIANTC - hope i am not too confusing :-)
>


--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Bret Halford Posted on 2012-10-15 20:22:18.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: memory navigation
References: <50771075@forums-1-dub> <5078e25b.69b5.1681692777@sybase.com> <50793622$1@forums-1-dub>
In-Reply-To: <50793622$1@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: <507c707a$1@forums-1-dub>
Date: 15 Oct 2012 13:22:18 -0700
X-Trace: forums-1-dub 1350332538 172.20.134.152 (15 Oct 2012 13:22:18 -0700)
X-Original-Trace: 15 Oct 2012 13:22:18 -0700, vip152.sybase.com
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31440
Article PK: 74328


On 10/13/2012 3:36 AM, Rob V wrote:
> On 13-Oct-2012 05:39, vtpcnk wrote:

>> so i guess when you search for a particular data set in a
>> particular table in a particular db, the ASE goes from : GAM
>> -> allocation page -> OAM and so on.
>
> No. When I need to read rows from table X, first you must read
> syspartitions. From here, you get the number of the first OAM page so
> you can read that page. Then on that page you find the allocation pages
> where the extents are located, so you can read that allocation page and
> then proceed to the actual extents for the table.

The above is the method for a DOL table scan. The process is a little
different for other types of scans.

Typically:

An APL table scan will go to syspartitions to get the firstpage
value for indid 0, and then follow the chain of page pointers from
the page headers to access the rest of the data

For both DOL and APL, a covered index scan will go to syspartitions
to get the firstpage value for the leaf level of the index being
scanned and follow the chain of page pointers from the page headers

For both DOL and APL, positioning by index key, ASE will go
to syspartitions to get the rootpage of the index and
then drill down the index tree to the leaf level.
(For a '<' query, it probably goes to firstpage).

-bret


Bret Halford Posted on 2012-10-15 16:12:38.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: memory navigation
References: <50771075@forums-1-dub> <5078e25b.69b5.1681692777@sybase.com>
In-Reply-To: <5078e25b.69b5.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: <507c35f6$1@forums-1-dub>
Date: 15 Oct 2012 09:12:38 -0700
X-Trace: forums-1-dub 1350317558 172.20.134.152 (15 Oct 2012 09:12:38 -0700)
X-Original-Trace: 15 Oct 2012 09:12:38 -0700, vip152.sybase.com
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31439
Article PK: 74327

Adding a bit of information to what Rob said:

On 10/12/2012 9:39 PM, vtpcnk wrote:> rob, i am find it difficult to

understand this. let me
> explain.
>
> maybe i should have raised the question first about how data
> is tracked on disk.
> - space for a database is split up into allocation units
> (256 pages each).
> - each db has a Global Allocation Map which gives the
> details of all allocation units for a db.

Not really details. The GAM just has a single bit for
each allocation unit that indicates if the allocation
unit is completely full or not. The main use of the GAM
comes when allocating a new extent as it helps quickly
identify allocation units that have free extents.

> - the allocation page (the first page of each allocation
> unit) has information about the pages/extents within an
> allocation unit which are allocated to various objects.
> - the OAM contains information regarding pages allocated to
> an object which lie across various allocation units.

The OAM is just slightly less detailed then that. Each
OAM entry has information on a single allocation unit and
indicates the total number of extents allocated to the object
and how many of the pages on those extents are currently
used or free (but not which pages exactly).

> so i guess when you search for a particular data set in a
> particular table in a particular db, the ASE goes from : GAM
> -> allocation page -> OAM and so on.

That is more the extent allocation method. ASE uses an
in-memory map of the segments to determine the subset of GAM
bits that map to allocation units the object may allocate
space on. A zero bit in the GAM indicates free extent(s).
Once allocated, the object's OAM is updated to reflect the
new extent.