Job Application for Ops Manager by wjr13602

VIEWS: 5 PAGES: 79

Job Application for Ops Manager document sample

More Info
									     <Insert Picture Here>




Top 10, no – make that 11, things about
Oracle Database 11g Release 1
Thomas Kyte
http://asktom.oracle.com
The Beginning...

                   •   Data Model with Structure
                   •   Data Independent of Code
                   •   Set-oriented
                   •   1977 the work begins
 First RDBMS: Version 2
 June 1979

• FIRST Commercial SQL RDBMS
• Impressive First SQL
  • Joins, Subqueries
  • Outer Joins, Connect By
• A Simple Server
  • No transactions, „Limited‟ Reliability
• Portability from the Start
  • Written in Fortran
  • But multi-platform – PDP11, Dec VAX
        Oracle7.3
        February 1996
• Partitioned Views                Spatial Data Option
• Bitmapped Indexes                Tablespaces changes - Coalesce,
• Asynchronous read ahead for       Temporary Permanent,
  table scans
                                   Trigger compilation, debug
• Standby Database
                                   Unlimited extents on STORAGE
• Deferred transaction recovery
  on instance startup
                                    clause.
• Updatable Join View              Some init.ora parameters
• SQLDBA no longer shipped.
                                    modifiable - TIMED_STATISTICS
• Index rebuilds                   HASH Joins, Antijoins
• DBV introduced                   Histograms
• Context Option                   Oracle Trace
• PL/SQL - UTL_FILE                Advanced Replication Object
                                    Groups
                    Oracle Database Innovation
                                                                                       Audit Vault
       30 years of                                                                  Database Vault
         sustained                                                             Grid Computing
                                                                           Self Managing Database
         innovation …                                                   XML Database
                                                                    Oracle Data Guard
                                                                Real Application Clusters
                                                            Flashback Query
                                                        Virtual Private Database
                                                 Built in Java VM
                                             Partitioning Support
                                          Built in Messaging
                                       Object Relational Support
                                    Multimedia Support
                                  Data Warehousing Optimizations
                              Parallel Operations                   … continuing with
                         Distributed SQL & Transaction Support
                     Cluster and MPP Support
                 Multi-version Read Consistency
                                                                    Oracle Database 11g
            Client/Server Support
         Platform Portability
       Commercial SQL Implementation

1977                                                                                                 2007
#1 Encrypted
Tablespaces
 Encrypted Tablespaces

• Oracle Database 10g Release 2 introduced column
  encryption
  • Could not range scan
  • Primary/foreign key issues

• Tablespace encryption Removes those limitations

• Many encryption algorithms
  •   3DES168
  •   AES128
  •   AES192
  •   AES256
      Encrypted Tablespaces

ops$tkyte%ORA11GR1> create tablespace encrypted
  2    datafile '/…/encrypted.dbf' size 10m
  3    ENCRYPTION default storage( encrypt );
Tablespace created.


ops$tkyte%ORA11GR1> create tablespace clear
  2    datafile '/…/clear.dbf' size 10m;
Tablespace created.
     Encrypted Tablespaces

ops$tkyte%ORA11GR1> create table t
 2   tablespace clear
 3   as
 4   select * from all_users;
Table created.


ops$tkyte%ORA11GR1> create index t_idx
 2   on t(lower(username))
 3   tablespace clear;
Index created.
   Encrypted Tablespaces

ops$tkyte%ORA11GR1> alter system checkpoint;
System altered.


$ strings /…/clear.dbf | grep -i ops.tkyte
       OPS$TKYTE    from the table
       ops$tkyte    from the index
      Encrypted Tablespaces

ops$tkyte%ORA11GR1> alter table t move
  2    tablespace encrypted;
Table altered.


ops$tkyte%ORA11GR1> alter index t_idx rebuild
  2    tablespace encrypted;
Index altered.
   Encrypted Tablespaces

ops$tkyte%ORA11GR1> alter system checkpoint;
System altered.


$ strings /…/encrypted.dbf | grep -i ops.tkyte


     [This space intentionally left blank]
        Encrypted Tablespaces

ps$tkyte%ORA11GR1> set autotrace traceonly explain
ops$tkyte%ORA11GR1> select * from t where lower(username) like 'ops$%';
Execution Plan
-------------------------------------------------------------
| Id     | Operation                      | Name   | Rows     | Bytes |
-------------------------------------------------------------
|      0 | SELECT STATEMENT               |        |        2 |   112 |
|      1 |    TABLE ACCESS BY INDEX ROWID| T       |        2 |   112 |
|*     2 |     INDEX RANGE SCAN           | T_IDX |         1 |       |
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
     2 - access(LOWER("USERNAME") LIKE 'ops$%')
             filter(LOWER("USERNAME") LIKE 'ops$%')
#2 Cache More
    Stuff
 Cache More


• Everyone knows the fastest way to do
  something is – to not do it
 • Client Side Cache
 • Server Results Cache (JIT-MV)
 • PL/SQL Function results cache
   Cache More

ops$tkyte%ORA11GR1> /*
ops$tkyte%ORA11GR1> drop table t;
ops$tkyte%ORA11GR1> create table t as select *
 from all_objects;
ops$tkyte%ORA11GR1> */


ops$tkyte%ORA11GR1> update t set
 object_type=object_type where rownum=1;
1 row updated.
     Cache More

ops$tkyte%ORA11GR1> set autotrace traceonly
ops$tkyte%ORA11GR1> select /*+ result_cache */
 2           owner,
 3           object_type,
 4           count(*) cnt
 5       from t
 6    group by owner, object_type
 7    order by owner, object_type
 8   /
249 rows selected.
              Cache More

Execution Plan
--------------------------------------------------------------------------------------------------
| Id     | Operation              | Name                       | Rows   | Bytes | Cost (%CPU)| Time      |
--------------------------------------------------------------------------------------------------
|      0 | SELECT STATEMENT       |                            | 58742 |    1606K|   284   (2)| 00:00:04 |
|      1 |     RESULT CACHE       | 5cwffcum1ajfud1088m1m73f81 |        |        |            |          |
|      2 |      SORT GROUP BY     |                            | 58742 |    1606K|   284   (2)| 00:00:04 |
|      3 |       TABLE ACCESS FULL| T                          | 58742 |    1606K|   280   (1)| 00:00:04 |
--------------------------------------------------------------------------------------------------


Result Cache Information (identified by operation id):
------------------------------------------------------
      1 - column-count=3; dependencies=(OPS$TKYTE.T); parameters=(nls); name="select /*+ result_cache */
             owner,
             object_type,
             count(*) cnt
     from t
    group by owner, object_type
    order by"
     Cache More

Statistics
----------------------------------------------------------
             0   recursive calls
             0   db block gets
       1005      consistent gets
             0   physical reads
             0   redo size
       6922      bytes sent via SQL*Net to client
        596      bytes received via SQL*Net from client
         18      SQL*Net roundtrips to/from client
             1   sorts (memory)
             0   sorts (disk)
        249      rows processed
     Cache More
ops$tkyte%ORA11GR1> /
249 rows selected.
Statistics
----------------------------------------------------------
             0   recursive calls
             0   db block gets
             0   consistent gets
             0   physical reads
             0   redo size
       6922      bytes sent via SQL*Net to client
        596      bytes received via SQL*Net from client
         18      SQL*Net roundtrips to/from client
             0   sorts (memory)
             0   sorts (disk)
        249      rows processed
     Cache More

ops$tkyte%ORA11GR1> select * from (
 2   select /*+ result_cache */
 3           owner, object_type, count(*) cnt
 4       from t
 5    group by owner, object_type
 6    order by owner, object_type
 7    )
 8    where cnt > 100
 9   /
38 rows selected.
     Cache More

Statistics
----------------------------------------------------------
             0   recursive calls
             0   db block gets
             0   consistent gets
             0   physical reads
             0   redo size
       1516      bytes sent via SQL*Net to client
        442      bytes received via SQL*Net from client
             4   SQL*Net roundtrips to/from client
             0   sorts (memory)
             0   sorts (disk)
         38      rows processed
          Cache More

SQL> create or replace                 SQL> create or replace
 2   function not_cached                2   function cached
 3   ( p_owner in varchar2 )            3   ( p_owner in varchar2 )
 4   return number                      4   return number
                                        5   result_cache
                                        6   relies_on(T)
 5   as                                 7   as
 6           l_cnt number;              8           l_cnt number;
 7   begin                              9   begin
 8           select count(*)           10           select count(*)
 9             into l_cnt              11             into l_cnt
10             from t                  12             from t
11            where owner = p_owner;   13            where owner = p_owner;
12           dbms_lock.sleep(1);       14           dbms_lock.sleep(1);
13           return l_cnt;             15           return l_cnt;
14   end;                              16   end;
15   /                                 17   /
Function created.                      Function created.
     Cache More

SQL> exec dbms_output.put_line( not_cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.93


SQL> exec dbms_output.put_line( not_cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.29


SQL> exec dbms_output.put_line( not_cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.07
     Cache More

SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.09


SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01


SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
      Cache More

SQL> update t set owner = initcap(owner) where rownum = 1;
1 row updated.


SQL> commit;
Commit complete.


SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.25


SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
        Cache More

SQL> exec dbms_output.put_line( cached( 'SYS' ) );
29339
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.21


SQL> exec dbms_output.put_line( cached( 'SYS' ) );
29339
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01


SQL> exec dbms_output.put_line( cached( 'SCOTT' ) );
6
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
 #3 Standby
Just got better
 Standby Database


• Logical Standby was…
 • Limited in type support
 • But was always open for business


• Physical Standby was…
 • Easy
 • But considered “not useful day to day”
 Standby Database


• Logical Standby has…
 • XMLType support
 • DBMS_RLS & DBMS_FGA support
 • TDE support
    Active Data Guard:
    Develop & Test on Standby DB
           Production
           Database



                             • Use physical standby database
                               for development & testing
               Developers,     • Preserves zero data loss in test/dev mode
                 Testers
                             • Flashback DB to back-out
                               changes & use as standby
                                      Eliminates cost of
Standby                                idle DR system
Database
   Active Data Guard:
   Report & Backup from Standby DB
Production
Database



                        • Offload reporting to standby
 Reporting                  • Simultaneously available for recovery
                        • Offload backups to standby
                 Standby    • Complete database and fast incremental
                 Database     backups


                              Improves performance on
       Backups                  production database
Active Data Guard –
More than a Standby
      Disaster    Disaster and
protection only   performance protection
    Recovery      Simultaneous read
    mode only     and recovery
      Used in     Use daily in testing
 disaster only    and production
       Manual
     intensive    Automated

     Low ROI      High ROI
 #4 Real
Application
 Testing
     Real Application Testing –
     Database Replay
• Recreate actual production database workload
• Capture production workload incl. concurrency
• Replay workload in test with production timing
• Analyze & fix issues before production

Middle                     Production               Test (RAC)
 Tier                     Environment              Environment`


                           Capture DB
  Oracle                    Workload
 DB servers                                                       Replay DB
                                                                  Workload


                           Storage
#5 Smaller
more secure
 DMP files
       Datapump

 • COMPRESSION
    • ALL, DATA_ONLY, METADATA_ONLY, NONE

$ expdp / directory=tmp dumpfile=uncompressed.dmp compression=NONE schemas=ops\$tkyte
Export: Release 11.1.0.6.0 - Production on Friday, 21 September, 2007 12:23:26
. . exported "OPS$TKYTE"."BIG_TABLE"                      24.57 MB   250000 rows
. . exported "OPS$TKYTE"."T"                              6.791 MB    67945 rows


$ expdp / directory=tmp dumpfile=compressed.dmp compression=ALL schemas=ops\$tkyte
Export: Release 11.1.0.6.0 - Production on Friday, 21 September, 2007 12:23:58
. . exported "OPS$TKYTE"."BIG_TABLE"                      3.110 MB   250000 rows
. . exported "OPS$TKYTE"."T"                              762.1 KB    67945 rows


$ ls -l /tmp/*compressed.dmp
-rw-r-----   1 ora11gr1 ora11gr1   4124672 Sep 21 12:24 /tmp/compressed.dmp
-rw-r-----   1 ora11gr1 ora11gr1 33136640 Sep 21 12:23 /tmp/uncompressed.dmp
     Datapump


• ENCRYPTION                  • REUSE_DUMPFILES
 •   All
 •   Data_only
                              • Ability to use DML error
 •   Metadata_only
                                logging features
 •   None
                                • DATA_OPTIONS parameter
 •   Encrypted_columns_only
• PARTITION_OPTIONS
 • None
 • Departition
 • Merge
#6 Virtual
Columns
 Virtual Columns


• Create Table
• Alter Table Add Column
• Are „column expressions‟
 • Expressions involving other columns in table
 • Constants
 • Deterministic functions
• Ease of use and Optimizer enhancement
     Virtual Columns

ops$tkyte%ORA11GR1> /*
ops$tkyte%ORA11GR1> create table emp
ops$tkyte%ORA11GR1> as
ops$tkyte%ORA11GR1> select all_objects.*, object_id sal,
                          round(dbms_random.value( 1000, 100000 )) comm
ops$tkyte%ORA11GR1>     from all_objects
ops$tkyte%ORA11GR1> /
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> */


ops$tkyte%ORA11GR1> exec dbms_stats.gather_table_stats( user, 'EMP' )
PL/SQL procedure successfully completed.
       Virtual Columns

ops$tkyte%ORA11GR1> select avg( sal+comm ) avg_comp, avg(sal) avg_sal, avg(comm)
 avg_comm from emp;


  AVG_COMP     AVG_SAL    AVG_COMM
---------- ---------- ----------
85376.9437 34821.6827    50555.261


ops$tkyte%ORA11GR1> select
              count(case when sal+comm > 85376.9437 then 1 end) above_comp,
  2           count(case when sal        > 34821.6827   then 1 end) above_sal ,
  3           count(case when       comm > 50555.261 then 1 end) above_comm
  4    from emp;


ABOVE_COMP    ABOVE_SAL ABOVE_COMM
---------- ---------- ----------
      33957      33830       34036
        Virtual Columns

ops$tkyte%ORA11GR1> select * from emp where sal > 34821.6827;
--------------------------------------------------------------------
| Id     | Operation             | Name | Rows   | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------
|      0 | SELECT STATEMENT      |      | 34673 |   3893K|   309   (1)| 00:0
|*     1 |     TABLE ACCESS FULL| EMP   | 34673 |   3893K|   309   (1)| 00:0
--------------------------------------------------------------------


ABOVE_COMP      ABOVE_SAL ABOVE_COMM
---------- ---------- ----------
       33957       33830      34036
        Virtual Columns

ops$tkyte%ORA11GR1> select * from emp where comm > 50555.261;
--------------------------------------------------------------------
| Id     | Operation             | Name | Rows   | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------
|      0 | SELECT STATEMENT      |      | 33943 |   3811K|   309   (1)| 00:0
|*     1 |     TABLE ACCESS FULL| EMP   | 33943 |   3811K|   309   (1)| 00:0
--------------------------------------------------------------------


ABOVE_COMP      ABOVE_SAL ABOVE_COMM
---------- ---------- ----------
       33957       33830      34036
        Virtual Columns

ops$tkyte%ORA11GR1> select * from emp where sal+comm > 85376.9437;
--------------------------------------------------------------------
| Id     | Operation             | Name | Rows   | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------
|      0 | SELECT STATEMENT      |      |   3398 |   381K|   309   (1)| 00:0
|*     1 |     TABLE ACCESS FULL| EMP   |   3398 |   381K|   309   (1)| 00:0
--------------------------------------------------------------------


ABOVE_COMP      ABOVE_SAL ABOVE_COMM
---------- ---------- ----------
       33957       33830      34036
   Virtual Columns

ops$tkyte%ORA11GR1> ALTER TABLE emp ADD (comp
 AS (sal+comm));
Table altered.


ops$tkyte%ORA11GR1> exec
 dbms_stats.gather_table_stats( user, 'EMP',
 method_opt=> 'for columns comp' );
PL/SQL procedure successfully completed.
        Virtual Columns

ops$tkyte%ORA11GR1> select * from emp where sal+comm > 85376.9437;
--------------------------------------------------------------------
| Id     | Operation             | Name | Rows   | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------
|      0 | SELECT STATEMENT      |      | 33927 |   3975K|   309   (1)| 00:0
|*     1 |     TABLE ACCESS FULL| EMP   | 33927 |   3975K|   309   (1)| 00:0
--------------------------------------------------------------------


ABOVE_COMP      ABOVE_SAL ABOVE_COMM
---------- ---------- ----------
       33957       33830      34036
#7 Partitioning
just got better
Partitioning



   • Composite Completely
   • Virtual Column Partitioning
   • Partition by Reference
   • Interval Partitioning
Enhanced Partitioning

 • Partition (or index) on virtual (computed) columns
 • New composite partitioning
                    Range          List   Hash
            Range    11g            9i     8i
            List     11g           11g    11g




 RANGE-RANGE        LIST-RANGE             LIST-LIST
  Order Date by       Region by             Region by
   Order Value       Order Value          Customer Type
   Partitioning
   by REFERENCE
Table ORDERS
                                    • RANGE(order_date)
                        ...   ...   • Primary key order_id
 Jan 2006    Feb 2006
                              • Partitioning key inherited
                                through PK-FK relationship
                              • Avoids redundant storage,
                                maintenance of order_date
Table LINEITEMS
                                  • RANGE(order_date)
                        ...   ... • Foreign key order_id

  Jan 2006   Feb 2006
Partitioning
Automation
                       New “INTERVAL” partitioning
                     • Automatically creates a new
                       partition when data outside the
 ORDERS                existing range is first inserted
                        • E.g., monthly partitions, automatic
                          new partition first day of the month
                     • Composite partitioning:
                       interval, interval-list, interval-
Jul   Aug      Sep     hash, and interval-range
                     • Automates partition
                       management
  Sep 1 2007
     Partitioning

ops$tkyte%ORA11GR1> create table audit_trail
 2   ( ts       timestamp,
 3       data   varchar2(30)
 4   )
 5   partition by range(ts)
 6   interval (numtodsinterval(1,'day'))
 7   store in (users, example )
 8   (partition p0 values less than
 9   (to_date('22-sep-2007','dd-mon-yyyy'))
10   )
11   /
Table created.
         Partitioning

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
     2    from user_tab_partitions
     3   where table_name = 'AUDIT_TRAIL';


PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- ----------------------------------
P0           USERS      TIMESTAMP' 2007-09-22 00:00:00'
     Partitioning

ops$tkyte%ORA11GR1> insert into audit_trail
 2   select sysdate+rownum, 'x'
 3       from all_users
 4   where rownum <= 5
 5   /


5 rows created.
         Partitioning

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
     2    from user_tab_partitions
     3   where table_name = 'AUDIT_TRAIL';


PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- ----------------------------------
P0           USERS      TIMESTAMP' 2007-09-22 00:00:00'
SYS_P66      EXAMPLE    TIMESTAMP' 2007-09-23 00:00:00'
SYS_P67      USERS      TIMESTAMP' 2007-09-24 00:00:00'
SYS_P68      EXAMPLE    TIMESTAMP' 2007-09-25 00:00:00'
SYS_P69      USERS      TIMESTAMP' 2007-09-26 00:00:00'
SYS_P70      EXAMPLE    TIMESTAMP' 2007-09-27 00:00:00'
6 rows selected.
#8 The long
awaited pivot
           Pivot

ops$tkyte%ORA11GR1> select deptno,
  2              sum( decode( job, 'CLERK', sal ) ) clerk,
  3              sum( decode( job, 'SALESMAN', sal ) ) salesman,
  4              sum( decode( job, 'MANAGER', sal ) ) manager,
  5              sum( decode( job, 'ANALYST', sal ) ) analyst,
  6              sum( decode( job, 'PRESIDENT', sal ) ) president
  7        from emp
  8     group by deptno
  9     order by deptno
 10    /
      DEPTNO          CLERK   SALESMAN   MANAGER    ANALYST   PRESIDENT
---------- ---------- ---------- ---------- ---------- ----------
            10         1300                2450                     5000
            20         1900                2975        6000
            30         950       5600      2850
           Pivot

ops$tkyte%ORA11GR1> select *
  2        from (select deptno, job, sal
  3               from emp ) e
  4     pivot( sum(sal) for job in
  5     ( 'CLERK', 'SALESMAN', 'MANAGER',
  6         'ANALYST', 'PRESIDENT' ) )
  7     order by deptno
  8    /


      DEPTNO     'CLERK' 'SALESMAN'     'MANAGER'   'ANALYST' 'PRESIDENT'
---------- ---------- ---------- ---------- ---------- -----------
            10       1300                   2450                    5000
            20       1900                   2975        6000
            30        950        5600       2850
#9 Flashback
Data Archive
  Flashback Data Archive
  Total Data Recall
       Select * from orders
   AS OF ‘Midnight 31-Dec-2004’     • Tamper-proof data
                                      archive
                                    • Efficient storage and
                                      retrieval of undo
                                    • Keep data for months,
                       Archive        years, decades!
                       Tables       • Fast access to even very
                                      old data
    User              Flashback     • View data, versions of
Tablespaces          Data Archive     rows as of any time
      Oracle 11g Database           • Control data retention
                                      time, purging of data
 #10 Finer
 Grained
Dependency
 Tracking
Finer Grained Dependency Tracking


• Fewer Invalidations
  • Add to a specification – so what
  • Add/Drop/Modify a column – so what
  • Holds true with view modifications too
  • Change a synonym pointer – so what
  • Replace a procedure – so what
     Finer Grained Dependency Tracking

ops$tkyte%ORA10GR2> create or replace package p1
 2   as
 3           function f return number;
 4   end;
 5   /
Package created.


ops$tkyte%ORA10GR2> create or replace package p2
 2   as
 3           g_global number := p1.f;
 4   end;
 5   /
Package created.
         Finer Grained Dependency Tracking

ops$tkyte%ORA10GR2> select object_name, status
     2    from user_objects
     3   where object_name like 'P_';


OBJECT_NAME                       STATUS
------------------------------ -------
P1                                VALID
P2                                VALID
         Finer Grained Dependency Tracking

ops$tkyte%ORA10GR2> create or replace package p1
     2   as
     3              function f return number;
     4              procedure p;
     5   end;
     6   /
Package created.
ops$tkyte%ORA10GR2> select object_name, status
     2        from user_objects
     3   where object_name like 'P_';
OBJECT_NAME                           STATUS
------------------------------ -------
P1                                    VALID
P2                                    INVALID
         Finer Grained Dependency Tracking

ops$tkyte%ORA11GR1> create or replace package p1
     2   as
     3              function f return number;
     4              procedure p;
     5   end;
     6   /
Package created.
ops$tkyte%ORA11GR1> select object_name, status
     2        from user_objects
     3   where object_name like 'P_';
OBJECT_NAME                           STATUS
------------------------------ -------
P2                                    VALID
P1                                    VALID
          Finer Grained Dependency Tracking

ops$tkyte%ORA10GR2> create table t ( x int, y int );
Table created.
ops$tkyte%ORA10GR2> create or replace procedure p
  2   as
  3     begin
  4             for x in ( select x, y from t )
  5             loop
  6                    null;
  7             end loop;
  8     end;
  9   /
Procedure created.
ops$tkyte%ORA10GR2> select status from user_objects where object_name = 'P';
STATUS
-------
VALID
         Finer Grained Dependency Tracking

ops$tkyte%ORA10GR2> alter table t add z int;
Table altered.


ops$tkyte%ORA10GR2> select status from user_objects where object_name = 'P';
STATUS
-------
INVALID


ops$tkyte%ORA11GR1> select status from user_objects where object_name = 'P';
STATUS
-------
VALID
         Finer Grained Dependency Tracking

ops$tkyte%ORA10GR2> alter procedure p compile;
Procedure altered.
ops$tkyte%ORA10GR2> alter table t drop column z;
Table altered.


ops$tkyte%ORA10GR2> select status from user_objects where object_name = 'P';


STATUS
-------
INVALID
ops$tkyte%ORA11GR1> select status from user_objects where object_name = 'P';


STATUS
-------
VALID
#11 OLTP Table
 Compression
Oracle Advanced
Compression
      • Oracle 9i compresses data only during bulk
        load; useful for DW and ILM
      • Oracle 11g compresses w/ inserts, updates
      • Trade some cpu for disk & i/o efficiency
      • Compress large application tables
         • Transaction processing, data warehousing
      • Compress all data types: structured,
        unstructured
      • Savings cascade to all db copies: test, dev,
        standby, mirrors, archiving, backup, etc.
      <Insert Picture Here>




Q&A

								
To top