Oracle 9i: Introduction to SQL - SQL Fundamentals

Types of SQL

1. Data Manipulation Language (DML)

    a. SELECT - retrieve data

    b. INSERT  - store data

    c. UPDATE - modify data

    d. DELETE - remove data

    e. MERGE - store/modify data

    f. EXPLAIN PLAN - show execution plan

    g. LOCK TABLE - restrict access to a table

2. Data Definition Langauge (DDL)

    a. CREATE - create new database object

    b. ALTER - alter existing database object

    c. DROP - remove an existing object permenantly

    d. RENAME - modify the name of an existing object

    e. TRUNCATE - remove all rows from a table without dropping its structure

    f. GRANT

    g. REVOKE

    h. AUDIT

    i. NOAUDIT

    j. COMMENT - add info into data dictionary

3. Transaction Control

    a. COMMIT

    b. ROLLBACK

    c. SAVE POINT

    d. SET TRANSACTION

4. Session Control

    a. ALTER SESSION

    b. SET ROLE

5. System Control

    a. ALTER SYSTEM

Data Types

1. Character

    a. CHAR (n); 1 <= n <= 2000 bytes, default = 1 

    b. NCHAR (n)

    c. VARCHAR2 (n); 1 <= n <= 4000 bytes; MUST specify value for n

    d. NVARCHAR2 (n)

2. Number

   a. NUMBER (n,m); 1 <= n <= 38, -84 <= m <= 127; IF n and m are missing, assume NUMBER (38, 127) or NUMBER (38, -84)

3. Long and Raw

4. Date and Time

5. Large Object

6. Row ID

Operators

1. Arithmetic Operators

    -> +/-, +, -, *, /

2. Concatenation Operator

    -> ||

3. Set Operators

    -> UNION, UNION ALL, INTERSECT, MINUS

4. Logical Operators

    -> NOT, AND, OR

5. Others

    -> IN, NOT IN, BETWEEN...AND, EXISTS, IS NULL, IS NOT NULL, LIKE

** Operator precedence: +/- then *, / then +, -, ||

Literals (constant values)

1. Text: 'this is a line of text'

2. Integer: 12, -90 (maximum up to 38 digits)

3. Number: 3, -4.23, 7E-10

4. Interval

CASE Expression

1. CASE column WHEN equal_to_value THEN return_value ELSE default_return_value END

2. CASE WHEN condition THEN return_value ELSE default_return_value END

Notes:

1. Keywords, table names and column names are case insensitive

2. DUAL table is a single row and single column table

3. By default, NULL values are sorted last (unless NULLS FIRST is specified)

4. Equility test for NULL will return 0 row (e.g. WHERE column = NULL)

About My SQL Server Performance Tuning - Day 4 & Day 5

Day 4:

 1. Turn on SQL Trace to capture performance of tuned scripts

Day 5:

 1. Present tuning results

     a. CPU usage

     b. Memory usage

     c. Hard disk I/O

     d. Network usage

     e. Overall server performance

     f. Overall database performance (peak & non-peak hours)

     g. Tuned scripts' performance

About My SQL Server Performance Tuning - Day 3

Day 3:

 1. Apply all scripts into database

     a. Index tuning scripts

     b. Plan guide

     c. Add/Drop/Move data/log files

     d. Adjust TempDB

     e. Create database maintenance plans

Note:

 1. OLTP and OLAP have different tuning objectives. OLTP aims for faster insertion while OLAP aims for faster data retrieval.

 2. Under certain circumstances, we may need to create new table(s) to achieve certain objectives (normally for reporting purposely)

 3. Sometimes, we, as DBAs, may not have the influence over front  end application. Plan guide may only provide limited capability to tune the queries.

 4. It is very rare that we need to modify existing database structure (this require too much programming effort)

 

About My SQL Server Performance Tuning - Day 2

Day 2:

 1. Group similar queries together (by replacing parameters with @var1...n)

     a. Calculate AVERAGE READS, WRITES, CPU and DURATION for each group of queries

         -> serve as baseline of tuning

         -> tuned queries should be executed with less resource, which means lower than baseline

     b. Count frequency of execution for each group of queries

         -> perform impact analysis

     c. Select queries that can represent overall picture of database performance

         -> Approach 1: select a single query from each group that is close to AVERAGE

         -> Approach 2: select the slowest query from each group

         -> Approach 3: select MOD (highest frequency) query from each group (have to divide same group of queries into multiple ranges)

 2. Schedule a SQL Job to run selected queries at certain interval (hourly, 2-hourly etc.)

     -> make sure we cover both peak and non-peak hours of a day

     -> one job step runs ONLY ONE query

 3. Turn on SQL Trace

 4. On separate environment, prepare scripts to tune queries

     a. Index tuning

         i.   Update statistics

         ii.  Rebuild index (adjust fill factor if necessary)

         iii. Create/Drop indexes

         -> SQL Server 2005 has a dynamic management view to check index usage

         -> remove unused indexes

         -> check recommendation for missing index

     b. Create plan guide

     c. Create/Remove/Move data/log files

     d. TempDB adjustment

     e. Create database maintenance plans

End of the day:

 1. Capture all values for selected baseline queries

About My SQL Server Performance Tuning - Day 1

Day 1:

  1. Collect hardware and software info

      a. CPU -> number of processors/cores, CPU speed, useable by SQL Server

      b. O/S -> platform, version, edition, 32/64 bit, service pack/patches

      c. Memory -> total available, allocated to SQL Server, speed (MHz)

      d. SQL Server -> version, edition, 32/64 bit, service pack/pacthes

      e. Hard Disk -> SAN/NAS/DAS, RAID, total space for each logical partition, available free space

      f. Network Interface -> bandwidth (LAN, Intranet, Extranet, Internet)

  2. Start SQL Trace to capture 100% data

       a. Columns: TextData, DatabaseID, HostName, SPID, ApplicationName, LoginName, Reads, Writes, Duration, StartTime, CPU, RowCounts

       b. Events: RPC:Completed, SQL:BatchCompleted, Exception

  3. Turn on Performance Monitor

       a. Processor

       b. Logical Disk

       c. SQL Server

       d. Memory

       e. Network Interface

       f. Process

  4. Understand business cycle

       a. Peak hours

       b. Peak days

       c. Peak seasons

End of the Day:

 1. Identify HIGH READS/WRITES/CPU/DURATION queries

 2. Identify frequent running queries

 3. Prepare baseline queries, which will be scheduled to run at certain interval on Day 2