Analytic 'First' and 'Last' Functions

When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, the FIRST and LAST functions eliminate the need for self-joins or views and enable better performance.(quoted from tahiti)

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions058.htm#sthref1414
http://www.psoug.org/reference/OLD/analytic_functions.html?PHPSESSID=26034dea1c0fb6e4ec0580b5f35f193f
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12864646978683

Model Clause 1

Here are some links for model clause, first two are from tahiti.
After i read and understand them, i will share some interesting parts.

http://www.oracle.com/technology/obe/obe10gdb/bidw/sqlmodel/sqlmodel.htm


http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/sqlmodel.htm#sthref1869

http://www.oreillynet.com/pub/a/network/2004/08/10/MODELclause.html

Automatic Workload Repository

Creating Snapshots
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;

Dropping Snapshots
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22,
high_snap_id => 32, dbid => 3310949047);
END;

Modifying Snapshot Settings
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, topnsql => 100, dbid => 3310949047);
END;

Run utlsyxsz.sql for sizing recommendations for the awr(in rdbms\admin).
http://www.ioug.org/performancetuning.pdf

SQL> @utlsyxsz.sql

This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
......

Run awrinfo.sql for awr components size.
SQL> @awrinfo.sql

http://forums.oracle.com/forums/thread.jspa?threadID=680095

Transport Awr Data to Test Environment
Run @$ORACLE_HOME/rdbms/admin/awrextr.sql in prod environment, move result into test environment and run @$ORACLE_HOME/rdbms/admin/awrload.sql to load into test environment.
After transporting awr data check dba_hist_snapshot for new awr data.Transported data has DBID of prod instance, so we can't use transported data in EM of test instance.
Reports for transported awr data can be produced via awrrpti.sql, awrsqrpi.sql , awrddrpi.sql or dbms_workload_repository package.

Generating AWR Reports

SQL> select t.snap_id,t.dbid,t.begin_interval_time from dba_hist_snapshot t order by t.snap_id desc;

SNAP_ID DBID BEGIN_INTERVAL_TIME
---------- ---------- -------------------------------------------------
9832 1055334377 06/04/2009 13:00:55,018
9831 1055334377 06/04/2009 12:01:04,018
.....
SQL> spool awr_rep_htm_03_04_09.html
SQL> select * from table(dbms_workload_repository.awr_report_html(1055334377 ,1,9795,9832 ));
......
SQL> spool off

Compare Period Report

SQL> spool awr_prod_01_apr_03_apr.html
SQL> select * from table(dbms_workload_repository.awr_diff_report_html(1055334377 ,1,9699 ,9722 ,1055334377,1,9747,9770));
SQL> spool off

Generating ASH Reports
Run ashrpt.sql or ashrpti.sql for reports.(in rdbms\admin)

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm#i27008

Performance Diagnosis Demystified: Best Practices for Oracle Database 10g

I should check deeply:
http://www.oracle.com/technology/products/manageability/database/pdf/ow05/PS_S998_273998_106-1_FIN_v1.pdf

Power of Snapshots in WM

Shrink operation decreased total virtual disk size to 40 GB, the gain was 14GB . Now first thing to do is take a snapshot of current state.
My main purpose is when i make irrevocable mistakes rolling back to previous snapshot; e.g: before and after database upgrade for test purposes. In addition, it can be usefull when i have different versions of database as snapshots on same virtual machine.
I upgraded my oracle instance from 10.2.0.1 to 10.0.2.3. And finally i upgraded to 11G R1.
Steps i followed:

• Read upgrade docs. from tahiti,
• Take snapshot of current version of oracle,
• Install new oracle software,
• Take snapshot of current state:previous version of instance with just new software ( before upgrade snashot)
• Upgrade instance to new version.
• Whenever irrevocable error occurs i roll back to previous snapshot.
• When upgrade successfully finished, i uninstall previous oracle software.
• Take snapshot of current state:new instance with new software only (after upgrade snapshot)

After all i have 5 snaphots so far. Now i can practice on 10.2.0.1 or 10.2.0.3 or 11.1.0.7.0.
My virtual machine now accoupies approximately 57 GB for all three versions.