harvard university oracle database administration session 13 performance and security
Post on 19-Dec-2015
224 views
TRANSCRIPT
Harvard University
Oracle Database Administration
Session 13
Performance 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 columns
FROM 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_table
FOR <select * from v$datatbase>;
EXPLAIN PLAN SET STATEMENT_ID = ‘value’
INTO plan_table
FOR <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 Tactical
Prevent
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/