Andrew Fraser DBA

Oracle User Group Scotland Conference 2012

My notes from the 2012 Oracle User Group Scotland Conference:

Caroline Stuart on Oracle Academy for high schools, 2 year colleges, 4 year colleges and universities. 25% discount on certification exams, hosting, training material (which are cut down versions of the Oracle courses). Deliver and “Are you job ready” lecture. Site is at http://academy.oracle.com/. Contacts Caroline Stuart and Iris Lanny at oracle.com. Powerpoint overview here.

Heli Helskyaho on Agile Database Design. Stages are:

1. Requirements analysis
2. conceptual design (E-R)
3. Logical (tables)
4. Physical (indexes, instances).

Use free download Oracle Data Modeller, which includes E-R diagramming, reverse engineering, version control.
Agile design means refactoring the database, needs analysis change impact.
Cheap coding now is not an excuse for bad database design.
Requires database schema versioning.
Use clear and understandable solutions, not clever ones. Agile is no excuse for not designing. All the rules for database design still apply, is just iterative

Jonathan Lewis on redo: Temporary tablespace operations are not recorded in redo log, with exception of global temporary tables, they are in temporary tablespace so no redo BUT some undo for global temporary tables and undo will itself generate redo. Table change causes undo which together are one set of two separate redos. Each resulting index change also causes undo and so is another set of two separate redos.
Use ‘SQL> alter system dump logfile ‘name.log’ dba min 3 51 dba max 3 51;’ to get information from redo logs (including passwords). Can dump another databases logs on eg your PC database.
From 10g there is private redo which is in SGA, but this is not used in RAC.

Tom Kyte on statistics: num_rows and num_distinct_values are the two most important statistics. Partitions have local stats and aggregated global stats, with 11g global is a bitmap summary – a significant improvement because aggregating works fine for num_rows but not for NDVs (e.g. 10 partitions with “US State” column, 50 NDVs in each local statistic, but 50*10 NDVs is poor global statistic). But if upgrading need to delete all stats and recaculate to get improved global bitmaps in place. Can gather extended stats on two columns which suspect are correlated (e.g. car make and colour)

Cardinality Feedback: 11gR2 new optimizer feature, enabled by default. More information here.

Tom Kyte on reorganizing: Useful SQL: ‘order by dbms_random.random’ and ‘pause’ has same effect as ‘accept dummy prompt continue’.
When to rebuild: within one transaction, a delete/insert/commit will not reuse the deleted part of the index (because not yet committed). But that evens out over many transactions, otherwise rebuild indexes. Also rebuild for ‘sweeper’ indexes e.g. on employee.empno where there are physical deletes instead of logical deletes.
In 10g and above set atomic_refresh=>false on MV refreshes if you can take a truncate+insert append, otherwise indexes grow.
Not mentioned by Tom Kyte but bitmap indexes also need rebuilt after inserts/updates/deletes or (better) converted by b-tree’s.
Could reverse key ‘sweeper’ seqno indexes.
Enhanced online table redefinition with dbms_redefinition:

1. create new empty table
2. start redef (insert append)
3. copy_table_dependents (grants etc.)
4. finish redef (needs undo retention set long enough)

alternatives: online segment shrink ‘alter table name shrink space compact’
or (index only) ‘alter index name coalesce’
or (index only) ‘alter index name rebuild online’ – this temporary has two indexes so need twice the space. ‘alter index shrink’ often done AFTER a coalesce.
Export/import should be avoided, risk of loosing objects or converting character sets.

Jonathan Lewis on Optimizer: Can have function based indexes on CASE statements, so e.g.

create index fbiname on products ( case product_group when 'CLASSICAL CD' then id end );
exec dbms_stats.gather_table_stats (ownname => user , tabname => 'products , ? )
select * from products , orders where case products.product_group when 'CLASSICAL CD' end = orders.id_product ;

Can also create materialized view of subset of interest

create materialized view name build immediate
refresh fast on commit -- dangerous for performance
enable query rewrite
as select id -- just columns want in query select
from products
where product_group = 'CLASSICAL CD' -- just rows want in select

Also Index organized tables and (in 11g) virtual columns.
In 11g, use no_merge hint if you want to stop the optimizer exploding subqueries or inline views.

Frits Hoogland on multiblock reads: direct path reads always load into private memory PGA, bypassing the SGA.