:

The Latest Oracle Posts

DBMS_STATS – Gathering Database Statistics

There are various thoughts on the correct way to gather statistics in a database. Do you collect them weekly/monthly/daily? Do you do it when the objects have had a certain level of changes made to them? Do you do it straight in PROD or test it in a QA environment first? In this article I […]

Continue Reading

System Statistics in the Oracle Database

If you can, refer to Oracle’s Metalink note: 149560.1 Firstly, you must have DBA privileges or GATHER_SYSTEM_STATISTICS role to update dictionary system statistics. Secondly, Oracle recommends that you gather system statistics during peak load in order to give the optimizer the ability to choose the best plan based on system resource usage and throughput as well […]

Continue Reading

11g Install File Not Found

11g Install File Not Found

The Problem I was installing the Oracle database 11g software. I had extracted the software from two zip files which were downloaded from Metalink. Two folders were created. When running the installation via the GUI, I received the error messages below, in addition to many other similar messages. File not found D:\Oracle\product\11.2\owb\external\oc4j_applications\applications\WFMLRSVCApp.ear D:\Oracle\product\11.2\oc4j\j2ee\oc4j_applications\applications\WFMLRSVCApp.ear D:\Oracle\product\11.2\owb\external\oc4j_applications\applications\WFMGRApp.ear D:\Oracle\product\11.2\owb\external\oc4j_applications\applications\WFMGRApp.ear […]

Continue Reading

Control File Parallel Write Wait Event

Usually, you will find that high wait times on the control  file parallel write wait event will be indicative of high log switching. The query below will show you which session performed  control file transactions. select                a.sid,                decode(a.type, ‘BACKGROUND’,  ‘BACKGROUND-‘||substr(a.program, instr(a.program,'(‘,1,1)), ‘FOREGROUND’) type,                b.time_waited,                round(b.time_waited/b.total_waits,4) average_wait,        […]

Continue Reading

AWR Report Shows High % and Wait Times

The Problem I was using an AWR report in order to carry out some testing on a DEV environment. After generating the AWR report, I noticed that the percentage differences and wait times on the report looked wrong. None of the percentages tallied up to 100% and the numbers were huge for a lot of […]

Continue Reading

CPU Time in Top 5 Timed Events on Statspack/AWR Report

When I first take a look at a statspack or Automatic Workload Repository (AWR) report, and there have been no reported performance  problems, I home in on the Top 5 Timed Events section toward the top of the AWR report. It is from here that you are able to get clues as to which direction you should go in, and therefore […]

Continue Reading

Oracle Partitioning Fundamentals

Partitioning It’s a topic which a large amount of information could be written. There are so many ways to partition, good ways, bad ways, complicated ways and out right ugly ways! If you have read through the Oracle documentation you will know that there is a document written purely for very large databases and partitioning. […]

Continue Reading

ORA-20000: index or partition of such index is in unusable state

ORA-20000: index or partition of such index is in unusable state

The Problem During one of our overnight jobs which gathers statistics on several tables after having data loaded into them, the job failed with the ORA-20000 error message. The problem was because we had a partition splitting job which ran against the table which caused the indexes to become unusable after the partition had been […]

Continue Reading

The Oracle STARTUP command

If you are only interested in learning how to restart Oracle please read the section lower down on “STARTUP FORCE”. This is a quick way to restart your Oracle database but it’s not the recommended or cleanest approach! Before I explain a little about how to start the Oracle database let’s clarify the different parts of […]

Continue Reading