What is the thread_stack in Oracle and MSSQL compared to MySQL

Question

I'm using a recursive stocked procedure in MySQL and had to change the thread_stack value in the my.ini from 128K to 512K.

I need to deploy the same procedure now to an Oracle and Microsoft SQL Server.

What are the equivalent configuration values in Oracle, SQLServer of the MySQL thread_stack?

Answer 1

This is what I found for SQL Server:

http://blogs.msdn.com/b/arvindsh/archive/2008/08/24/sql-worker-thread-stack-sizes.aspx

http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

To sum up, 512K on 32 bit, 2M on 64 bit Windows.

For Oracle, its not available in the public documentation, but its explained on the support site in this note: Oracle Database and the Windows NT memory architecture, Technical Bulletin (Doc ID 46001.1). You can list (or change) the thread stack size (called as Reserved Memory per Thread) with the ORASTACK utility. The default size of the stack depends on the version and platform.

For example, on 64 bit Windows, the 64 bit 11.2 and 12.1 versions have default stack sizes as 3.1M and 3.5M:

11.2:

orastack X:\oracle\base\product\db11203ee\BIN\oracle.exe

Dump of file X:\oracle\base\product\db11203ee\BIN\oracle.exe

Current Reserved Memory per Thread  = 3100000
Current Committed Memory per Thread = 12160

12.1:

orastack C:\oracle\base\product\db12101ee\BIN\oracle.exe

Dump of file C:\oracle\base\product\db12101ee\BIN\oracle.exe

Current Reserved Memory per Thread  = 3500000
Current Committed Memory per Thread = 12160

11.2 on 32 bit Windows, 1M:

orastack C:\oracle\product\11.2.0\dbhome_1\BIN\oracle.exe

Dump of file C:\oracle\product\11.2.0\dbhome_1\BIN\oracle.exe

Current Reserved Memory per Thread  = 1048576
Current Committed Memory per Thread = 4096