ORACLE DBA

In this post I'll try to summarize all sorts of statistics in Oracle, I strongly recommend reading the full article, as it contains information you may find it valuable in understanding Oracle statistics. ################################ Database | Schema | Table | Index Statistics ################################ Gather Database Stats: =================== SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(      ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',      CASCADE => TRUE,      degree => 4,      OPTIONS => 'GATHER STALE',      GATHER_SYS => TRUE,      STATTAB => PROD_STATS); CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will determine whether to collected or not. DEGREE => 4 :Degree of parallelism. options:         =>'GATHER' :Gathers statistics on all objects in the schema.        =>'GATHER AUTO' :Oracle determines whi

           Description:              I have an update statement which modifies around 30000 records. Statement executed successfully on a single record, but when I execute to update all records it is going to fail with following error message: ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], [] Solution:             ·           Open and view the alert log file when error occurred ·           Get the trace file name from alert log file and open it ·           Search the keyword “Plan Table” in trace file ·           Here you found details of the query causing problem ·           Identify the tables from above query and execute the following: Analyze table validate structure cascade; ·           Identify all the indexes on the above tables and execute the following: Analyze index validate structure; ·           Here I found an index which fail to validate and return the below error: ORA-00600:

If you’re a DBA on Enterprise Edition with the Diagnostics Pack and you don’t use the Automatic Workload Repository (AWR) reports, you are seriously missing out. AWR is the holy grail of snapshot-based metrics at the instance level. There are a ton of resources  out there covering AWR , so I won’t be getting into much detail about standard run-of-the-mill AWR reports. Suffice it to say that: AWR reports are an outstanding starting point for delving into bottlenecks during a single snapshot window They provide metrics for all major components of the Oracle instance Database level metrics are also included for I/O times Query details and statistics are also included for the snapshot window being viewed Sometimes it is enough just to look at the DB Time and Top 5 Timed Events (remember, DB Time / Elapsed Time = Average Active Sessions, a great indicator of load for the snapshot window). Other times, you have to delve a little deeper to get a good view of what the instance/databa