AskDba.org Weblog » Entries tagged with "performance"
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 Is it network or database server or some issue with application server? 1)Vmstat output reported Normal Run queue’s/memory utilisation/idle cpu on Application server A and Database Server DB . Rules out resource issue. 2) Connections from other application server B was taking normal time. Rules out Database server issue 3) If incorrect password was entered in connection details, second attempt resulted in normal quick response time. Indicates some network issues. 4) System Admin checked for network issues with traceroute command and reported normal results. Even though SA confirmed that there was no network … Read entire article »
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 http://mwidlake.wordpress.com/2009/07/20/automated-statistics-gathering-silently-fails/ Automated Statistics Gathering Silently Fails #2 http://mwidlake.wordpress.com/2009/07/23/automated-statistics-gathering-silently-fails-2/ Automatic Statistics Gathering Fails #3 http://mwidlake.wordpress.com/2009/07/29/automatic-statistics-gathering-fails-3/ Automated statistics gathering silently fails Automated Statistics Gathering Silently Fails #2 Automatic Statistics Gathering Fails #3 This post is based on some of the discussions on the thread. Please note that this is not intended to discuss bugs (if any) associated with the job Gather_stats_job was introduced with Oracle 10g to gather statistics for database objects which has stale statistics (10% of data has changed, you can query dba_tab_modifications) or the tables for which the statistics has not been gathered (new tables … Read entire article »
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 transaction are performed or none of them (Atomicity). If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then the refresh of each specified materialized view is done in a separate transaction. In case of 9i if a single mview was refreshed , it used to be truncated and then refreshed (which actually was not atomic). In case of multiple mviews being refreshed (part of refresh group), rows were deleted and then the insert was done. As part of change in 10g, if atomic_refresh is … Read entire article »
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 previous partition. EXEC DBMS_STATS.COPY_TABLE_STATS (‘owner’, ‘tabname’, ‘SourcePart’, ‘TargetPart’) You can find more details about it in below link http://optimizermagic.blogspot.com/2009/02/maintaining-statistics-on-large.html There is one more link explaining this with example http://dioncho.wordpress.com/2009/02/12/how-to-copy-partition-stats-we-got-easier-way/ Article from Optimizer development group states that the low and high value for the target partition is set from the partition key values. But when I used dbms_stats.copy_table_stats, I found that it is not the case and it actually copies the low and high values from the previous partition. You can use following function to check the high and low … Read entire article »
Filed under: performance
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 script should be invoked by awrrpt, Rem unless you want to pick a database other than the default. In actual you need only two privileges to run the script. One is SELECT_CATALOG_ROLE and other is execute permission on dbms_workload_repository procedure. If you don’t give privilege explicitly on this package you get following errors select output from table(dbms_workload_repository.awr_report_text( :dbid, … Read entire article »
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 can run some commands which will help in collection useful information about the problem. This will work only in Oracle 10g and higher version. After successfully getting connected run the following commands to generate Hanganalyze and systemstate traces: SQL> oradebug setmypid SQL> oradebug unlimit SQL> oradebug dump systemstate 266 SQL> oradebug tracefile_name – This will give you the name of the tracefile generated. SQL > oradebug dump hanganalyze 2 SQL > oradebug tracefile_name To analyze these trace files one should be aware of Metalink Note: 215858.1. After analyzing these files I found that following event was active and … Read entire article »
Filed under: oracle, performance
Subscribe To Feed
License
AskDba.org Forum
- Mahesh.G on "DR rebuild- Data restore issue" March 11, 2010
- Saurabh Sood on "DR rebuild- Data restore issue" March 11, 2010
- sunkkuma on "DR rebuild- Data restore issue" March 11, 2010
- Amit Bansal on "DB and Listener availability monitoring script" March 11, 2010
- animesh on "DR rebuild- Data restore issue" March 11, 2010
Recent Comments