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!!

OLTP (OS) vs OLAP (DW, BI)

On Line Transaction Processing (Operational System)
-Source of data: Operational data; OLTPs are the original source of the data.
-Purpose of data: To control and run fundamental business tasks
-What the data: Reveals a snapshot of ongoing business processes
-Inserts and Updates: Short and fast inserts and updates initiated by end users
-Queries: Relatively standardized and simple queries Returning relatively few records
-Processing Speed: Typically very fast
-Space Requirements: Can be relatively small if historical data is archived
-Database Design: Highly normalized with many tables
-Backup and Recovery: Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability
-Mostly updates
-Many small transactions
-Mb-Tb of data
-Clerical users
-Up-to-date data
-Consistency, recoverability critical
-Bottleneck: maybe cpu

On Line Analytical Processing (Data Warehouse)
-Source of data: Consolidation data; OLAP data comes from the various OLTP Databases
-Purpose of data: To help with planning, problem solving, and decision support
-What the data: Multi-dimensional views of various kinds of business activities
-Inserts and Updates: Periodic long-running batch jobs refresh the data
-Queries: Often complex queries involving aggregations
-Processing Speed: Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes
-Space Requirements: Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP
-Database Design: Typically de-normalized with fewer tables; use of star and/or snowflake schemas
-Backup and Recovery: Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method-Mostly reads
-Queries long, complex
-Gb-TB of data
-Decision-makers, analysts as users
-summarized, consolidated data
-Bottleneck: maybe I/O

Others:
HBA (host bus adapter) connects a host system (the computer) to other network and storage devices. The terms are primarily used to refer to devices for connecting SCSI, Fibre Channel and eSATA devices, but devices for connecting to IDE, Ethernet, FireWire, USB and other systems may also be called host adapters.

Set serveroutput on

SET SERVEROUTPUT ON in SQL*Plus is equivalent to:
dbms_output.enable(buffer_size => NULL);

dbms_output.enable(buffer_size IN INTEGER DEFAULT 20000);
exec dbms_output.enable(1000000);

dbms_output.disable;
exec dbms_output.disable;

AWR tables

Each of the AWR table names is prefixed with wrm$ (Metadata tables), wrh$ (History tables), or wri$ (Advisory tables).

DBA_HIST_*

select table_name from dba_tables where owner = 'SYS' and table_name like 'WR%';
select view_name from dba_views where owner = 'SYS' and view_name like 'DBA\_HIST\_%' escape '\';

Text source of the stored objects

Views: dba_source, all_source, user_source
Fileds: owner, name, type, line, text.

Sqlplus prompt

$ORACLE_HOME/sqlplus/admin/glogin.sql

Method 1:
-- Default for XQUERY
COLUMN result_plus_xquery HEADING 'Result Sequence'

-- Default for pagesize
set pagesize 1000
-- Default Instance name in SQLPROMPT
set termout off
define iname=idle
column instance_name new_value iname
select instance_name from v$instance;
set sqlprompt "&iname> "
set termout on

Method 2:
SET SQLPROMPT "&_user'@'&_connect_identifier>"

Reference table, small table > Keep pool

alter table tabname storage (buffer_pool keep);

cursor_sharing

cursor_sharing:
exact,similar,force

bind variable for SQL > exact
not well design with bind variable design > similar
worse one > force

Some was concept

6.0:
server 1 > application server
6.1:
Deployment Manager profile > application server
IHS:
Plugin(XML)

DS > Alias

ND:
= HA, LB

edge cp(active),(edge cp(standby))
edge cp(standby),(edge cp(active))
^
IHS(web server),IHS(web server)
x
WAS,WAS

Awr report!

$ORACLE_HOME/rdbms/admin/awrrpt.sql

WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------
SSD 534227347 ssd2 2 10.1.0.3.0 YES amd44.supers

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 3 28-Jul-05 18:59:48 124 14.4
End Snap: 4 28-Jul-05 20:00:19 106 .7
Elapsed: 60.52 (mins)
DB Time: 9.87 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 768M Std Block Size: 8K
Shared Pool Size: 244M Log Buffer: 512K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 14,614.81 7,031.32
Logical reads: 1,690.46 813.30
Block changes: 81.77 39.34
Physical reads: 1.52 0.73
Physical writes: 12.73 6.12
User calls: 8.15 3.92
Parses: 1.40 0.68
Hard parses: 0.00 0.00
Sorts: 0.59 0.28
Logons: 0.03 0.02
Executes: 19.37 9.32
Transactions: 2.08

% Blocks changed per Read: 4.84 Recursive Call %: 75.25
Rollback per transaction %: 2.08 Rows per Sort: 9.38


Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.97 Redo NoWait %: 99.98
Buffer Hit %: 99.91 In-memory Sort %: 100.00
Library Hit %: 99.99 Soft Parse %: 99.86
Execute to Parse %: 92.76 Latch Hit %: 99.98
Parse CPU to Parse Elapsd %: 104.48 % Non-Parse CPU: 99.66

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 37.87 38.16
% SQL with executions>1: 84.98 86.15
% Memory for SQL w/exec>1: 77.84 80.07

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) DB Time Wait Class
------------------------------ ----------- ----------- ---------
enq: TX - row lock contention 596 208 35.17 Application
CPU time 204 34.39
gc cr block 2-way 49,722 77 13.00 Cluster
gc current block 2-way 36,791 52 8.74 Cluster
gc buffer busy 1,726 29 4.86 Cluster
-------------------------------------------------------------

Data Masking

Data Masking
- Fine-grained access control (FGAC)
- Virtual private database (VPD, 11g)

Oracle® Enterprise Manager Licensing Information

http://download.oracle.com/docs/cd/B19306_01/license.102/b40010/toc.htm

Oracle Database 10g Static & Dynamic Views

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm

Reverse SQL of grant role

select 'grant ' privilege ' on ' table_name ' to ' grantee ';' from dba_tab_privs where grantor='Jeff';

Check DB connection

echo "Checking DB connection..."
echo "connect ${DBA_NAME}/${DBA_PASS}@${DB_NAME}"> ${ConnSql}
echo "spool ${SQL_LOG}" >> ${ConnSql}
echo "select * from global_name;" >> ${ConnSql}
echo "exit" >> ${ConnSql}

${ORACLE_BIN}/sqlplus -s /nolog @${ConnSql} > /dev/null

rm $ConnSql 2>/dev/null

if [ -z "`grep 'GLOBAL_NAME' ${SQL_LOG}`" ]; then
rm ${SQL_LOG}
echo "failed"
exit
else
rm ${SQL_LOG}
echo "ok"
fi

ksh read file line by line

while read line ; do
echo $line
done < file.txt

Route error to standard output

cat 1.sh > 1.txt 2>&1

Output template with sed

:>/cat 2.txt
test <result>
:>/cat 2.txt | sed -e "s/<result>/ok/" > 3.txt
:>/cat 3.txt
test ok

Check port

lsof -i |grep 8890
lsof -i TCP:8895

Expressions of if

Comparisons:
-eq equal to
-ne not equal to
-lt less than
-le less than or equal to
-gt greater than
-ge greater than or equal to

File Operations:
-s file exists and is not empty
-f file exists and is not a directory
-d directory exists
-x file is executable
-w file is writable
-r file is readable

Others:
[ -n "$var" ] : Test if the variable $var contains something
-z test for zero length of a string

vi search and replace

:set number
:32,56s/guy/gal/gc

from line 32 to 56 replace guy by gal
/c - confirm

:1,$s/abc/def/g

search whole document replace abc by def

Script parameter

Check number of parameter pass in:
if [ $# -gt 0 ]

Read in all parameter:
$@

Or use loop to read and cater particular parameter:
bc -svr p1 -db p2

Inline alter user password

chgPassword=`/opt/oracle/9.2.0/bin/sqlplus
-L ${user_id}/${OLD_PWD}@$db_name<<XX
alter user $user_id identified by "$NEW_PWD";
exit;
XX`
RESULT=`echo ${chgPassword}`
if [ -z "$RESULT" ] ; then
      ActionStatus "FAILED"
else
      echo ${NEW_PWD} > ${pwdPath}${ds_name}.pwd
      ActionStatus "END"
fi