MySQL server sql tuning.

1. When you need to tune MySQL sql clauses first thing to do is set slow query and general query logs on:

Slow query log shows queries that take more than 'long_query_time' parameters (you can change this also) time to execute. And General query log contains all SQL statements received from clients.
But remember that these log will generate overhead so it is best to do this kind of tuning in test enviroment.

You can set slow query log on/off from my.cnf init file (or with starting server with these parameters) with slow_query_log[={0|1}] and optionally set the path and filename with slow_query_log_file=<path_and_log_name>  .
Mysql Servers older than 5.1.29 parameter name is 'log-slow-queries' for path and file name.

General query log is set with parameters on my.cnf (or with starting server with these parameters) general_log[={0|1}] to enable or disable, And optionally path and filename with  general_log_file=<path_and_log_name>.
Mysql Servers older than 5.1.29 parameter name is 'log' for path and file name.

After changes in my.cnf restart the MySQL server.

And  from the 5.1 version you can change these logs on the fly with these commands:

mysql> SET GLOBAL slow_query_log = ON;       (or set global slow_query_log=1; )
mysql> SET GLOBAL slow_query_log = OFF;      (or set global slow_query_log=0;)

mysql> SET GLOBAL general_log = ON;              (or set global general_log=1; )
mysql> SET GLOBAL general_log = OFF;            (or set global general_log=0;)

And logpaths can be seen from (check: log_output if this is file then check: general_log_file, slow_query_log_file) :
mysql> show variables;  



After you have set logs on you run normal load into database and check the logs. From slow log you see which SQL statements take's the most time to run. And then you start to tune those statements that are taking most time and are runned most often.

You can use mysqldumpslow command to summarize the queries that appear in the slow queries log. This will make it easier to find problematic statements (it will count same statements etc...).
mysqldumpslow [options] <path_and_slow_log_filename>

More info about mysqldumpslow:
http://dev.mysql.com/doc/refman/5.1/en/mysqldumpslow.html




2. Tuning single SQL clauses with explain and sql profiler:

When you want to tune single problematic SQL clause first start sql profiler (this should be working from 5.0.37 and newer versions MySQL Servers.):

#Turn sql profiler on:
mysql> SET @@profiling = 1;
#To see parameter value:
mysql> SELECT @@profiling;
#Turn sql profiler off:
mysql> SET @@profiling = 0;

#After you have set sql profiler on. Run the problematic sql with explain:
#For example (this is just an example not real slow query):
mysql> explain SELECT * FROM INFORMATION_SCHEMA.TABLES ;
+----+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra                                  |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+
|  1 | SIMPLE      | TABLES | ALL  | NULL          | NULL | NULL    | NULL | NULL | Open_full_table; Scanned all databases |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+
1 row in set (0.00 sec)
----------
'possible_keys' value tell's you what kind of keys/indexes query is trying to use. And 'Extra' tell's you how MySQL is trying to resolve the query (sort the data with where, index etc..). Explain tell's you also execution time but it is better to use profiler for time comparison when tuning queries.

#Check problematic sql profile number for more info (it is the last Query_ID in profiles) (this shows the last 15 queries default but you can change that value from profiling_history_size parameter. ) :
mysql> show profiles ;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration   | Query                                           |
+----------+------------+-------------------------------------------------+
|        1 | 0.00038300 | select @@profiling                              |
|        2 | 0.00084500 | show databases                                  |
|        3 | 0.00021600 | show profile for query2                         |
|        4 | 0.15603600 | SELECT * FROM INFORMATION_SCHEMA.TABLES         |
|        5 | 0.00068200 | explain SELECT * FROM INFORMATION_SCHEMA.TABLES |
+----------+------------+-------------------------------------------------+
5 rows in set (0.00 sec)
#Check problematic sql profile (this shows you steps that are made when query is running and also time for each step.) (You can also give options for 'show profile' command (CPU, MEMORY,SWAP... ) read more from here: http://dev.mysql.com/doc/refman/5.1/en/show-profile.html ):
mysql> show profile for query 5 ;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000197 |
| Opening tables     | 0.000099 |
| System lock        | 0.000016 |
| Table lock         | 0.000050 |
| init               | 0.000052 |
| optimizing         | 0.000020 |
| statistics         | 0.000026 |
| preparing          | 0.000024 |
| executing          | 0.000038 |
| end                | 0.000022 |
| query end          | 0.000015 |
| freeing items      | 0.000038 |
| removing tmp table | 0.000020 |
| closing tables     | 0.000034 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000016 |
+--------------------+----------+
16 rows in set (0.00 sec)

#Now that you have better info what the query is doing and what is the most time consuming step (is it using correct index? Or can you change the query itself. etc...). You need to tune it and then run explain/profile steps again to see if you get improment for the query (compare first and new profile and explain plan).

#Usually making correct index is easiest solution for basic query problems. So you create new index and check if query is starting to use it and if it is doing improment for performance. (Sometimes you need to drop old indexes and/or use optimizer hint for queries to get them use correct indexes. More about index hints: http://dev.mysql.com/doc/refman/4.1/en/index-hints.html ):

mysql> create index...

mysql> explain select ...

mysql> show profiles ;

mysql> show profile for query 6 ;
.
.
.


Remember also that if you are tuning 'group by' clauses there is certain limits with those ( read more here: http://dev.mysql.com/doc/refman/5.1/en/group-by-optimization.html ). And usually big index which contains all or most of the columns of query are more efficient (but also takes more time to update). And remember to drop all unnecessary indexes.

Oracle Database and files corruption checks.

1. Corruption check with RMAN:

With RMAN you can check physical and logical corruptions.

#To check only physical corruptions:
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

#To check both logical and physical corruptions:
 RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL; 

NOTE! BACKUP VALIDATE does not take real backup it is just checking the files. But you can  use CHECK LOGICAL when you are backuping your database. You can also check backups before restoring them with RESTORE DATABASE VALIDATE or RESTORE ARCHIVELOG ALL VALIDATE;


With RMAN you can also use VALIDATE command without BACKUP. And this gives your more options  for checking and this can also check control and sp files.:

#These check both logical and physical corruptions.
VALIDATE DATABASE;
VALIDATE BACKUPSET <backupset number>;
VALIDATE DATAFILE <datafile_number> BLOCK <block_number>;



2. Corruption check with dbv (DBVERIFY utility):

There is also dbv tool in Oracle server home directory bin folder. And with it you can also check datafile and segment corruptions. dbv finds both physical and logical corruptions.
#Example (there is more options but with this you can get started. Remember that blocksize can vary between databases. You can check it from dba_tablespaces view. (HELP gives all options)):
dbv  file=<filename> blocksize=<blocksize> logfile=<filename>
dbv file=system.dbf blocksize=8192 logfile=corrupt_check.log

And of course you can make for example a shell script where you run this check for all datafiles.

You can use dbv to check certain segments:
dbv segment_id=<segment_id>  logfile=corrupt_check.log


NOTE! Whatever of these tools you are using to check corruptions all findings are added in the 
V$DATABASE_BLOCK_CORRUPTION view.

Oracle Controlfile move or rename.

If you need to move/rename Oracle controlfile this is how to do it:
#Check current controlfiles from sqlplus (as oracle user in database server):
sqlplus / as sysdba
SQL>  select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------

/data/oradata/testdb/CONTROL01.CTL
/arch/oradata/testdb/CONTROL02.CTL
/redo/oradata/testdb/CONTROL03.CTL



#Check parameter which set's the controlfiles:
SQL> show parameter control_files

NAME                                                             TYPE                                                         VALUE
-----------------------------------------------------  -------------------------------------------------  -----------------
----------------------------
control_files                                                    string                                                           /data/oradata/
testdb/CONTROL01.CTL,  /arch/oradata/testd
b/CONTROL02.CTL, /redo/oradat
a/testdb/CONTROL03.CTL



# Change control_files value in SPFILE (I change CONTROL03.CTL to CONTROL03_NEW.CTL):
SQL> alter system set control_files='/data/oradata/testdb/CONTROL01.CTL', '/arch/oradata/testdb/CONTROL02.CTL', '/redo/oradata/testdb/CONTROL03_NEW.CTL' SCOPE=SPFILE;

#Shutdown database changes take effect after you restart the database:
SQL> shutdown immediate;
SQL> exit


#Make same change from OS side (as oracle user):
In unix:
mv /redo/oradata/testdb/CONTROL03.CTL /redo/oradata/testdb/CONTROL03_NEW.CTL
In Windows you can do this change from Windows explorer.


#Restart database from sqlplus (as oracle user):
sqlplus / as sysdba
SQL> startup


#Check that controlfiles changes are ok:
SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------

/data/oradata/testdb/CONTROL01.CTL
/arch/oradata/testdb/CONTROL02.CTL
/redo/oradata/testdb/CONTROL03_NEW.CTL

Oracle Replications version support

Oracle have several ways to do data replication:
Basic Replication (materialized views),
Advanced Replication (materialized views),
Data Guard,
Streams.

And of course there is also differences between between EE edition and SE edition which replication (and how) you can use:


Basic Replication support both EE and SE


Advanced Replication is EE only.


Data Guard is EE only. (but you can create your on archivelog synchronise scripts for SE standby's)


Streams can be used in SE but only with synchronous capture (this capture only DML and is recommended for only a couple of tables.). If you plan to use Streams for many tables, entire schema or database then you should use capture process which is EE only.


If you decide to do data replication yourself for example with triggers and database link. Then those are available for all editions.

Oracle database version check.

There is several ways to check your Oracle database version.

1.Oracle Universal Installer shows you all installed products.
Unix run runInstaller or in Windows run setup.
In both OS this file can be find in $ORACLE_HOME/oui/bin .



2. But easier way is to just connect into database with sqlplus like this (as oracle user):
sqlplus system@test

#First you see the sqlplus version:
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 5 08:08:53 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

#Then you have to give password:
Enter password:

#And after that you see database server version of the database your connected:
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL>



3. When you are connected into database via sqlplus you can also check v$version view. This view shows more detailed version information of current database server:
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production



NOTE! If you have several server installations or server and client installations on same server then you should use full paths with these commands. Otherwise you can get mixed information with these commands. In sqlplus example I used different version client and database server and that is why sqlplus version and database server versions are different.