ORACLE技術情報

プランテーブルの作成

 sqlplus XXX/XXX@XXX
 SQL> @$ORACLE_HOME/rdbms/admin/utlxplan
 SQL>

プランのとり方

 explain plan for
 ：
 SQL文
 ：

プランデータの表示方法

 col id format a2;
 col operation format a20;
 col options format a10;
 col object_name format a10;
 select id,lpad(' ',2*level-1)||
 operation operation,options,object_name
 from plan_table
 start with id=0
 connect by prior id=parent_id

より詳しいトレースのとり方

 alter session set events='10046 trace name context forever ,level 12'
 /
 spool name
 set timing on trims on page 10000 term off
 SQL文
 /
 spool off

tkprof

 tkprof trace_file outputfile explain=userid/passwd

autotraceのとり方

①PLAN表の作成
autotraceを使用したいユーザでログインし以下のスクリプトを実行します。

 SQL>@$ORACLE_HOME/rdbms/admin/utlxplan.sql

②PLUSTRACEロールの作成 SYSDBA権限を持つユーザでログインし以下のスクリプトを実行します。

 SQL>connect / as sysdba
 SQL>@$ORACLE_HOME/sqlplus/admin/plustrce.sql

③PLUSTRACE権限の付与 SYSDBA権限を持つユーザから以下のSQLを実行します。

 SQL>grant plustrace to username

④autotraceの取得

 SQL> set autotrace traceonly
 SQL> SQL文実行
 SQL> set autotrace off

※：実行するユーザーには「plustrace」権限が必要。
http://www.monyplaza.net/tips/oracle/ora_sqltrace_sqlplus.xtp

統計情報の更新

 analyze table テーブル名 compute statistics;
 analyze table テーブル名 compute statistics;

※estimate statisticsとcompute statisticsでは、どれだけ統計情報を正確に作成する
かという違いがある。

アーカイブログの出力要求

 alter system switch logfile;

アーカイブログの出力要求だけでなく、出力が完了するまで確約する。

 alter system log current;

DLの取り出し方

expしたファイルからDDLを取り出すことが可能

expファイルからcreate indexを取得する方法

impのパラメータでINDEXES=Nに設定すると、索引がインポートされず、
INDEXFILE=ファイル名を指定するとインデックスのDDLが生成される。

TNSのping

 tnsping サービス名

データファイルを削除してしまった場合

 alter database datafile ファイル名 offline drop;

UTL_FILEで指定できるファイルリストの長さ

 512バイト

UTL_FILEでエラーが発生した場合原因が何かを調査する方法

 エラーが以下の場合
 ORA-06510: ユーザー定義の例外が処理されませんでした。
 ORA-06512: "SYS.UTL_FILE", 行: 85
 ORA-06512: "SYS.UTL_FILE", 行: 120
 
 SQL> select line, text from dba_source 
 2> where name = 'UTL_FILE' and line in (85, 120);

ロック解除待ちのSQL文

 select a.sid,a.serial#,b.type,to_char(b.ctime/60,'999990.9') Min,c.sql_text
 from v$session a,v$lock b,v$sqlarea c
 where a.lockwait = b.kaddr
 and a.sql_address = c.address
 order by b.ctime desc

オブジェクトのロック時間を算出する

 select to_char(sysdate,'HH24:MI') TIme,a.owner||'.'||a.object_name OBJECT
 ,decode(b.locked_mode
 ,1,'null'
 ,2,'row share'
 ,3,'row exclusive'
 ,4,'share'
 ,5,'share row exclusive'
 ,6,'exclusive') LOCKED_MODE
 ,to_char(c.ctime/60,'999990.9') MIN
 from dba_objects a,v$locked_object b,v$lock c
 where a.object_id = b.object_id
 and b.session_id = c.sid
 and b.xidsqn = c.id2
 and b.xidusn > 0

ロックオブジェクトを出すSQL

 select object_id,session_id,process,locked_mode
 from v$locked_object;

現在ロックを保持しているSQL

 SET LINE 120
 COL OBJECT_NAME FORMAT A20
 COL SID FORMAT 999

 SELECT L.SID,O.OBJECT_NAME,L.TYPE,L.LMODE,L.BLOCK,Q.SQL_TEXT
 FROM V$LOCK L,V$SESSION S,DBA_OBJECTS O,
      V$SQLAREA Q
 WHERE
       L.ID1=O.OBJECT_ID
 AND   L.SID=S.SID
 AND   S.SQL_ADDRESS=Q.ADDRESS
 AND  BLOCK <> 0    --このままで、誰かをロック開放待ちにしているSQLを表示し
 ます。
 ORDER BY L.SID
 ;

プロシージャの再コンパイル

 ALTER PROCEDURE プロシージャ名 COMPILE
 ALTER PACKAGE パッケージ名 COMPILE;

※パッケージの場合は内部のプロシージャもコンパイルする。

ORACLEインストール費

標準15万円

HA構成の場合はその2倍

セッション情報の見方

 select SID,SERIAL# ,USERNAME,COMMAND, MACHINE
 from v$session

UTF-8の場合

 キャラクター・コード：UTF8

DBMS_OUTPUTの出力を可能にする

よく忘れるので...^^;

 SET SERVEROUTPUT ON

パラメータを見る方法

8i以降
```
 show parameter user_dump_dest
```

8i以前

 select name,value from v$parameter where name='user_dump_dest'

カンマ区切り少数を数値に置換

 SELECT TO_NUMBER('\123,456.78','L999G999D99') FROM DUAL;
 
 TO_NUMBER('\123,456.78','L999G999D99')
 --------------------------------------
 123456.78

ブロックサイズの獲得

 select value from v$parameter where name='db_block_size';

フリーサイズと使用率を算出させる方法

 sqlplus -S system/manager << EOF
 set feedback off
 col "表領域名" format a12;
 col "表領域容量(Byte)" format a16;
 col "空き容量(Byte)" format a16;
 col "使用率(%)" format a10;
 select 
 t.tablespace_name
 as "表領域名",
 to_char(t.bytes,'999,999,999,999')
 as "表領域容量(Byte)",
 to_char(f.bytes,'999,999,999,999')
 as "空き容量(Byte)",
 to_char((t.bytes - f.bytes)/t.bytes * 100,'990.99') || '%' as "使用率(%)"
 from
 (
 select tablespace_name,
 sum(bytes) as bytes
 from dba_data_files
 group by tablespace_name
 ) t,
 (
 select tablespace_name,
 sum(bytes) as bytes
 from dba_free_space
 group by tablespace_name
 ) f
 where t.tablespace_name = f.tablespace_name;
 EOF

マテビューの作成例

 create materialized view mv19
 tablespace mview parallel 8
 refresh complete
 enable query rewrite
 as
 select sum(l_extendedprice * (1 - l_discount)) as revenue
 from lineitem, parts
 where
 ( p_partkey = l_partkey
 and p_brand = 'Brand#12'
 and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
 and l_quantity >= 1 and l_quantity <= 1 + 10
 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG')
 and l_shipinstruct = 'DELIVER IN PERSON' )
 or
 ( p_partkey = l_partkey
 and p_brand = 'Brand#23'
 and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
 and l_quantity >= 10 and l_quantity <= 10 + 10
 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG')
 and l_shipinstruct = 'DELIVER IN PERSON' )
 or
 ( p_partkey = l_partkey
 and p_brand = 'Brand#34'
 and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
 and l_quantity >= 20 and l_quantity <= 20 + 10
 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG')
 and l_shipinstruct = 'DELIVER IN PERSON' ) ;

ORACLE 8.1.7の2枚目以降のCD投入

コンソール上でリターンキーを押下し、CD-ROMのマウントポイントから移動することで
CD-ROMの入れ替えが出来る。

キャッシュヒット率算出

 select (a.value+b.value) "Logi Reads",
 c.value "Phy Reads",
 round(((1-(c.value / (a.value+b.value))) * 100),3) "Buffer Hit Ratio"
 from v$sysstat a,v$sysstat b,v$sysstat c
 where a.name = 'db block gets' and
 b.name = 'consistent gets' and
 c.name = 'physical reads'

Net*8の注意(8i)

initXX.oraにservice_namesを設定しているか？

listener.oraに下記記述があるか

 # Generated by Oracle configuration tools.
 
 LISTENER =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = xxx)(PORT = 1521))
 )
 
 SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (ORACLE_HOME = /export/home/oracle/app/oracle/product/8.1.7)
 (SID_NAME = xxx)
 )
 )

LOCAL_LISTENERがtnsnames.oraに記載されているか？

 LOCAL_LISTENER=
 "(ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(HOST=XXXX)(PORT=XXXX)))"

IMPORTエラー

下記の場合、ORACLE8.1.7のexportファイルをORACLE8.1.6のimportを使用して
行ったときこのエラーが発生する。ORACLE8.1.7のインポートで行えば問題なし。
エクスポート・ファイルは従来型のパス経由でEXPORT:V08.01.07が作成しました。

警告: オブジェクトは別のユーザー:MARKLINESによってエクスポートされました。

JA16SJISキャラクタ・セットとJA16SJIS NCHARキャラクタ・セットでインポートが完了しました。
IMP-00069: 各国の環境キャラクタ・セットのハンドルに変換できません。
IMP-00000: エラーが発生したためインポートを終了します。

ORA-01003

エクスポート（exp73）実行時のログを確認すると ORA-01003：解析された文がありません。と表示されます。

私が経験したことではないので、うまく行くかどうかわかりませんが、catexp.sql を再
実行したあとエクスポートし直す。

という解決方法を見たことがあります。ORA-1003 が exp で発生する場合は、そのエラー
の前に他のエラーが起こっていることが多いです。もし、他のエラーが発生していな
ければ上記 catexp を sys または internal ユーザーで実行してみてください。

ORA-01552

 ORA-01552: cannot use system rollback segment for non-system 
  tablespace 'DM_RENESAS_DCTM_DOCBASE'

initXX.oraにrollback_segments=(RB1,RB2,..,..)が記述されていないときに発生。

エラー集

 ORA-01102	：　名前(initXX.oraないのinstance名もしくはservice_name)が重複しているか、すでに起動している。
 ORA-00205	：　制御ファイルが存在しない。
 ORA-01990	：　remote_login_passwordfile=EXCLUSIVEのとき、orapwXXファイルがない
 			orapwd file=/export/home2/oracle/ora901/app/product/9.0.1/dbs/orapwXXX
   password=change_on_install entries=2
 ORA-01092	： orapwXXファイルがないためのエラー

utlxplan.sqlの実行ユーザ

sysユーザーで実行します。

SQL*loaderのカラム最大値

文字型のデータを取り込む場合、型の指定をなし、もしくはcharだけで取り込むと、最大256バイトまでしか登録できない。

サイズを増やしたい場合は、char(1000)のようにサイズを指定する。

VARCHAR2にスペースを登録することは可能。

↑

NUMBER型でもlike検索は可能。

sqlplusで認証失敗時に3回認証を繰り返さない方法。

sqlplus -Lで行う。

9iのdbassit は dbca

dbcaでスクリプトを作成するには、「New Database」で作成する。

アーカイブモードの状態確認

 sqlplus /nolog
 	：
 archive log list

ARCHIVE LOGモードへの変更方法

 shutdown
 startup mount
 ALTER DATABASE ARCHIVELOG;
 ALTER DATABASE OPEN;

Oracle 9iでinitファイルの変更を反映させる方法

１．サーバー・パラメータ・ファイルを再作成する。

　　本によれば、以下のコマンドで再作成できるそうです。これにより、init<SID>.oraへの変更点が反映できるそうです。

　　(init<SID>.oraにUTL_FILE_DIR =* などと書いてから実行する)

　　CREATE SPFILE[='spfile_name'] FROM PFILE[='pfile_name']

　　'spfile_name'　作成するサーバー・パラメータ・ファイル名を指定ファイル名はspfile<SID>.ora

　　'pfile_name'　　初期化パラメータファイルを指定ファイル名はinit<SID>.ora

２．$ORACLE_HOME/bin/dbstart を修正

　　上記、スクリプトを以下のように変更し、init<SID>.oraを強制的に　　読み込ませる。

　　startup pfile='/opt/oracle/product/9.0.1/dbs/init<SID>.ora'

パーティショニング化

パーティション化とは、大規模な表や索引を、パーティションというより小さくて管理し
やすい部分に分割して、この種の表や索引をサポートするときの主な問題に対処しま
す。

パーティション・オブジェクトのパーティションはすべて、単一ブロック・サイズの表領
域に常駐する必要があります。

表は最大64,000 のパーティションに分割できます。どの表もパーティション化できます
が、LONG データ型またはLONG RAW データ型の列を含む表は例外です。ただし、
CLOB またはBLOB の各データ型の列を含む表は使用できます。

用意されているパーティション化方法は次のとおりです。

レンジ・パーティション化
リスト・パーティション化
ハッシュ・パーティション化
コンポジット・パーティション化

レンジ・パーティション化の例

 CREATE TABLE sales_range
 (salesman_id NUMBER(5),
 salesman_name VARCHAR2(30),
 sales_amount NUMBER(10),
 sales_date DATE)
 PARTITION BY RANGE(sales_date)
 (
 PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
 PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
 );

リスト・パーティション化の例

 CREATE TABLE sales_list
 (salesman_id NUMBER(5),
 salesman_name VARCHAR2(30),
 sales_state VARCHAR2(20),
 sales_amount NUMBER(10),
 sales_date DATE)
 PARTITION BY LIST(sales_state)
 (
 PARTITION sales_west VALUES('California', 'Hawaii'),
 PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
 PARTITION sales_central VALUES('Texas', 'Illinois')
 PARTITION sales_other VALUES(DEFAULT)
 );

db_cache_size

バイト換算されたSGAサイズ

システム・グローバル領域（SGA）のバッファ・キャッシュ・サブコンポーネントのサ
イズが動的になりました。DB_BLOCK_BUFFERS 初期化パラメータは、新しい動的パラ
メータDB_CACHE_SIZE に置き換えられました。このパラメータでは、標準データベー
ス・ブロック・サイズ用のバッファ・サブキャッシュのサイズを指定します。データ
ベースで複数のブロック・サイズを指定した場合、バッファ・キャッシュはサブキャッ
シュで構成されます。最大4 つのDB_nK_CACHE_SIZE 初期化パラメータを使用して、
追加したブロック・サイズ用のバッファ・サブキャッシュのサイズを指定できます。

KEEPバッファ・プール

よく再利用されるデータ・ブロックを保持しておく場所。マスタ系のテーブル等はここ
に保持するようにするとよい。

サイズは初期化パラメータDB_KEEP_CACHE_SIZE

ラージプール

ユーザが共有サーバ経由で接続をする場合、セッション情報等をSGA内で保有する必要が
ある。このとき、ラージプールが設定されていると、この領域を使用する。

サイズは初期化パラメータLARGE_POOL_SIZEで定義される。

JAVAプール（計算のしようが無く、実績＝コストから算出するしかない）

JAVAを使用するときに必要になる領域。サイズは初期化パラメータJAVA_POOL_SIZEで定義
される。使用しない場合は、明示的に0を指定する必要がある。

制御ファイルのトレース

 sqlplus /nolog
 SQL> connect sys/change_on_install as sysdba;
 SQL> alter database backup controlfile to trace;

ORACLEがハングしたとき、

dbw0をkillする

アイドル権を渡したコネクション

as sysdbaでアイドル接続権限が与えられる。

マテビューの注意

マテビューはROWIDにインデックスを張ることで更新のパフォーマンスチューニングを向上できる。

USE_CONCAT

USE_CONCAT ヒントを使用すると、問合せのWHERE 句のOR 条件の組合せが、UNION ALL
集合演算子を使用する複合問合せに強制的に変換されます。通常、この変換は、連結を
使用する問合せのコストが、使用しない場合のコストよりも低い場合にかぎり実行しま　す。

/+ ORDERD FIRST_ROWS /

テーブルを見る順番をfrom句の順番でテーブルを参照する。 where句は特に気にしなくてもいい。

↑

IN句で使用できるリスト数の制限

1000個まで

255を超えるカラムのSQL*Loaderの取り込み

コントロールファイルに

 カラム名 char(256)

と指定する。

DB_LINK先テーブルへのSQL文の注意点

DB_LINK先のテーブルに対してSELECT文を実行すると、分散トランザクションの関係上エ
ンキューが発生するため、行ロックが起こる（テーブルに対する行ロックではない
が）。必ず、commitをすることでこの行ロックは解除される。

マテビュー色々

○リフレッシュモード変更方法

 ALTER MATERIALIZED VIEW MVEW名 REFRESH ON [DEMAND|COMMIT];

○マテビューのリフレッシュモード確認方法

 SELECT REFRESH_MODE FROM USER_MVIEWS;

○マテビューログ

 MLOG$テーブル名

○リフレッシュの掛け方(全件)

 execute DBMS_MVIEW.REFRESH_ALL_MVIEWS();

○リフレッシュの掛け方(テーブル単位)

 execute DBMS_MVIEW.REFRESH('テーブル名');

外部キーの無効化

 ALTER TABLE test DISABLE CONSTRAINT test_mgr_fk;

インデックスが使用されているかどうかを調べる方法

 SQL> ALTER INDEX xx01_idx1 MONITORING USAGE ;
 索引が変更されました。
 
 SQL> SELECT TABLE_NAME,INDEX_NAME,MONITORING,USED FROM V$OBJECT_USAGE ;
 TABLE_NAME           INDEX_NAME                     MON USE
 -------------------- ------------------------------ --- ---
 XX01                 XX01_IDX1                      YES NO
 
 SQL> select count(*) from xx01;
 
 SQL> SELECT TABLE_NAME,INDEX_NAME,MONITORING,USED FROM V$OBJECT_USAGE ;
 TABLE_NAME           INDEX_NAME                     MON USE
 -------------------- ------------------------------ --- ---
 XX01                 XX01_IDX1                      YES YES 
 
 SQL> ALTER INDEX xx01_idx1 NOMONITORING USAGE ;
 索引が変更されました。
 
 SQL> SELECT TABLE_NAME,INDEX_NAME,MONITORING,USED FROM V$OBJECT_USAGE ;
 TABLE_NAME           INDEX_NAME                     MON USE
 -------------------- ------------------------------ --- ---
 XX01                 XX01_IDX1                      NO  YES

キャッシュされているインデックスの件数を参照する方法

SYSTEMアカウントでログインし以下のＳＱＬを実行

 SELECT A.TABLE_NAME,
      A.OBJECT_NAME,
      COUNT(*)
 FROM
 (
 SELECT
      I.TABLE_NAME,
      O.OBJECT_NAME
 FROM V$BH V ,DBA_INDEXES I,DBA_OBJECTS O
 WHERE O.OWNER='VUP'
     AND O.OBJECT_TYPE ='INDEX'
     AND V.OBJD=O.OBJECT_ID
     AND O.OBJECT_NAME=I.INDEX_NAME
 ORDER BY I.TABLE_NAME,I.INDEX_NAME
 ) A
 GROUP BY
 A.TABLE_NAME,A.OBJECT_NAME;

索引の利用率

SYSTEMアカウントで以下のＳＱＬを実行して下さい。

 --
 --システム全体として索引を有効に使用しているか？
 --索引を使用したスキャンの割合表示
 --
 SELECT I.VALUE/(I.VALUE+S.VALUE+L.VALUE) AS INDEX_USE
 FROM
 (SELECT VALUE
 FROM V$SYSSTAT A
 WHERE A.NAME = 'table scans (rowid ranges)') I
 ,
 (SELECT VALUE
 FROM V$SYSSTAT B
 WHERE B.NAME = 'table scans (short tables)') S
 ,
 (SELECT VALUE
 FROM V$SYSSTAT C
 WHERE C.NAME= 'table scans (long tables)') L
 ;
 結果
 INDEX_USE
 ----------
 .351295114

テーブルキャッシュ方法

初期化パラメータで

 DB_KEEP_CACHE_SIZE        　長く保持するキャッシュ領域,
 DB_RECYCLE_CACHE_SIZE 　頻繁に更新されるキャッシュ領域

該当する表を保持したい場合は

 alter table テーブル名 storage buffer pool

句で keepを指定します。こうすると先に指定したkeepバッファ領域にバッファされます。

ちなみにストアドプロシージャなどは dbms_shared_poolというパッケージがあります。

パラメータ

初期化パレメータにプロセス、セッション関連のパラメータとして

 processes　同時接続できるユーザプロセス数
 sessions　　同時接続できるユーザセッション数
 transactions　同時実行できるトランザクション数

があります。

processes　はデフォルトでCPU数とパラレルクエリ関係のプロセス数から算出され
ますが、オラクルのバックグランドプロセスも含める必要があります。

sessionsはデフォルトで(processes *1.1)+5が設定されます。

transactionsもsessions,processesとの兼ね合いで決まります。

ヒントの種類

	ヒントの種類	ヒントの意味
最適化アプローチに関するヒント	ALL_ROWS	最高のスループットとなるように最適化される（全表スキャン、ソート／マージ結合が選択されやすくなる）
	FIRST_ROWS(n)	レスポンスタイムを最短にするように最適化される（索引スキャンとネステッド・ループ結合が選択されやすくなる）
	RULE	ルールベースのアプローチを選択する
アクセス・パスに関するヒント	FULL	全表スキャンを選択する
	INDEX	全表スキャンを選択する
	HASH	ハッシュスキャンを選択する
結合順序に関するヒント	ORDERED	FROM句に指定された順序で表を結合する
	LEADING	結合順序の最初の表を指定する
	STAR	可能な場合、スター問い合わせを選択する
結合方法に関するヒント	USE_NL	ネステッド・ループ結合を選択する
	USE_MERGE	ソート／マージ結合を選択する
	USE_HASH	ハッシュ結合を選択する
そのほかのヒント	APPEND	ダイレクト・パスINSERTを選択する
そのほかのヒント	CACHE	取得されたブロックが、バッファ・キャッシュ内でLRUリストの最後に使用されたものの位置に配置される

http://www.atmarkit.co.jp/fdb/rensai/orasql03/orasql03_3.html

 HASHは小さいテーブルの時に利用。（オンメモリーに持ってくる）

Statspackの導入方法

①sysdba権限を持つユーザーでログインします。

 $sqlplus /nolog
 SQL> connect / as sysdba

②Statspack用の表領域を作成します。
既存の表領域でもかまいませんが、できればStatspack専用の表領域を作成してく
ださい。　尚表領域は任意に指定してください。

 SQL> create tablespace STATS_DATA datafile '/XXX/XXX/XXX/stats_data01.dbf'
 size XXXm

※最低100MB必要でした。

③Statspackを導入します。
Statspackを導入する前にStatspackデータを格納する表領域名、一時表領域名、
Statspack専用ユーザであるperfstatユーザのパスワードを確認する必要が
あります。

表領域名等の確認ができれば、sysdba権限ユーザで、以下の手順を実行します。
ここでは、以下の名称を使用します。

PERFSTATユーザのパスワード

perfstat

 SQL> define default_tablespace='STATS_DATA'
 SQL> define temporary_tablespace='TEMP'
 SQL> define perfstat_password='perfstat'
 SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql

④ログファイルを確認します。　スクリプトを実行するとカレントディレクトリに
spcusr.lis、spctab.lis、spcpkg.lisファイルが作成されます。このログファイル
にORA-、SP2-エラーがないことを確認します。

もしエラーが発生した場合、以下のスクリプトを実行して、Statspack環境を全て
削除し、エラーの原因を解決してから、③を再度実行します。

 $sqlplus /nolog
 SQL> connect / as sysdba
 SQL> @$ORACLE_HOME/rdbms/admin/spdrop.sql

Statspackの実行手順

①初期化パラメータTIMED_STATISTICSの設定を確認します。初期化パラメータ
TIMED_STATISTICSをTRUEに設定しないと、時間に関する情報を取得できません。
そのため、必ずパラメータ値をTRUEに設定してください。

②perfstatユーザで接続し、SnapShot?(※１）を取得します。

 $sqlplus perfstat/パスワード
 SQL> execute statspack.snap(i_snap_level=>7);

※１：SNAPSHOTは、Statspack実行時点のデータベース統計情報を表しています。

エラーがなければ、1回分のデータが取得されたことになります。

Statspackは、2回のSnapshot間の差分をレポート出力します。そのため、1回だけで
はレポート出力することができないので、2回②を実行してから、以下の手順でレ
ポートを作成します。

Statspackレポート作成手順

①perfstatユーザで接続してスクリプトを実行します。　スクリプトを実行すると、
何度か入力要求が行われます。　そのときの説明を>>>でコメントします。

 $sqlplus perfstat/パスワード
 SQL>@$ORACLE_HOME/rdbms/admin/spreport.sql
 
 省略
 
 Completed Snapshots
                            Snap                  Snap
 Instance     DB Name         Id   Snap Started   Level Comment
 -----------  ------------- ---- ---------------- ----- -----------
 v920         V920             1 07 Oct 2004 01:29    7
                               2 07 Oct 2004 01:35    7
 
 Specify the Begin and End Snapshot Ids
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 >>> ここでレポートファイル名を指定します。指定せずにリターンキーを押すと、
 >>> デフォルトのファイル名が指定されます。
 report_nameに値を入力してください:
 
 省略
 
 End of Report
 
 SQL> exit

カレントディレクトリに指定したファイルが作成されます。

(デフォルトの場合、sp_[開始SNAPSHOT_ID]_[終了SNAPSHOT_ID].lstファイルにな
ります。）

Statspackその他

Statspackは、SNAPSHOTを取得するたびにデータは表に格納されます。そのため、数多くのSNAPSHOTを取得すると、
表領域が圧迫されますので、Statspackデータをメンテナンスする必要があります。

sppurge.sql	指定したSNAPSHOT_IDデータを削除	perfstatユーザで実行
sptrunc.sql	全てのデータをTRUNCATE	perfstatユーザで実行
spdrop.sql	Statspack環境を削除	sysdba権限で接続して実行

HWMの変更方法

ハイウォータマークを下げる唯一のコマンドはTRUNCATEです。TRUNCATEは、行データをすべて削除し、
テーブル作成後に追加されたエクステントを開放します。もちろんハイウォータマークはリセットされます。

パラレルクエリーの例

 CREATE INDEX ULI_USER_CST_HENKN_TBL_MCL_I2 ON
 ULI_USER_CST_HENKN_TBL_MCL (JGS_ID)
 NOLOGGING
 PARALLEL(DEGREE 4)
 /

パラレルクエリーの注意点

SQLの実行計画からパラレルクエリーで処理されているtkprofの結果に[:Q58000]などの表示や、PXで
始まるイベントが含まれており、これはパラレルクエリーで処理されたことを表します。

パラレルクエリーで処理されるケースとしては、ヒント文でのパラレルヒント指定や、表に対し
てパラレル度が設定されている時に処理される可能性があります。
今回のSQLにはヒント文の指定がないようなので、表にパラレル度が設定されていると思われます。
次のSQLでdegree列が1以外であれば、その値が表に設定されたパラレル度になります。

 SQL> select table_name,degree from dba_tables
 where table_name='表名';

 効果を見るには、次のALTER文でパラレル度を変更することができます。

パラレル度　4へ
```
 SQL> alter table 表名 paralle 4;
```
デフォルトのパラレル度1へ
```
 SQL> alter table 表名 paralle 1;
```

コストベース・オプティマイザを利用している場合、意図しない実行計画になる可能性もありますので、
同じようにSQL*Traceを取得して確認して下さい。

尚、パラレルクエリー処理はバッファキャッシュ(メモリー)を経由せずに直接、ファイルに読み込みを行
いますので、ヒット率にその数値は反映されません。
v$sysstatの値もデータベース起動時の累積値になりますので、SQLの実行前後で値を取得し、差分の値で
ヒット率を確認してみてください。

スキーマ単位でanalyzeを実行するPL/SQL

SQL*Plusでオブジェクト所有ユーザでログインし以下のスクリプトを実行する。

 BEGIN
 dbms_stats.gather_schema_stats(ownname=> 'ユーザ名',
      estimate_percent=> パーセント数 , cascade=> TRUE );
 END;
 /

実行プラン情報

Buffer ：オンメモリーソート
Merge Join : ソートしてからマージする。
Hash Join :キー項目をオンメモリー
Range Scan :キーがユニークでないため範囲内でスキャンしている

Create Database後に実行するスクリプト

 @$ORACLE_HOME/rdbms/admin/catalog.sql
 @$ORACLE_HOME/rdbms/admin/catproc.sql
 @$ORACLE_HOME/rdbms/admin/catexp.sql
 @$ORACLE_HOME/sqlplus/admin/pupbld.sql

表領域内で使用可能となったエクステント（空きブロック）を強制的に結合させる

 alter tablespace TABLE_SPACE_NAME coalesce;

マテリアライズドビューはEXPORT/IMPORTはサポートしていない。

テーブルを別テーブルスペースに移動する方法

 alter table テーブル名 move tablespace テーブルスペース名;

エラースタックを出力する方法

 alter session set events '32337 trace name errorstack level 3';
 SQL文
 alter session set events '32337 trace name errorstack off';

analyzeを行ったかどうかの確認方法

 ALL_TABLES、USER_TABLESなどのLAST_ANALYZEDカラムを参照

latch free

 リソースの共有待ち
 WAIT #1: name='latch free' ela= 10 p1=16315681376' p2=98 p3=0
 #1 : イベント
 ela: 100万分1秒
 p1 : ファイルID
 p2 : ブロックID
 p3 : ブロック数

oerr

 oerr [ora|imp|exp] エラー番号

セッション管理

 select * from v$sqltext where address= (select prev_sql_addr from v$session
 where sid='89')
 order by piece

 select * from v$lock where block=1
 LMODE:6:排他
 ID1:13943：オブジェクト

 select * from dba_objects where object_id=13943
 CNM_PRFL_KJB_MMV

APPEND

insert文に対応するものとしてinsert /*+ APPEND */ into というがある。
テーブル（テーブル名２）のデータを、退避用のテーブル（テーブル名１）にコピーする場合、

 SQL> insert /*+ APPEND */ into テーブル名１ select * from テーブル名２;

とすることで、ダイレクト・パス・ロードが適用され、高速に処理できる。
（ダイレクト・パス・ロードは、テーブルの高水位標(HighWaterMark?)以降の領域への書き込みについてのみ適用される。ダイレクト・パス・ロードが高速なのはＲＥＤＯログファイルを生成しないためだ。障害復旧を考慮すれば、オンライン処理には使用すべきでない）。

expのダイレクトパスモード

expはdirect=yオプションをつけることで実行を早くすることができる。
セットに、RECORDLENGTH=65535をつける必要がある。

last_ddl_time

一時表領域の追加

 ALTER TABLESPACE SHIFT_TEMP
    ADD TEMPFILE '/oracle/dev1/oradata/shift_temp2.dbf'  SIZE  5120M

SEO		[PR] 爆速!無料ブログ無料ホームページ開設無料ライブ放送