Oracle Logbook

September 19, 2007

Views – Performance impact

Filed under: Uncategorized — heliodias @ 6:11 pm

  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

Advertisements

September 17, 2007

PL/SQL New Features and Enhancements in 11g

Filed under: Uncategorized — heliodias @ 5:23 pm
  • 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

    Filed under: Uncategorized — heliodias @ 5:14 pm

    Good article about monitoring PL executions on v$session_longops

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

    September 13, 2007

    Small Rowid using bigfile tablespace

    Filed under: Uncategorized — heliodias @ 4:42 pm

    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

    Filed under: Uncategorized — heliodias @ 4:21 pm

    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/

    September 12, 2007

    Interpreting Wait Events to Boost System Performance

    Filed under: Uncategorized — heliodias @ 3:07 pm

    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

    Filed under: Uncategorized — heliodias @ 2:27 pm

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

    September 11, 2007

    Understanding System Statistics

    Filed under: Uncategorized — heliodias @ 8:50 pm

    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

    Filed under: Uncategorized — heliodias @ 3:31 pm

    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

    Filed under: Uncategorized — heliodias @ 11:52 am

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

    Next Page »

    Blog at WordPress.com.