Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

December 28, 2006

Expanded Supplied Packages with 10g – Part I

Filed under: Oracle 10g New Features — H.Tonguç Yılmaz @ 1:33 pm

Beside New Dictionary Enhancements with 10g Series I will start to write on Expanded Supplied Packages with 10g. With 10g several packages upgraded and enhanced, also documentation is upgraded. “catproc.sql” can be used to identify these packages installed by default.

Oracle 9iR2 Supplied PL/SQL Packages & Types Reference Covers 101 Packages
Oracle 10gR1 PL/SQL Packages & Types Reference Covers 166 Packages
Oracle 10gR2 PL/SQL Packages & Types Reference Covers 180 Packages

– New Oracle9i Oracle Supplied Packages (19)

DBMS_APPLY_ADM DBMS_CAPTURE_ADM
DBMS_FGA DBMS_FLASHBACK
DBMS_LDAP DBMS_LIBCACHE
DBMS_LOGMNR_CDC_PUBLISH DBMS_LOGMNR_CDC_SUBSCRIBE
DBMS_METADATA DBMS_ODCI
DBMS_OUTLN_EDIT DBMS_REDEFINITION
DBMS_TRANSFORM DBMS_WM
DBMS_XMLGEN DBMS_XMLQUERY
DMBS_XMLSAVE UTL_ENCODE
UTL_URL

– New Oracle9i R2 Oracle Supplied Packages (18)

DBMS_APPLY_ADM DBMS_CAPTURE_ADM
DBMS_LOGSTDBY DBMS_MGWADM
DBMS_MGWMSG DBMS_PROPAGATION_ADM
DBMS_RULE DBMS_RULE_ADM
DBMS_STORAGE_MAP DBMS_STREAMS
DBMS_STREAMS_ADM DBMS_XDB
DBMS_XDBT DBMS_XDB_VERSION
DBMS_XMLDOM DBMS_XMLPARSER
DBMS_XPLAN DBMS_XSLPROCESSOR

– New Oracle10g R1 Oracle Supplied Packages (51) and 60 Packages Updates in Oracle10gR1

DBMS_ADVANCED_REWRITE DBMS_SERVICE
DBMS_ADVISOR DBMS_SQLTUNE
DBMS_CRYPTO DBMS_STAT_FUNCS
DBMS_DATAPUMP DBMS_STREAMS_AUTH
DBMS_DATA_MINING DBMS_STREAMS_MESSAGING
DBMS_DATA_MINING_TRANSFORM DBMS_STREAMS_TABLESPACE_ADM
DBMS_DIMENSION DBMS_WARNING
DBMS_FILE_TRANSFER DBMS_WORKLOAD_REPOSITORY
DBMS_FREQUENT_ITEMSET DBMS_XDBZ
DBMS_JAVA DBMS_XMLSTORE
DBMS_MONITOR HTF
DBMS_SCHEDULER HTMLDB_APPLICATION
DBMS_SERVER_ALERT HTMLDB_CUSTOM_AUTH
HTMLDB_ITEM SDO_GEOR
HTMLDB_UTIL SDO_GEOR_UTL
HTP SDO_NET
OWA_CACHE SDO_SAM
OWA_COOKIE SDO_TOPO
OWA_CUSTOM SDO_TOPO_MAP
OWA_IMAGE UTL_COMPRESS
OWA_OPT_LOCK UTL_DBWS
OWA_PATTERN UTL_I18N
OWA_SEC UTL_LMS
OWA_TEXT UTL_MAIL
OWA_UTIL WPG_DOCLOAD
SDO_GCDR

– New Oracle10g R2 Oracle Supplied Packages (14) and 53 Packages Updates in Oracle10gR2

DBMS_AQELM DBMS_FILE_GROUP
DBMS_AQIN DBMS_PREDICTIVE_ANALYTICS
DBMS_CHANGE_NOTIFICATION DBMS_PREPROCESSOR
DBMS_DB_VERSION DBMS_RLMGR
DBMS_EPG DBMS_TDB
DBMS_ERRLOG SDO_NET_MEM
DBMS_EXPFIL UTL_NLA

PL/SQL has grown tremendously with Oracle 10g R1 and R2. This growth includes a wide range of both DBA and Developer enhancements. We need to learn these features and make sure thoroughly understand these prior to start using. Knowing what Oracle has provided will expand your arsenal, utilize your cost and get you a competitive advantage especially in terms of being time-to-market. These packages are all supported by Oracle and tested over and over again, so dont reinvent the wheel and get rid of the maintenance costs of a newly developed applications ;)

Continue reading with Part 2

References used :
“The PL/SQL Grid: Time to Expand to 10g R1 & 10g R2¨ presentation by Joe Trezzo
What’s New in PL/SQL in Oracle Database 10g? Oracle Technology Network(OTN) Paper

Advertisements

Oracle Product Family and Pricing Highlights

Filed under: Oracle Other — H.Tonguç Yılmaz @ 12:54 pm

This Oracle Whitepaper prepared at August 2006 summaries the five oracle editions, the advanced options and the management pack of Oracle;

The editions:
Standard Edition One,Standard edition, Entreprise Edition, Personal Edition, Express Editions

The options:
RAC, Partitioning, Advanced Security, Label Security, OLAP, Data Mining, Spatial

The management packs:
Change management, Diagnostic, Tuning and Configuration Management Packs

Oracle database software comes at a cost. As of March 2006, the Enterprise Edition sells at a list price of US$40,000 per machine processor. Standard Edition comes cheaper, $15,000 per processor (it can run on up to 4 processors but has fewer features than Enterprise Edition, it lacks proper parallelization, etc; but remains quite suitable for running medium-sized applications). Standard ONE edition sells even more cheaply, $5000 per processor (but limited to 2 CPUs). Standard Edition ONE sells on a per-seat basis, and costs $149 per user, with a 5-user minimum. Oracle Corporation usually sells the licenses with an extra 22% cost for support and upgrades (access to MetaLink – Oracle’s support site) which customers need to renew annually.

Oracle Express Edition(Oracle XE), an addition to the Oracle database product family(beta version released in 2005, production version released in February 2006), offers a free version of the Oracle 10g Release 2 RDBMS, but limited to 4 Gb of user data, 1 Gb of RAM, and which will use no more than one CPU and which lacks some Oracle features like an internal JVM, partitioning, bitmap indexes and materialized views.

So what does “Release 10.2.0.2.0” mean?

1st Digit: “10” is a major database release number.
2nd Digit: “2” is the database maintenance release number.
3rd Digit: “0” is the application server release number.
4th Digit: “2” identifies a release level specific to a component.
5th Digit: “0” identifies a platform specific release.

To check the Oracle version from the SQL*Plus prompt, issue following sql:

SELECT banner FROM v$version WHERE banner LIKE ‘Oracle%’;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bi

SELECT version FROM product_component_version WHERE product LIKE ‘Oracle%’;

VERSION
—————————————————————-
10.2.0.2.0

From banner column of v$version you can also identify if you are runing Oracle in 32-bit or 64-bit architecture. For another easy way to identify this you may check this link.

Refences Used :
http://www.oracle.com/database/product_editions.html
http://www.oracle.com/support/lifetime-support-policy.html
Oracle® Wiki
OracleBrains.Com Archive, Understanding Oracle Release Number Format Article

New Dictionary Enhancements with 10g – Part I

Filed under: Oracle 10g New Features — H.Tonguç Yılmaz @ 9:39 am

For dictionary terminalogy in Oracle please read this chapter of Oracle® Database Concepts Guide.

In this series I will give some examples on my favorite new dictionary views after 10g, I hope you like them as much as I do :)

1- OS Statistics and Finding System CPU Utilization with 10g

Operating system monitoring tools can be used to determine what processes are running on the system as a whole. If the system is too heavily loaded, check the memory, I/O, and process management areas described later in this section. Tools such as sar -u on many UNIX-based systems allow you to examine the level of CPU utilization on your entire system. CPU utilization in UNIX is described in statistics that show user time, system time, idle time, and time waiting for I/O. A CPU problem exists if idle time and time waiting for I/O are both close to zero (less than 5%) at a normal or low workload. On Windows, use the administrative performance tool to monitor CPU utilization. This utility provides statistics on processor time, user time, privileged time, interrupt time, and DPC time.

In Oracle 10g CPU and memory statistics are gathered by default. This helps Automatic Database Diagnostic Monitor(ADDM) determine how the database activity is related to the bottleneck found. Every process running on your system affects the available CPU resources. Therefore, tuning non-Oracle factors can also improve Oracle performance.
Use the V$OSSTAT or V$SYSMETRIC_HISTORY view to monitor system utilization statistics from the operating system. Useful statistics contained in V$OSSTAT and V$SYSMETRIC_HISTORY include:

– Number of CPUs
– CPU utilization
– Load
– Paging
– Physical memory

Also you may use these views to access the historical information provided by these v$ views: DBA_HIST_SYSMETRIC_HISTORY and DBA_HIST_OSSTAT

2- BLOCKING_SESSION_STATUS and BLOCKING_SESSION columns on V$SESSION

Prior to 10g it was really complicated and costly to identify the sessions who are locking another. I can not think any easier way for this need :)


SELECT blocking_session_status, blocking_session FROM v$session

BLOCKING_SE BLOCKING_SESSION
———– —————-
NOT IN WAIT
NO HOLDER
NO HOLDER
NO HOLDER
NO HOLDER
UNKNOWN
NO HOLDER
NO HOLDER
NO HOLDER
NO HOLDER
NO HOLDER
...

Also you may want to check these views for session history information on 10g: DBA_HIST_ACTIVE_SESS_HISTORY
and V$ACTIVE_SESSION_HISTORY

3- Identify Tracing Enabled Sessions

sql_trace% columns in v$session view now enables us to identify which sessions are producing trace files in the database at that time.


-- first lets check the initial conditions
SELECT sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
FROM v$session
WHERE osuser = 'TURKCELL\TCHASYILMAZ' ;

SID    SERIAL# SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
---------- ---------- --------- --------------- ---------------
       184      14563 DISABLED  FALSE           FALSE
      1047      21161 DISABLED  FALSE           FALSE
1068      11068 DISABLED  FALSE           FALSE

-- then lets enable sql tracing on the first session
BEGIN
dbms_monitor.session_trace_enable(session_id => 184,
serial_num => 14563,
waits      => TRUE,
binds      => FALSE);
END;
/

-- and re-check the status of the sessions
SELECT sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
FROM v$session
WHERE osuser = 'TURKCELL\TCHASYILMAZ' ;

SID    SERIAL# SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
---------- ---------- --------- --------------- ---------------
       184      14563 ENABLED   TRUE            FALSE
1047      21161 DISABLED  FALSE           FALSE
1068      11068 DISABLED  FALSE           FALSE

-- tracing can be stopped by session_trace_disable function
BEGIN
dbms_monitor.session_trace_disable(session_id => 184,
serial_num => 14563) ;
END;
/
commit ;

- These may be used to start tracing at database level
EXECUTE dbms_monitor.database_trace_enable;
EXECUTE dbms_monitor.database_trace_enable (binds=>TRUE);
EXECUTE dbms_monitor.database_trace_enable (waits=>TRUE);

- This may be used to start tracing at instance level
EXECUTE dbms_monitor.database_trace_enable (instance_name=>’RAC1);

All outstanding traces can be displayed in an Oracle Enterprise Manager report or with the DBA_ENABLED_TRACES or v$client_stats views. In the DBA_ENABLED_TRACES view, you can determine detailed information about how a trace was enabled, including the trace type. The trace type specifies whether the trace is enabled for client identifier, session, service, database, or a combination of service, module, and action.

First there’s the statistics (that oracle gathers all the time), which you from 10.x can “scope” in on not only session or system level but also client id, module, action and service:


select sid,username,client_identifier from v$session where username='SYS';

SID USERNAME                       CLIENT_IDENT
---------- ------------------------------ ------------
159 SYS

exec dbms_session.set_identifier('some.id');

select sid,username,client_identifier from v$session where username='SYS';

SID USERNAME                       CLIENT_IDENT
---------- ------------------------------ ------------
159 SYS                            some.id

select * from v$client_stats;

no rows selected

exec dbms_monitor.client_id_stat_enable('some.id');

select client_identifier,stat_name,value from v$client_stats;

CLIENT_IDENT STAT_NAME                           VALUE
------------ ------------------------------ ----------
some.id      user calls                              2
some.id      DB time                               314
some.id      DB CPU                                314
some.id      parse count (total)                     1
some.id      parse time elapsed                     67
some.id      execute count                           2
some.id      sql execute elapsed time              115
some.id      opened cursors cumulative               1
...
27 rows selected.

select sysdate from dual;

select client_identifier,stat_name,value from v$client_stats;

CLIENT_IDENT STAT_NAME                           VALUE
------------ ------------------------------ ----------
some.id      user calls                             11
some.id      DB time                              1582
some.id      DB CPU                               1582
some.id      parse count (total)                     3
some.id      parse time elapsed                    226
some.id      execute count                           9
some.id      sql execute elapsed time              901
some.id      opened cursors cumulative               3
...
27 rows selected.

exec dbms_monitor.client_id_stat_disable('some.id');

select * from v$client_stats;

no rows selected

Another tool we have that’s now also “scopable” (by client id, etc.) is Sql trace. For example, you can trace across sessions without tracing the entire server. Using the same id as in above example:


select * from dba_enabled_traces;

no rows selected

exec dbms_monitor.client_id_trace_enable(client_id => 'some.id',waits => true);

select * from dba_enabled_traces;

TRACE_TYPE
---------------------
PRIMARY_ID
----------------------------------------------------------------
QUALIFIER_ID1
------------------------------------------------
QUALIFIER_ID2                    WAITS BINDS INSTANCE_NAME
-------------------------------- ----- ----- ----------------
CLIENT_ID
some.id

TRUE  FALSE

exec dbms_monitor.client_id_trace_disable('some.id');

select * from dba_enabled_traces;

no rows selected

Continue reading with Part 2

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

References Used : Oracle® Database Reference 10g Release 2 (10.2)
Oracle® Database New Features Guide 10g Release 2 (10.2)
Oracle ACE Fredrik Adolfsson answer on http://forums.oracle.com

DBMS_OUTPUT is UNLIMITED by default after 10g

Filed under: Oracle 10g New Features — H.Tonguç Yılmaz @ 8:25 am

Familiar two Oracle errors when using dbms_output in pl/sql;

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line

With SQL*Plus 10.2.0.1.0 serveroutput size is by default unlimited;

Code Listing 21 : DBMS_OUTPUT is UNLIMITED by default after 10g

On TopN Analysis: Rownum – Row_Number – Rank

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 7:16 am

Here is another reason why you might test before making a decision on which function to use by your previous experiences. In this scenerio there is large partitioned log table just having a primary jey on nlog_id column and we want to find the last N inserted rows.

Code Listing 20 : On TopN Analysis: Rownum – Row_Number – Rank

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

Refences Used : On Top-n and Pagination Queries By Tom Kyte, Oracle Magazine Article

Create a free website or blog at WordPress.com.