Showing posts with label MySQL for Oracle DBAs. Show all posts
Showing posts with label MySQL for Oracle DBAs. Show all posts

Monday, July 13, 2009

Moving from MySQL to an Oracle Database Server

I was asked recently in one of my blogs what are some of the pure technical advantages of moving from MySQL to Oracle. I kind of laughed when I read the question because nothing is likely to stir up more perspectives and emotions than asking about moving from one database server vendor to another. So here goes ten technical considerations of moving to an Oracle database server from a MySQL server. I'm keeping the list to pure database server technology. There are also a lot of reasons to move to MySQL as well from other database server vendors (my whole blog is on this).

Technical Advantages moving from a MySQL Server to an Oracle database server
  1. Scalability: Much higher vertical scalability and higher OLTP transaction capability. Ability to set up shared server architecture for more user connection scalability.
  2. Metadata: Lots of very detailed metadata information as well as wait events for tuning.
  3. Stored Routines: Compiled stored procedures with much higher scalability. Java and .NT stored procedure capability.
  4. SQL functionality: Lots of SQL functions and analytical SQL functions. Lots of XML functionality built into database server.
  5. Backups: Hot online backups with Oracle's Recovery Manager
  6. Automatic Management: Lots of automatic management with Automatic Database Diagnostic Management (ADDM), Advisors, Automatic Workload Repository (AWR) , Automatic Segment Management, Shared Memory Management, etc.
  7. Data Warehousing: Bitmap indexes, materialized views, analytical functions, star joins, sort-merge joins, hash joins, etc.
  8. Grid Control: Oracle Enterprise Manager is a very robust enterprise tool for managing and monitoring multiple tiers of an Oracle infrastructure.
  9. Inexpensive disk strategy: Striping and mirroring capability with Oracle's Automatic Storage Management (ASM).
  10. Flashback technology: Lots of recovery capability with Flashback technology such as Flashback Database, Flashback Table, Flashback Versions Query, etc.

Anyway, a few things to talk about over coffee and cookies. :)

Wednesday, July 1, 2009

Differences between Oracle and MySQL

Some key differences for DBAs between Oracle and MySQL database servers include:
  • Different tools used to manage and monitor database servers.
  • Oracle architecture is process based, MySQL architecture is thread based.
  • Different tools used for backup and recovery.
  • Database specific SQL syntax.
  • Database specific SQL functions.
  • Different syntax for stored routines. MySQL has no packages.
  • MySQL routines are not compiled and run in each session thread and not in global memory.
  • MySQL only supports row-level triggers.
  • Different startup and shutdown processes.
  • Oracle RAC is a shared disk solution while MySQL Cluster is a shared nothing solution.
  • The default configuration for MySQL is very lenient in terms of data integrity. A MySQL DBA must tighten down data integrity for it to work like traditional databases.
  • With Oracle, the CREATE DATABASE command is used to create the physical storage for the database server.
  • With MySQL, the mysql_install_db script is used to create the physical storage for the database server.
  • The term "database" in Oracle means all the physical files associated with an instance.
  • The term "database" in MySQL means a schema. The term database and schema can be used interchangeably in MySQL.
  • In Oracle a database user owns all the schema objects.
  • In MySQL the database schema owns all the schema objects.
  • Oracle supports role based security. With MySQL, scripts and stored routines are used to programatically organize security permissions.
  • Oracle has a lot more feature functionality that makes it very popular.
  • MySQL has a lot less functionality than Oracle that makes it very popular.
  • Oracle has tons of options for creating a table. Different characteristics and behavior are defined with these options.
  • The key with MySQL tables are defining a storage engine with a table. The storage engine defines characteristics such as row level locking versus table level locking, referential integrity, support for different types of indexes and features. Different optimizations, storage, tuning and backup and recovery are required for each storage engine. Storage engines are a KEY feature in MySQL.
  • MySQL supports different ISOLATION levels of READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ AND SERIALIZABLE.
Oracle DBAs are not just managing Oracle databases any longer. They are supporting, SQL Server, MySQL, Business applications, application servers in a lot of Oracle environments. The more software you can support in an organization the more marketable you are. Its important that Oracle DBAs be able to support the corporate environments that are running database software from multiple vendors and the software surrounding databases.

There are reasons that MySQL customers like the light weight, easy to use MySQL database server. If an Oracle DBA compares MySQL to Oracle feature by feature they are going to be very disappointed. An Oracle DBA needs to look at the feature/functionality that makes MySQL so popular. The feature/functionality is not tons of features, its that it is light weight, fast and easy to manage.

The closest analogy I can make to an experienced Oracle DBAs, is that a MySQL database server is similar in management to what an Oracle version 7 database was like. MySQL uses cache hit ratios, has minimal wait events and DBAs still need to write scripts to surround the MySQL database.

Starting up and Shutting Down a MySQL Server

There are different ways for starting up and shutting down a MySQL server. A MySQL server when it starts up it starts up all the way. When a MySQL server shuts down it shuts down all the way. MySQL cannot startup and and shutdown at different levels the way an Oracle database server can.

MySQL Optimal Configuraton Architecture

Oracle DBAs understand the importance of defining guidelines and standards and using them in the configuration and management of database servers. Years ago the Optimal Configuration Architecture (OFA) developed a base set of guidelines and naming conventions for Oracle DBAs.

I developed the MySQL Optimal Configuration Architecture (MOCA) modeled after OFA to give new MySQL DBAs a set of guidelines and standards to consider when configurating MySQL database servers.

Managing MySQL Storage

Storage needs to be managed for any MySQL instance. A MySQL database server does not have the same flexibility as an Oracle database server in terms of how to lay out the data storage. It is important to learn the data storage defaults and options.


Tuesday, June 30, 2009

Managing the MySQL Instance

An Oracle DBA needs to learn how to manage a MySQL Instance. The MySQL instance has different memory buffers and caches like any database server. Part of MySQL's uniqueness is that it has memory areas to be configured that are associated with different types of storage engines.

Learning the MySQL Architecture

MySQL is an database server and anyone managing the MySQL database server has to learn the architecture, and feature functionality like any other database server. The biggest mistake to make with a new MySQL environment is thinking it is a toy and trivial to work with. A MySQL server needs to be properly configured, tuned and managed like any other database server.

The key to learning any database server is to learn its feature/functionality and the architecture.

Oracle vs. MySQL

As MySQL continues to explode in the marketplace, there are more Oracle and SQL Server DBAs learning MySQL. For Oracle DBAs, its important to understand that MySQL is a different type of database server.
  • Oracle is the aircraft carrier of database servers. Oracle has by far more features than any other database server. Oracle supports a number of database server solutions such as Real Application Clusters (RAC), Data Guard (standby database) and Oracle Streams (replication).
  • MySQL is the speed boat of database servers. MySQL is a very light weight database server that excels in web applications. MySQL is designed to be fast, easy to use and simple to install.
I'm always asked about porting Oracle applications to MySQL or MySQL applications to Oracle. Well if someone buys an aircraft carrier they probably bought it for specific reasons and the same for a speed boat. Its very unlikely that someone is going to switch from one to the other and be happy. However, each database server has areas where it excels.

Thursday, June 11, 2009

Converting an Oracle Schema to MySQL

Both Oracle and MySQL provide a number of sample schemas so users can build different schema environments and load them with data. So I thought I'd get one of the Oracle sample schemas and go through the exercise of converting it to a MySQL schema. I could have gone in either direction, since this is more of a MySQL blog I made MySQL the destination. Before I start let me introduce some of the Oracle and MySQL sample schemas.

Oracle Sample Schemas:
  • Human Resources: (HR) is a simple schema containing tables with basic primary and foreign key relationships such as: employees, departments, jobs, locations, regions, countrys, jobs, etc. This can be extended to support Oracle Internet Directory demos.
  • Order Entry (OE) is a more complex set of tables demonstrating the different datatypes supported by Oracle. Tables dealing with products, sales, customers, etc. are used in this schema.
  • Online Catalog (OC) is a supporting schema that demonstrates object-relational database objects built inside the OE schema.
  • Product Media (PM) is a schema that supports multimedia datatypes. Audio, video, images, large text data are all used in this schema.
  • Queued Shipping (QS) to demonstrate Oracle Advanced Queuing capabilities. Advanced Queuing and XML are used in this schema.
  • Sales History (SH) is a schema supporting a large number of tables. Features like partitioning and Oracle analytics can be used with this schema.

MySQL Sample Schemas:
  • World is a basic schema using three tables City, Country and CountryLanguages that use MyISAM tables.
  • Sakila is a schema that adds additional complexity using an online DVD store to demonstrate more of the MySQL 5.1 features.
  • Employees is a schema with a much larger data set containing employees, deparments, salaries, titles, etc that are very intuitive to work with. There are approximately 4 million records to work with. Different storage engines and partitioning are often benchmarked using this schema.
Summary of changes made to convert Oracle Schema to MySQL Schema

Here is a summary of changes made to convert the Oracle schema script to a MySQL schema script. The goal here is to provide a level of understanding of converting a schema.
  • Comment out SQL*Plus specific commands.
  • Change REM to --
  • Change VARCHAR2 to VARCHAR
  • Change NUMBER to INT
  • Change NUMBER(x,x) to DECIMAL(x,x)
  • Change CONSTRAINT keyword Syntax
  • For InnoDB tables, define PRIMARY KEY as part of the CREATE TABLE command and not a separate CREATE INDEX command.
  • Make sure tables are created as InnoDB. set storage_engine=innodb;
  • Modify constraint syntax for FOREIGN KEY definitions.
  • Fully quality PRIMARY KEY column in FOREIGN KEY constraint.
  • Remove READ ONLY clause with CREATE VIEW.

Converting the Oracle HR sample schema to MySQL

I decided the Oracle HR schema would be a good sample schema to convert to a MySQL schema to demonstrate things to consider in creating schema scripts. I started with the cre_hr.sql script and converted it to a cre_hr_mysql.sql script. The Oracle cre_hr.sql script I started with can be found at:
  • http://www.oracle.com/technology/obe/sqldev_obe/osdm/files/hr_cre.sql

MySQL equivalent of the Oracle HR schema script

Here is the corresponding MySQL create script. The only thing I did not address were the sequential incremental values used with Oracle sequences. I included the Oracle CREATE SEQUENCE commands in so it would show the adjustments I made to use autoincrement but also show that the incremental values would need to be addressed. A little more work needs to be done here to verify the data loads would create the same values in an Oracle environment. As I mentioned my main goal was to give someone a feel for the code differences in creating a schema between Oracle and MySQL.

-- hr_cre_mysql.sql


/*
Notes of changes:
Comment out SQL*Plus specific commands.
Change REM to --
Change VARCHAR to VARCHAR
Change INT to INT
Change INT(x,x) to DECIMAL(x,x)
Change CONSTRAINT keyword Syntax
For InnoDB tables, define PRIMARY KEY as part of the CREATE TABLE command and not a separate CREATE INDEX command.
Make sure tables are created as InnoDB.
set storage_engine=innodb;
Modify constraint syntax for FOREIGN KEY definitions.
Fully quality PRIMARY KEY column in FOREIGN KEY constraint.
Remove READ ONLY clause with CREATE VIEW.
*/

DROP DATABASE IF EXISTS hr;
CREATE DATABASE hr;
USE hr;

CREATE TABLE regions
( region_id INT NOT NULL
, region_name varchar(25),
primary key (region_id)
);


CREATE TABLE countries
( country_id CHAR(2) NOT NULL
, country_name varchar(40)
, region_id INT
, PRIMARY KEY (country_id)
) ;


ALTER TABLE countries
ADD (FOREIGN KEY (region_id)
REFERENCES regions(region_id)
) ;

CREATE TABLE locations
( location_id INT(4)
, street_address varchar(40)
, postal_code varchar(12)
, city varchar(30) NOT NULL
, state_province varchar(25)
, country_id CHAR(2)
, PRIMARY KEY (location_id)
) auto_increment=3300;

/*
CREATE SEQUENCE locations_seq\
START WITH 3300\
INCREMENT BY 100\
MAXVALUE 9900\
NOCACHE\
NOCYCLE;\
*/

ALTER TABLE locations
ADD ( FOREIGN KEY (country_id)
REFERENCES countries(country_id)
) ;

CREATE TABLE departments
( department_id INT(4)
, department_name varchar(30) NOT NULL
, manager_id INT(6)
, location_id INT(4)
, PRIMARY KEY (department_id)
) auto_increment=280;

ALTER TABLE departments
ADD ( FOREIGN KEY (location_id)
REFERENCES locations (location_id)
) ;

/*
CREATE SEQUENCE departments_seq\
START WITH 280\
INCREMENT BY 10\
MAXVALUE 9990\
NOCACHE\
NOCYCLE;\
*/

CREATE TABLE jobs
( job_id VARCHAR(10)
, job_title VARCHAR(35) NOT NULL
, min_salary INT(6)
, max_salary INT(6)
, PRIMARY KEY (job_id)
) ;


CREATE TABLE employees
( employee_id INT(6)
, first_name varchar(20)
, last_name varchar(25) NOT NULL
, email varchar(25) NOT NULL
, phone_INT varchar(20)
, hire_date DATE NOT NULL
, job_id varchar(10) NOT NULL
, salary DECIMAL(8,2)
, commission_pct DECIMAL(2,2)
, manager_id INT(6)
, department_id INT(4)
, UNIQUE KEY (email)
, PRIMARY KEY (employee_id)
) auto_increment=207;

DELIMITER //
CREATE TRIGGER emp_sal_min BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF (salary <= 0 ) THEN call unknown(); END IF; END// DELIMITER ; ALTER TABLE employees ADD ( FOREIGN KEY (department_id) REFERENCES departments(department_id)); ALTER TABLE employees ADD ( FOREIGN KEY (job_id) REFERENCES jobs (job_id)); ALTER TABLE employees ADD ( FOREIGN KEY (manager_id) REFERENCES employees(employee_id)) ; ALTER TABLE departments ADD ( FOREIGN KEY (manager_id) REFERENCES employees (employee_id)) ; /* CREATE SEQUENCE employees_seq\ START WITH 207\ INCREMENT BY 1\ NOCACHE\ NOCYCLE;\ */ CREATE TABLE job_history ( employee_id INT(6) NOT NULL , start_date DATE NOT NULL , end_date DATE NOT NULL , job_id VARCHAR(10) NOT NULL , department_id INT(4) , PRIMARY KEY (employee_id, start_date) ) ; DELIMITER // CREATE TRIGGER jobhist_dates BEFORE INSERT ON job_history FOR EACH ROW BEGIN IF (end_date > start_date ) THEN call unknown();
END IF;
END//

DELIMITER ;

ALTER TABLE job_history
ADD (FOREIGN KEY (job_id)
REFERENCES jobs(job_id));


ALTER TABLE job_history
ADD ( FOREIGN KEY (employee_id)
REFERENCES employees(employee_id));

ALTER TABLE job_history
ADD (FOREIGN KEY (department_id)
REFERENCES departments(department_id)) ;


CREATE OR REPLACE VIEW emp_details_view
(employee_id, job_id, manager_id,
department_id, location_id, country_id,
first_name, last_name, salary,
commission_pct, department_name, job_title,
city, state_province, country_name, region_name)
AS SELECT
e.employee_id, e.job_id, e.manager_id,
e.department_id, d.location_id, l.country_id,
e.first_name, e.last_name, e.salary,
e.commission_pct, d.department_name, j.job_title,
l.city, l.state_province, c.country_name, r.region_name
FROM
employees e, departments d, jobs j,locations l,
countries c, regions r
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id
AND c.region_id = r.region_id
AND j.job_id = e.job_id ;

Saturday, February 16, 2008

MySQL and Oracle Parallels

When I look at the current and future releases of MySQL I see a lot of parallels to Oracle releases. Although MySQL and Oracle have taken different approaches there is a thread of commonality.
  • MySQL 4 and Oracle 6 releases were acceptable releases for their time but their features limited the software from having a larger impact in the database market. I always joke that Oracle Version 6 made Sybase and Oracle 7 along with Sybase 10 helped destroy Sybase.
  • MySQL 5 and Oracle 7 took the database releases so they could take new large steps in the database market.
  • MySQL 6 and Oracle 8 are releases that take the database software for each company to gigantic new levels in terms of scalability and the ability to handle significantly larger systems. In terms of performance, high availability and reduces administration.
I believe the future of MySQL with version 5 and the upcoming version 6 is very bright.

Monday, January 21, 2008

Stored Procedures in MySQL

Stored procedures were introduced in the MySQL database server in version 5.0. It has been interesting to see the growth of stored procedures in the MySQL community because it is completely different with how stored procedures grew in the Oracle community.

Stored Procedures in Oracle
Stored procedures were introduced in Oracle 7, back in the client/server glory days. DBAs and developers using Oracle Forms and Reports seen the immediate benefit of using stored procedures in the database and PL/SQL stored procedures became very popular. A large part of this was due to developers considered themselves "database developers" so they tried to always leverage the features in the database. The PL/SQL language has grown through all the releases of Oracle and now in Oracle 11g Database Server, PL/SQL is still extremely popular and experienced Oracle DBAs and developers find tremendous benefits in writing stored procedures.

Stored Procedures in MySQL
I have not seen a great adoption of stored procedures in MySQL. I believe one of the reasons is due to the community of developers writing code for MySQL and open source applications. The core developers who write applications for MySQL are using Perl, Python, Ruby on Rails and PHP. They think first of how to write applications leveraging the benefits of the language they are working with and not the database. They write feature and GUI rich applications that run in the middle tier. So it is not natural for most of they to look and leverage the features in the MySQL database server. As web applications grow larger and access larger data sets this approach can effect performance as applications grow popular.

Remember the Wisdom of the Ancient Ones
If you study history or watch the history channel you are probably aware that long time ago, there were a group of ancient ones who had acquired great knowledge that has slowly been lost through the ages. They were called the Main Framers. One of the core beliefs of these ancient ones was that the closer you put your applications to the data the faster they run if they are data intensive. Slowly with the growth of client/server and then multi-tiered applications, this great wisdom has been slowly forgotten over time. There are new generations of developers who now even question if the people known as the Main Framers even really existed or if it is a myth.

The Benefits of using Stored Procedures
When writing data intensive algorithms, the most efficient place to run this code is in the database server. Runtime environments leverage the execution of the application code, not the database code. It does not make sense to access data, send it across the network, then filter and massage it in the middle tier application code. It is best to run this data intensive code in the database server, filter it and massage it in the database server and send what needs to be displayed to the middle tier. This approach will reduce network traffic and leverage what the database server and the middle tier each do best.

Growth of Stored Procedures in MySQL
Version 5 of MySQL introduced stored procedures with basic functionality. In version 6 of MySQL and future releases you can expect to see a lot of enhancements that will continue to leverage running data intensive code in the database server.

It would be wise for open source developers to look at how they can filter and massage data in the database server and send to the middle tier only the data they need to process and display for their application.

Sunday, September 23, 2007

Looking at MySQL and Oracle















I am going to build a list of features, commands and concepts from MySQL and Oracle. This comparison will not go into the pros and cons or which database has more options or features. The goal is to give someone a feel for the differences. I am a big evangelist for Oracle and MySQL technologies. This is a list I will build over a period of time.

The following are websites for downloads, documentation, code, blogs, forums, whitepapers, newsletters, etc. Be careful about going to these sites you will get hooked and spend hours on these two great sites.
Feature/Functionality

The Oracle Database Server is the flagship of large enterprise databases. Oracle excels at the level of data warehouses or running large enterprise applications like the financials for a Fortune 100 company. At the same time MySQL is generating a lot of momentum because customers are seeing they can implement it at the fraction of the cost and complexity of Oracle. Each database has their place in the market in which they fit well.

MySQL does not have all the functionality of the Oracle database server, but that is also what makes it so attractive. Not everybody needs all the functionality that comes with the Oracle database server. Most companies I go to are paying for all of Oracle's functionality but only using a very small percentage of what it can offer. Also, as web applications become more popular more functionality is being built in the middle-tier versus using database functionality.

With MySQL it is either free or its commercial licenses are a fraction of Oracle's cost. MySQL environments usually require less hardware, resources and money to get them up and running. The cost comparison makes it a great option for Small and Medium Businesses (SMB), government and state organizations and for projects that do not need the enterprise flagship functionality of Oracle. As more and more web applications are being built, J2EE and Internet developers are building more functionality in the middle-tier, negating a lot of the functionality that comes with an enterprise database server.

I've been very impressed with how fast MySQL can process different types of SQL statements and how good of a job the optimizer does in the MySQL environment. The code is lighter and more efficient because it does not have as many options to wade through. Sometimes being light and nimble can offer tremendous advantages for application developers that understand how to leverage the functionality of MySQL.

Products

Below is a list of popular products used with MySQL and Oracle:
MySQL is an open source solution. Products like Eclipse, Apache and JBoss are used to offer a multi-tiered web solution using MySQL. MySQL usually uses other open source products to provide a full multi-tiered offering. LAMP or a derivative of LAMP is seen in MySQL multi-tiered environments.

Oracle offers a complete line of products with JDeveloper, Oracle Application Server, SQL Developer, etc. Third party products are available as well (i.e. Toad, SQL Navigator, UniCenter, ...). Open source products such as JBoss and Eclipse can also be used in Oracle environments. The big difference is that Oracle offers a large line of vendor commercial products. The table below only shows Oracle specific products under the Oracle section.


This table will provide an example of what a MySQL and Oracle environment may look like.













































MySQL Oracle
Eclipse Oracle JDeveloper
Struts, Spring
Application Development Framework
J2EE, PHP, Ruby on Rails,
Groovy, .NET
J2EE

Hibernate TopLink
Apache, JBoss Oracle Application Server
TCP/IP, Unix Sockets,
Shared Memory, NT Pipes
Oracle Net Services
Mysqladmin, MySQL Administrator,
Phymyadmin
Enterprise Monitor
OEM Database Control



MySQL Query Browser, Toad, NavicatOracle SQL Developer

mysql client tool
mysqlimport
mysqlcheck
mysqldump
myisamchk
myisampack
SQL*Plus client tool
SQL*Loader
dbverify
Data Pump, RMAN
dbms_repair package

MySQL (GPL)
MySQL Enterprise
Oracle Express
Oracle Standard
Oracle Enterprise
OpenLDAP, Fedora Directory Server Oracle Internet Directory


Note: The reader needs to understand the products are usually not always equivalent. Some examples include:
  • Oracle environments also support Ruby on Rails, .NET, Groovy, .NET, etc. However, the big push by Oracle is to use J2EE since Oracle Fusion applications run using J2EE. Also, organizations usually do not purchase a 100K or 1M Oracle license to run Ruby on Rails for their enterprise. Yet, Oracle does offer Oracle Express, a free database up to 4GB of storage where PHP, Ruby on Rails become a better option.
  • Mysqldump is a very simple tool for creating a backup. Oracle Data Pump and Recovery Manager (RMAN) are tools that are more enterprise level tools.
  • MySQL does not need additional networking software to work. It uses built-in networking protocols like TCP/IP. Oracle Net Services runs on top of TCP/IP. Oracle Net Services may or may not be free depending on what you are using on the client side. I realize Oracle has EZ Connect but it is not a replacement for Oracle Net Services.

Databases

One of the differences between MySQL and Oracle is how they treat the term database. A MySQL database is closer to an Oracle schema and all the storage for that schema. I’ll add an architectural picture of MySQL and Oracle that will help with this.

MySQL
  1. The MySQL physical server environment is made up of all the individual databases, binary log, and additional files and logs.
  2. A MySQL database is how to organize a group of database objects such as tables, views, triggers, etc.
  3. There would be a separate MySQL database for a human resource and a sales system to organize each set of objects separately.

Oracle:
  1. In Oracle the physical server environment is a database that encompasses all the physical files (storage) for a specific Oracle server (instance). This includes all files for data files, index files, redo log files, archive log files, etc.
  2. Oracle organizes database objects in a schema. A schema is an Oracle user and all tables, views, sequences, synonyms, stored procedures, etc. the objects and resources owned by the user.
  3. Oracle would use a separate “schema” for storing information for a human resource and a sales system if they were in the same database.
Summary:
A MySQL server environment is made up of server memory, background processes associated with multiple databases.

An Oracle server environment is made up of server memory and background processes (instance) associated with a single database organized with multiple schemas.

Startup Files
MySQL - The my.cfg or my.ini is the startup file for a MySQL server environment.

Oracle - An init.ora or spfile.ora file is used to start an Oracle server instance.

Metadata
The way MySQL and Oracle DBAs access metadata is similar but totally different. :)

With MySQL there are a large number of SHOW commands that will display detailed information on MySQL databases (data schemas), table structures etc. With Oracle the data dictionary views of USER_%, ALL_%, DBA_%, V$, etc. are used. The SHOW command in Oracle is only or the SQL*Plus client interface that will display local SQL*Plus variables in terms of how the SQL*Plus client is configured.

Both environments allow GLOBAL (instance) and LOCAL (session) variables to be defined. Below MySQL commands (mysql>) and Oracle commands (SQL) for accessing metadata will be compared.

-- Display server parameters.
mysql> SHOW VARIABLES;
SQL> SELECT name, value FROM V$parameter;

-- Set the sort buffer size.
mysql> SET GLOBAL sort_buffer_size=20 * 1024 * 1024;
SQL> ALTER SYSTEM SET sort_area_size=20971520;

-- Display information on triggers.
mysql> SHOW TRIGGERS LIKE 'fin%'\G
SQL> SELECT trigger_name, trigger_body FROM dba_triggers
WHERE trigger_name LIKE 'FIN%';

Storage Options
MySQL uses storage engines. Each of these storage engines deals with transactions, memory allocations, etc. differently. Very important a DBA understand the ramifications between the different types of storage. Some of the more popular storage engines include
  • MyISAM
  • InnoDB
  • Memory
  • Federated
  • Archive
  • Blackhole
  • Falcon - New transactional storage available in MySQL 6.0.
MySQL stores data for tables in flat files that reside on the disk. RAW is available but not used much any more.
Oracle does not have the equvalent of storage engines. Oracle supports some of the following types of table storage:
  • HEAP - regular table (default). Supports ACID transactions as well as being used able to be used for data warehouses.
  • IOT - Index organized table. Storage is in a b-tree structure.
  • External - Table definition that is mapped to an external file.
Oracle storage is managed in tablespaces. Tablespaces can store multiple data segments (tables) and index segments (indexes). Tablespaces organize Oracle storage structures. Tablespaces can store their data using RAW devices, operating system files and Automatic Storage Management (ASM). ASM uses Oracle internal storage mechanisms to write directly to physical devices without a file system. ASM can perform striping and mirroring using an Oracle Logical Volume Manager (ASM instance).


Creating Tables
Both MySQL and Oracle both support:
Regular tables
Indexes
Temporary tables
Partitioning

The big difference is that MySQL storage engines determine the functionality of MySQL tables. Another big difference is that MySQL uses AUTO_INCREMENT functionality to increment primary keys and unique identifiers. Oracle uses sequences to increment unique identifiers.

Stored procedures and triggers
MySQL and Oracle both support stored procedures and triggers. The MySQL stored procedure functionality is fairly new. However, the core of the language for writing stored procedures is available in MySQL. Oracle's procedural language has been around since 1991, so it obviously is more mature.

What's nice is that both of them use an ADA like syntax, so the core syntax of both languages looks almost exactly alike. Going from Oracle PL/SQL to MySQL's stored procedures would be a very easy transition.