SQL> alter session set workarea_size_policy=manual; --执行后可以手动调整排序区而不是自动使用pga

Session altered

SQL> alter session set sort_area_size=1073741824; --提高当前会话的排序区

Session altered

SQL> alter session set sort_area_retained_size=1073741824; --排序结束后用于返回排序行。

Session altered

SQL> alter session set db_file_multiblock_read_count=128; --增加一次读取的block的数量.

Session altered

SQL> set timing on;
SQL> set serveroutput on;

SQL> create index IDX_PRWAY_BILLID_update_time on TMS_WAY_BILL (update_time) nologging online parallel 4
2 tablespace IDEAS_DATA
3 pctfree 10
4 initrans 10
5 maxtrans 255
6 storage
7 (
8 initial 64K
9 next 1M
10 minextents 1
11 maxextents unlimited
12 ) ;

Index created

Executed in 1435.04 seconds

SQL> alter index IDX_PRWAY_BILLID_update_time noparallel ;

Index altered

Executed in 0.637 seconds

监控索引使用情况:
alter index IX_USERINFO$SALARY monitoring usage ;
alter index IX_USERINFO$SALARY nomonitoring usage ;
select * from v$object_usage;

--查监控历史
select u.name owner,

   io.name index_name,
   t.name table_name,
   decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
   decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
   ou.start_monitoring start_monitoring,
   ou.end_monitoring end_monitoring

from sys.user$ u,

   sys.obj$         io,
   sys.obj$         t,
   sys.ind$         i,
   sys.object_usage ou

where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and u.user# = io.owner#
order by 6 desc

标签: none

添加新评论