Posts Tagged ‘Performance’
Optimizer Choosing Nested-Loop Joins Instead of Hash-Joins
In one of my databases, one application query suddenly started to pick Nested-Loop joins instead of Hash-Joins and took almost 6 hours to complete which gets completed in less than 10 secs with Hash-Joins. The same query in another similar database with same configuration [Read More]
User Sessions stuck on resmgr:cpu quantum wait event
We were experiencing lot of session getting stuck on resmgr:cpu quantum in our database. In fact at a time we had 70 sessions which were stuck on this wait event and our cpu load average was touching 60 Checking active resource plan, we found that DEFAULT_MAINTENANCE_PLAN [Read More]
Plan Stability using Sql Profiles and SQL Plan Management
PLAN STABILITY How many times you have noticed a query using Index X when you wanted it to use index Y or query performing Nested Loop join when Hash Join would have completed the query much faster.Or take a scenario when the application suddenly starts using wrong plan [Read More]
V$SQL_SHARED_CURSOR in 11.2.0.2
Oracle 11.2.0.2 has introduced new column “REASON” to V$SQL_SHARED_CURSORS. I came across this extremely useful column while debugging multiple child cursor issue (11.2.0.2 seems to have too many bugs which can cause multiple child cursors). As per documentation [Read More]
Oracle Database Appliance Introduced
Oracle has introduced it new product: ODA (Oracle Database Appliance) I was not able to join Oracle President Mark Hurd’s webcast where he supposed to announce a new Oracle product, now viewing this webcast HERE Kerry Osborne has shared information about this product, [Read More]
Performance Management Guide on AIX
While trying to find the amount of physical memory used by oracle process on AIX, I got reference of a document from Metalink: Performance Management Guide It tell us about which process is using how much memory and how to interpret the output of commands like: vmstat, [Read More]
ORA-01722 with Full Table Scan
My application developers approached me with an issue which is very unique to me. They were complaining about a query which was failing with ORA-01722 “invalid number” after an upgrade to 11.1.0.7 from 10.2.0.4. The syntax of the query is like: select max(a) [Read More]
October 2010 Blogroll Report
It’s been time since we saw log-buffer edition with last being published on 1st October 2010 and Coskan’s weekly blogroll report I found them really useful to read top blog articles for week. So I decided to tag few articles posted in October 2010 which I [Read More]
Sqlplus connection on AIX taking too long
Recently we faced a performance issue, where in sqlplus connection from application server A (AIX 5.3) to Database server DB (different physical server,10gR2 on AIX 5.3) was taking nearly 1-2 minutes. We did some quick check’s to narrow down the problematic area.i.e [Read More]
GATHER_STATS_JOB – Is it enabled?
Few days back , Martin had posted a series of post on a issue where GATHER_STATS_JOB was failing silently for a large object. If you have missed it, you can check following links Automated statistics gathering silently fails [Read More]
Mview Complete Refresh and Atomic_refresh parameter
This post discusses issue faced by me while working on Materialized view (mview) creation at new site(database). Starting Oracle 10g, how atomic_refresh works with complete refresh has undergone a change. Atomic_refresh parameter is to allow either all of the tasks of a [Read More]
dbms_stats.copy_table_stats does not alter low/high value
I was working on a performance issue arising due to missing table stats on a new partition for large Partitioned table.This was a 10.2.0.4 database on IBM AIX 5L. So as to resolve the issue , I used dbms_stats.copy_table_stats to copy the stats to new parition from the [Read More]
You Don’t need SysDBA privilege to run Awrrpt..
Well I tried to Sensationalize this post by using such a heading Though I just wanted to point out that Awrrpt script can be run without sysdba privilege too. Actually if you open awrrpti.sql script , it contains following line Rem NOTES Rem Run as SYSDBA. Generally this [Read More]
Connections to DataBase Hang Including “/ as sysdba”
Recently I faced one issue where all the connection to database hung and it was also not possible to login to database using “/ as sysdba”. To get access of sqlplus I used the following syntax: $ sqlplus -prelim / as sysdba With “prelim” option we [Read More]
Index Access best approach?? Not Always…
There is a common misconception that Index access is the best access method for query execution. This approach leads to people concentrate on removing Full Table Scans (FTS) from the Query execution plan. I recently worked on a performance tuning issue where I found such [Read More]
