TuningTuning
SQL Trace FacilitySQL Trace Facility
Sono in grado di fornire informazioni preziose, per ogni statement SQLchiamato 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 Filewith TKPROF
Formatting the Trace Filewith TKPROF
$ tkprof tracefile.trc output.txt [options]
tracefile.trc output.txt
USER_DUMP_DEST
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_outputSORT=(option1,option2,……..) EXECPU , EXEELA, ……….PRINT=integer
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=booleanEnables and disables the listing of SQL statements issued by the user SYS.
TABLE=schema.tableSpecifies the schema and name of the table into which TKPROF temporarily placesexecution plans before writing them to the output file.
EXPLAIN=user/passwordDetermines the execution plan for each SQL statement in the trace file and writes these execution plans to the output file.
RECORD= file_record_outputCreates 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.PRFEXPLAIN=SCOTT/TIGER TABLE=SCOTT.TEMP_PLAN_TABLE_A INSERT=STOREA.SQL SYS=NOSORT=(EXECPU,FCHCPU)
Note the other parameters in this example:•The EXPLAIN value causes TKPROF to connect as the user SCOTT and use theEXPLAIN PLAN statement to generate the execution plan for each traced SQLstatement.•The TABLE value causes TKPROF to use the table TEMP_PLAN_TABLE_A inthe schema SCOTT as a temporary plan table.•The INSERT value causes TKPROF to generate a SQL script namedSTOREA.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 SQLstatements from the output file. In this way you can ignore internal Oraclestatements such as temporary table operations.•The SORT value causes TKPROF to sort the SQL statements in order of the sumof the CPU time spent executing and the CPU time spent fetching rows beforewriting them to the output file.
Tabular Statistics
TKPROF lists the statistics for a SQL statement returned by the SQL trace facility inrows and columns. Each row corresponds to one of three steps of SQL statementprocessing.
PARSE This step translates the SQL statement into an execution plan.This step includes checks for proper security authorization and checks for theexistence 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 StatisticsTKPROF 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.
•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 ViewsDynamic 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$
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$
OPENOPEN
MOUNTMOUNT
NOMOUNTNOMOUNT
Data dictionaryData dictionary
Accessing Dynamic Performance ViewsAccessing Dynamic Performance Views
Dynamic performance views Dynamic performance views reading data from diskreading data from disk
Dynamic performanceDynamic performanceviews reading from memoryviews reading from memory
SHUTDOWN SHUTDOWN
Example V$Example V$
SGA
V$PARAMETERV$PARAMETERV$SGAV$SGAV$OPTIONV$OPTIONV$PROCESSV$PROCESSV$SESSION V$SESSION V$VERSIONV$VERSIONV$INSTANCEV$INSTANCE
Control fileV$THREADV$THREADV$CONTROLFILE V$CONTROLFILE V$DATABASEV$DATABASEV$DATAFILEV$DATAFILEV$DATAFILE_HEADER V$DATAFILE_HEADER V$LOGFILEV$LOGFILE
V$SESSTAT
V$STATNAME
V$SYSSTAT
Automated Performance Tuning System
La metodica che si pone alla base della strategia è architettata su quattro stepdistinti:
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 divenirereali 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 dedicatial 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.
select name, value from v$sysstat where name in ('sorts (memory)','sorts (disk)');
Controllo 4 -Analisi Aree di Sort
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_segmentswhere extents > 25 and owner not in ('SYS') order by owner,segment_type, extents desc;
Auditing GuidelinesAuditing 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
• 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 CategoriesAuditing 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
• 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 AuditingDatabase Auditing
Parameter file
Enable database
auditing
DBA
Audit options
Specify audit options
Database
User
Execute command
Serverprocess
Audit trail
Generate
audit trail
OS audit
trail
Review
audit
information
Enabling Auditing OptionsEnabling Auditing Options
• Statement auditing
• Privilege auditing
• Schema object auditing
• Statement auditing
• Privilege auditing
• Schema object auditing
AUDIT select any table
BY summit BY ACCESS;
AUDIT select any table
BY summit BY ACCESS;
AUDIT user;AUDIT user;
AUDIT LOCK ON summit.employee
BY ACCESS WHENEVER SUCCESSFUL;
AUDIT LOCK ON summit.employee
BY ACCESS WHENEVER SUCCESSFUL;
Data Dictionary View
ALL_DEF_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_OBJ_AUDIT_OPTS
Description
Default audit options
Statement auditing options
Privilege auditing options
Schema object auditing
options
Viewing Auditing OptionsViewing Auditing Options
Audit Trail View
DBA_AUDIT_TRAIL
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
Description
All audit trail entries
Records for AUDIT EXISTS/NOT
EXISTS
Records concerning schema
objects
All connect and disconnect entries
Statement auditing records
Viewing Auditing ResultsViewing Auditing Results
Autonomous TransactionsAutonomous 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.
• 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 TransactionsAutonomous Transactions
PROCEDURE proc1 IS
emp_id NUMBER;
BEGIN
emp_id := 1234;
COMMIT;
INSERT ...
SELECT ...
proc2;
DELETE
COMMIT;
END proc1;
MTMTbeginsbegins
PROCEDURE proc2 IS
PRAGMA
AUTONOMOUS_TRANSACTION;
dept_id NUMBER;
BEGIN
dept_id := 90;
UPDATE ...
INSERT ...
UPDATE ...
COMMIT;
END proc2;
PRAGMA
AUTONOMOUS_TRANSACTION;
MTMTsuspendssuspends
ATATbeginsbegins
ATATendsendsMTMTresumesresumes
MTMTendsends
MT = Main TransactionMT = Main TransactionAT = Autonomous TransactionAT = Autonomous Transaction
Autonomous TransactionsAutonomous Transactions
PROCEDURE bank_trans... log_card_usage (cardnum, loc); INSERT INTO txn VALUES (9001,1000,...);END bank_trans;
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;
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;
PRAGMA AUTONOMOUS_TRANSACTION;
ExampleExample
Oracle Supplied PackagesOracle 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
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
Interacting with Operating System Files
• utl_file Oracle supplied package– Provides text file input/output capabilities– Is available with version 7.3 and later
• 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?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
• 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
Open the text file
Get linesfrom thetext file
Put linesinto thetext file
More lines to
process?
YesYes
NoNo Close the
text file
File Processing Using utl_file
File Processing Using utl_file
utl_file Procedures and Functions
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
• 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;/
PL/SQL PL/SQL subprogramsubprogram
Calling External Routines from PL/SQL
Calling External Routines from PL/SQL
With external routines, you make “callouts” and, optionally, “callbacks” through PL/SQL.With external routines, you make “callouts” and, optionally, “callbacks” through PL/SQL.
External External procedureprocedure
Java class Java class methodmethod
C routineC routine
Benefits of External RoutinesBenefits 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
• 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
1
BEGIN myproc
PL/SQL PL/SQL subprogramsubprogram
AliasAliaslibrarylibrary
23 Listener
process4
extprocprocess
Userprocess
How PL/SQL Calls a C External RoutineHow PL/SQL Calls a C External Routine
Shared libraryShared library
5
External routineExternal routine
6
7
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 RoutinesDevelopment Steps for External C Routines
1. Create and compile the external routine in 3GL.
2. Link external routine into the shared library at theoperating 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.
1. Create and compile the external routine in 3GL.
2. Link external routine into the shared library at theoperating 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.
• Use the CREATE LIBRARY statement to create an alias library object.
• Grant EXECUTE privilege on the alias library.
• Publish the external routine.
• Call the external C routine through PL/SQL.
• Use the CREATE LIBRARY statement to create an alias library object.
• Grant EXECUTE privilege on the alias library.
• Publish the external routine.
• Call the external C routine through PL/SQL.
CREATE OR REPLACE LIBRARY library_name IS|AS
'file_path';
GRANT EXECUTE ON library_name TO user|ROLE|
PUBLIC
Creating an Alias LibraryCreating an Alias Library
Accessing a Shared Library Through Publishing
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.
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.
• Identify the external body within a PL/SQL program to publish the external C routine.
• The external body contains the external C routine information.
• Identify the external body within a PL/SQL program to publish the external C routine.
• The external body contains the external C routine information.
CREATE OR REPLACE FUNCTION function_name (parameter_list)RETURN datatype regularbody|externalbodyEND;
IS|AS LANGUAGE C LIBRARY libname [NAME C_function_name] [CALLING STANDARD C | PASCAL] [PARAMETERS (param_1, [param_n]);
Publishing an External C RoutinePublishing an External C Routine
Oracle LocksOracle 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:
Internal locks and latches Internal locks and latches protect internal database structures such as datafiles. Internal locks and latches are entirely automatic.
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.
Oracle DML LocksOracle DML LocksDML 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 providesthe 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 StatementLock 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 StatementLock 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 informationSummarizes the information
Oracle DDL LocksOracle 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 LocksOracle 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 LocksOracle 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 transactionthat 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 control file
Primary DB
Primary Instance
Redo log
Arc log
ARCH
Standby DB
DBWR
Standby Instance
Recovery proc
Standby control file
RFS
Arc log
Net 9i
Recovery Mode1ReadOnly Mode2
Activate3
NLS FeaturesNLS Features
• Language supportLanguage support
• Territory support Territory support
• Character set supportCharacter set support
• Linguistic sortingLinguistic sorting
• Message supportMessage support
• Date and time formatsDate and time formats
• Numeric formats Numeric formats
• Monetary formatsMonetary formats
• Language supportLanguage support
• Territory support Territory support
• Character set supportCharacter set support
• Linguistic sortingLinguistic sorting
• Message supportMessage support
• Date and time formatsDate and time formats
• Numeric formats Numeric formats
• Monetary formatsMonetary formats
Different Types of Encoding Schemes
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)
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
Character Sets and National Character Sets of a Database
Database 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
National 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 GuidelinesNLS 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.
• 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 Specifying Language-Dependent Behavior
Initialization parameter
Environment variable
ALTER SESSION command
Specifying Language-Dependent Behavior for the Server
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
• 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
PARAMETER
NLS_LANGUAGE
NLS_DATE_LANGUAGE
NLS_SORT
NLS_TERRITORY
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_DATE_FORMAT
NLS_NUMERIC_CHARACTERS
VALUES
AMERICAN
AMERICAN
BINARY
AMERICA
$ AMERICA
DD-MON-YY
,.
Dependent Language and Territory Default Values
Dependent Language and Territory Default Values
Specifying Language-Dependent Behavior for the Session
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
• 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
Character Sets in Client-Server Architecture
CREATE DATABASE ...
CHARACTER SET <charset>
NATIONAL CHARACTER SET
<ncharset>
...
CREATE DATABASE ...
CHARACTER SET <charset>
NATIONAL CHARACTER SET
<ncharset>
...
NLS_LANG=<language>_<territory>.<charset>
NLS_NCHAR=<ncharset>
NLS_LANG=<language>_<territory>.<charset>
NLS_NCHAR=<ncharset>
Specifying Language-Dependent Behavior for the Session
Specifying Language-Dependent Behavior for the Session
ALTER SESSION SET
NLS_DATE_FORMAT=‘DD.MM.YYYY’;
ALTER SESSION SET
NLS_DATE_FORMAT=‘DD.MM.YYYY’;
DBMS_SESSION.SET_NLS(‘NLS_DATE_FORMAT’,
’’’DD.MM.YYYY’’’) ;
DBMS_SESSION.SET_NLS(‘NLS_DATE_FORMAT’,
’’’DD.MM.YYYY’’’) ;
SortingSorting
• Oracle provides a linguistic sort.
• NLS_SORT specifies types of sort.
• The NLSSORT function reflects linguistic comparison.
• 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
ALTER SESSION SET NLS_SORT=GERMAN;
SELECT letter FROM letters ORDER BY letter;LETTER
------
ä
z
Using NLS Parameters in SQL Functions
Using NLS Parameters in SQL Functions
SELECT TO_CHAR(hiredate,’DD.MON.YYYY’,
‘NLS_DATE_LANGUAGE=GERMAN’) FROM emp;
SELECT TO_CHAR(hiredate,’DD.MON.YYYY’,
‘NLS_DATE_LANGUAGE=GERMAN’) FROM emp;
SELECT ename, TO_CHAR(sal,’9G999D99’,
‘NLS_NUMERIC_CHARACTERS=‘‘,.’’’)
FROM emp;
SELECT ename, TO_CHAR(sal,’9G999D99’,
‘NLS_NUMERIC_CHARACTERS=‘‘,.’’’)
FROM emp;
Obtaining Information About Character Sets
Obtaining Information About Character Sets
NLS_DATABASE_PARAMETERS:NLS_DATABASE_PARAMETERS:
• PARAMETER NLS_CHARACTERSET,
NLS_NCHAR_CHARACTERSET
• VALUE
NLS_DATABASE_PARAMETERS:NLS_DATABASE_PARAMETERS:
• PARAMETER NLS_CHARACTERSET,
NLS_NCHAR_CHARACTERSET
• VALUE
Obtaining Information About NLS Settings
Obtaining Information About NLS Settings
• NLS_INSTANCE_PARAMETERS:NLS_INSTANCE_PARAMETERS:
– PARAMETER ( NLS initialization parameters that have been explicitly set)
– VALUE
• NLS_SESSION_PARAMETERS:NLS_SESSION_PARAMETERS:
– PARAMETER ( NLS session parameters)
– VALUE
• NLS_INSTANCE_PARAMETERS:NLS_INSTANCE_PARAMETERS:
– PARAMETER ( NLS initialization parameters that have been explicitly set)
– VALUE
• NLS_SESSION_PARAMETERS:NLS_SESSION_PARAMETERS:
– PARAMETER ( NLS session parameters)
– VALUE
Obtaining Information About NLS Settings
Obtaining Information About NLS Settings
• V$NLS_VALID_VALUES:V$NLS_VALID_VALUES:
– PARAMETER (LANGUAGE, SORT, TERRITORY, CHARACTERSET)
– VALUE
• V$NLS_PARAM ETERS:V$NLS_PARAM ETERS:
– PARAMETER (NLS sessionparameters, NLS_CHARACTERSET)
– VALUE
• V$NLS_VALID_VALUES:V$NLS_VALID_VALUES:
– PARAMETER (LANGUAGE, SORT, TERRITORY, CHARACTERSET)
– VALUE
• V$NLS_PARAM ETERS:V$NLS_PARAM ETERS:
– PARAMETER (NLS sessionparameters, NLS_CHARACTERSET)
– VALUE
Top Related