MySQL InnoDB statistics gathering (analyze table)

Posted by David Yahalom under MySQL

I’ve seen many MySQL DBAs completely unaware of the fact that like most modern RDBMS systems, MySQL also relies on using statistics for keeping track of data distribution in tables and for optimizing join statements.

The syntax for analyzing a table for statistics is very simple:

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE <em class="replaceable"><code>tbl_name</code></em> [, <em class="replaceable"><code>tbl_name</code></em>] ...

• You must hold insert and select privileges on the table you want to analyze.
• On MyISM tables this will result in a READ LOCK on the table.
• On InnoDB tables this will result in a WRITE LOCK on the table.

Analyzing data distribution in tables is relevant to both MyISM and InnoDB tables.

The equivalent to the above command is using:

myisamchk --analyze

…on MyISM tables and

mysqlcheck -Aa -uroot -p

or

mysqlcheck --analyze --all-databases<strong>

..on InnoDB tables.

One more thing, keep in mind that unlike myisamchk, mysqlcheck can run online while the database is open.

Installing Oracle 9 on RHEL4

Posted by David Yahalom under Linux, Oracle

Installing Oracle 9i on RHEL4 isn’t difficult but requires a certain set of pre-installation actions that aren’t very well documented in the Oracle official installation readme.

This could lead to allot of frustration and hair pulling during installation if you don’t follow the exact set of actions described in this document.

The following errors are known errors which may appear during the Oracle 9i installation on RHEL4. Usually, the cause for these problems is a bad configuration or package dependency at the O/S level. These errors can usually be easily fixed but following the procedure described in this document.

compat-gcc-32 is needed by compat-oracle-rhel4-1.0.5.i386

error invoking target 'install' of makefile
$ORACLE_HOME/sqlplus/lib/ins_sqlplus.mk

error invoking target relink of makefile
$ORACLE_HOME/precomp/lib/ins_precomp.mk

GLIBC_2.0 not defiend in file libc.so.6 with link time reference...

Installing RHEL4/ CentOS 4.1

Continue to installing RHEL4/CentOS 4.1 normally as you would usually do just make sure that you have allocated at least 2GB of swap space, disabled SE Linux (THIS IS CRITICAL) and installed the following packages:
X Window System, GNOME, Editors, Server configuration tools, Administration tools, development tools and system tools.

Create users and groups:

As with any Oracle installation we will need to create two groups and one user before we can continue with the installation.

To do so we will run the following commands as root:

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle

This creates two Linux groups named oinstall and dba and a user called Oracle that is made a member of these two groups.

Create the directory structure:

Before we extract and install the Oracle software we will need to create the directory structure first. You can obviously use any directory structure you want but the dir structure I’m using here is popular amongst many DBAs.

Run the following commands as root:

mkdir /oracle
mkdir /oracle/9i
chown -R oracle:oinstall /oracle
mkdir /osource
chown -R oracle:oinstall /osource

This creates two directories (/oracle and /osource). The oracle home will be located in /oracle and the raw installation files in /osource.

Now we will edit the bash_profile file for the Oracle user located in /home/oracle/.bash_profile and add the following lines at the end:

#### Oracle 9i parameters ####
ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/9i
ORACLE_SID=[ENTER SID NAME HERE]
PATH=$ORACLE_HOME/bin:$PATH:.
LD_LIBRARY_PATH=$ORACLE_HOME/lib;
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib;
LD_ASSUME_KERNEL=2.4.19;</code>

export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
export LD_LIBRARY_PATH
export CLASSPATH
export LD_ASSUME_KERNEL

Download Oracle 9i for Linux:

Download the Oracle CDs from otn.oracle.com. The version I’m using in this tutorial is 9.2.0.4.

Beside the Oracle CDs you will also need to download an additional compatibility patch from Metalink. Patch number 4198954. This patch contains two RPMs that we need to install:

compat-oracle-rhel4-1.0-5.i386.RPM
compat-libcwait-2.0-2.i386.RPM

But more on this later on. For now, just download the zip file from the note.

Save all files in the /osource directory we created earlier.

Unpacking the files:
After we have downloaded the Oracle CDs we will need to extract the files inside.

To do this run the following commands as the oracle user:

Cd /osource
gunzip ship_9204_linux_disk1.cpio.gz
gunzip ship_9204_linux_disk2.cpio.gz
gunzip ship_9204_linux_disk3.cpio.gz

Followed by:

cpio -idmv < ship_9204_linux_disk1.cpio
cpio -idmv < ship_9204_linux_disk2.cpio
cpio -idmv < ship_9204_linux_disk3.cpio

(CPIO is an archive utility usually used for tape backups. You can read more about it here.)

After successful completion of the above steps you should now have three directories inside /osource named “Disk1″, “Disk2″ and “Disk3″.

Configure host parameters:

Make sure /etc/hosts contains a FQN of this server. If the hostname of the server isn’t pingable from the server itself, you’ll have problems later on.

[IP_ADDR] [FQ machine name] [machine name]

Configure kernel Parameters:

Login as root and add the following lines to /etc/sysctl.conf file:

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_max = 262144

Make sure there is a cartridge return after the last line. This is important.

Save the file and run /sbin/sysctl -p to apply the new values to the currently running host.

Add the following lines to /etc/security/limits.conf file:

oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384

Pre installation configuration:

As with Metalink note 303859.1 there are additional packages required before we can continue with our Oracle installation.

All the required packages are on the RHEL CDs but they might not have been installed during the initial O/S installation (depending on the selections you
made during package selection).

RHEL Disk 2 / Centos 4.1 Disk 1
cd /media/cdrom/CentOS/RPMS
OR
cd /media/cdrom/RedHat/RPMS

rpm -Uvh xorg-x11-deprecated-libs-6*
rpm -Uvh make-3*
rpm -Uvh compat-libstdc++-33-3*

RHEL Disk 3 / Centos 4.1 Disk 2
cd /media/cdrom/CentOS/RPMS
OR
cd /media/cdrom/RedHat/RPMS

rpm -Uvh xorg-x11-devel*
rpm -Uvh xorg-x11-deprecated-libs-devel-6*
rpm -Uvh gcc-c++-3*
rpm -Uvh gnome-libs-1*
rpm -Uvh freetype-devel*
rpm -Uvh compat-db*
rpm -Uvh gcc-3*
rpm -Uvh fontconfig-devel*

RHEL Disk 4 / Centos 4.1 Disk 3
cd /media/cdrom/CentOS/RPMS
OR
cd /media/cdrom/RedHat/RPMS

rpm -Uvh compat-libgcc-296-2*
rpm -Uvh openmotif21-2*
rpm -Uvh compat-gcc-32-c++-3*
rpm -Uvh compat-gcc-32-3*
rpm -Uvh compat-libstdc++-296-2*
rpm -Uvh libaio-devel-0*
rpm -Uvh libaio-0*
rpm -Uvh gnome-libs-devel-1*

It is okay if during installations of some of these packages you’ll receive a message indicating some of these packages are already installed.

Now we’ll install the Metalink patch (4198954) we downloaded earlier. Extract the two packages located in the patch and run the following commands:

rpm -Uvh compat-libcwait-2*
rpm -Uvh compat-oracle-rhel4-1*

Be advised that the second rpm is actually only a placeholder for checking if certain RPMs are installed on your system. If during the installation of either of the two RPMs above you get an error indicating a dependency error (such as “compat-gcc-32 is needed by compat-oracle-rhel4-1.0.5.i386″) DO NOT CONTINUE! Go back to the beginning of this document and check if there was anything you may have accidentally missed. The installation will not work otherwise.

Installation:

Now you are ready to continue with the graphical installation of Oracle 9.

Quickly find foreign-keys depended tables

Posted by David Yahalom under DB2 LUW

Today I needed a quick way to find out all of the tables foreign keys are referencing for a given table. That is, if I have a table named TABLE1 which has a few foreign keys defined on it, I want a list of all the tables these foreign keys are referencing.

The easier way to do this is by querying the the database catalog tables (’data dictionary’). It took me some googleing to find out exactly what DB2 system catalog contains the relevant foreign key information.

SYSCAT.SYSRELS

You can easily use the following query to get a list of all tables referenced via the foreign keys of a given table(s):

select distinct reftbname
from sysibm.sysrels
where tbname in ('TABLE1', TABLE2', 'TABLE3')

Truncate in DB2, DB2 CLP tricks, simple DB2 tracing

Posted by David Yahalom under DB2 LUW

Truncate table in DB2

One of the things I really missed when going DB2 from Oracle is the truncate table command.

In Oracle you have the option to quickly truncate a table using the truncate table command. This is a really good feature as the difference between doing truncate or a DML delete is huge in terms of performance.

You have to remember that when doing a truncate you are actually doing a non-logged delete of the table which is much less I/O intensive then a regular DML delete. This is most useful if you want to truncate a table during work hours without increasing server load.

However, apparently, DB2 does have a truncate feature, the command is just not as straightforward as writing truncate table in Oracle.

To truncate a table in DB2, simply write:

alter table schema.table_name activate not logged initially with empty table

From what I was able to read, this will delete the table content without doing any kind of logging which will go much easier on your server’s I/O.

Change the DB2 CLP prompt

Working with multiple servers or databases in DB2 can be dangerous if you are using the command line interface and forget which session is connected to which server.

So, in order to save yourself allot of future headaches and lost sleep hours while trying to mount restore tapes you can issue the following command which will change the DB2 CLP prompt to a meaningful (user@database):

db2set DB2_CLPPROMPT=(%da@%d):

You can get creative and also use the following environment variables in building your custom DB2 prompt:

%d = Currently connected database name
%i = Attached instance name
%ia = Authorization ID of the current instance
%n = Newline (for a two line prompt)
%da = Authorization ID of the current database

Nifty DB2 CLP commands you usually forget:

history

Lists the last 20 commands entered and prefixes each with a number. The maximum number of commands kept in memory can be customized with the DB2 registry variable DB2_CLP_HISTSIZE.

runcmd command_number_n
[/sourcode]

Re executes command number n from the list given by the history command. If n is not specified (or n = -1), the previous command is invoked.

[sourcecode language="sql"]
edit command_number_n
[/sourcode]

Edits the command number n using an editor defined by the DB2 registry variable DB2_CLP_EDITOR. If not set, this uses the vi editor on Linux/UNIX and Notepad on Windows.

[sourcecode language="sql"]
Exclamation mark (!)
[/sourcode]

This is the escape character that lets you issue operating system commands from within the CLP interactive mode

How to monitor locks:

[sourcecode language="sql"]
1) db2 update monitor switches using LOCK on
2) db2 get snapshot for locks on opal_d > /tmp/locks.txt
3) db2 reset monitor all

Do a simple DB2 trace

DB2 has several ways to do an SQL query trace. This method is using the explain plan with snapshotsql prefix:

1) Create a text file with the query you want to trace. For example, create a file named c:\query1.sql and paste your query inside.

2) Add the following text before the SELECT operator:

explain plan with snapshot for

So that your query will look something like this:

explain plan with snapshot for
SELECT count(*) from syscat.tables;

3) Run the following command:

db2 -tvf c:query1.sql

This will prepare the execution plan and save it in the explain tables within the system catalog.

4) Run the following command which will output the execution plan to a txt file:

db2exfmt -d database_name -u username password -1 -o c:explain_plan.txt

How to set a single table read-only in Oracle

Posted by David Yahalom under Oracle

It is easy to set an entire TABLESPACE read-only in Oracle by using the following command:<

ALTER TABLESPACE tbs_name READ ONLY;

But what about setting a single table in read only mode?

Usually the answer was “you can’t”. That is, however, not completely true.

While there isn’t any dedicated syntax in Oracle for setting a table read only, there is a quick and easy workaround. Simply create a check constraint on the table while specifying disable validate.

ALTER TABLE table_name ADD CONSTRAINT table_name_read_only check(1=1) disable validate;

Trying to insert, delete or update that table would yield the an ORA-25128: No insert/update/delete on table with constraint disabled and validated error and prevent the DML operations.

Solve DB2 “stuck” shared quiesce problems

Posted by David Yahalom under DB2 LUW

I knew this day was coming and I was just too afraid to admit it. After years of experiencing all kinds of strange Oracle problems (ORA-600 this, Ora-600 that) I thought moving on to a simpler architecture (DB2) would prove more weirdness-resistant. Actually, I knew all long this is not true but I liked the false sense of hope it gave me. -sigh- Anyway, I’m giving DB2 an official “pain in the ass” medal for the problems it caused me today. Oracle earned its fair share of these medals in the past too, so DB2 should feel just at home.

Today, one of our developers came to me saying that a DML operating she wrote is “hanging” and the table on which this operation was issued was non-responsive. Just as she walked into my room the “hang” released and we were able to query the problematic table again. We wrote some quick queries to verify all is well. Ten seconds after that I received a phone call from another developer saying that he is received a “SQL0290N Table space access is not allowed. SQLSTATE=55039″.

Okay, so something went bad with the Quiesce process in DB2?

Relaxed, I thought the problem is probably caused because the table is in load pending state or a quiesce state. So I quickly did a list tablespaces command and saw the tablespace #3 is indeed in quiesced shared mode.

The handbook procedure in such cases is to do a quiesce reset on any table from the problematic tablespace. Well, I tried that and guess what? Nada. While the command itself executed okay, the result was more of the same. Table space access is not allowed errors.

This seems like a phantom quiesce mode in DB2 (a wonderful architectural “feature” DB2 probably learned from Satan himself, nonetheless). In such a case, there are a few more trick to try like to try and reacquire the quiesce by the same user that caused it and then to reset it or to try a load terminate operation. Neither of these worked. Tablespace is still in quiesce mode. Maybe putting the tablespace in backup pending mode should do the trick? No such luck. You simply can’t because the table is already Quiesced shared.

The solution that worked for me, after doing a lot of guesswork (and I’ll put money that it will probably work for you in such situations as well) is:

1) Do a list tablespaces show detail command to find out the tablespace that is in QUIESCE SHARED mode.

Tablespace ID = 3
Name = PROD_TBS1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0001
Detailed explanation:
Quiesced: SHARE
Total pages = 2723840
Useable pages = 2723808
Used pages = 2181056
Free pages = 542752
High water mark (pages) = 2708288
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Number of quiescers = 1
Quiescer 1:
Tablespace ID = 3 <<--- This is the tablespace id that is in Quiece mode
Object ID = 12 <<--- This is the table inside the tablespace that is causing the QUIESCED SHARED

2) Run this query to find out which object is loking the tablespace in QUIECE SHARED mode:

SELECT SUBSTR(TAB1.DEFINER,1,15) AS DEFINER, SUBSTR(TAB2.TABSCHEMA,1,15) AS TABSCHEMA,SUBSTR(TAB1.TABNAME,1,15) AS TABNAME, SUBSTR(TAB2.TBSPACE,1,15) AS TBSPACE_NAME FROM SYSCAT.TABLES TAB1, SYSCAT.TABLESPACES TAB2 WHERE TABLEID=OBJECT_ID AND TAB2.TBSPACEID=TABLESPACE_ID AND TAB1.TBSPACEID=TAB2.TBSPACEID;

This is the result you will receive:

DEFINER TABSCHEMA TABNAME TBSPACE_NAME
--------------- --------------- --------------- ---------------
DB2INST9 TABSCM TABLE1 PROD_TBS1

There we have it. The problematic table name.

3) Now, lets try what we tried before, but this time, instead of just using a “quiesce table” command on a random table from the problematic tablespace, lets use it on the same table that is causing the problems.

db2 quiesce tablespaces for table TABSCM.TABLE1 share

Now we have re-quiesced the table back with the same locking level as it was originally quiesced with (shared).

3) And in order to get rid of the annoying quiesce do a:

db2 quiesce tablespaces for table TABSCM.TABLE1 reset

After this all is back to normal.

The reason I’m annoyed is because there is nothing online that suggests doing this. There are bits and bytes of info here and there but nothing concrete. Even IBMs solution for this is to simply to Re-Quiesce the tablespace (and not the table itself):

To remove a phantom quiesce:

1. 1 Connect to the database with the same user ID used when the quiesce mode was set.
2. Use the LIST TABLESPACES command to determine which table space is quiesced.
3. Re-quiesce the table space using the current quiesce

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0002003.htm

Monitor locks in DB2

Posted by David Yahalom under DB2 LUW

Since locking in DB2 can be a real problem if not managed correctly, it is very important to keep a close eye on row and table locks.

I know of two easy ways to monitor locks.

The easiest one is simply to execute the following query from the DB2 CLI:

SELECT SUBSTR(appl_name, 1,15) as APPLICATION_WAITING,
SMALLINT(agent_id) as USER_WAITING_ID,
SUBSTR(authid,1,15) AS USER_WAITING,
SMALLINT(agent_id_holding_lk) AS USER_HOLDING_ID,
lock_mode AS HOLDING,
lock_object_type AS TYPE,
lock_mode_requested AS REQUESTED_LOCK_MODE
FROM SYSIBMADM.LOCKWAITS

Then use this to drill down to a specific lock for more details:

SELECT appl_status,
SUBSTR(PRIMARY_AUTH_ID,1,10) AS USER_HOLDING_LOCK,
SUBSTR(appl_name, 1,15) AS APP_HOLDING_LOCK,
SUBSTR(client_nname,1,20) AS CLIENT_HOLDING_LOCK
FROM SYSIBMADM.SNAPAPPL_INFO
WHERE AGENT_ID = <>>

Another way to monitor locks which will result in more detail displayed is:

1) Allow DB2 to monitor locks in the database:

db2 update monitor switches using LOCK on

2) Run the following command which will output the monitoring statistics to a text file of your choice:

db2 get snapshot for locks on database_name > /tmp/locks.txt

3) Keep in mind that there is a certain amount of overhead involved while using the monitor locks option, this is why I always turn the locking monitor off when it is not needed.

db2 reset monitor all

IBM migration toolkit

Posted by David Yahalom under DB2 LUW

I want to recommend a great free tool from IBM called the “IBM migration toolkit” (now avail. in V2 beta).

This GUI tool allows you to migrate data from Oracle, SQL and a varaity of other databases to IBM DB2 (both LUW and Z/OS). I found this very useful with my DB2 deployment and if you are working in a place where you need to transfer data from Oracle/SQL server to DB2, try this tool fist before going to higher-end (pricey) solutions as it might fit your needs.

“The IBM Migration Toolkit is an easy-to-use tool that allows you to migrate your data from a wide variety of source databases to either DB2 or Informix Dynamic Server, regardless of platform.”

Great Oracle RLS article

Posted by David Yahalom under Security, Oracle

Yesterday I stumbled across a great article about Oracle Row Level Security (RLS) with a great and detailed beginners tutorial.

RLS is a great way to implement application security where many users must connect to the same data but only see different parts of the data based on their role or position in the organization.

In many cases RLS is easier to implement and maintain compared to using views, tirggers or using different schema for data access as business rules and security implementations are done through one PL/SQL procedure per table instead of being spread throughout the applications code.

Another great benefit of RLS is tighter security than what views or triggers provide because RLS can prevent a user from circumventing the security policy by accessing the database directly and query the tables underlying the views.

Oracle Row Level Security: Part 1

Is my DB2 optimizer a tad drunk?

Posted by David Yahalom under DB2 LUW

Today I have noticed a strange phenomena in one of our DB2 databases. When doing a low zero-selectivity query (that is, query without a WHERE clause), the DB2 optimizer preferred to use an index scan rather than do a table scan.

That seemed a bit strange so I tried running the same query twice. Once without any indexes on the underlying table (to force a table scan) and then again with (to allow the optimizer use an index).

The results really surprised me.

According to the optimizer, the table scan query will perform slower than the index scan query.

I know that with Oracle systems, the rule of the thumb is that using a full table scan would perform faster than an index scan when the total number of rows retrieved from a table is between 1% and 15% of table data.

It is very logical. If the database has to read the entire table wouldn’t it be faster just to read the blocks directly from the table container rather than doing an INDEX READ + ROWID lookup for the entire table data?

The way I see it:

SELECT * FROM TABLE

Performing a TABLE SCAN to get the data: X I/O blocks read from the table.
Performing an INDEX SCAN to get the data: X I/O blocks read from the table + Y I/O blocks read from the index.

Or am I wrong?

Any of you can shed some light on why the DB2 optimizer reports the table scan as slower than an index scan in this case?

Here is the real world example of what I mean:

SELECT COUNT *
FROM TABLE1;

Using an INDEX:

Access Plan:
-----------
Total Cost: 72284.4
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
95047
FETCH
( 2)
72284.4
26250.8
/---+---
95047 95047
RIDSCN TABLE: USER
( 3) TABLE1
556.668
275.886
|
95047
SORT
( 4)
556.667
275.886
|
95047
IXSCAN
( 5)
427.788
275.886
|
95047
INDEX: USER
TABLE1_IDX1

Without an INDEX:

Access Plan:
-----------
Total Cost: 73526
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
95047
TBSCAN
( 2)
73526
27725
|
95047
TABLE: USER
TABLE1