Statements that are using the most CPU
Statements that are performing the most disk I/O operations
Statements that are having the most executions
Statements that are taking the longest time to execute (elapsed time)
LOOK FOR RESOURCE INTENSIVE SQL STATEMENTS
heaviest time consumer is CPU-related e.g. CPU Other or CPU Parse time or an I/O-related Wait Event e.g. db file sequential read or db file scattered read:
TOP Wait Events
TOP SQL STATEMENTS
containing the SQL statements with most buffer accesses
containing the SQL statements with most read I/Os from disk.
containing the SQL statements executed the most times.
containing the SQL statements with most soft parse calls.
containing the SQL statements occupying the most Library Cache memory.
containing the SQL statements with most versions (children cursors).
SQL WITH HIGH CPU OTHER TIME IN STATSPACK
SQL ordered by Parse Calls
HIGH DISK I/O WAITS
Identifying SQL statements responsible for most physical reads from the Statspack section SQL ordered by Reads has similar concepts as for SQL ordered by Gets.
SQL STATEMENTS WITH MOST EXECUTIONS
SQL STATEMENTS WITH HIGH SHARED POOL CONSUMPTION
This can help with Shared Pool and Library Cache/Shared Pool latch tuning
Thursday, May 31, 2007
New 10g background processes
CTWR :This is a new process Change Tracking Writer (CTWR) which works with the new block changed tracking features in 10g for fast RMAN incremental backups.
MMNL:The Memory Monitor Light (MMNL) process is a new process in 10g which works with the Automatic Workload Repository new features (AWR) to write out full statistics buffers to disk as needed.
MMON:The memory monitor (MMON) process was introduced in 10g and is associated with the Automatic Workload Repository new features used for automatic problem detection and self-tuning. MMON writes out the required statistics for AWR on a scheduled basis.
RBAL: This is the ASM related process that performs rebalancing of disk resources controlled by ASM.
ARBx :These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.
ASMB:The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.
RVWR: Flashback database
Major init.ora parameters used in a Data warehouse
PARALLEL_AUTOMATIC_TUNING: Oracle determines the default values for parameters that control parallel execution. In addition to setting this parameter, you must specify the
PARALLEL clause for the target tables in the system. Oracle then tunes all subsequent parallel operations automatically.
PARALLEL_ADAPTIVE_MULTI_USER: Enables an adaptive algorithm designed to improve performance in multi-user environments that use parallel execution. The algorithm automatically reduces the requested degree of parallelism based on the system load at query startup time. The effective degree of parallelism is based on the default degree of parallelism, or the degree from the table or hints, divided by a reduction factor.
PARALLEL_EXECUTION_MESSAGE_SIZE: specifies the size of messages for parallel execution. Larger values require a larger shared pool. Larger values result in better performance at the cost of higher memory use.
PARTITION_VIEW_ENABLED: specifies whether the optimizer uses partition views. If you set this parameter to true, the optimizer prunes (or skips) unnecessary table accesses in a partition view and alters the way it computes statistics on a partition view from statistics on underlying tables.
STAR_TRANSFORMATION_ENABLED: determines whether a cost-based query transformation will be applied to star queries
QUERY_REWRITE_ENABLED: allow you to enable or disable query rewriting globally for the database. To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization.
QUERY_REWRITE_INTEGRITY: determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on un-enforced relationships.
PARALLEL clause for the target tables in the system. Oracle then tunes all subsequent parallel operations automatically.
PARALLEL_ADAPTIVE_MULTI_USER: Enables an adaptive algorithm designed to improve performance in multi-user environments that use parallel execution. The algorithm automatically reduces the requested degree of parallelism based on the system load at query startup time. The effective degree of parallelism is based on the default degree of parallelism, or the degree from the table or hints, divided by a reduction factor.
PARALLEL_EXECUTION_MESSAGE_SIZE: specifies the size of messages for parallel execution. Larger values require a larger shared pool. Larger values result in better performance at the cost of higher memory use.
PARTITION_VIEW_ENABLED: specifies whether the optimizer uses partition views. If you set this parameter to true, the optimizer prunes (or skips) unnecessary table accesses in a partition view and alters the way it computes statistics on a partition view from statistics on underlying tables.
STAR_TRANSFORMATION_ENABLED: determines whether a cost-based query transformation will be applied to star queries
QUERY_REWRITE_ENABLED: allow you to enable or disable query rewriting globally for the database. To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization.
QUERY_REWRITE_INTEGRITY: determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on un-enforced relationships.
SOX compliance used with respect to Oracle database?
Sarbanes-Oxley (SOX) -> SOX compliance is defined by the corporation referencing a set of internal controls frameworks. SOX compliance should be done in the context for an enterprise-wide SOX initiative.
Security (No Generic ID's, Review of access privileges, Enterprise wide password policy)
Access -> Security -> User Mgmt (Uniqe name for each a/c, password mgmt)
Access -> Security -> Segregation of duties (DEv no access to prod etc)
Access -> Auditing -> Appl/DB/OS auditing
Change Management (everything should be documented and go by ticket or change request systems )
Changes -> Change Management -> Object Migration/Schema Changes
Changes -> Change Management -> Appl configuration/Schema changes
Changes -> Change Management -> Appl Configurigation/DB configurigation
Changes -> Change Management -> Appl/DB/OS Patches
Operations -> Monitoring & Troubleshooting -> Appl/DB/OS
Operations -> Availablity -> Appl/DB/OS
Security (No Generic ID's, Review of access privileges, Enterprise wide password policy)
Access -> Security -> User Mgmt (Uniqe name for each a/c, password mgmt)
Access -> Security -> Segregation of duties (DEv no access to prod etc)
Access -> Auditing -> Appl/DB/OS auditing
Change Management (everything should be documented and go by ticket or change request systems )
Changes -> Change Management -> Object Migration/Schema Changes
Changes -> Change Management -> Appl configuration/Schema changes
Changes -> Change Management -> Appl Configurigation/DB configurigation
Changes -> Change Management -> Appl/DB/OS Patches
Operations -> Monitoring & Troubleshooting -> Appl/DB/OS
Operations -> Availablity -> Appl/DB/OS
Factors to be checked if any query is running slow today?
There is various aspects/ scenario involve in this like issue with N/W, OS Server, DB Server, Application issues, Query issue
N/W -> Check the N/W speed by netstat UNIX Command. Is there any packet loss? This can also be detected by a WAIT event at DB level.
O/S Level: At OS level check the CPU Usage, IOSTAT, Memory usage, how is the service time of the disk onto which issue is coming, Is there any IO bottlenecks, Message file, overall stats of the server like process, how process is proceeding truss, fuser –cu.
DB Server: Check the Alert log for any possible errors, Check the Resources taken by the query and overall resource consumption at the Server level, Is there any lock on the object, any wait events, Service time of the disk containing data, any Hot Spots?
Application Level: How is the query performing yesterday, what is the expected time of completion, Run explain plan, check in the statspack for the overall stats.
Query/Object Level Issues: Any index invalid, any new index created, any new constraint added, any abnormal loading of data in the table, If the stats of the table is current, Check for the parallelism, How query is behaving in test environment (Sometime we have seen that on the same ver of Oracle you may get different versions plans).
N/W -> Check the N/W speed by netstat UNIX Command. Is there any packet loss? This can also be detected by a WAIT event at DB level.
O/S Level: At OS level check the CPU Usage, IOSTAT, Memory usage, how is the service time of the disk onto which issue is coming, Is there any IO bottlenecks, Message file, overall stats of the server like process, how process is proceeding truss, fuser –cu.
DB Server: Check the Alert log for any possible errors, Check the Resources taken by the query and overall resource consumption at the Server level, Is there any lock on the object, any wait events, Service time of the disk containing data, any Hot Spots?
Application Level: How is the query performing yesterday, what is the expected time of completion, Run explain plan, check in the statspack for the overall stats.
Query/Object Level Issues: Any index invalid, any new index created, any new constraint added, any abnormal loading of data in the table, If the stats of the table is current, Check for the parallelism, How query is behaving in test environment (Sometime we have seen that on the same ver of Oracle you may get different versions plans).
Suggestions for ETL team for the bulk loading in a Warehouse
1) Turn of Logging, Direct load insert (Skip the conventional path i.e skip evaluation buffer).
2) Pre-Sort the files – Sorting the file in the order of primary key index speeds up the indexing processing.
3) Drop & Re-Index if possible
4) Some times at ETL layer the partition/Parallelism also helps it break the files into data same as partitions in Oracle
5) If using RBS can assign a BIG RBS during the processing.
6) If partition table see if partition exchange is possible if not then DO Parallel inserts in different partitions.
7) Pre-Allocate extents to the staging table
2) Pre-Sort the files – Sorting the file in the order of primary key index speeds up the indexing processing.
3) Drop & Re-Index if possible
4) Some times at ETL layer the partition/Parallelism also helps it break the files into data same as partitions in Oracle
5) If using RBS can assign a BIG RBS during the processing.
6) If partition table see if partition exchange is possible if not then DO Parallel inserts in different partitions.
7) Pre-Allocate extents to the staging table
Manual steps involve in upgradation of Oracle 9i to 10g
1. Install 10g Software in a new Oracle Home.
2. Run the pre-Upgrade utility ($ORACLE_HOME/rdbms/admin/utlu1011.sql)
3. If any recommendations from pre-Upgrade utility make the changes as suggested (Like at Kernal Level, Memory level or OS level)
4. Shutdown the database to be upgraded.
5. Create / Setup new environment pointing to new 10g Home for the upgrded Home.
6. Switch the new Oracle database 10g home and startup the 10g database
7. (SQL> Start upgrade)
8. Create SYSAUX tablespace
9. Run the upgrade scripts (u0902000.sql)
10. Run the post upgrade status script (utlu101s.sql TEXT)
11. Re-compile any PL/SQL, JAVA invalid code.
SHMMAX 4294967295 Maximum size of a single shared memory segment
SHMMIN 1 Minimum size of a single shared memory segment
SHMMNI 100 Maximum number of shared memory segments in entire system
SHMSEG 10 Maximum number of shared memory segments one process can attach
SEMMNS 2000 Maximum number of semaphores in entire system
SEMMSL 1000 Maximum number of semaphores per set
SEMMNI 100 Maximum number of semaphore sets in entire system
SEMOPM 100 Maximum number of operations per semop call
SEMVMX 32767 Maximum value of a semaphore
NOTE: Upgrade Path
7.3.3 (or lower) -> 7.3.4 -> 8.1.7.4 -> 10.2
7.3.4 -> 8.1.7.4 -> 10.2
8.0.n -> 8.1.7.4 -> 10.2
8.1.n -> 8.1.7.4 -> 10.2
Direct Upgrade possible ->8.1.7.4, 9.0.1.4, 9.2.0.4, 10.1.0.2
Direct Upgrade not supported from 8.1.6, 8.1.5, 8.0.5, 8.0.4,8.0.3, 7.3.4
2. Run the pre-Upgrade utility ($ORACLE_HOME/rdbms/admin/utlu1011.sql)
3. If any recommendations from pre-Upgrade utility make the changes as suggested (Like at Kernal Level, Memory level or OS level)
4. Shutdown the database to be upgraded.
5. Create / Setup new environment pointing to new 10g Home for the upgrded Home.
6. Switch the new Oracle database 10g home and startup the 10g database
7. (SQL> Start upgrade)
8. Create SYSAUX tablespace
9. Run the upgrade scripts (u0902000.sql)
10. Run the post upgrade status script (utlu101s.sql TEXT)
11. Re-compile any PL/SQL, JAVA invalid code.
SHMMAX 4294967295 Maximum size of a single shared memory segment
SHMMIN 1 Minimum size of a single shared memory segment
SHMMNI 100 Maximum number of shared memory segments in entire system
SHMSEG 10 Maximum number of shared memory segments one process can attach
SEMMNS 2000 Maximum number of semaphores in entire system
SEMMSL 1000 Maximum number of semaphores per set
SEMMNI 100 Maximum number of semaphore sets in entire system
SEMOPM 100 Maximum number of operations per semop call
SEMVMX 32767 Maximum value of a semaphore
NOTE: Upgrade Path
7.3.3 (or lower) -> 7.3.4 -> 8.1.7.4 -> 10.2
7.3.4 -> 8.1.7.4 -> 10.2
8.0.n -> 8.1.7.4 -> 10.2
8.1.n -> 8.1.7.4 -> 10.2
Direct Upgrade possible ->8.1.7.4, 9.0.1.4, 9.2.0.4, 10.1.0.2
Direct Upgrade not supported from 8.1.6, 8.1.5, 8.0.5, 8.0.4,8.0.3, 7.3.4
Subscribe to:
Comments (Atom)