一、explain plan
a)explain plan for select ...
select * from table(dbms_xplan.display());
function display (table_name varchar2 default 'PLAN_TABLE',
statement_id varchar2 default null,
format varchar2 default 'TYPICAL')
- table_name:
specifies the table name where the plan is stored. This
parameter defaults to "PLAN_TABLE" which is the default
plan table for the explain plan.
- statement_id:
specifies the statement id of the plan to display. This
parameter defaults to NULL.
- format:
'BASIC': Display only the minimum, i.e. the operation id,
the operation name and its option
'TYPICAL': This is the default. Display most information
of the explain plan (operation id, name and option,
#rows, #bytes and optimizer cost. Pruning,
parallel and predicate information are only
displayed when applicable.
'ALL': Maximum level. Like typical with additional display
of the SQL statements generated for PX servers
(only if parallel).
'SERIAL': Like typical except that parallel information are
not displayed, even if the plan is parallel
b)dbms_xplan.display_awr(sql_id VARCHAR2,
plan_hash_value INTEGER DEFAULT NULL,
db_id INTEGER DEFAULT NULL,
format VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;
c)dbms_xplan.display_cursor(
sql_id VARCHAR2 DEFAULT NULL,
cursor_child_no INTEGER DEFAULT 0,
format VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;
select child_number from v$sql_shared_cursor where sql_id='...';
d)dbms_xplan.display_sqlset(
sqlset_name VARCHAR2,
sql_id VARCHAR2,
plan_hash_value INTEGER DEFAULT NULL,
format VARCHAR2 DEFAULT 'TYPICAL',
sqlset_owner VARCHAR2 DEFAULT NULL)
RETURN dbms_xplan_type_table PIPELINED;
2、v$sql_shared_cursor的reason为OPTIMIZER_MISMATCH—优化器环境与现有的子游标不匹配时括号中的数字意义
1 = Degree used is not the default DOP
2 = In (RAC) cases where instance count is not the same, or session CPU count is not the same, or thread count is not the same
3 = _parallel_syspls_obey_force is FALSE
4 = The PQ mode does not match.
5 = The degree does not match.
6 = The parallel degree policy does not match.
7 = The session limit is not the same as the cursor limit but the cursor limit is the same as the degree used.
8 = The cursor limit is greater than the degree used and the session limit is less than the cursor limit
9 = The cursor limit is less than the degree used and the session limit is not the same as the cursor limit
10 = Optimizer mode difference
11 = Materialized View mismatch
12 = Optimizer environment mismatch (ie an optimizer parameter is different)
13 = Cardinality Feedback is use
二、sqlplus启动执行命令
[$ORACLE_HOME/sqlplus/admin,$SQLPATH,.]\glogin.sql,login.sql
显示所有参数
show all
show user,show error
三、sql多行结果转一行
select i,wmsys.wm_concat(a) from t group by i;
四、当前session_id
select sid, serial#, status from v$session where audsid=userenv('sessionid');
select sid from v$mystat where rownum=1;
select userenv('sid') from dual;
五、flashback
db_recovery_file_dest：指定闪回恢复区的位置
db_recovery_file_dest_size：指定闪回恢复区的可用空间大小
db_flashback_retention_target：指定数据库可以回退的时间，单位为分钟，默认1440分钟，也就是一天。
v$flash_recovery_area_usage: flash_recovery_area空间使用情况（分类）
v$recovery_file_dest： 空间使用
dba_outstanding_alerts:空间告警
V$CONTROLFILE, V$LOGFILE, V$ARCHIVED_LOG, V$DATAFILE_COPY 等数据字典的列IS_RECOVERY_DEST_FILE ,指明相关的文件是否在恢复区内
六、数据字典
select reason,object_type,suggested_action,creation_time from dba_outstanding_alerts;
性能、资源相关告警，补充alert
select * from v$resource_limit;
资源使用历史
七、闪回查询
利用undo
当前scn
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
Scn转换成时间：
select to_char(scn_to_timestamp(3998591352171),'YYYY-MM-DD HH24:MI:SS') from dual；
时间转换成scn：
select timestamp_to_scn(to_date('2011-04-14 11:10:25','YYYY-MM-DD HH24:MI:SS')) from dual；
查询该时间点（或scn）的数据，如下：
select * from t as of timestamp to_timestamp('2000-01-01 01:01:01', 'yyyy-mm-dd hh24:mi:ss');
select * from t as of scn xxx;
