Databases

Taken from http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB, BDB, and NDBCLUSTER.

For InnoDB and BDB tables, MySQL only uses table locking if you explicitly lock the table with LOCK TABLES. For these storage engines, we recommend that you not use LOCK TABLES at all, because InnoDB uses automatic row-level locking and BDB uses page-level locking to ensure transaction isolation.

DBAs often need to know if they should install Oracle 32-bit or 64-bit software on a given server. If the operating system support 64-bit applications, the 64-bit Oracle distribution can be loaded. Otherwise, the 32-bit distribution must be installed. The following Unix commands will tell you whether your OS is running 32-bits or 64-bits:

Solaris - isainfo -v (this command doesn't exist on Solaris 2.6 because it is only 32-bits)

HP-UX - getconf KERNEL_BITS

AIX - bootinfo -K

You can find lots of online resources which describe how to mirror an MSSQL database, however the ones I've seen all manage to fail to explain all the necessary prerequisites. The first step to correctly configure a database for mirroring is to take a full backup of the database and another backup of its transaction log file:

BACKUP DATABASE my_db TO DISK='c:\my_db_full.BAK' WITH INIT 
BACKUP LOG my_db TO DISK='c:\my_db_log.TRN' WITH INIT 

Then we need to restore the database (but not recovering it, so we keep it closed for mirroring) on our destination site:

The most efficient way to remove duplicate rows from a table is by using what we database freaks call 'CTAS'. It is an exotic acronym for the SQL statement 'CREATE TABLE AS'. Imagine table T1 contains duplicate rows and we want to create table T2 as a non-duplicate version of T1. The syntax is as follows:

CREATE TABLE T1 AS SELECT DISTINCT * FROM T2;

In Oracle this will generate lots of temp information so be sure you provide an ample temp tablespace!

You can use Oracle's explain plan to tune your queries by viewing how Oracle is using indexes or dividing the query. You must first build your plan table (in your schema) by issuing the following command:

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.

Now you can 'ask' Oracle how it is processing your SQL statements:

SQL> select * from dual;

D
-
X

SQL> explain plan for select * from dual;

Explained.

SQL> delete plan_table;
SQL> explain plan for select /*+ rule */ * from test_for_ep where a = 5;

Explained.

To create a linked server you can use the following T-SQL:

USE MASTER
GO

EXEC master.dbo.sp_MSset_oledb_prop 'OraOLEDB.Oracle', 'AllowInProcess', 1
GO
EXEC sp_addlinkedserver
@server = 'ORA_DB',
@srvproduct = 'Oracle',
@provider = 'OraOLEDB.Oracle',
@datasrc = 'ORA_DB'
GO
Exec sp_serveroption 'ORA_DB' , 'rpc' , TRUE
GO
Exec sp_serveroption 'ORA_DB' , 'data access' , TRUE
GO
Exec sp_serveroption 'ORA_DB' , 'rpc out' , TRUE
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'ORA_DB',
@useself = 'False',
@rmtuser = 'madvip',

To query data from a remote SQL Server you need to first setup a linked server. Then you can query the linked server by using the following command:

SELECT *
FROM linked-server.database-name.schema-name.table-name

As most of you already know, deleting rows from a table doesn't deallocate space. The only way to shrink the datafiles is by using the ALTER DATAFILE...RESIZE statement, but we must know by how much we can shrink it! Tom Kyte provided this nifty script which will generate the ALTER DATABASE commands to shrink datafiles to their smallest possible size:

column value new_val blksize
select value 
  from v$parameter 
 where name = 'db_block_size'
/

select 'alter database datafile ''' || 
 file_name || ''' resize ' ||
 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )
 || 'm;' cmd

Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:

* Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.

If you have a very time consuming DDL, such as creation of an index on a very big table, I recommend to enable PARALLEL DDL. This enhances the performance by a big factor. Just put this command before the DDLs:

ALTER SESSION ENABLE PARALLEL DDL;

As a test I have created an index preceded by the above statement. Notice how the DDL is parallelized:

 1   ?     3520 ora8i    156 24   324M 52928K sleep    7:18 23.22 23.18 ora_p000_my_db
 1   ?     3522 ora8i    156 24   324M 52928K sleep    5:53 22.20 22.16 ora_p001_my_db