New Dictionary Enhancements with 10g – Part I

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


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

———– —————-

Also you may want to check these views for session history information on 10g: DBA_HIST_ACTIVE_SESS_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

---------- ---------- --------- --------------- ---------------
       184      14563 DISABLED  FALSE           FALSE
      1047      21161 DISABLED  FALSE           FALSE
1068      11068 DISABLED  FALSE           FALSE

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

-- and re-check the status of the sessions
SELECT sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
FROM v$session

---------- ---------- --------- --------------- ---------------
       184      14563 ENABLED   TRUE            FALSE
1047      21161 DISABLED  FALSE           FALSE
1068      11068 DISABLED  FALSE           FALSE

-- tracing can be stopped by session_trace_disable function
dbms_monitor.session_trace_disable(session_id => 184,
serial_num => 14563) ;
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('');

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

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

select * from v$client_stats;

no rows selected

exec dbms_monitor.client_id_stat_enable('');

select client_identifier,stat_name,value from v$client_stats;

CLIENT_IDENT STAT_NAME                           VALUE
------------ ------------------------------ ----------      user calls                              2      DB time                               314      DB CPU                                314      parse count (total)                     1      parse time elapsed                     67      execute count                           2      sql execute elapsed time              115      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
------------ ------------------------------ ----------      user calls                             11      DB time                              1582      DB CPU                               1582      parse count (total)                     3      parse time elapsed                    226      execute count                           9      sql execute elapsed time              901      opened cursors cumulative               3
27 rows selected.

exec dbms_monitor.client_id_stat_disable('');

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 => '',waits => true);

select * from dba_enabled_traces;

-------------------------------- ----- ----- ----------------


exec dbms_monitor.client_id_trace_disable('');

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

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

On TopN Analysis: Rownum – Row_Number – Rank

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

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

The differences between a marketing mindset and a scientific mindset

Without science, the forces of marketing and superstition combine to create waste that you pay for.

Cultures governed by superstition and marketing

1. Seller provides information that’s in the seller’s best interest
2. Waste
3. Guesses, hopes

Cultures governed by science

1. Seller provides accurate information because you can tell the difference
2. Efficiency
3. Fully informed decisions

Marketing mindset

1. Motive – Selling
2. Worst thing that could happen – Not selling
3. Communication style – Subjective
4. Spin The whole truth, Self-credit

Scientific mindset

1. Motive – Learning
2. Worst thing that could happen – Not learning
3. Communication style – Objective
4. Nothing but the truth, it’s hard!

Science begins with doubt. The late Dr. Richard Feynman, one of the most important physicists and educators of the twentieth century, characterized a scientist as someone who actively doubts, who instead of believing everything he sees and hears, subjects his observations to formal scrutiny. It is an immensely valuable habit to get into. It is the habit of asking one’s self questions like Really?, How can I be certain?, and What if I’m wrong?

If you ever find yourself unable to think of how to construct a test, or unable to think of anything useful to test, visit Tom Kyte’s page There you’ll find an entire community engaging in the kind of behavior described above.

References used : TESTING ORACLE PERFORMANCE presentation by Cary Millsap

History of Oracle

1978 -> Oracle V1; first commercial SQL relational database management system (RDBMS), Main architect Bob Miner, ran on pdp-11 under rsx; 128Kb memory, written in assembly, separated oracle and user codesto overcome the memory limitations

1979 -> Oracle V2; written in pdp-11 assembly language, ran on vax/vms in compatibility mode

1980 -> Oracle V3; written in C, soptable source code, introduced Transactions

1984 -> Oracle V4; introduced read consistency, ported to many plathforms, first interopability between PC and server

1986 -> Oracle V5; true client-sever, vax cluster support, distributed queries

1989 -> Oracle V6; OLTP performance enhancements, online backup/recovery, row level locking, plsql language, parallel server

1993 -> Oracle V7; declarative referential integrity, stored procedures and triggers, shared SQL, parallel execution, Advanced replication

1997 -> Oracle V8; Object-relational database, three-tier architecture, partitioning

1999 -> Oracle V8i; Java in database and native java support, XML support, Oracle Internet Directory, Summary management interMedia, Data warehousing enhancements, ported to Linux, Business components for java(BC4J), WebDB introduced(eventually mature into Portal and ApEx)

2001 -> Oracle V9i; Automatic segment space management, Real Apllication Clusters, Internet security enhancements, Data Guard, Advanced globalization support, record-breaking TPC-C benchmark results, 1st to complete 3 terabyte TPC-H world record

2003 -> Oracle V10g; Enterprise Grid Computing, 64-bit Linux with IPF

2005 -> Oracle VXE; free Oracle 10gR2 database

2007 -> Oracle V11g; as announced at Openworld 2006

Version Date Release Name

2 June 1979
3 March 1983
4 October 1984
5.0 April 1985
6.0 July 1988
7.0 June 1992
7.1 May 1994
7.2 May 1995
7.3 February 1996
8.0 June 1997 Oracle 8
8.1.5 February 1999 Oracle 8i Release 1
8.1.6 November 1999 Oracle 8i Release 2
8.1.7 August 2000 Oracle 8i Release 3
9.0.1 June 2001 Oracle 9i Release 1
9.2 May 2002 Oracle 9i Release 2
10.1 January 2004 Oracle 10g Release 1
10.2 July 2005 Oracle 10g Release 2

References used :

The Storage Hierarchy Summary in an Oracle Database

In summary, the hierarchy of storage in Oracle is as follows:

* A database is made up of one or more tablespaces.
* A tablespace is made up of one or more data files. These files might be cooked files in a file system, raw partitions, ASM managed database files, or a file on a clustered file system. A tablespace contains segments.
* A segment (TABLE, INDEX, and so on) is made up of one or more extents. A segment exists in a tablespace, but may have data in many data files within that tablespace.
* An extent is a logically contiguous set of blocks on disk. An extent is in a single tablespace and, furthermore, is always in a single file within that tablespace.
* A block is the smallest unit of allocation in the database. A block is the smallest unit of I/O used by a database.

References used : Oracle® Database Concepts 10g Release 2 (10.2)

On Oracle Certification

I strongly believe that in Turkey Human Resource specialists and even the Information Technology managers who are dominant during the recruiting process are not aware of the Oracle Certification terms; OCA, OCP and OCM.

Also what an OCP is or is not is another discussion topic. The very known Tom Kyte is not an OCP :) check this post

For me having a two-three years of Oracle development or administration experience in a busy company is much more important than having an OCP degree. The reason is simple, because this exam is not effective to check the participant’s Oracle experience and easy conditions are there to pass for someone who studies sample questions for several weeks. Check this post for an example :)

But when it comes to having an OCM degree it is really different, OCM credential is for the most advanced Oracle database administrator. This individual has comprehensive and extensive experience on a wide variety of topics since this exam consists an intensive two-day hands-on practical examination.

Recommended minimum skills include:

1. Proficient with Oracle 10g SQL
2. Working knowledge of LINUX command language that includes:

    a. Formatting and executing basic OS commands
    b. Creating and navigating through directory structures
    c. File management using copy, move, and delete
    d. Linux environment text editors
    e. Setting environment variables

3. The ability to locate and launch Oracle executables that include:

    a. RMAN utility
    b. Oracle Net Manager
    c. Oracle Net Configuration Assistant
    d. OEM
    e. Listener Utility
    f. OMS
    g. Oracle Password Utility
    h. Database Creation Assistant

4. Proficient with Oracle Enterprise Manager
5. Proficient in using Oracle Net Manager and the Oracle Net Configuration Assistant to configure networking
6. Advanced knowledge and use of Oracle Enterprise Server technology and features
7. Familiarity navigating through online Oracle documentation
8. Proficient with using Mozilla 1.6 browser software

You may want to check these free Oracle Certificate Practice Tests, also these vendors demos are available(Cisco®,CompTIA,Lotus,Microsoft,Novell,PMI,Sun,ISC2,Check Point,IBM,ITIL)

But if you want to experience a real challenge on your PL/SQL knowledge I reccomend you to try this one :)

For more information on Oracle certification you may want to check these resources;

The Power of Attitude

Our lives are not determined by what happens to us, but by how we respond to what happens; not by what life brings us, but by attitude we bring to life. A positive attitude causes a chain reaction of positive thoughts, events, and outcomes. It is a catalyst… a spark that creates extraordinary results.

From a strictly mathematical viewpoint proof goes like this ;)

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z is represented as:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26.

11+14+15+23+12+5+4+7+5 = 96%

8+1+18+4+23+15+18+11 = 98%

1+20+20+9+20+21+4+5 = 100%

So, one can then conclude with mathematical certainty that while Knowledge and Hard Work will get you close, Attitude will get you there :)

Refences Used : The PL/SQL Grid: Time to Expand to 10g R1 & 10g R2 presentation by Joe Trezzo

How to Answer Questions the Smart Way

#1. Don’t answer questions to which you don’t know the answer
#2. Explain yourself
#3. Give as little assistance as necessary
#4. Show your workings
#5. Use humour judiciously
#6. If you can’t say something nice don’t say anything at all
#7. Avoid jargon, baffling acronyms and idiolects
#8. Never never never just respond with RTFM. Not ever.
#9. Meditate on eternity
#10. Keep your newbie mind

Refences Used : How To Be A Good Guru post by APC