Oracle Miscellaneous New Features

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

Enhancements in Managing Multitier Environments - New Dimensions for Statistics Collection and Tracing

The new dimensions for collecting statistics are:

o Client identifier
o Service name
o Combinations of service name, module name, and action name

Enabling Collection of Client and Service Statistics

For client-Level Statistics use:

DBMS_MONITOR.CLIENT_ID_STAT_ENABLE(<client_id>)
DBMS_MONITOR.CLIENT_ID_STAT_DISABLE(<Client_id>
)

For Service-Level Statistics:

DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(<service_
name>,<module_name>, <action_name>)

For example:

DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
service_name=>'APPS1',module_name =>'PAYROLL')

To enable tracing for a Service named APPS1:

DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('APPS1',
DBMS_MONITOR.ALL_MODULES,
DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL)

To disable tracing specified in the previous step:

DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE('APPS1')

Note: The DBMS_APPLICATION_INFO has two procedures, SET_MODULE and SET_ACTION, which allow programmers to specify module and action names.

Of course, statistic accumulation for a session ID is still possible:

DBMS_MONITOR.SESSION_TRACE_ENABLE
(SESSION_ID=>139, SERIAL_NUM=>53, WAITS=>TRUE,
BINDS=>FALSE);

For information about tracing in the instance and database level, refer to "Database and Instance Level Trace".

Marking the Trace Files

You can also add your own marker to the trace file names so you can more easily find the generated files.

ALTER SESSION SET TRACEFILE_IDENTIFIER
="hr_report"

Viewing the New Statistics

Once you have enabled the collection of the new client identifier, service, module, and action names statistics, you can view them by using Database Control. There are also several new views:

DBA_ENABLED_AGGREGATIONS - Displays information about enabled statistics aggregation

DBA_ENABLED_TRACES - Shows all enabled traces in the system

V$CLIENT_STATS - Displays statistics on a client level (CLIENT_IDENTIFIER based)

V$SERVICE_STATS - Displays basic performance statistics

V$SERV_MOD_ACT_STATS - Displays statistics for a combination of serve /module/action names.

Using the TRCSESS Tool to Analyze Trace Files

You can use Oracle�s trcsess command-line utility to consolidate the information from all your trace files into a single output file.

trcsess output="hr_report.trc" service="APPS1"
module="PAYROLL" action="bulk load"

You can then run TKPROF against the consolidated trace file to generate a report.

..\udump> tkprof hr_report.trc
output=hr_trc_report SORT=(EXEELA, PRSELA,FCHELA)

SQL and PL/SQL Enhancements

UTL_COMPRESS Package - Oracle Database 10g provides the new UTL_COMPRESS
package to compress and uncompress data, with the compressed output compatible with the output of the familiar GZIP and GUNZIP compression utilities.

UTL_MAIL Package - In order to use the UTL_MAIL package to send email, you must first execute the utlmail.sql and prvtmail.plb scripts located in your ORACLE_HOME/rdbms/admin directory.

Regular Expressions

Oracle provides the following regular expression functions for text complex searching:

o REGEXP_LIKE
o REGEXP_REPLACE
o REGEXP_INSTRING
o REGEXP_SUBSTRING

Case-Insensitive and Accent-Insensitive Query and Sort

When you use the NLS_SORT parameter, you can use the optional suffixes AI or CI to specify whether the sort is accent insensitive (AI) or case insensitive (CI).

NLS_SORT = <NLS_sort_name>[_AI| _CI]
NLS_SORT = FRENCH_M_AI

CLOB and NCLOB Implicit Conversions

Oracle Database 10g introduces the implicit conversion between LOBs and NCLOBs. Oracle now supports implicit conversion in SQL IN/OUT bind variables, PL/SQL function and procedure parameter passing, and PL/SQL variable assignment.

User-Specified Quoting Characters

You use the new quote operator q to provide your own quotation mark delimiters.