Index Usage – 1
-
In 12.2 Oracle introduced Index Usage Tracking to replace the previous
option for “alter index xxx monitoring usage”. A recent post on the Oracle
database ...
Acum 3 zile
You've got to have a dream. When you lose your dreams, you die.
cd ORACLE_HOME/chgip/scripts
./chgiphost.sh -mid
cd ORACLE_HOME\chgip\scripts
cmd /c chgiphost.bat -mid
Verify the logs
col metric_name for a25 col metric_unit for a25 select metric_name, value, metric_unit from v$sysmetric where metric_name like'%CPU%' where group_id=2;
with AASSTAT as ( select decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait') CLASS, sum(round(m.time_waited/m.INTSIZE_CSEC,3)) AAS from v$waitclassmetric m, v$system_wait_class n where m.wait_class_id=n.wait_class_id and n.wait_class != 'Idle' group by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait') union select 'CPU_ORA_CONSUMED' CLASS, round(value/100,3) AAS from v$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 union select 'CPU_OS' CLASS , round((prcnt.busy*parameter.cpu_count)/100,3) AAS from ( select value busy from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt, ( select value cpu_count from v$parameter where name='cpu_count' ) parameter union select 'CPU_ORA_DEMAND' CLASS, nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS from v$active_session_history ash where SAMPLE_TIME > sysdate - (60/(24*60*60)) ) select ( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) + CPU_ORA_CONSUMED + decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL, decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS, CPU_ORA_CONSUMED CPU_ORA, decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT, COMMIT, READIO, WAIT from ( select sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED, sum(decode(CLASS,'CPU_ORA_DEMAND' ,AAS,0)) CPU_ORA_DEMAND, sum(decode(CLASS,'CPU_OS' ,AAS,0)) CPU_OS, sum(decode(CLASS,'Commit' ,AAS,0)) COMMIT, sum(decode(CLASS,'User I/O' ,AAS,0)) READIO, sum(decode(CLASS,'Wait' ,AAS,0)) WAIT from AASSTAT) /
FOR ALL COLUMNS SIZE 1
clause), and manually add them when you decide that you really need one.
|
Oracle 10g, by default, collects statistics for an index during
index creation. It is done by design. The internal parameter "_optimizer_compute_index_stats", is set to
TRUE by default. alter session set "_optimizer_compute_index_stats"=FALSE;
IN this case, the time for rebuilding the index is much lower :
15% faster for an index rebuild