tuning n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Tuning PowerPoint Presentation
Download Presentation
Tuning

Loading in 2 Seconds...

play fullscreen
1 / 66

Tuning - PowerPoint PPT Presentation


  • 138 Views
  • Uploaded on

Tuning. SQL Trace Facility. Sono in grado di fornire informazioni preziose, per ogni statement SQL chiamato in causa, generando le seguenti statistiche attive: a livello di sessione (alter session set sql_trace = TRUE)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Tuning' - ronat


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2

SQL Trace Facility

  • Sono in grado di fornire informazioni preziose, per ogni statement SQL
  • chiamato in causa, generando le seguenti statistiche attive:
  • a livello di sessione (alter session set sql_trace = TRUE)
  • 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
Formatting the Trace Filewith TKPROF

$ tkprof tracefile.trc output.txt [options]

tracefile.trc

output.txt

USER_DUMP_DEST

slide4

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,……..)  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=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.
slide5

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.
slide7

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.
slide8

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.
slide9

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

Dynamic performance views reading data from disk

NOMOUNT

Dynamic performance

views reading from memory

SHUTDOWN

slide10

Example V$

V$PARAMETERV$SGAV$OPTIONV$PROCESSV$SESSION V$VERSIONV$INSTANCE

SGA

V$THREADV$CONTROLFILE V$DATABASEV$DATAFILEV$DATAFILE_HEADER V$LOGFILE

Control file

slide11

V$SESSTAT

V$SYSSTAT

V$STATNAME

slide12

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.
slide13

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

slide14

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

slide15

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.

slide16

Controllo 4 -Analisi Aree di Sort

select name,

value

from v$sysstat

where name in ('sorts (memory)','sorts (disk)');

slide17

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
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 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

Execute command

Enable database auditing

User

DBA

Parameter file

Serverprocess

Specify audit options

Review

audit information

Generate audit trail

Audit options

OS audit trail

Audittrail

Database Auditing

Database

enabling auditing options
Enabling Auditing Options

AUDIT user;

  • Statement auditing
  • Privilege auditing
  • Schema object auditing

AUDIT select any table BY summit BY ACCESS;

AUDIT LOCK ON summit.employeeBY ACCESS WHENEVER SUCCESSFUL;

viewing auditing options
Viewing Auditing Options

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 results
Viewing Auditing Results

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

slide24

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.
slide25

PROCEDURE proc2 IS

PRAGMA

AUTONOMOUS_TRANSACTION;

dept_id NUMBER;

BEGIN

dept_id := 90;

UPDATE ...

INSERT ...

UPDATE ...

COMMIT;

END proc2;

PRAGMA

AUTONOMOUS_TRANSACTION;

MT

begins

MT

suspends

AT

begins

MT

ends

AT

ends

MT

resumes

Autonomous Transactions

MT = Main Transaction

AT = Autonomous Transaction

PROCEDURE proc1 IS

emp_id NUMBER;

BEGIN

emp_id := 1234;

COMMIT;

INSERT ...

SELECT ...

proc2;

DELETE

COMMIT;

END proc1;

slide26

PRAGMA AUTONOMOUS_TRANSACTION;

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
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
slide28

Interacting with Operating System Files

  • utl_file Oracle supplied package
    • Provides text file input/output capabilities
    • Is available with version 7.3 and later
slide29

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
slide30

Yes

More lines to process?

No

File Processing Using utl_file

Get linesfrom thetext file

Open the text file

Close the text file

Put linesinto thetext file

slide31

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
slide33

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

Java class method

C routine

External procedure

Calling External Routines from PL/SQL

With external routines, you make “callouts”

and, optionally, “callbacks” through PL/SQL.

PL/SQL subprogram

benefits of external routines
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

Listenerprocess

2

4

6

extprocprocess

7

5

Aliaslibrary

PL/SQL subprogram

1

Userprocess

External routine

Shared library

How PL/SQL Calls a C External Routine

BEGIN myproc

slide37

How an External C Routine Is Called

      • The user process invokes a PL/SQL program.
      • 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 processremains 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
Development 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.

creating an alias library
Creating an Alias Library
  • 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

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.
publishing an external c routine
Publishing an External C Routine
  • 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|externalbody

END;

IS|AS LANGUAGE C

LIBRARY libname

[NAME C_function_name]

[CALLING STANDARD C | PASCAL]

[PARAMETERS (param_1, [param_n]);

slide42

Oracle Locks

  • Oracle automatically uses different types of locks to
  • control concurrent access to data
  • 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.

slide43

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.

slide44

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.

slide45

Lock Table Statement

lockmode is one of the following:

ROW SHAREallows 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 EXCLUSIVEis the same as ROW SHARE, but also prohibits locking in

SHARE mode. Row Exclusive locks are automatically obtained

when updating, inserting, or deleting.

SHAREallows concurrent queries but prohibits updates to the locked table.

SHARE ROW EXCLUSIVEis 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.

EXCLUSIVEallows queries on the locked table but prohibits any other activity

on it.

slide47

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.

slide48

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

slide49

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.

slide50

Oracle Standby Implementation

  • Fail over Solution
  • Disaster Recovery Solution (if remote)
  • Ease of implementation
  • Minimum impact on Production System
  • Read Only Standby Database
slide51

Primary Instance

Standby Instance

3

2

1

ReadOnly Mode

Recovery Mode

Activate

Net 9i

ARCH

Redo log

Recovery proc

DBWR

Arc log

Arc log

RFS

Primary DB

Standby DB

Primary control file

Standby control file

Overview of Managed Oracle Standby DB

nls features
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
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
Character Sets and National Character Sets of a Database

National Character Sets

Defined at creation timeCannot be changed withoutre-creationStore data columns of type NCHAR, NVARCHAR2 and NCLOBCan store fixed-width and varying-width multibyte character sets

Database Character Sets

Defined at creation timeCannot be changed withoutre-creationStore data columns of typeCHAR, VARCHAR2, CLOB, LONGCan store varying-width character sets

nls guidelines
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
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

dependent language and territory default values
Dependent Language and Territory Default Values

VALUES

AMERICANAMERICAN BINARY

AMERICA $ AMERICA DD-MON-YY ,.

PARAMETER

NLS_LANGUAGENLS_DATE_LANGUAGE NLS_SORT

NLS_TERRITORYNLS_CURRENCY NLS_ISO_CURRENCY NLS_DATE_FORMAT NLS_NUMERIC_CHARACTERS

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
character sets in client server architecture
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 session1
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
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
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
Obtaining Information About Character Sets
  • NLS_DATABASE_PARAMETERS:
    • PARAMETER NLS_CHARACTERSET,
    • NLS_NCHAR_CHARACTERSET
    • VALUE
obtaining information about nls settings
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 settings1
Obtaining Information About NLS Settings
  • V$NLS_VALID_VALUES:
      • PARAMETER (LANGUAGE, SORT, TERRITORY, CHARACTERSET)
      • VALUE
  • V$NLS_PARAM ETERS:
      • PARAMETER (NLS sessionparameters, NLS_CHARACTERSET)
      • VALUE