Harvard University

Oracle Database Administration

Session 13Performance and Security

Harvard University

Performance

Wholistic view System level Database level Application level

Harvard University

Performance Tuning

Trade-offs Between Response Time and Throughput

OLTP applications define performance in terms of throughput

Decision Support Systems define performance in terms of response time

Response time = service time + wait time

Harvard University

SQL Processing Architecture

Harvard University

SQL Syntax The syntax for the SELECT statement is:

– SELECT columnsFROM tablesWHERE predicates;

From http://www.techonthenet.com/sql/select.php

Good Information source for basic SQL statements

Harvard University

Parser

The parser performs two functions:– Syntax analysis: This checks SQL statements

for correct syntax– Semantic analysis: Checks that the current

database objects and object attributes are correct

Harvard University

Optimizer

The optimizer is the heart of the SQL processing engine. The Oracle server provides one method of optimization: cost-based optimizer (CBO).

Default CBO – all_rows

Harvard University

Row Source Generator

The row source generator receives the optimal plan from the optimizer

It outputs the execution plan for the SQL statement

A set of rows returned by an execution step is called a row source

The execution plan is a collection of row sources, structured in the form of a tree

Harvard University

Row Source Generator

A row source is an iterative control structure It processes a set of rows, one row at a time,

in an iterative manner. A row source produces a data set.

Harvard University

SQL Execution The combination of steps required to execute

a statement is called an execution plan An execution plan includes an access method

for each table that the statement accesses and an ordering of the tables(the join order), if required.

Harvard University

SQL Execution

To perform a full table scan, Oracle reads all rows in the table, examining each row to determine whether it satisfies the statement’s WHERE clause

Harvard University

SQL Execution

SQL execution is the component that operates on the execution plan associated with a SQL statement

It then produces the results of the query.

The optimizer determines the most efficient way to execute a SQL statement

Harvard University

SQL Execution

To execute a DML statement, Oracle may need to perform many steps.

Each of these steps either retrieves rows of data physically from the database or prepares them in some way for use in the next step

Harvard University

Explain Plan

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements

A SQL statement’s plan is the sequence of operations Oracle performs to execute the statement

Harvard University

Explain Plan

The components of execution plans include:– An ordering of the tables referenced by the

statement.– An access method for each table mentioned in

the statement.– A join method for tables affected by join

operations in the statement.

Harvard University

Explain Plan

EXPLAIN PLAN output shows how Oracle executes SQL statements

The EXPLAIN PLAN results cannot differentiate between well-tuned statements and those that are poorly performed

If the EXPLAIN PLAN output shows that a statement uses an index, this does not mean the statement runs efficiently

Harvard University

Explain Plan

Sometimes using indexes can be extremely inefficient

It is best to use EXPLAIN PLAN to determine an access plan, and later prove that it is the optimal plan through testing

When evaluating a plan, always examine the statement’s actual resource consumption

Harvard University

Explain Plan

Use the SQL trace facility and TKPROF to examine individual SQL statement performance (relative cost)

Before issuing an EXPLAIN PLAN statement, create a table to hold its output

Run the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema

Harvard University

Explain Plan

The UTLXPLAN.SQL is located under $ORACLE_HOME/rdbms/admin

PLAN_TABLE is the default table into which the EXPLAIN PLAN statement inserts rows describing execution plans

Harvard University

Explain Plan EXPLAIN PLAN SET STATEMENT_ID = ‘1’

INTO plan_tableFOR <select * from v$datatbase>;

EXPLAIN PLAN SET STATEMENT_ID = ‘value’ INTO plan_tableFOR <sql_statement>;

Harvard University

Explain Plan

Display the most recent plan table output using the following scripts:

– UTLXPLS.SQL - Shows plan table output for serial processing

– UTLXPLP.SQL - Shows plan table output with parallel execution columns

http://www.dbspecialists.com/presentations.html#explain_and_tkprof

Harvard University

Autotrace

SET AUTOTRACE ON EXPLAIN STATISTICS

SET AUTOTRACE OFF

Harvard University

System Statistics The gathered systems statistics are:

– single block readtime in ms– multiblock readtime in ms– cpu speed in mhz– average multiblock_read_count in number of

blocks

Harvard University

System Statistics

http://www.oracle.com/technology/pub/articles/lewis_cbo.html

http://www.oracle.com/technology/pub/articles/tech_dba.html

Harvard University

Security Framework

Confidentiality

Availability

Integrity

Harvard University

Environment Boundaries

Database

System Layer

Subnet layer

Network Layer

Domain Edge

Database Layer

Network Layer

Global Network

Harvard University

Strategic and TacticalPrevent

Remediate

Monitor

React

detect

Policy

Complience

Harvard University

Security Readiness

Security Framework Defines steps Repeatable Verifiable Business ‘requirements’ focused

http://iase.disa.mil/stigs/SRR/index.html

Harvard University

Security Readiness Regulations

– Sarbanes-Oxley (SOX)– Gramm-Leach-Bliley (GLBA)– Health Insurance Portability and Accountability

(HIPAA)– Payment Card Industry Data Security (PCI)– Federal Information Security Management

(FISMA)

Harvard University

Security Readiness Secure the Network Monitor the Network Access Control Methods

– Who– What

Audit Access and Data – Who – When– What

Harvard University

Security Readiness

Encrypt specific data– SSN #– Credit Card #

Document Procedures

Harvard University

Security Patches

Released 4 times a year– January– April– July– October

The single most significant step to take to protect your databases

Harvard University

Security Patches Ensure that you are using current product

versions Apply the appropriate Mandatory patches Stay current with Security patches for the

‘environment’ Database and Application Server patches are

cumulative Application Products are not cumulative

Harvard University

Init.ora parameters

Sql92_security = true– User must have ‘select’ on a table to be able to

run ‘update’ or ‘delete’ statements 07_dictionary_accesssibility = false

Harvard University

Accounts Default Accounts and default passwords

– 700+ accounts depending on what products are installed

– 3rd party products User profiles

– Complex passwords– Script utlpwdmg.sql – /u01/app/oracle/product/{ORACLE_HOME}/

rdbms/admin

Harvard University

Accounts This script sets the default password resource

parameters This script needs to be run to enable the password

features. However the default resource parameters can be

changed based on the need. A default password complexity function is also

provided. This function makes the minimum complexity checks

like the minimum length of the password, password not same as the username, etc. The user may enhance this function according to the need.

Harvard University

Passwords

The default password list– http://www.petefinnigan.com/default/

default_password_list.htm More password information

– http://www.databasejournal.com/features/oracle/article.php/3395721

Harvard University

SSL Secure Socket Layer Certificates Prevent attacks from the ‘side’ Network sniffing Virtual Private Network (VPN) tunnel Virtual Private

Harvard University

Listener

From 10g onwards no specific ‘listener’ password required

Only the owner of the tnslsnr process can run ‘listener’ command

The ‘listener.ora’ file should be write protected

http://andrewfraser.wordpress.com/2007/05/24/listener-passwords-always-for-9i-never-for-10g/

Harvard University

Transparent Data Encryption Transparent data encryption is a key-based

access control system Must be decrypted by a key to be understood The key is stored in an Oracle Wallet In 10g, columns in tables can be encrypted In 11g, tablespaces and/or columns can be

encrypted

Harvard University

Transparent Data Encryption

http://www.oracle.com/technology/deploy/security/database-security/transparent-data-encryption/tde_faq.html

http://www.databasejournal.com/features/oracle/article.php/3649956

Harvard University

SQL Injection It is a method of inserting SQL commands as

if they were user input An attacker can append data and commands

to an existing sql query Usually occurs at the URL The username and password screens are the

most vulnerable as they are the ‘front door’ to your application

Harvard University

Buffer Overflow An application has an array (buffer) setup to

hold data. Usually with languages C and C++ The array may not have specific boundaries

setup If identified, a hacker can write data to this

buffer, overrun the array boundary and replace other data values stored in memory

Harvard University

Buffer Overflow

Overflow is the process of writing to adjacent buffers

http://www.windowsecurity.com/articles/Analysis_of_Buffer_Overflow_Attacks.html

Harvard University

Auditing Enable auditing at the DB level by issuing the

below listed command ALTER SYSTEM SET audit_trail=db

SCOPE=SPFILE; (if spfile in use) Add the following to the startup script EXECUTE

dbms_workload_repository.create_snapshot() audit connect;

Harvard University

Auditing Audit data is stored in the aud$ table in the

‘system’ tablespace Purge the data in the AUD$ table periodically

or make sure that there is adequate space in “SYSTEM” tablespace.

Recycle the environment to enable auditing

Harvard University

Auditing

Data collected– Login/logout– Source IP– Programs used to connect from

Describe aud$ for more information

Harvard University

Advanced Security Option (ASO) Encrypted sql*net Used to secure direct connections to the

database. Can encrypt data moving in both directions Needs client side setup

– Sql*net.ora– SQLNET.CRYPTO_SEED = (some value)

SQLNET.ENCRYPTION_TYPES_SERVER SQLNET.ENCRYPTION_TYPES_CLIENT

Harvard University

Hacking

http://www.dbasupport.com/oracle/ora10g/hacking01.shtml

Harvard University

Forensics If some one gets into your database, what did

that do. – What did they look at– What did they change– Did they extract any data

http://www.ngssoftware.com/ http://www.ngssoftware.com/company-news/

oracle-forensics-a-new-series-of-papers-by-david-litchfield/

Harvard University

Recap

Design performance into the database setup and configuration

Have a security and performance mind set Understand your application and make

decisions based on that understanding No absolutes Be willing to change course

Harvard University

Reading Oracle Performance Tuning, Oracle Press

– Richard J.Niemiec Oracle 10g and 11g Security Guide Practical Oracle Security (e-book)

– http://www.syngress.com/catalog/?pid=4620 Other Sources

– http://infosecurity.us/– http://www.ngssoftware.com/