Limiting I/O and CPU resources using 11g Oracle Resource Manager

Recently I was working on using Oracle DBMS_RESOURCE_MANAGER to limit resources usage by read-only queries. This was required to prevent long running ad-hoc and poorly written queries.Instead of writing custom sql /script to kill long running session, we decided to utilize Oracle Database Resource Manager. We decided using elapsed_time as criteria for cancelling sql.But during…

11gr2-Formatted crsctl output

If you don’t like crsctl status res –t output then try using below 🙂 Can also download here crsstat11g. I have tried this on linux crsctl status res |grep -v "^$"|awk -F "=" 'BEGIN {print " "} {printf("%s",NR%4 ? $2"|" : $2"\n")}'|sed -e 's/ *, /,/g' -e 's/, /,/g'|\ awk -F "|" 'BEGIN { printf "%-40s%-35s%-20s%-50s\n","Resource…

Checking column usage information using dbms_stats

Oracle 11gR2 DBMS_STATS introduced useful function report_col_usage to report column usage i.e if column is being used for equality,like predicates. I came to know it while reading Optimizer whitepaper .As per document, DBMS_STATS.REPORT_COL_USAGE reports column usage information and records all the SQL operations the database has processed for a given object. This information is used by Oracle…

Poll on Sql Plan Management

Dominic is conducting poll on SPM and Sql Profiles usage on his website. Link can be found here  I have been using SPM and SQL Profiles (using coe_xfr_profile.sql) to fix plans for queries  and believe me its very easy and quick way of fixing problems in production database. I have not yet used SPM to…

ORA-7445 core dump [kokscold()+849] from EM 12c

The first problem reported by my EM 12c is an ORA-7445 error. After checking the alert log following is the exact message: Sun Feb 19 12:09:41 2012 Errors in file /u01/app/oracle/diag/rdbms/pkldb/pkldb/trace/pkldb_j035_14712.trc (incident=52961): ORA-07445: exception encountered: core dump [kokscold()+849] [SIGSEGV] [ADDR:0x50] [PC:0x115E16F] [Address not mapped to object] [] Incident details in: /u01/app/oracle/diag/rdbms/pkldb/pkldb/incident/incdir_52961/pkldb_j035_14712_i52961.trc From the incident trace…

11gR2:Listener Startup Issues

In this blog post I will be discussing listener startup issues faced in 11gR2 RAC. I will be constantly updating this post based on my experiences or any comments on this blog post. Let’s get started.You will experience following errors while starting listener using srvctl [[email protected]]~% srvctl start listener -n prod01 PRCR-1013 : Failed to…