Tweet
My Statspack has following details.
Enqueue activity DB/Inst: Snaps: 1791-1808
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc
One of my 10.2.0.1 database was hanging despite allocating ample opf resources. The CPU, Memory,Disk accesses were normal but under heavy load server would freeze for no valid reason. I had to raise TAR with Oracle.
The architecture of the AWR is quite simple. The MMON background process polls the x$ fixed tables from the SGA region and stores them in the AWR tables. From there, the performance data is instantly available for analysis. The Enterprise Manager can be used for graphical data display. Alternatively, the Automatic Database Diagnostic Monitor (ADDM) can be used for automated tuning analysis, or SQL*Plus can be used if customized Oracle tuning size reports are desired. Read more…
I was trying to execute following SQL through client side tools and found network delays were contributing to the SQl execution times. This SQL normally returns in less than 50 millisec and now taking more than 700 Msec. I am addicted to TOAD -TORA GUI created Execution plans and was looking for a an easy way to get execution times as well as plans on sqlplus. Here is how you do it .
| Statspack | AWR | |||
| STATS$DATABASE_INSTANCE | Tables store historical data or snapshots | |||
| STATS$IDLE_EVENT | ||||
| STATS$LEVEL_DESCRIPTION | ||||
| STATS$ROLLSTAT | ||||
| STATS$SESSION_EVENT | ||||
| STATS$SNAPSHOT | ||||
| STATS$SQL_PLAN_USAGE | ||||
| STATS$SQL_SUMMARY | WRH$_ACTIVE_SESSION_HISTORY | WRI$_ADV_ACTIONS | ||
| STATS$STATSPACK_PARAMETER | WRH$_ACTIVE_SESSION_HISTORY_BL | WRI$_ADV_ASA_RECO_DATA | ||
| STATS$BG_EVENT_SUMMARY | WRH$_BG_EVENT_SUMMARY | WRI$_ADV_DEFINITIONS | ||
| WRH$_BUFFERED_QUEUES | WRI$_ADV_DEF_PARAMETERS | |||
| WRH$_BUFFERED_SUBSCRIBERS | WRI$_ADV_DIRECTIVES | |||
| STATS$BUFFER_POOL_STATISTICS | WRH$_BUFFER_POOL_STATISTICS | WRI$_ADV_FINDINGS | ||
| WRH$_COMP_IOSTAT | WRI$_ADV_JOURNAL | |||
| WRH$_CR_BLOCK_SERVER | WRI$_ADV_MESSAGE_GROUPS | |||
| WRH$_CURRENT_BLOCK_SERVER | WRI$_ADV_OBJECTS | |||
| WRH$_DATAFILE | WRI$_ADV_PARAMETERS | |||
| STATS$DB_CACHE_ADVICE | WRH$_DB_CACHE_ADVICE | WRI$_ADV_RATIONALE | ||
| WRH$_DB_CACHE_ADVICE_BL | WRI$_ADV_RECOMMENDATIONS | |||
| STATS$DLM_MISC | WRH$_DLM_MISC | WRI$_ADV_REC_ACTIONS | ||
| WRH$_DLM_MISC_BL | WRI$_ADV_SQLA_FAKE_REG | |||
| STATS$ENQUEUE_STAT | WRH$_ENQUEUE_STAT | WRI$_ADV_SQLA_MAP | ||
| WRH$_EVENT_NAME | WRI$_ADV_SQLA_STMTS | |||
| WRH$_FILEMETRIC_HISTORY | WRI$_ADV_SQLA_TMP | |||
| STATS$FILESTATXS | WRH$_FILESTATXS | WRI$_ADV_SQLT_BINDS | ||
| WRH$_FILESTATXS_BL | WRI$_ADV_SQLT_PLANS | |||
| STATS$INSTANCE_RECOVERY | WRH$_INSTANCE_RECOVERY | WRI$_ADV_SQLT_RTN_PLAN | ||
| WRH$_INST_CACHE_TRANSFER | WRI$_ADV_SQLT_STATISTICS | |||
| WRH$_INST_CACHE_TRANSFER_BL | WRI$_ADV_SQLW_COLVOL | |||
| WRH$_JAVA_POOL_ADVICE | WRI$_ADV_SQLW_STMTS | |||
| STATS$LATCH | WRH$_LATCH | WRI$_ADV_SQLW_SUM | ||
| WRH$_LATCH_BL | WRI$_ADV_SQLW_TABLES | |||
| STATS$LATCH_CHILDREN | WRH$_LATCH_CHILDREN | WRI$_ADV_SQLW_TABVOL | ||
| WRH$_LATCH_CHILDREN_BL | WRI$_ADV_TASKS | |||
| STATS$LATCH_MISSES_SUMMARY | WRH$_LATCH_MISSES_SUMMARY | WRI$_ADV_USAGE | ||
| WRH$_LATCH_MISSES_SUMMARY_BL | WRI$_AGGREGATION_ENABLED | |||
| WRH$_LATCH_NAME | WRI$_ALERT_HISTORY | |||
| STATS$LATCH_PARENT | WRH$_LATCH_PARENT | WRI$_ALERT_OUTSTANDING | ||
| WRH$_LATCH_PARENT_BL | WRI$_ALERT_THRESHOLD | |||
| STATS$LIBRARYCACHE | WRH$_LIBRARYCACHE | WRI$_ALERT_THRESHOLD_LOG | ||
| WRH$_LOG | WRI$_DBU_CPU_USAGE | |||
| WRH$_METRIC_NAME | WRI$_DBU_CPU_USAGE_SAMPLE | |||
| WRH$_MTTR_TARGET_ADVICE | WRI$_DBU_FEATURE_METADATA | |||
| WRH$_OPTIMIZER_ENV | WRI$_DBU_FEATURE_USAGE | |||
| WRH$_OSSTAT | WRI$_DBU_HIGH_WATER_MARK | |||
| WRH$_OSSTAT_BL | WRI$_DBU_HWM_METADATA | |||
| WRH$_OSSTAT_NAME | WRI$_DBU_USAGE_SAMPLE | |||
| STATS$PARAMETER | WRH$_PARAMETER | WRI$_OPTSTAT_AUX_HISTORY | ||
| WRH$_PARAMETER_BL | WRI$_OPTSTAT_HISTGRM_HISTORY | |||
| WRH$_PARAMETER_NAME | WRI$_OPTSTAT_HISTHEAD_HISTORY | |||
| STATS$PGASTAT | WRH$_PGASTAT | WRI$_OPTSTAT_IND_HISTORY | ||
| STATS$PGA_TARGET_ADVICE | WRH$_PGA_TARGET_ADVICE | WRI$_OPTSTAT_OPR | ||
| WRH$_PROCESS_MEMORY_SUMMARY | WRI$_OPTSTAT_TAB_HISTORY | |||
| STATS$RESOURCE_LIMIT | WRH$_RESOURCE_LIMIT | WRI$_SCH_CONTROL | ||
| STATS$ROWCACHE_SUMMARY | WRH$_ROWCACHE_SUMMARY | WRI$_SCH_VOTES | ||
| WRH$_ROWCACHE_SUMMARY_BL | WRI$_SEGADV_CNTRLTAB | |||
| WRH$_RULE_SET | WRI$_SEGADV_OBJLIST | |||
| WRH$_SEG_STAT | WRI$_SQLSET_BINDS | |||
| WRH$_SEG_STAT_BL | WRI$_SQLSET_DEFINITIONS | |||
| STATS$SEG_STAT_OBJ | WRH$_SEG_STAT_OBJ | WRI$_SQLSET_MASK | ||
| WRH$_SERVICE_NAME | WRI$_SQLSET_PLANS | |||
| WRH$_SERVICE_STAT | WRI$_SQLSET_PLANS_TOCAP | |||
| WRH$_SERVICE_STAT_BL | WRI$_SQLSET_PLAN_LINES | |||
| WRH$_SERVICE_WAIT_CLASS | WRI$_SQLSET_REFERENCES | |||
| WRH$_SERVICE_WAIT_CLASS_BL | WRI$_SQLSET_STATEMENTS | |||
| WRH$_SESSMETRIC_HISTORY | WRI$_SQLSET_STATISTICS | |||
| STATS$SESSTAT | WRH$_SESS_TIME_STATS | WRI$_SQLSET_WORKSPACE | ||
| STATS$SGA | WRH$_SGA | WRI$_TRACING_ENABLED | ||
| STATS$SEG_STAT , STATS$SGASTAT | WRH$_SGASTAT | |||
| WRH$_SGASTAT_BL | ||||
| WRH$_SGA_TARGET_ADVICE | ||||
| STATS$SHARED_POOL_ADVICE | WRH$_SHARED_POOL_ADVICE | |||
| STATS$SQL_STATISTICS | WRH$_SQLSTAT | |||
| WRH$_SQLSTAT_BL | ||||
| STATS$SQLTEXT | WRH$_SQLTEXT | |||
| WRH$_SQL_BIND_METADATA | ||||
| STATS$SQL_PLAN | WRH$_SQL_PLAN | |||
| WRH$_SQL_SUMMARY | ||||
| STATS$SQL_WORKAREA_HISTOGRAM | WRH$_SQL_WORKAREA_HISTOGRAM | |||
| WRH$_STAT_NAME | ||||
| WRH$_STREAMS_APPLY_SUM | ||||
| WRH$_STREAMS_CAPTURE | ||||
| WRH$_STREAMS_POOL_ADVICE | ||||
| WRH$_SYSMETRIC_HISTORY | ||||
| WRH$_SYSMETRIC_SUMMARY | ||||
| STATS$SYSSTAT | WRH$_SYSSTAT | |||
| WRH$_SYSSTAT_BL | ||||
| STATS$SYSTEM_EVENT | WRH$_SYSTEM_EVENT | |||
| WRH$_SYSTEM_EVENT_BL | ||||
| WRH$_SYS_TIME_MODEL | ||||
| WRH$_SYS_TIME_MODEL_BL | ||||
| WRH$_TABLESPACE_SPACE_USAGE | ||||
| WRH$_TABLESPACE_STAT | ||||
| WRH$_TABLESPACE_STAT_BL | ||||
| WRH$_TEMPFILE | ||||
| STATS$TEMPSTATXS | WRH$_TEMPSTATXS | |||
| WRH$_THREAD | ||||
| STATS$UNDOSTAT | WRH$_UNDOSTAT | |||
| WRH$_WAITCLASSMETRIC_HISTORY | ||||
| STATS$WAITSTAT | WRH$_WAITSTAT | |||
| WRH$_WAITSTAT_BL | ||||
Displays partition information for the specified index, or all indexes. Read more…
undo_retention parameter will not guarantee a RETENTION unless you define a RETENTION GUARANTEE CLAUSE on tablespace level Read more…
It’s a tricky question. I have explained here by using a 33GB SAP BW driving table called VBAP which needed a major work. At end I had to rebuild this table using Quest Shareplex.
Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.
Traditionally, performing a function on an indexed column in the where clause of a query guaranteed an index would not be used. Oracle 8i introduced Function Based Indexes to counter this problem. Rather than indexing a column, you index the function on that column, storing the product of the function, not the original column data. When a query is passed to the server that could benefit from that index, the query is rewritten to allow the index to be used. The following code samples give an example of the use of Function Based Indexes:
You have to periodically check your indexes to see if they become skewed and, therefore, good candidates for rebuild.
Index monitoring could be initiated and stopped using ALTER INDEX syntax shown below.
Let’s create a virtual index
07:59:12 orcl> create index hr.emp2_emp_id_virtual on hr.employees2(employee_id) nosegment;
Index created.
Choose the join order that gives you least number of rows to be joined to the other tables. That is, if you’re joining three tables, the one with the more restrictive filter should be joined first to one of the other two tables.
Here is a situation, This afternoon I have seen my CPU hitting through the roof on a Live system. There are about 6 Oracle instances and one is chewing more than 90% of CPU time.
SET LINESIZE 145
SET PAGESIZE 9999COLUMN sid FORMAT 999 HEADING ‘SID’
COLUMN oracle_username FORMAT a12 HEADING ‘Oracle User’ JUSTIFY right
COLUMN os_username FORMAT a9 HEADING ‘O/S User’ JUSTIFY right
COLUMN session_program FORMAT a18 HEADING ‘Session Program’ TRUNC
COLUMN session_machine FORMAT a8 HEADING ‘Machine’ JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING ‘PGA Memory’
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING ‘PGA Memory Max’
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING ‘UGA Memory’
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING ‘UGA Memory MAX’
Installation
The following scripts must be run as a user with SYSDBA privilege:
* SPCREATE.SQL: Creates entire Statspack environment (calls SPCUSR.SQL, SPCTAB.SQL, SPCPKG.SQL) * SPDROP.SQL: Drops entire Statspack environment (calls SPDTAB.SQL, SPDUSR.SQL)
Optimizer stats can play a key part in deciding execution plan. Here is an example
Table “RSODSACTREQ” has 313783 of total rows
select A.snap_id AWR_SNAP_ID, A.INSTANCE_NUMBER,
to_char(B.BEGIN_INTERVAL_TIME,’DD-MON-YYYY HH24:MI:SS’) AWR_START_TIME,
to_char(B.END_INTERVAL_TIME,’DD-MON-YYYY HH24:MI:SS’) AWR_END_TIME,
A.RESOURCE_NAME, MAX_UTILIZATION
from sys.wrh$_resource_limit A, sys.wrm$_snapshot B
where A.resource_name like ‘%processes%’
and A.snap_id=b.snap_id
and A.INSTANCE_NUMBER= B.INSTANCE_NUMBER
and A.INSTANCE_NUMBER= 1
and B.BEGIN_INTERVAL_TIME > sysdate – 12/24;
select
sid,
username,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in (‘physical reads’,
‘physical writes’,
‘physical writes direct’,
‘physical reads direct’,
‘physical writes direct (lob)’,
‘physical reads direct (lob)’)
group by
b.sid, nvl(b.username,p.name)),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in (‘physical reads’,
‘physical writes’,
‘physical writes direct’,
‘physical reads direct’,
‘physical writes direct (lob)’,
‘physical reads direct (lob)’))
order by
3 desc;
Different DBAs have different views on % for analyze. The oracle documentation recommends to carry full analyze on entire database which is not possible for most live systems runnning into terabytes.
In past I had performance issue on my database just over 300 GB. There were one table ORDER_DATA with 400 million rows. That one table pulled entire system down number of times just because it wasn’t properly analyzed and oracle didn’t knew data distribution in the table.
I was struggling to understand where things are going wrong as we were analysing entire table every night but not with hash buckets-histograms and surprisingly in SQL execution it was using a right index.
Exporting and Importing Statistics
Caveat: Always use import/export and use imp/exp utility on schema user who owns tables. I have wasted a week where I was exporting as DBA for XYZ user and then importing into different system under different username.
1. Login as PERFSTAT user on database. It won’t work unless U login as PERFSTAT user
Change a.statistic# to respective value
Stats for Working Hours
select to_char(trunc(b.snap_time),'DD-MM-YYYY') ,statistic#,name, sum(value) from STATS$SYSSTAT A, stats$snapshot B where a.snap_id=b.snap_id and trunc(b.snap_time) > trunc(sysdate -30) and to_char(b.SNAP_TIME,'HH24') > 8 and to_char(b.SNAP_TIME,'HH24') <18 and a.statistic#=54 group by trunc(b.snap_time) ,statistic#,name order by trunc(b.snap_time)
