Tuning
SQL Trace Facility
Sono in grado di fornire informazioni preziose, per ogni statement SQL
chiamato in causa, generando le seguenti statistiche attive:
1) a livello di sessione (alter session set sql_trace = TRUE)
2) a livello di intera istanza (parametro di configurazione del file initSID.ora
sql_trace=true)
•
•
•
•
Numero di parse, execute e fetch
Tempo di CPU e tempo di elapsed (trascorso)
Numero di letture logiche e letture fisiche
Numero di record processati
archiviate, in formato interno, in un file denominato trace file
Formatting the Trace File
with TKPROF
$ tkprof tracefile.trc output.txt [options]
tracefile.trc
USER_DUMP_DEST
output.txt
Al fine di prospettare le informazione storicizzate nel file di trace sopra
descritto, occorre dare in pasto lo stesso archivio ad un formattatore
denominato TKPROF la cui sintassi di avvio risulta essere:
TKPROF
file_trace_input
file_output
SORT=(option1,option2,……..)
PRINT=integer
 EXECPU , EXEELA, ……….
Lists only the first integer sorted SQL statements into the output file.
INSERT=file_scripts_sql_output
Creates a SQL script that stores the trace file statistics in the database. This script
creates a table and inserts a row of statistics for each traced SQL statement.
SYS=boolean
Enables and disables the listing of SQL statements issued by the user SYS.
TABLE=schema.table
Specifies the schema and name of the table into which TKPROF temporarily places
execution plans before writing them to the output file.
EXPLAIN=user/password
Determines the execution plan for each SQL statement in the trace file and writes
these execution plans to the output file.
RECORD= file_record_output
Creates a SQL script with the specified filename with all of the nonrecursive SQL in
the trace file.
Example
This example runs TKPROF, accepts a trace file named
"dlsun12_jane_fg_svrmgr_007.trc", and writes a formatted output file named
"outputa.prf":
TKPROF DLSUN12_JANE_FG_SVRMGR_007.TRC OUTPUTA.PRF
EXPLAIN=SCOTT/TIGER TABLE=SCOTT.TEMP_PLAN_TABLE_A
INSERT=STOREA.SQL SYS=NO
SORT=(EXECPU,FCHCPU)
Note the other parameters in this example:
•The EXPLAIN value causes TKPROF to connect as the user SCOTT and use the
EXPLAIN PLAN statement to generate the execution plan for each traced SQL
statement.
•The TABLE value causes TKPROF to use the table TEMP_PLAN_TABLE_A in
the schema SCOTT as a temporary plan table.
•The INSERT value causes TKPROF to generate a SQL script named
STOREA.SQL that stores statistics for all traced SQL statements in the database.
•The SYS parameter with the value of NO causes TKPROF to omit recursive SQL
statements from the output file. In this way you can ignore internal Oracle
statements such as temporary table operations.
•The SORT value causes TKPROF to sort the SQL statements in order of the sum
of the CPU time spent executing and the CPU time spent fetching rows before
writing them to the output file.
Tabular Statistics
TKPROF lists the statistics for a SQL statement returned by the SQL trace facility in
rows and columns. Each row corresponds to one of three steps of SQL statement
processing.
PARSE
This step translates the SQL statement into an execution plan.
This step includes checks for proper security authorization and checks for the
existence of tables, columns, and other referenced objects.
EXECUTE
This step is the actual execution of the statement by Oracle.
For INSERT, UPDATE, and DELETE statements, this step modifies the data.
For SELECT statements, the step identifies the selected rows.
FETCH
This step retrieves rows returned by a query. Fetches are only performed
for SELECT statements.
TKPROF Statistics
•COUNT Number of times a statement was parsed, executed, or fetched.
•CPU Total CPU time in seconds for all parse, execute, or fetch calls for the
statement.
•ELAPSED Total elapsed time in seconds for all parse, execute, or fetch calls for the
statement.
•DISK Total number of data blocks physically read from the datafiles on disk for all
parse, execute, or fetch calls.
•QUERY Total number of buffers retrieved in consistent mode for all parse, execute,
or fetch calls. Buffers are retrieved in consistent mode for queries.
•CURRENT Total number of buffers retrieved in current mode. Buffers are retrieved
in current mode for statements such as INSERT, UPDATE, and DELETE.
•ROWS Total number of rows processed by the SQL statement. This total does not
include rows processed by subqueries of the SQL statement.
Dynamic Performance Views
1) Are maintained by the Oracle server and continuously updated
2) Contain data about disk and memory structures
3) Contain data that is useful for performance tuning
4) Have public synonyms with the prefix V$
Accessing Dynamic Performance Views
OPEN
Data dictionary
MOUNT
NOMOUNT
SHUTDOWN
Dynamic performance views
reading data from disk
Dynamic performance
views reading from memory
Example V$
SGA
Control file
V$PARAMETER
V$SGA
V$OPTION
V$PROCESS
V$SESSION
V$VERSION
V$INSTANCE
V$THREAD
V$CONTROLFILE
V$DATABASE
V$DATAFILE
V$DATAFILE_HEADER
V$LOGFILE
V$SESSTAT
V$STATNAME
V$SYSSTAT
Automated Performance Tuning System
La metodica che si pone alla base della strategia è architettata su quattro step
distinti:
1 - Individuazione degli aspetti del Data Base che si desidera porre sotto analisi,
definizione delle regole e rintraccio dei valori limite.
2 - Collecting data. Acquisizione delle informazioni legate a:
• database
• instance
• schema
• environment
• workload (carico di lavoro).
3 - Viewing dei collected data. Prospetto, tramite reportistiche semplici e di immediata
comprensione, dei dati precedentemente raccolti.
4 - Analyzing data / generate recommendations.
Nel caso in cui i valori limite non siano soddisfatti ecco il sistema intervenire
con una serie di utili consigli se non addirittura con un insieme di risoluzioni
automatiche poste in essere.
Sono controlli che rientrano sotto il nome di "Routine Tuning" da considerarsi come
"prevenzione" essendo gli stessi un help per anticipare quelli che potrebbero divenire
reali problemi prima chegli stessi si presentino con la propria complessita' risolutiva.
Controllo 1-%Library Cache Misses < 1%
select round(sum(reloads)/sum(pins)*100,2) Col1
from v$librarycache;
> shared_pool_size
Controllo 2 -%Data Dictionary Cache Misses < 10%
select round(sum(getmisses)/sum(gets)*100,2) Col1
from v$rowcache;
> shared_pool_size
Controllo 3 -Ratio Logico/Fisico Critico > 80%
select round(((1-(a.value/(b.value+c.value)))*100),2) Col1
from v$sysstat a,
v$sysstat b,
v$sysstat c
where a.name = 'physical reads'
-- accessi fisici
and b.name = 'db block gets'
-- accessi logici
and c.name = 'consistent gets'; -- accessi logici
> db_block_buffer
Controllo 4 -Analisi Aree di Sort
Parallelamente all'area condivisa di ram (SGA), esistono un insieme di aree
dimensionalmente ridotte, non condivise ed in relazione 1:1 con i processi dedicati
al supporto delle connessioni utente (PGA) la cui funzionalita' principale e'
rintracciabile nel supporto alle politiche di sort e di grouping.
Ogni statement che richiama operazioni quali sort e grouping, sfrutta per il
raggiungimento dell'obiettivo l'area PGA. Nel caso in cui la stessa risulti non
sufficiente per accomodare l'attività, quest'ultima migra sui segmenti temporanei
opportunamente creati su disco, con un degrado significativo dei tempi di esecuzione.
Non esiste un limite da utilizzarsi come confronto. Viene demandata alla sensibilità
del DBA, la decisione su di una rianalisi dei parametri di initSID.ora:
sort_area_retained_size
dimensione in bytes allocata nella PGA per
potenziali SORT
sort_area_size
dimensione in bytes allocabile nella PGA per sicuri
SORT.
Sono individuate due soglie (min & max) per cercare di risolvere il maggior numero
di attivita' integralmente in ram.
Controllo 4 -Analisi Aree di Sort
select name,
value
from v$sysstat
where name in ('sorts (memory)','sorts (disk)');
Controllo 5 -Analisi Oggetti con + 25 extents
Il controllo prosegue concentrandosi sugli oggetti (segmenti) con un livello di criticità,
un livello di frammentazione (numero di extents associati) elevato e potenzialmente
colpevolizzabile di un peggioramento delle performances del sistema.
select owner,
segment_name,
segment_type,
tablespace_name,
extents
from dba_segments
where extents > 25
and owner not in ('SYS')
order by owner,segment_type, extents desc;
Auditing Guidelines
•
Define your purpose of auditing
– Suspicious database activity
– Gather historical information
•
Define what you want to audit
– Audit users, statements, or objects
– By session not by access
– Successful or unsuccessful
•
Manage your audit trail
– Monitor the growth of the audit trail
– Protect the audit trail from unauthorized access
Auditing Categories
•
Auditing privileged operations
– Always audited
– Startup, shutdown, and SYSDBA connections
•
Database auditing
– Enabled by DBA
– Cannot record column values
•
Value-based or application auditing
– Implemented through code
– Can record column values
– Used to track changes to tables
Database Auditing
Enable database
auditing
Execute command
User
Parameter file
DBA
Review
audit
information
Specify
audit options
Server
process
Generate
audit trail
Audit options
OS audit
trail
Database
Audit trail
Enabling Auditing Options
•
Statement auditing
AUDIT user;
•
Privilege auditing
AUDIT select any table
BY summit BY ACCESS;
•
Schema object auditing
AUDIT LOCK ON summit.employee
BY ACCESS WHENEVER SUCCESSFUL;
Viewing Auditing Options
Data Dictionary View
Description
ALL_DEF_AUDIT_OPTS
Default audit options
DBA_STMT_AUDIT_OPTS
Statement auditing options
DBA_PRIV_AUDIT_OPTS
Privilege auditing options
DBA_OBJ_AUDIT_OPTS
Schema object auditing
options
Viewing Auditing Results
Audit Trail View
Description
DBA_AUDIT_TRAIL
All audit trail entries
DBA_AUDIT_EXISTS
Records for AUDIT EXISTS/NOT
EXISTS
DBA_AUDIT_OBJECT
Records concerning schema
objects
DBA_AUDIT_SESSION
All connect and disconnect entries
DBA_AUDIT_STATEMENT
Statement auditing records
Autonomous Transactions
•
•
•
•
•
•
An independent transaction started by another
transaction
Independent of the main transaction; not nested
transactions
Do not roll back if the main transaction rolls back.
Changes become visible to other transactions
upon a commit.
Only individual routines can be marked
autonomous.
You cannot mark a nested PL/SQL block as
autonomous.
Autonomous Transactions
MT = Main Transaction
AT = Autonomous Transaction
PROCEDURE proc1
IS
emp_id
NUMBER;
PROCEDURE proc2 IS
BEGIN
PRAGMA
emp_id := 1234;
AUTONOMOUS_TRANSACTION;
dept_id NUMBER;
COMMIT;
INSERT ...
SELECT ...
MT
begins BEGIN
dept_id := 90;
proc2;
UPDATE ...
DELETE
COMMIT;
END proc1;
MT
ends
INSERT ...
UPDATE ...
COMMIT;
END proc2;
MT
suspends
AT
begins
AT
ends
MT
resumes
Autonomous Transactions
Example
PROCEDURE bank_trans
...
log_card_usage (cardnum, loc);
INSERT INTO txn VALUES (9001,1000,...);
END bank_trans;
PROCEDURE log_card_usage
(p_cardno
IN
NUMBER,
p_loc
IN
NUMBER )
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO usage VALUES (p_cardno, p_loc);
COMMIT;
END log_card_usage;
Oracle Supplied Packages
There are more than 70 packages
supplied with the Oracle RDBMS that are
granted to all users (PUBLIC).
They fall into three categories:
• Application development support
• Server management support
• Distributed database packages
Interacting with Operating
System Files
• utl_file Oracle supplied package
– Provides text file input/output capabilities
– Is available with version 7.3 and later
What Is the utl_file Package?
• Extends I/O to text files within PL/SQL
• Provides security for directories on the server through
the init.ora file
• Is similar to standard operating system I/O
–
–
–
–
–
Open files
Get text
Put text
Close files
Use the exceptions specific to the
utl_file package
File Processing Using
utl_file
Open the
text file
Get lines
from the
text file
Put lines
into the
text file
Yes
More
lines to
process?
No
Close
the
text file
utl_file Procedures and
Functions
•
•
•
•
•
•
•
Function fopen
Function is_open
Procedure get_line
Procedure put_line
Procedure new_line
Procedure fflush
Procedure fclose, fclose_all
procedure stampa_testata_file (xpath in varchar2,
xfile in varchar2) is
fileout UTL_FILE.file_type;
begin
fileout := UTL_FILE.fopen(xpath,xfile,'w');
UTL_FILE.put_line(fileout, '=====================================>>>> ');
UTL_FILE.put_line(fileout, '==== Demone Remoto di Controllo RDBMS ===>>>> ');
UTL_FILE.put_line(fileout, '====
===>>>> ');
UTL_FILE.put_line(fileout, '=====================================>>>> ');
UTL_FILE.put_line(fileout, '====SysDate ====== '||to_char(sysdate
,
'dd/mm/yyyy hh24:mi:ss')||' =============>>>> ');
UTL_FILE.put_line(fileout, '=====================================>>>> ');
UTL_FILE.fclose (fileout);
end;
/
Calling External Routines from
PL/SQL
With external routines, you make “callouts”
and, optionally, “callbacks” through PL/SQL.
Java class
method
C routine
PL/SQL
subprogram
External
procedure
Benefits of External Routines
• Integrates the strength and capabilities of different languages to
give transparent access to these routines from within the
database
• Extensibility: Provide functionality in the database that is
specific to a particular application, company, or technological
area
• Reusability: Can be shared by all users on a database, as well
as moved to other databases or computers, providing standard
functionality with limited cost in development, maintenance,
and deployment
How PL/SQL Calls a C External Routine
3
2
Listener
process
4
6
7
Alias
PL/SQL
subprogram library
extproc
process
5
BEGIN
myproc
1
User
process
External routine Shared library
How an External C Routine Is Called
1. The user process invokes a PL/SQL program.
2. The server process executes a PL/SQL subprogram,
which looks up the alias library.
3. The PL/SQL subprogram passes the request to the listener.
4. The listener process spawns the extproc process.
The extproc process remains active throughout your
Oracle session until you log off.
5.The extproc process loads the shared library.
6.The extproc process executes the external procedure.
7.The data status is returned to the server.
Development Steps for External C Routines
1. Create and compile the external routine in 3GL.
2. Link external routine into the shared library at the
operating system level.
3. Create an alias library schema object to map to the operating
system shared library.
4. Grant execute privileges on the library.
5. Publish the external C routine by creating the PL/SQL
subprogram unit specification, which references the alias library.
6. Execute the PL/SQL subprogram that invokes the external
routine.
Creating an Alias Library
• Use the CREATE LIBRARY statement to create an
alias library object.
CREATE OR REPLACE LIBRARY library_name IS|AS
'file_path';
• Grant EXECUTE privilege on the alias library.
GRANT EXECUTE ON library_name TO
user|ROLE|PUBLIC
• Publish the external routine.
• Call the external C routine through PL/SQL.
Accessing a Shared Library Through
Publishing
Publish the external routine in PL/SQL
• The body of the subprogram contains the external
routine registration.
• The external routine runs on the same machine.
• Access is controlled through the alias library.
Publishing an External C Routine
• Identify the external body within a PL/SQL program
CREATE
OR REPLACE
FUNCTIONCfunction_name
to publish
the external
routine.
(parameter_list)
RETURN datatype
regularbody|externalbody
END;
• The external body contains the external C routine
information.
IS|AS LANGUAGE C
LIBRARY libname
[NAME C_function_name]
[CALLING STANDARD C | PASCAL]
[PARAMETERS (param_1, [param_n]);
Oracle Locks
Oracle automatically uses different types of locks to
1) control concurrent access to data
2) prevent destructive interaction between users.
Oracle locks fall into one of the following general categories:
DDL locks (dictionary locks)
DDL locks protect the structure of schema
objects.
For example
the definitions of tables and views.
DML locks (data locks) DML locks protect data.
For example
table locks lock entire tables
row locks lock selected rows.
Internal locks and latches
Internal locks and latches protect internal
database structures such as datafiles.
Internal locks and latches are entirely
automatic.
Oracle DML Locks
DML operations can acquire data locks at two different levels:
1) for specific rows
2) for entire tables.
The only DML locks Oracle acquires automatically are row-level locks.
There is no limit to the number of row locks held by a statement or
transaction.
Row locking provides the finest grain locking possible and so provides
the best possible concurrency and throughput.
A transaction acquires an exclusive DML lock for each individual row
modified by one of the following statements:
INSERT,
UPDATE,
DELETE,
SELECT with the FOR UPDATE clause.
A locked row/table remains locked until you either commit your
transaction or roll it back.
Lock Table Statement
This lock manually overrides automatic locking and permits or denies
access to a table or view by other users for the duration of your operation.
NOWAIT specifies that Oracle returns control to you immediately if the specified
table (or specified partition or subpartition) is already locked by another user. In this
case, Oracle returns a message indicating that the table, partition, or subpartition is
already locked.
Lock Table Statement
lockmode is one of the following:
ROW SHARE allows concurrent access to the locked table, but prohibits users
from locking the entire table for exclusive access.
ROW SHARE is synonymous with SHARE UPDATE, which is
included for compatibility with earlier versions of Oracle.
ROW EXCLUSIVE is the same as ROW SHARE, but also prohibits locking in
SHARE mode. Row Exclusive locks are automatically obtained
when updating, inserting, or deleting.
SHARE
allows concurrent queries but prohibits updates to the locked table.
SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to
look at rows in the table but to prohibit others from locking the table
in SHARE mode or updating rows.
EXCLUSIVE allows queries on the locked table but prohibits any other activity
on it.
Summarizes the information
Oracle DDL Locks
A DDL lock protects the definition of a schema object (for example, a table).
Oracle acquires a dictionary lock automatically on behalf of any DDL
transaction requiring it.
Users cannot explicitly request DDL locks.
Only individual schema objects that are modified or referenced are locked
during DDL operations; the whole data dictionary is never locked.
Most DDL operations require exclusive DDL locks for a resource to prevent
destructive interference with other DDL operations that might modify or
reference the same schema object.
During the acquisition of an exclusive DDL lock, if another DDL lock is
already held on the schema object by another operation, the acquisition
waits until the older DDL lock is released and then proceeds.
Oracle DDL Locks
A share DDL lock is acquired on a schema object for DDL statements
that include the following commands:
AUDIT,
NOAUDIT,
COMMENT,
CREATE [OR REPLACE] VIEW
PROCEDURE
PACKAGE
PACKAGE BODY
FUNCTION
TRIGGER
CREATE SYNONYM
CREATE TABLE
Oracle Dead Locks
Oracle automatically detects deadlock situations and resolves them
by rolling back one of the statements involved in the deadlock,
thereby releasing one set of the conflicting row locks.
A corresponding message also is returned to the transaction
that undergoes statement-level rollback.
Oracle Standby
Implementation
•
•
•
•
•
Fail over Solution
Disaster Recovery Solution (if remote)
Ease of implementation
Minimum impact on Production System
Read Only Standby Database
Overview of Managed Oracle Standby DB
Primary Instance
ARCH
Standby Instance
Net 9i
Redo log
Recovery proc
DBWR
Arc log
Arc log
RFS
Primary DB
Standby DB
Primary
control file
Standby
control file
1
Recovery Mode
2
ReadOnly Mode
3
Activate
NLS Features
• Language support
• Territory support
• Character set support
• Linguistic sorting
• Message support
• Date and time formats
• Numeric formats
• Monetary formats
Different Types
of Encoding Schemes
Oracle supports different classes of
character encoding schemes:
• Single-byte character sets
– 7-bit
– 8-bit
• Varying-width multibyte character set
• Fixed-width multibyte character set
• Unicode (UTF8, AL24UTFFSS)
Character Sets and National Character Sets of a
Database
Database Character Sets
National Character Sets
Defined at creation time
Cannot be changed without
re-creation
Store data columns of type
CHAR, VARCHAR2, CLOB,
LONG
Can store varying-width
character sets
Defined at creation time
Cannot be changed without
re-creation
Store data columns of type
NCHAR, NVARCHAR2 and
NCLOB
Can store fixed-width and
varying-width multibyte
character sets
NLS Guidelines
• Choose a closely related database
character set and national character set.
• String operations might be faster with
fixed-width character sets.
• Variable-width character sets use space
more efficiently.
Specifying Language-Dependent Behavior
Initialization parameter
Environment variable
ALTER SESSION
command
Specifying Language-Dependent Behavior for the
Server
•
NLS_LANGUAGE specifies:
- The language for messages
- Day and month names
- Symbols for A.D, B.C, A.M, P.M.
- The default sorting mechanism
• NLS_TERRITORY specifies:
- Day and week numbering
- Default date format, decimal character,
group separator, and the default ISO
and local currency symbols
Dependent Language and Territory Default
Values
PARAMETER
VALUES
NLS_LANGUAGE
AMERICAN
NLS_DATE_LANGUAGE
AMERICAN
NLS_SORT
BINARY
NLS_TERRITORY
AMERICA
NLS_CURRENCY
$
NLS_ISO_CURRENCY
NLS_DATE_FORMAT
NLS_NUMERIC_CHARACTERS
AMERICA
DD-MON-YY
,.
Specifying Language-Dependent Behavior for the
Session
•
Environment variable: NLS_LANG=<language>_<territory>.<charset>
•
Additional environment variables:
• NLS_DATE_FORMAT
• NLS_DATE_LANGUAGE
• NLS_SORT
• NLS_NUMERIC_CHARACTERS
• NLS_CURRENCY
• NLS_ISO_CURRENCY
• NLS_CALENDAR
Character Sets
in Client-Server Architecture
NLS_LANG=<language>_<territory>.<charset>
NLS_NCHAR=<ncharset>
CREATE DATABASE ...
CHARACTER SET <charset>
NATIONAL CHARACTER SET
<ncharset>
...
Specifying Language-Dependent Behavior for the
Session
ALTER SESSION SET
NLS_DATE_FORMAT=‘DD.MM.YYYY’;
DBMS_SESSION.SET_NLS(‘NLS_DATE_FORMAT’,
’’’DD.MM.YYYY’’’) ;
Sorting
• Oracle provides a linguistic sort.
• NLS_SORT specifies types of sort.
• The NLSSORT function reflects
linguistic comparison.
ALTER SESSION SET NLS_SORT=GERMAN;
SELECT letter FROM letters ORDER BY letter;
LETTER
-----ä
z
Using NLS Parameters
in SQL Functions
SELECT TO_CHAR(hiredate,’DD.MON.YYYY’,
‘NLS_DATE_LANGUAGE=GERMAN’) FROM emp;
SELECT ename, TO_CHAR(sal,’9G999D99’,
‘NLS_NUMERIC_CHARACTERS=‘‘,.’’’)
FROM emp;
Obtaining Information
About Character Sets
NLS_DATABASE_PARAMETERS:
• PARAMETER
NLS_CHARACTERSET,
NLS_NCHAR_CHARACTERSET
• VALUE
Obtaining Information
About NLS Settings
• NLS_INSTANCE_PARAMETERS:
– PARAMETER ( NLS initialization parameters that
have been explicitly set)
– VALUE
• NLS_SESSION_PARAMETERS:
– PARAMETER ( NLS session parameters)
– VALUE
Obtaining Information
About NLS Settings
• V$NLS_VALID_VALUES:
– PARAMETER
(LANGUAGE, SORT, TERRITORY, CHARACTERSET)
– VALUE
• V$NLS_PARAM ETERS:
– PARAMETER (NLS session
parameters, NLS_CHARACTERSET)
– VALUE