I have a client which called me yesterday and complained that users were experiencing performance degradation while there was no major change on database.
After some investigations, I found that auto 10g stats gathering is on and from DBA_OPTSTAT_OPERATIONS and DBA_TAB_STATS_HISTORY I found that last stats gathering was close to the time that users started complaining about performance.So as my first guess I pointed to the new stats as culprit. To prove this guess, I was Lucky that database was 10g so that old version of stats are saved automatically.
I went with the idea that If new stats resulted to suboptimal execution plan, old stats could be restored to fix the issue.
To restore statistics, I ran exec dbms_stats.restore_table_stats({owner} ,{table name}, {timestamp}); which replaced old stats with new stats.
After restoring old stats, performance problem was resolved.
Also in order to keep stats on this table intact, I lock stats on that specific table for a while.
Showing posts with label 10g. Show all posts
Showing posts with label 10g. Show all posts
TIP #65 : 10g listener.
Today, a client came up with a question on whether or not password protected listener is safer than non-password protected listener in 10g.
To answer, In Oracle 10g, listener is secure by itself and there is no need to set a password for listener as in older version to protect listener.
By default, listener uses local OS authentication which means that only the user who owned listener can admin it. This feature is enabled by default.
If you run lsnrctl status in 10g, you should see any line like the following in output:
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production
Start Date
Uptime
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File
Listener Log File
In above example, If listener is started as Oracle user and user X attempts to admin it or Oracle user from a different node attempts to admin it, the following error will appear.
TNS-01190: The user is not authorized to execute the requested
On the other hand, if a password is set for 10g listener, all users who know the password can admin listener.For the password protected listener in 10g, the result of lsnrctl status would be something like this :
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production
Start Date
Uptime
Trace Level off
Security ON: Password or Local OS Authentication
SNMP ON
Listener Parameter File
Listener Log File
To conclude, if only user who started listener is allowed to admin listener, you do not need to set password for 10g listener.Listener by itself is protected and the only user who can admin the listener is listener owner.To me, it seems to be more restricted.
However, if you want other users to admin listener, you still need to have password protected listener. All users who knows password can run admin command for listener.
To me, it seems less restricted.
To answer, In Oracle 10g, listener is secure by itself and there is no need to set a password for listener as in older version to protect listener.
By default, listener uses local OS authentication which means that only the user who owned listener can admin it. This feature is enabled by default.
If you run lsnrctl status in 10g, you should see any line like the following in output:
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production
Start Date
Uptime
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File
Listener Log File
In above example, If listener is started as Oracle user and user X attempts to admin it or Oracle user from a different node attempts to admin it, the following error will appear.
TNS-01190: The user is not authorized to execute the requested
On the other hand, if a password is set for 10g listener, all users who know the password can admin listener.For the password protected listener in 10g, the result of lsnrctl status would be something like this :
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production
Start Date
Uptime
Trace Level off
Security ON: Password or Local OS Authentication
SNMP ON
Listener Parameter File
Listener Log File
To conclude, if only user who started listener is allowed to admin listener, you do not need to set password for 10g listener.Listener by itself is protected and the only user who can admin the listener is listener owner.To me, it seems to be more restricted.
However, if you want other users to admin listener, you still need to have password protected listener. All users who knows password can run admin command for listener.
To me, it seems less restricted.
TIP 53# : Exclude option in data pump on Window.
As all know, filtering option in datapump (10g version of traditional export/import) is very powerful.
I just wanted to use this option and exclude a table from whole schema export per client `s request.A client is running 10g database on Windows.
Regarding to Oracle documentation and expdp help=y, it is supposed to be as simple as adding exclude=table:table_name.
However, I got the following errors when I ran it.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE_NAME_EXPR filter is badly formed.
ORA-00920: invalid relational operator
I tried option with the following syntax :
exclude=TABLE:"= 'TABLE_NAME'"
exclude=TABLE:TABLE_NAME
exclude=TABLE:" IN ('TABLE_NAME') "
No luck. Got the same error.
Finally, I figured out that window can not parse " and need to add escape seuqence.
So exclude option on windows should have syntax like this :
exclude=TABLE:\"='TABLE_NAME'\"
I just wanted to use this option and exclude a table from whole schema export per client `s request.A client is running 10g database on Windows.
Regarding to Oracle documentation and expdp help=y, it is supposed to be as simple as adding exclude=table:table_name.
However, I got the following errors when I ran it.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE_NAME_EXPR filter is badly formed.
ORA-00920: invalid relational operator
I tried option with the following syntax :
exclude=TABLE:"= 'TABLE_NAME'"
exclude=TABLE:TABLE_NAME
exclude=TABLE:" IN ('TABLE_NAME') "
No luck. Got the same error.
Finally, I figured out that window can not parse " and need to add escape seuqence.
So exclude option on windows should have syntax like this :
exclude=TABLE:\"='TABLE_NAME'\"
TIP 51# : Performance issue after upgrade to 10g.
I faced with a performance issue for a client right after upgrade from 9i to 10g.
We all know that optimizer in 10g is different animal which is supposed to work better and more inteligent.This is almost true but not always.
Some queries after upgrade from 9i to 10g may have performance issue which means they may run slower in 10g.
For figuring out whether or not performance issue is because of new enhanced features in 10g please turn off the following options and run query in 10g.
alter session set "_optimizer_cost_based_transformation" =off; (Disable subquery unnesting and view merging -- New 10g optimizer feature).
alter session set "_gby_hash_aggregation_enabled" = FALSE; (Disable Hash group by aggregation -- New 10g optimizer feature).
If disabling above options resolve the situation, the performance issue is definitely because of new features in 10g optimizer. Otherwise, optimizer would not be culprit.
TIP #46 : Using 'Plus archivelog' in backup
I had a client which using Oracle 10gR3 on Windows.Its backup strategy was simple. Daily incremental level 1 backup and weekly level 0 backup.Database was configured to use flash recovery area for archivelogs and backups.
I regularly get flash recovery full problem with retention policy 2 weeks.Suprisingly database is only 1GB.
Bacjup script was as simple as 'backup database ...... plus archivelog ';
Finally, I found that problem was because of plus archivelog.
Regarding to RMAN document, RMAN performs these steps when "plus archivelog" command is in backup script.
I regularly get flash recovery full problem with retention policy 2 weeks.Suprisingly database is only 1GB.
Bacjup script was as simple as 'backup database ...... plus archivelog ';
Finally, I found that problem was because of plus archivelog.
Regarding to RMAN document, RMAN performs these steps when "plus archivelog" command is in backup script.
- Runs an ALTER SYSTEM ARCHIVE LOG CURRENT statement.
- Runs the BACKUP ARCHIVELOG ALL command. Note that if backupoptimization is enabled, then RMAN only backs up logs that have not yetbeen backed up.
- Backs up the files specified in the BACKUP command.
- Runs an ALTER SYSTEM ARCHIVE LOG CURRENT statement.
- Backs up any remaining archived redo logs.
If backup otimization is off (default RMAN), each time all archivelogs are backed up which means that on daily basis backup size grows since archivelogs in flash recovery are not removed as space exists.
In a nutshell, when "plus archivelog" is used in backup script , make sure that optimization is on in RMAN configuration otherwise in each backup whole archive logs are backed up.
TIP #35 : 10g editions.
Oracle release four edition for 10g.
- Express Edition
- Standard Edition One
- Standard Edition
- Enterprise Edition
Express Edition :
- Entry level database based on Oracle
- Free to develop, deploy and distribute
- 1 CPU limit
Standard Edition One :
- Full featured database which suits for small-to-medium business
- 2 CPU limit.
- Reasonable price. 149$/user with 5 users minimum.
Standard Edition
- Full featured database for small-to-medium business.
- Mostly address performance, availability, scalability and security
- 4 CPU limit.
Enterprise Edition
- Full featured database for enterprise class business
- No limitation in CPU.
- Suitable for transaction processing, BI and content management applications.
For details, refer to http://www.oracle.com/database/product_editions.html
TIP #29 : RMAN and Resetlogs in 10g.
After each incomplete recovery, database should be opened with resetlogs.
In 9i and below, if database is need to be restored to time prior to resetlogs, different incarnation should be restored.In addition, since new log thread starts with number 1 after resetlogs, all previous backups become obsolete.
In 10g, this is not an issue anymore. RMAN can use any backup before or after resetlogs to recover Oracle database.No need to use different incarnation.
In 9i and below, if database is need to be restored to time prior to resetlogs, different incarnation should be restored.In addition, since new log thread starts with number 1 after resetlogs, all previous backups become obsolete.
In 10g, this is not an issue anymore. RMAN can use any backup before or after resetlogs to recover Oracle database.No need to use different incarnation.
TIP #28 : RMAN 10g and incremental backup.
Why incremental backup ?
The most important reason for doing incremental backups is associated with data warehouse environments, where many operations are done in NOLOGGING mode and data changes do not go to the archived log files.
Considering the massive size of data warehouses today, and the fact that most of the data in them does not change, full backups are neither desirable nor practical. Therefore , doing incremental backups in RMAN is an ideal alternative.
WhyDBAs dis not like incremental backup in 9i ?
Oracle does full scan for finding changes in 9i which generates some performance problem.This is why most DBAs did not like incremental backup in 9i.
What is new with 10g incremental backup ?
Block changes can be tracked in 10g which means that Oracle does not need to do full scan anymore to find out changes.
To enable track changing run the following command.
alter database enable block change tracking using file '/rman_bkups/change.log';
To disable block track change.
alter database disable block change tracking;
The most important reason for doing incremental backups is associated with data warehouse environments, where many operations are done in NOLOGGING mode and data changes do not go to the archived log files.
Considering the massive size of data warehouses today, and the fact that most of the data in them does not change, full backups are neither desirable nor practical. Therefore , doing incremental backups in RMAN is an ideal alternative.
WhyDBAs dis not like incremental backup in 9i ?
Oracle does full scan for finding changes in 9i which generates some performance problem.This is why most DBAs did not like incremental backup in 9i.
What is new with 10g incremental backup ?
Block changes can be tracked in 10g which means that Oracle does not need to do full scan anymore to find out changes.
To enable track changing run the following command.
alter database enable block change tracking using file '/rman_bkups/change.log';
To disable block track change.
alter database disable block change tracking;
TIP #27 : Required RMAN backups for restore.
In 10g, before doing recovery, it is possible to find out backups required to perform a restore operation.This feature is called recovery preview and can be done by adding preview at the end of restore command.
restore database preview;
It can also be run for specific restore operation.
restore tablespace users preview;
restore database preview;
It can also be run for specific restore operation.
restore tablespace users preview;
TIP #24 : RESUMABLE_TIMEOUT in 10g
RESUMABLE_TIMEOUT is new parameter in 10g which allows DBAs to enable/disable resumable statements and specify resumable timeout at the system level. In 9i this parameter could only be set on session level.
Sometimes DBAs does not know how much space is required for UNDO,TEMP or datafile to complete a big job.By setting this parameter, any space issue does not break the job.It reports an error in Alert log and the job automatically resumes once DBA has fixed the issue.
Here is a test scenario from Metalink :
1. Make the datafile autoextend off
2. Create a big table on the tablespace where the datafile is autoextend off
3. Insert rows into the table until it exceeds the space required by the INSERT operation.
4. Monitor the alert.log:
you find the following info:
statement in resumable session 'User SYS(0), Session 17, Instance 1' was suspended due to ORA-01653: unable to extend table SYS.T1 by 100 in tablespace TEST1
5. Check the table WRI$_ALERT_OUTSTANDING or view DBA_OUTSTANDING_ALERTS : SQL> select decode(message_level, 5, 'WARNING', 1, 'CRITICAL') alert_level, reason from dba_outstanding_alerts where reason like '%resumable%';
6. Modify the datafile autoextend to ON and check the alert.log.
Depending on success or failure, you get the following info:
Sometimes DBAs does not know how much space is required for UNDO,TEMP or datafile to complete a big job.By setting this parameter, any space issue does not break the job.It reports an error in Alert log and the job automatically resumes once DBA has fixed the issue.
Here is a test scenario from Metalink :
1. Make the datafile autoextend off
2. Create a big table on the tablespace where the datafile is autoextend off
3. Insert rows into the table until it exceeds the space required by the INSERT operation.
4. Monitor the alert.log:
you find the following info:
statement in resumable session 'User SYS(0), Session 17, Instance 1' was suspended due to ORA-01653: unable to extend table SYS.T1 by 100 in tablespace TEST1
5. Check the table WRI$_ALERT_OUTSTANDING or view DBA_OUTSTANDING_ALERTS : SQL> select decode(message_level, 5, 'WARNING', 1, 'CRITICAL') alert_level, reason from dba_outstanding_alerts where reason like '%resumable%';
6. Modify the datafile autoextend to ON and check the alert.log.
Depending on success or failure, you get the following info:
Subscribe to:
Posts (Atom)