SQL Injection
KIT712: SQL Tuning II
Adapted from Oracle SQL Optimisation Training Slides)
*
Why to tune your sql?
Which scenario is worse?
SQL Statement 1
Executed 100 times
Caused 100 minutes of wait time for end user
Waited 99% of time on “db file sequential read”
SQL Statement 2
Executed 1 time
Caused 100 minutes of wait time for end user
Waited 99% on “enq: TX – row lock contention”
*
Speaking of wait time and wait events, I have a little test for you. Which of these scenarios is worse? SQL statement 1 that executed 1000 times, made the end users wait for 10 minutes and waited 99% of the time on “db file sequential read”. Or, SQL statement 2 that executed 1 time, made the end user wait for 10 minutes and spend 99% of it’s time waiting on a locking problem? The answer is both are equally bad, they both made the end user wait for 10 minutes. End users don’t care what they waited for, only that it took 10 minutes. SQL statement 2 may be harder to tune, because locking problems are typically application design issues, but both are equal in the eyes of your customer, the user.
SQL Tuning
Identify the problematic SQL
Review Execution/Access/Explain plan
Check whether appropriate indexes are used
Do you need to create another index?
If data is from more than one table, check the Join order and join method.
Give hints to the oracle optimiser?
Execution Plan
Execution plans show the detailed steps necessary to execute a SQL statement
These steps are expressed as a set of database operators that consumes and produces rows
The order of the operators and their implementation is decided by the optimizer using a combination of query transformations and physical optimization techniques
The display is commonly shown in a tabular format, but a plan is in fact tree-shaped
Execution Plan
Gathering Execution Plan
EXPLAIN PLAN
Estimated execution plan – can be wrong for many reasons
V$SQL_PLAN
Real execution plan
Use DBMS_XPLAN for display
Under certain conditions the plan shown with EXPLAIN PLAN can be different from the plan shown using V$SQL_PLAN
How to get an Execution Plan?
Generate & display execution plan for the last SQL stmts executed in a session
SQL>SELECT prod_category, avg(amount_sold)
FROM sales s, products p
WHERE p.prod_id = s.prod_id
GROUP BY prod_category;
no rows selected
SQL> SELECT plan_table_output
FROM table(dbms_xplan.display_cursor(null,null,’basic’));
——————————————
Id Operation Name
——————————————
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
——————————————-
AUTOTRACE
To start tracing statements using AUTOTRACE:
To display the execution plan only without execution:
To display rows and statistics:
To get the plan and the statistics only (suppress rows):
For automatically getting a report on the execution plan and the statement execution statistics
Oracle Database 11g: SQL Tuning Workshop 5 – *
AUTOTRACE: Examples
You can control the report by setting the AUTOTRACE system variable. The following are some examples:
SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution plan and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY EXPLAIN: The AUTOTRACE report shows only the optimizer execution path without executing the statement.
SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows the SQL statement execution statistics and rows.
SET AUTOTRACE TRACEONLY: This is similar to SET AUTOTRACE ON, but it suppresses the printing of the user’s query output, if any. If STATISTICS is enabled, the query data is still fetched, but not printed.
SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default.
What’s a Good Plan for the Optimizer?
The Optimizer has two different goals
cost
The cheaper, the better
performance
The faster, the better
What is Cost?
A magically number the optimizer makes up?
Resources required to execute a SQL statement?
Result of complex calculations?
Estimate of how long it will take to execute a statement?
Actual Definition
Cost represents units of work or resources used
Optimizer uses CPU & memory usage plus IO as units of work
Cost is an estimate of the amount of CPU and memory plus the number of disk I/Os, used in performing an operation
Cost is an internal Oracle measurement
What is performance?
Getting as many queries completed as possible?
Getting fastest possible elapsed time using the fewest resources?
Getting the best concurrency rate?
Actual Definition
Performance is fastest possible response time for query
Goal is to complete the query as quickly as possible
Optimizer does not focus on resources needed to execute the plan
Understanding Execution Plan
Cardinality– Estimate of the number of rows coming out of each of the operations.
Access method – The way in which the data is being accessed, via either a table scan or index access.
Is the data being accessed in the best way? Scan? Index lookup?
Join method – The method (e.g., hash, sort-merge, etc.) used to join tables with each other.
Is Join method suitable for following query?
Join type – The type of join (e.g., outer, anti, semi, etc.).
Are the right join types being used?
Join order – The order in which the tables are joined to each other.
Are tables being joined in the correct order?
Parallel Execution – In case of parallel execution, is each operation in the plan being conducted in parallel? Is the right data redistribution method being used?
In order to determine if you are looking at a good execution plan or not, you need to understand how the Optimizer determined the plan in the first place. You should also be able to look at the execution plan and assess if the Optimizer has made any mistake in its estimations or calculations, leading to a suboptimal plan. The components to assess are
*
Cardinality
Estimate of number rows that will be returned
Cardinality for a single value predicate = num_rows total / num_distinct total
E.g. 100 rows total, 10 distinct values => cardinality=10 rows
Why should you care?
Influences access method and Join Order
If estimate is off it can have a huge impact on a plan
Density is 1/num_distinct for columns without a histogram
For columns with a histogram density is calculated differently
Access Paths
The access path can be:
Full table scan
Table access by Rowid
Index unique scan
Index range scan (descending)
Index skip scan
Full index scan
Fast full index scan
Index joins
Bitmap indexes
Full table reads all rows from a table and filters out those that do not
meet the where clause predicates. Does multi block IO. Influenced by
Value of init.ora parameter db_multi_block_read_count
Parallel degree
Lack of indexes
Hints
Typically selected if no indexes exist or the ones present cant be used
Or if the cost is the lowest due to DOP or DBMBRC
Rowid of a row specifies the datafile and data block containing the
row and the location of the row in that block. Oracle first obtains the
rowids either from the WHERE clause or through an index scan of one
or more of the table’s indexes. Oracle then locates each selected row
in the table based on its rowid.
With an Index unique scan only one row will be returned. It will be used
When a statement contains a UNIQUE or a PRIMARY KEY constraint that
guarantees that only a single row is accessed.
An index range scan Oracle accesses adjacent index entries and then
uses the ROWID values in the index to retrieve the table rows. It can be
Bounded or unbounded. Data is returned in the ascending order of
index columns. It will be used when a stmt has an equality
predicate on non-unique index, or an incompletely specified unique
index, or range predicate on unique index. (=, <, >,LIKE if not on leading edge)
Uses index range scan descending when an order by descending
clause can be satisfied by an index.
Normally, in order for an index to be used, the columns defined on
the leading edge of the index would be referenced in the query however,
If all the other columns are referenced oracle will do an index skip scan to
Skip the leading edge of the index and use the rest of it. Advantageous if
there are few distinct values in the leading column of the composite index
and many distinct values in the non-leading key of the index.
A full scan does not read every block in the index structure, contrary to what
its name suggests. An index full scan processes all of the leaf blocks of an
index, but only enough of the branch blocks to find the first leaf block can
be used because all of the columns necessary are in the index
And it is cheaper than scanning the table and is used in any of the following situations:
An ORDER BY clause has all of the index columns in it
and the order is the same as in the index (can contain a subset of the columns in the index).
The query requires a sort merge join & all of the columns referenced in the query are in the index.
Order of the columns referenced in the query matches the order of the leading index columns.
A GROUP BY clause is present in the query, and the columns in the GROUP BY clause are present in the index.
A Fast full index scan is an alternative to a full table scan when the index c
ontains all the columns that are needed for the query, and at least one
column in the index key has the NOT NULL constraint. A fast full scan accesses
all of the data in the index itself, without accessing the table. It cannot be used
to eliminate a sort operation, because the data is not ordered by the index key.
It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
An index join is a hash join of several indexes that together contain all the table
columns that are referenced in the query. If an index join is used, then no table
access is needed, because all the relevant column values can be retrieved
from the indexes. An index join cannot be used to eliminate a sort operation.
A bitmap join uses a bitmap for key values and a mapping function that
converts each bit position to a rowid. Bitmaps can efficiently merge
indexes that correspond to several conditions in a WHERE clause,
using Boolean operations to resolve AND and OR conditions.
Access Path examples
A table countries contains 10K rows & has a primary key on country_id – What plan would you expect for these queries?
Select country_id, name from countries where country_id in (‘AU’,’FR’,’IE‘);
Select country_id, name from countries where country_id between ‘AU’ and ‘IE’;
Select country_id, name from countries where name=’USA’;
Join Method
A Join retrieve data from more than one table
Possible join Methods are
Nested Loops joins
Hash Joins
Sort Merge joins
Cartesian Joins
Nested loop joins are useful when small subsets of data are being joined
and if the join condition is an efficient way of accessing the second table (index look up),
That is the second table is dependent on the outer table (foreign key). For every row in
the outer table, Oracle accesses all the rows in the inner table. Consider it
Like two embedded for loops.
Hash joins are used for joining large data sets. The optimizer uses the smaller of two
tables or data sources to build a hash table on the join key in memory. It then
scans the larger table, probing the hash table to find the joined rows. Hash joins selected
If an equality predicate is present
Sort merge joins are useful when the join condition between two tables is an
inequality condition (but not a nonequality) like <, <=, >, or >=. Sort merge joins
perform better than nested loop joins for large data sets. The join consists of two steps:
Sort join operation: Both the inputs are sorted on the join key.
Merge join operation: The sorted lists are merged together.
A Cartesian join is used when one or more of the tables does not have any join
conditions to any other tables in the statement. The optimizer joins every row
from one data source with every row from the other data source,
creating the Cartesian product of the two sets. Only good if the tables involved are
Small. Can be a sign of problems with cardinality.
An outer join returns all rows that satisfy the join condition and also returns
some or all of those rows from the table without the (+) for which no rows from
the other satisfy the join condition. Take query: Select * from customers c, orders o
WHERE c.credit_limit > 1000 AND c.customer_id = o.customer_id(+)
The join preserves the customers rows, including those rows without a corresponding row in orders
Join Type Example 1
What Join type should be use for this Query?
SELECT e.name, e.salary, d.dept_name
FROM hr.employees e, hr.departments d
WHERE d.dept_name IN (‘Marketing‘,’Sales’)
AND e.department_id=d.department_id;
Employees has 107 rows
Departments has 27 rows
Foreign key relationship between Employees and Departments on dept_id
Join Type Example 2
What Join type should be use for this Query?
SELECT o.customer_id, l.unit_price * l.quantity
FROM oe.orders o ,oe.order_items l
WHERE l.order_id = o.order_id;
Orders has 105 rows
Order Items has 665 rows
Join Type Example 3
What Join type should be use for this Query?
SELECT o.order_id,0.order_date,e.name
FROM oe.orders o , hr.employees e;
Orders has 105 rows
Employees has 107 rows
Join Type Example 4
What Join type should be use for this Query?
SELECT d.department_id,e.emp_id
FROM hr.employees e FULL OUTER JOIN hr.departments d
ON e.department_id = d.department_id;
Employees has 107 rows
Departments has 27 rows
Foreign key relationship between Employees and Departments on dept_id
A full outer join acts like a combination of the left and right outer joins. In addition to the inner join, rows from both tables that have not been returned in the result of the inner join are preserved and extended with nulls. In other words, full outer joins let you join tables together, yet still show rows that do not have corresponding rows in the joined tables.
PLSQL
KIT712: ADVANCED SQL USING PL/SQL BLOCKS
*
PL/SQL
PL/SQL = Procedural Language extensions to SQL An Oracle-specific language combining features of:
modern, block-structured programming language
database interaction via SQL
Designed to overcome declarative SQL’s inability to specify control aspects of DB interaction.
Used to add procedural capabilities to Oracle tools.
Where is PL/SQL used?
Mainly for writing stored procedures and triggers etc.
What is a stored procedure:
Program executed through a single SQL statement
Stored within the database
Executed in the process space of the server
Advantages:
Can encapsulate application logic while staying “close” to the data
Reuse of application logic by different users of the database
Avoid tuple-at-a-time return of records through cursors
*
Why PL/SQL?
Consider trying to implement the following in SQL (SQL*Plus):
If a user attempts to withdraw more funds than they have from their account, then indicate “Insufficient Funds”, otherwise update the account
A possible implementation:
ACCEPT person PROMPT ‘Name of account holder: ‘
ACCEPT amount PROMPT ‘How much to withdraw: ‘
UPDATE Accounts SET balance = balance – &amount WHERE holder = ‘&person’ AND balance > &amount;
SELECT ‘Insufficient Funds’ FROM Accounts WHERE holder = ‘&person’ AND balance <= &amount; Why PL/SQL ? Two problems: doesn't express the "business logic" nicely performs both actions when (balance-amount < amount) We could fix the second problem by reversing the order (SELECT then UPDATE). But in SQL there's no way to avoid executing both the SELECT and the UPDATE PL/SQL: Clean, elegant and efficient procedural extention for SQL CREATE PROCEDURE withdraw(person varchar2, amt number)…; SQL> EXEC withdraw(‘John’, 3200);
PL/SQL Syntax
PL/SQL is block-structured, where a block consists of:
DECLARE (optional)
declarations for constants, variables and local procedures
BEGIN (Mandatory)
PL/SQL and SQL statements ;
EXCEPTION (Optional)
–Action to be performed when an error occur
END; (Mandatory)
/
Notation
“/” – executes the SQL, PL/SQL stored statement in buffer
“;” ends a SQL, PL/SQL statement
Printing strings to STDOUT in PL/SQL:
set serveroutput on
exec dbms_output.put_line(‘Hello’);
Variables
Variables can be used for
Temporary storage of data
Manipulation of stored values
Reusability
Variables
A variable name:
• Must start with a letter
• Can include letters or numbers
• Can include special characters (such as $, _, and #)
• Must contain no more than 30 characters
• Must not include reserved words
Constants and Variables (Declarations)
Variables and constants are declared by specifying:
Name [ CONSTANT ] Type [ := Expr ] ;
Examples:
amount INTEGER;
part_number NUMBER(4);
in_stock BOOLEAN;
owner_name VARCHAR(20);
tax_rate CONSTANT REAL := 0.23;
max_credit CONSTANT REAL := 5000.00;
my_credit REAL := 2000.00;
Variables (Declarations)….
Variables can also be defined in terms of:
the type of an existing variable or table column
the type of an existing table row (implict RECORD type)
Examples:
quantity INTEGER;
start_qty quantity%TYPE;
employee Employees%ROWTYPE;
name Employees.name%TYPE;
Assigning Values to Variables
A standard assignment operator is available:
in_stock := FALSE;
tax := price * tax_rate;
amount := TO_NUMBER(SUBSTR(‘750 dollars’,1,3));
person1 := person2; — record assignment
Values can also be assigned via SELECT…INTO:
SELECT price * (1+tax_rate) INTO cost
FROM StockList WHERE item = ‘Cricket Bat’;
total := total + cost;
Assignments
SELECT…INTO can assign a whole row at once:
DECLARE
emp Employees%ROWTYPE;
my_name VARCHAR(20);
pay NUMBER(8,2);
BEGIN
SELECT * INTO emp FROM Employees
WHERE id# = 966543;
my_name := emp.name;
…
SELECT name,salary INTO my_name,pay FROM Employees
WHERE id# = 966543;
END;
—- These SELECT statements are ‘checked’ at compile time (Static SQL)
Data Types
PL/SQL constants and variables can be defined using:
Standard SQL data types (CHAR, DATE, NUMBER, …) , VARRAYs, Nested Tables,….
User-defined SQL data types (e.g., geometry)
+
Built-in PL/SQL types (BOOLEAN, BINARY_INTEGER)
PL/SQL structured types (RECORD, TABLE)
Users can also define new data types in terms of these.
There is also a CURSOR type for interacting with SQL.
Record Types
Correspond to Modula RECORDs or C structs, and also closely related to SQL table row type. New record types can be defined via:
TYPE TypeName IS RECORD
(Field1 Type1, Field2 Type2, …);
Example:
TYPE Student IS RECORD (
id# NUMBER(6),
name VARCHAR(20),
course NUMBER(4) );
Records can also be Row types of defined Oracle tables, e.g.,
TYPE emprec is EMP%ROWTYPE;
Records Example
Record components are accessed via Var.Field notation.
fred Student;
…
fred.id# := 123456;
fred.name := ‘Fred’;
fred.course := 3978;
Record types Examples
TYPE Day IS RECORD (day NUMBER(2), month NUMBER(2), year NUMBER(4));
TYPE Person IS RECORD (name VARCHAR(20), phone VARCHAR(10), birthday Day);
Control Structures
PL/SQL has conventional set of control structures:
IF for selection
FOR, WHILE, LOOP for repetition
Along with exceptions to interrupt normal control flow. And a NULL; statement to do nothing.
Conditional Selection
Conditional Selection is expressed via:
IF Cond1 THEN
Statements1;
ELSIF Cond2 THEN
Statements2;
ELSIF Cond3 THEN
Statements3; …
ELSE
Statementsn;
END IF;
ELSIF and ELSE parts are optional.
Iteration
Iteration is expressed via:
LOOP
Statements; …
EXIT …
MoreStatements;
END LOOP;
WHILE Cond LOOP
Statements;
END LOOP;
FOR Var IN LoVal..HiVal LOOP
Statements;
END LOOP;
Iteration (contd.)
EXIT WHEN Cond;
is shorthand for
IF Cond
THEN EXIT;
END IF;
Loops can be named to allow multi-level exits
<
…
<
EXIT outer WHEN i > 100;
…
END LOOP;
…
END LOOP;
Summary
PL/SQL: Procedural Language extension for SQL
Declarations for Variables,..
Assignments
PL/SQL-specific types: Records, Tables, Cursors
Exception Handling
Procedures and Functions
More Examples(Self Study)
*
*
*
*
*
*
*
Execution Plan———————————————————-0 SELECT STATEMENT Optimizer=ALL_ROWS1 0 MERGE JOIN (CARTESIAN)2 1 TABLE ACCESS (FULL) OF ‘PRODUCTS’ (TABLE) 3 1 BUFFER (SORT)4 3 TABLE ACCESS (FULL) OF ‘CUSTOMERS’ (TABLE)