Views – Performance impact

  The key problem is called push predicate e.g.

select * from some_view where col1=10; can become

select * from (select * from base_table where col1=10);
No difference in performance.
But sometimes Oracle can´t push predicate as the above example , then it becomes like:
select * from (select * from base_table) where col1=10;
can change dramatically the plan to worse.

Regards
Helio Dias
http://heliodias.com

PL/SQL New Features and Enhancements in 11g

Enhancements to Regular Expression Built-in SQL Functions

SIMPLE_INTEGER Datatype

CONTINUE Statement

Sequences in PL/SQL Expressions

Dynamic SQL Enhancements

Generalized Invocation

Named and Mixed Notation in PL/SQL Subprogram Invocations

Database Resident Connection Pool

Automatic Subprogram Inlining

PL/Scope

PL/SQL Native Compiler Generates Native Code Directly

DBMS_APPLICATION_INFO

Good article about monitoring PL executions on v$session_longops

http://www.oracle-base.com/articles/8i/DBMS_APPLICATION_INFO.php

Small Rowid using bigfile tablespace

Since the bigfile comprise just one file , isn´t necessary to store relative file number on rowid, so Oracle cut it 3 bytes from rowid.

I´ll investigate, but i guess it will improve performance (less space=>less memory=>more performance)

Parse Calls

By Jonathan Lewis

Here’s an extract from a report of activity in an Oracle session that I’ve just been running. Spot the anomaly: Name Value —- —– session cursor cache hits 3 parse count (total) 5 parse count (hard) 31 execute count 35 There are no tricks involved with this output, though the database activity is a little unusual, and I haven’t faked any numbers. So how come I’ve got more “hard parses” than “parses” ?

see more at:

http://jonathanlewis.wordpress.com/2007/07/03/parse-calls/

Interpreting Wait Events to Boost System Performance

by Roger Schrag and Terry Sutton In early 2002 Roger Schrag published a paper entitled, “Interpreting Wait Events to Boost System Performance.” That paper began with a definition of wait events and techniques for collecting wait event information, and then went on to discuss four examples of how wait event data had been used to solve real-life performance problems. The impetus for writing that paper had been that, while many resources were available that listed the wait event v$ views, we were not aware of any publication that used real-life examples to demonstrate how to use the wait event interface to solve problems or boost system performance.

http://www.dbspecialists.com/presentations/wait_events2.html

Complement information: (small guide)

http://www.perfvision.com/waits.php

Instance Tuning Using Performance Views

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm

Understanding System Statistics

by Jonathan Lewis

Understanding the optimizer’s use of system statistics can make a big difference when migrating to a new version of the Oracle Database.

What is the most important component of the Oracle Database engine? My vote goes to the optimizer. Everything the database does is SQL, and every piece of SQL has to be translated into something that can work efficiently. Whatever you do with your data, the optimizer gets involved.

http://www.oracle.com/technology/pub/articles/lewis_cbo.html

Transparent Data Encryption

By Arup NandaEncrypt sensitive data transparently without writing a single line of code.

It’s your organization’s worst nightmare: Someone has stolen backup tapes of your database. Sure, you built a secure system, encrypted the most sensitive assets, and built a firewall around the database servers. But the thief took the easy approach: He took the backup tapes, ostensibly to restore your database on a different server, start the database on it, and then browse the data at his leisure. Protecting the database data from such theft is not just good practice; it’s a requirement for compliance with most laws, regulations, and guidelines. How can you protect your database from this vulnerability?

One solution is to encrypt the sensitive data in the database and store the encryption keys in a separate location; without the keys, any stolen data is worthless. However, you must strike a balance between two contradictory concepts: the convenience by which applications can access encryption keys, and the security required to prevent the key theft. And to comply with company and federal regulations, you need a solution immediately, without any complex coding.

A new feature in Oracle Database 10g Release 2 lets you do just that: You can declare a column as encrypted without writing a single line of code. When users insert the data, the database transparently encrypts it and stores it in the column. Similarly, when users select the column, the database automatically decrypts it. Since all this is done transparently without any change to the application code, the feature has an appropriate name: Transparent Data Encryption (TDE).

http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html

Date Time operations

http://www.akadia.com/services/ora_date_time.html