Wednesday, April 28, 2004
Couple of things came up on Oracle-L recently that prompted some playing on the laptop.
1, Block sizes. It remains possible in 10g to choose completely mad block sizes - for example the 5k block size we have here.
SYS 27-APR-04@block>show parameter block
NAME TYPE VALUE
------------------------------------ ----------- =
----------------------------
db_block_buffers integer 0
db_block_checking boolean FALSE
db_block_checksum boolean TRUE
db_block_size integer 5120
db_file_multiblock_read_count integer 16
SYS 27-APR-04@block>show release
release 1001000200
SYS 27-APR-04@block>
This would not be a sensible thing to do in production. Why mention it? Well you might be thinking that Oracle can only use the traditional 2,4,8,16 or 32k block sizes. This is just not true. More to the point this page on Steve Adam's excellent web site gives suggestions as to how sensibly to set the block size for a database.
2, The old separating indexes and data for performance reasons is a myth chestnut came up again. Enough already. the two have no automagic correlation, if you have io contention find out where it is and separate those segments. Mean time some things to wonder about.
where does an IOT go then?
what about a bitmap join index?
what about parent/child tables?
10046 will likely show you what blocks you repeatedly wait for and there are other hot block scripts around as well.
0 Comments
1, Block sizes. It remains possible in 10g to choose completely mad block sizes - for example the 5k block size we have here.
SYS 27-APR-04@block>show parameter block
NAME TYPE VALUE
------------------------------------ ----------- =
----------------------------
db_block_buffers integer 0
db_block_checking boolean FALSE
db_block_checksum boolean TRUE
db_block_size integer 5120
db_file_multiblock_read_count integer 16
SYS 27-APR-04@block>show release
release 1001000200
SYS 27-APR-04@block>
This would not be a sensible thing to do in production. Why mention it? Well you might be thinking that Oracle can only use the traditional 2,4,8,16 or 32k block sizes. This is just not true. More to the point this page on Steve Adam's excellent web site gives suggestions as to how sensibly to set the block size for a database.
2, The old separating indexes and data for performance reasons is a myth chestnut came up again. Enough already. the two have no automagic correlation, if you have io contention find out where it is and separate those segments. Mean time some things to wonder about.
where does an IOT go then?
what about a bitmap join index?
what about parent/child tables?
10046 will likely show you what blocks you repeatedly wait for and there are other hot block scripts around as well.
Thursday, April 15, 2004
odbc drivers. For those of you on windows, or using windows clients it appears that the latest odbc drivers get posted to The ODBC download page on technet considerably before they appear on metalink.
0 Comments
Thursday, April 08, 2004
10g sqlplus.
Connor McDonald points out on his pages that there is no longer a requirement to enclose connection strings in quotes when starting sqlplus from the command line.
In addition an enhancement to 10g now ensures that login.sql and glogin.sql are read whenever sqlplus issues a connect as well as at startup. This means that it is now feasible to set the sql prompt to something meaningful and not have it mislead you if you change the db you are connected to (from dev to prod for example).
This is almost certainly what they had in mind as there are some useful predefined variables now for exactly this purpose.
_DATE contains the current date (dynamic) by default or else a fixed string
_PRIVILEGE contains the sysdba or sysoper priv if specified at connect
_USER contains the current username
_CONNECT_IDENTIFER contains the connection identifer used to connect
so my login.sql which reads
SET FEED OFF
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SET FEED ON
set sqlprompt "_USER' '_DATE '@'_CONNECT_IDENTIFIER>"
Gives me a sqlprompt of the form
<username> 08-APR-2004 13:55@<dbname>>
which is nice for those which database am I on moments.
0 Comments
Connor McDonald points out on his pages that there is no longer a requirement to enclose connection strings in quotes when starting sqlplus from the command line.
In addition an enhancement to 10g now ensures that login.sql and glogin.sql are read whenever sqlplus issues a connect as well as at startup. This means that it is now feasible to set the sql prompt to something meaningful and not have it mislead you if you change the db you are connected to (from dev to prod for example).
This is almost certainly what they had in mind as there are some useful predefined variables now for exactly this purpose.
_DATE contains the current date (dynamic) by default or else a fixed string
_PRIVILEGE contains the sysdba or sysoper priv if specified at connect
_USER contains the current username
_CONNECT_IDENTIFER contains the connection identifer used to connect
so my login.sql which reads
SET FEED OFF
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SET FEED ON
set sqlprompt "_USER' '_DATE '@'_CONNECT_IDENTIFIER>"
Gives me a sqlprompt of the form
<username> 08-APR-2004 13:55@<dbname>>
which is nice for those which database am I on moments.
Anyone out there tempted to run 10g and 9.2 on the same windows machine (presumably earlier versions as well) might note a gotcha I caught today - namely that the earlier version of dbca will not run successfully without removing the 10g Oracle Home and any associated 10g JRE paths from the %PATH% environment variable. This is a known bug, but as the status is non-public I can't confirm if it is windows specific.
0 Comments
Tuesday, April 06, 2004
The standard management pack for OEM, which contained the basics of OEM like the events and alert engines would appear to have become chargeable under 10g (actually it appears no longer to exist).
0 Comments
