Relationship of some dynamic view

v$session.paddr=v$process.addr
v$session.taddr=v$transaction.addr
v$session.lockwait=v$lock.kaddr
v$session.sql_id,prev_sql_id=v$sql/v$sqltext.sql_id
v$session.sid=v$sess_io.sid
v$session.sid=v$sesstat

I should make a diagram about them later.
With these dynamic view, we know what's going on at instance.

Location of statspack for Oracle 10g

$ORACLE_HOME/rdbms/admin/sp*.sql

Segment, extents, data block.

Check execution plan

set autotrace on

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=541550 Card=49 Bytes =15092)
0 COUNT (STOPKEY)
1 INLIST ITERATOR
2 TABLE ACCESS (BY INDEX ROWID) OF 'TC_xx_TRADE' (Co st=541550 Card=1462696 Bytes=450510368)
3 INDEX (RANGE SCAN) OF 'IDX_TC_REF_TRA_GMT_XIAOER' (N ON-UNIQUE) (Cost=4475 Card=1462696)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
898 consistent gets
0 physical reads
0 redo size
15366 bytes sent via SQL*Net to client
689 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49 rows processed

Plan Table:
@$ORACLE_HOME/rdbms/admin/utlxpls.sql

SQL Tuning for massive data update.

update table with
order by table1.rowid;
It can save a lot of I/O read time!

Detail about rowid
http://ningoo.itpub.net/post/2149/226286

Character set

col PROPERTY_VALUE format a30
col DESCRIPTION format a40
select * from database_properties where property_name like '%SET';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ -----------------------NLS_CHARACTERSET AL32UTF8 Character set
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set

SQL CHAR datatypes:
CHAR, VARCHAR2, CLOB, LONG
Unicode datatypes:
NCHAR, NVARCHAR2, NCLOB

NLS Parameter Setting Precedence
1, Set in SQL function (Current SQL funciton)
2, Explicit ALTER SESSION statement (Current session)
3, Client environment variable (implicit ALTER SESSION statement) (Current session)
4, Set by server initialization parameter (Instance)
5. Default (Instance)

HP Oracle Exadata Storage Server

Amazing!!