OracleのFAQ-パフォーマンスの調整
12787 ワード
、
[Q]
[A] system
$ORACLE_HOME/rdbms/admin/utlxplan.sql
$ORACLE_HOME/sqlplus/admin/plustrce.sql plustrace
，
SQL>create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
，
SQL> grant plustrace to public;
/
SET AUTOTRACE ON |OFF
| ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
[Q]
[A]
Alter session set sql_trace true|false
Or
Exec dbms_session.set_sql_trace(TRUE);
，
exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)
user_dump_dest （ Win ， unix ）
SELECT p1.value||'\'||p2.value||'_ora_'||p.spid||'.ora' filename
FROM
v$process p,
v$session s,
v$parameter p1,
v$parameter p2
WHERE p1.name = 'user_dump_dest'
AND p2.name = 'db_name'
AND p.addr = s.paddr
AND s.audsid = USERENV ('SESSIONID')
， Tkprof ，
Tkprof sys=n
[Q]
[A] alter system set sql_trace=true
，
alter system set events
'10046 trace name context forever,level 1';
，
alter system set events
'10046 trace name context off';
level 1 8
level 1： SQL ， sql_trace=true
level 4：
level 8：
level 12：
[Q] OS DB
[A] ， OS ， TOP OS ， OS DB ？
：
$more whoit.sh
#!/bin/sh
sqlplus /nolog <<EOF
connect / as sysdba
col machine format a30
col program format a40
set line 200
select sid,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss')
from v\$session where paddr in
( select addr from v\$process where spid in($1));
select sql_text from v\$sqltext_with_newlines
where hash_value in
(select SQL_HASH_VALUE from v\$session where
paddr in (select addr from v\$process where spid=$1)
)
order by piece;
exit;
EOF
， OS
$./whoit.sh Spid
[Q]
[A] analyze
Analyze table tablename compute statistics;
Analyze index|cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
FOR TABLE
FOR ALL [LOCAL] INDEXES
FOR ALL [INDEXED] COLUMNS;
ANALYZE TABLE tablename DELETE STATISTICS
ANALYZE TABLE tablename VALIDATE REF UPDATE
ANALYZE TABLE tablename VALIDATE STRUCTURE
[CASCADE]|[INTO TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]
。
， ，
Dbms_utility(8i )
Dbms_stats(8i )
dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
1、 ， DBMS_STATS， Analyze 。
a) ， ， Table
b) 。
c) Compute Statistics： ， ， ，
d)
e)
2、DBMS_STATS
a) Validate Structure
b) CHAINED ROWS, CLUSTER TABLE ， Analyze 。
c) DBMS_STATS Analyze， Cascade False， True
3、 oracle 9 External Table，Analyze ， DBMS_STATS 。
[Q]
[A] rebuild ，
rebuild ，
alter index index_name rebuild tablespace ts_name
storage(……);
， ， ，
SQL> set heading off
SQL> set feedback off
SQL> spool d:\index.sql
SQL> SELECT 'alter index ' || index_name || ' rebuild '
||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'
FROM all_indexes
WHERE ( tablespace_name != 'INDEXES'
OR next_extent != ( 256 * 1024 )
)
AND owner = USER
SQL>spool off
alter index index_name coalesce， leaf block
， ， 。
[Q] Hint
[A] select/delete/update /*+ hint */
select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
/* +
hint
select /*+ index(cbotab) */ col1 from cbotab;
select /*+ index(cbotab cbotab1) */ col1 from cbotab;
select /*+ index(a cbotab1) */ col1 from cbotab a;
TABLE_NAME ， ， hint ;
INDEX_NAME ，Oracle ;
， hint ;
[Q]
[A] Nologging
：Create table t1 nologging
as select * from t2;
append ，
noarchivelog ， append nologging 。
archivelog ， Nologging 。
insert /*+ append */ into t1
select * from t2
： 9i FORCE LOGGING， ， ， ， NO FORCE LOGGING。
Alter database no force logging;
FORCE LOGGING，
SQL> select force_logging from v$database;
[Q]
[A] ，Oracle ， ， ：
test, a,b,c,d， a,b,c inx_a(a,b,c)， b Inx_b(b)。
，where a=? and b=? and c=? inx_a，
where b=? inx_b
，where a=? and b=? and c=? group by b ？ （ ） ，oracle inx_b。 ， ， 。
， inx_b， inx_a。
where a=? and b=? and c=? group by b||'' -- b
where a=? and b=? and c=? group by b+0 -- b
，
， no_index ， ， ：
select /*+ no_index(t,inx_b) */ * from test t
where a=? and b=? and c=? group by b
[Q]Oracle
[A] 9i (Index Skip Scan).
index(a,b,c)，
where b=? ， index(a,b,c)
， ：
INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE)
Oracle ( CBO) Index Skip Scans :
1 。
2 （ ）。
3 ( / )。
4 SQL
。
[Q]
[A] nosegment ，
create index virtual_index_name on table_name(col_name) nosegment;
session ，
alter session set "_use_nosegment_indexes" = true;
explain plan for select ……
@$ORACLE_HOME/rdbms/admin/utlxpls
， ， ，
drop index virtual_index_name;
： ， ， ， ， 。
[Q]
[A]Oracle 9i ， ， ，
：
：alter index index_name monitoring usage;
：select * from v$object_usage;
：alter index index_name nomonitoring usage;
， ， ：
set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'
FROM dba_indexes
WHERE owner = USER;
spool off
set heading on
set echo on
set feedback on
------------------------------------------------
set heading off
set echo off
set feedback off
set pages 10000
spool stop_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
FROM dba_indexes
WHERE owner = USER;
spool off
set heading on
set echo on
set feedback on
[Q]
[A] OUTLINE SQL
OUTLINE
Create oe replace outline OutLn_Name on
Select Col1,Col2 from Table
where ……
Outline，
Drop Outline OutLn_Name;
OutLine， OUTLN OL$HINTS
， update outln.ol$hints outline
update outln.ol$hints(ol_name,'TEST1','TEST2','TEST2','TEST1)
where ol_name in ('TEST1','TEST2');
， Test1 OUTLINE Test2 OUTLINE
OUTLINE，
Alter system/session set Query_rewrite_enabled = true
Alter system/session set use_stored_outlines = true
[Q]v$sysstat class
[A]
1
2 Redo buffer
4
8
16 OS
32
64
128
[Q]
[A] Alter system kill session 'sid,serial#';
alter system disconnect session 'sid,serial#' immediate;
win ， oracle orakill （ Oracle ）
Linux/Unix ， kill OS
[Q]
[A] ， ， ， ， 。
DML ， ， DML ， ， 。
alter system kill session ‘sid,serial#’
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
，
， 。
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
， ， 。 ， V$rollname， xidusn USN
[Q] (extent )
[A] (100k)extent ， drop table ， CPU（Oracle fet$、uet$ ）， ， extent， ：
1. truncate table big-table reuse storage;
2. alter table big-table deallocate unused keep 2000m ( n-1/n);
3. alter table big-table deallocate unused keep 1500m ;
....
4. drop table big-table;
[Q]
[A]9i
ALTER DATABASE DATAFILE 'file name' RESIZE 100M
9i
ALTER DATABASE TEMPFILE 'file name' RESIZE 100M
， ， ， ， 。
[Q]
[A]
1、
SELECT username,sid,serial#,sql_address,machine,program,
tablespace,segtype, contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr
2、
SQL>Alter system kill session 'sid,serial#';
3、 TEMP
SQL>Alter tablespace TEMP coalesce;
1、 TEMP ts#
SQL> select ts#, name FROM v$tablespace;
TS# NAME
-----------------------
0 SYSYEM
1 RBS
2 USERS
3* TEMP
……
2、
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1'
：
temp TS# 3*, So TS#+ 1= 4
，
TS# = 2147483647
[Q] dump ，
[A]
1、 ， n m
alter system dump datafile n block m
2、
alter system dump logfile logfilename;
3、
alter session set events 'immediate trace name CONTROLF level 10'
4、
alter session set events 'immediate trace name FILE_HDRS level 10'
5、
alter session set events 'immediate trace name REDOHDR level 10'
6、 ， 10 ，
alter session set events 'immediate trace name SYSTEMSTATE level 10'
7、
alter session set events 'immediate trace name PROCESSSTATE level 10'
8、 Library Cache
alter session set events 'immediate trace name library_cache level 10'
[Q]
[A] 10000 to 10999，
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/
Unix ，
$ORACLE_HOME/rdbms/mesg/oraus.msg
event=10000
while [ $event -ne 10999 ]
do
event=`expr $event + 1`
oerr ora $event
done
/ ，
SET SERVEROUTPUT ON
DECLARE
l_level NUMBER;
BEGIN
FOR l_event IN 10000..10999
LOOP
dbms_system.read_ev (l_event,l_level);
IF l_level > 0 THEN
dbms_output.put_line ('Event '||TO_CHAR (l_event)||
' is set at level '||TO_CHAR (l_level));
END IF;
END LOOP;
END;
/
[Q] STATSPACK， ？
[A]Statspack Oracle 8i ， BSTAT/ESTAT ，
$ORACLE_HOME/rdbms/admin/spdoc.txt。
Statspack:
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- ，
sqlplus "/ as sysdba" @spcreate.sql --
Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; -- ，
-- ， ，
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql --
s:
spauto.sql - dbms_job ， STATPACK
sppurge.sql - ，
sptrunc.sql - (truncate)