Wednesday, October 26, 2011
Tuesday, October 25, 2011
RAC Speedup
If you want to make the query response speed up, then alter table with parallel option
alter table tablename parallel;
alter table tablename parallel;
VIP Concept
1- The system shown here is 2 node RAC
Wednesday, October 19, 2011
Create and configure a listener
The listener forwards client requests to supported services. These services can be configured statically in the listener.ora file or they can be dynamically registered with the listener. This dynamic registration feature is called service registration. The registration is performed by the PMON process.
Tuesday, October 18, 2011
Create and manage multiple network configuration files
LISTENER.ORA
If you have a server that is running multiple versions of Oracle software (multiple Oracle homes, each home has one or more database instances). You can use multiple listener.ora files.
Transparent Data Encryption (TDE) & LogMiner
1- Change in sqlnet.ora
######################SQLNET.ORA###############################
NAMES.DIRECTORY_PATH=(TNSNAMES, ONAMES, HOSTNAME)
SQLNET.EXPIRE_TIME= 10
sqlnet.authentication_services= (NTS)
Monday, October 17, 2011
Create and Manage a tablespace that uses NFS mounted file system file
Direct NFS
Direct NFS is a new feature introduced with Oracle 11g and is an optimized NFS (Network File System) client that provides faster and more scalable access to NFS storage located on NAS storage devices (accessible over TCP/IP). Direct NFS is built directly into the database kernel - just like ASM which is mainly used when using DAS or SAN storage.
Direct NFS is a new feature introduced with Oracle 11g and is an optimized NFS (Network File System) client that provides faster and more scalable access to NFS storage located on NAS storage devices (accessible over TCP/IP). Direct NFS is built directly into the database kernel - just like ASM which is mainly used when using DAS or SAN storage.
Create and manage bigfile tablespaces
Bigfile Tablespaces
- a tablespace with a single, but very large (up to 4G blocks) datafile.
-A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile.A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile.
- a tablespace with a single, but very large (up to 4G blocks) datafile.
-A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile.A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile.
Sunday, October 16, 2011
Configure the database environment to support optimal data access performance
The Symptoms and the Problems
A common pitfall in performance tuning is to mistake the symptoms of a problem for the actual problem itself. It is important to recognize that many performance statistics indicate the symptoms, and that identifying the symptom is not sufficient data to implement a remedy. For example:
A common pitfall in performance tuning is to mistake the symptoms of a problem for the actual problem itself. It is important to recognize that many performance statistics indicate the symptoms, and that identifying the symptom is not sufficient data to implement a remedy. For example:
Stripe data files across multiple physical devices and locations
Goal of striping data is simple:-
To avoid I/O bottlenecks during parallel processing, all tablespaces accessed by parallel operations should be striped. Many current OS's support disk striping hence DBA's need not stripe data across the disk.
To avoid I/O bottlenecks during parallel processing, all tablespaces accessed by parallel operations should be striped. Many current OS's support disk striping hence DBA's need not stripe data across the disk.
Saturday, October 15, 2011
Create and manage temporary, permanent, and undo tablespaces
Using Multiple Tablespaces
Using multiple tablespaces allows you more flexibility in performing database operations.Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently.
Using multiple tablespaces allows you more flexibility in performing database operations.Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently.
Determine and set sizing parameters for database structures
You can determine/set parameter sizes using Enterprise Manager's Server Tab-> Storage section. This is first section of configuration of database structure, and second is parameters form parameter file.
Create the database
Note: Practice performed on 11g Rel 1 (APPS DB)
Considerations Before Creating the Database
Database Planning Tasks
- Plan the database tables and indexes and estimate the amount of space they will require.
- Plan the layout of the underlying operating system files your database will comprise. To greatly simplify this planning task, consider using Oracle
Managed Files and Automatic Storage Management to create and manage the operating system files that comprise your database storage.
Tuesday, October 11, 2011
Error: Procedure entry point longjmp could not be located in dynamic link library orauts.dll
If you get "Procedure entry point longjmp could not be located in dynamic link library orauts.dll" while connecting the SQL Plus, your environment variables are not set properly.
Tuesday, October 04, 2011
Spell the numbers - Examples
Using JSP format
SELECT TO_CHAR(TO_DATE(123.50,'J'),'JSP') to_words FROM dual;
SELECT TO_CHAR (TO_DATE (TRUNC (&num), 'J'), 'JSP') || ' Point ' || TO_CHAR (TO_DATE (TO_NUMBER (SUBSTR (&num, INSTR (&num, '.') + 1)),'J'),'JSP') FROM DUAL;
Collect Full DML Statements for specific user without AUDIT
Use a logon trigger for the specific user to enable SQL tracing : it generates a trace file in the USER_DUMP_DEST location.The trace file contains all the SQL statements executed by the user after logon.
Create the following trigger in SYS schema:
create or replace trigger SCOTTLOG
after logon
on scott.schema
begin
-- optionally add a tag to the trace file name to make the files discernable:
execute immediate ' ALTER SESSION SET TRACEFILE_IDENTIFIER =''SCOTT''';
-- enable sql_trace for this session:
execute immediate 'ALTER SESSION SET SQL_TRACE TRUE';
end;
/
Note that all the SQLs of SCOTT user after logon will be traced and if the required collection is sufficient, disable the trigger to stop the collection and generation of trace files.
Note
i) Overhead involved is trace file generation in user_dump_dest.
ii) Grant the CREATE SESSION system privilege to the user account rather than granting the CONNECT role. This is necessary because in the versions older than 10gR2 the CONNECT role includes the ALTER SESSION system privilege and the user can disable sql tracing after logon. To avoid this, do not grant the CONNECT and RESOURCE roles , but grant the necessary privileges explicitly.
Ref: 309798.1
Create the following trigger in SYS schema:
create or replace trigger SCOTTLOG
after logon
on scott.schema
begin
-- optionally add a tag to the trace file name to make the files discernable:
execute immediate ' ALTER SESSION SET TRACEFILE_IDENTIFIER =''SCOTT''';
-- enable sql_trace for this session:
execute immediate 'ALTER SESSION SET SQL_TRACE TRUE';
end;
/
Note that all the SQLs of SCOTT user after logon will be traced and if the required collection is sufficient, disable the trigger to stop the collection and generation of trace files.
SQL> alter trigger scottlog disable;
i) Overhead involved is trace file generation in user_dump_dest.
ii) Grant the CREATE SESSION system privilege to the user account rather than granting the CONNECT role. This is necessary because in the versions older than 10gR2 the CONNECT role includes the ALTER SESSION system privilege and the user can disable sql tracing after logon. To avoid this, do not grant the CONNECT and RESOURCE roles , but grant the necessary privileges explicitly.
Ref: 309798.1
How to check if the IO of the Database is Slow
We will outline some of the thresholds whereby RDBMS Support may consider IO to be slow and thus a potential reason for a performance problem. If the underlying cause for slow performance is found to be a result of slow IO at the OS level, then the appropriate vendor responsible for the IO subsystem (hardware and software) should be engaged to diagnose and correct the situation
How To Configure Anti-Virus On Windows Server Running Oracle Database
When an Anti-virus performs a scan on a file it holds a lock on it. This lock interrupts the normal functioning of the database. To prevent any disaster situation such as database crash/hang, we recommend the following files to be excluded from online anti-virus scanning.
DCD & TCP Keep Alive (Windows)
KNOWN PROBLEMS OR LIMITATIONS for DCD
1- Of the few reported problems, perhaps the most significant is DCD's poor performance on Windows NT. Dead connections are cleaned up only when the server is rebooted and the database is restarted. Exactly how well DCD works on NT depends on the client's proto implementation.
1- Of the few reported problems, perhaps the most significant is DCD's poor performance on Windows NT. Dead connections are cleaned up only when the server is rebooted and the database is restarted. Exactly how well DCD works on NT depends on the client's proto implementation.
All Users with Client side Info
Create Or Replace Force View Sys.Av_users_all (Logon_time,
Sid,
Serial#,
Sid,
Serial#,
Monday, October 03, 2011
Calling External Procedure (DLL) from PL/SQL
If Oracle has been configured for the external procedures , you can call any DLL from your PL/SQL. How to configure for EXTPROC please click on the link below
Configuring SQL*Net for External Procedures
External procedures are functions written in a third-generation language (3GL) such as C, and callable from within PL/SQL or SQL as if they were a PL/SQL procedure or function. External procedures enable you to take advantage of the strengths and capabilities of a 3GL programming language in a PL/SQL environment.
Saturday, October 01, 2011
Restricting user to change the password
If for some reason you want to restrict the user to change the password ,
Use the event trigger AFTER ALTER with the attribute function ora_des_encrypted_password
specific for ALTER USER events:
CREATE or REPLACE TRIGGER pass_change AFTER ALTER on database BEGIN IF ora_sysevent='ALTER' and ora_dict_obj_type = 'USER' and ora_des_encrypted_password is not null THEN RAISE_APPLICATION_ERROR(-20003, 'You are not allowed to alter password user.'); END IF; END; /
Note:
In the trigger, instead of raising the error, you may want to insert a row into a custom audit table.
In the trigger, instead of raising the error, you may want to insert a row into a custom audit table.
How To Audit Application Username Using Trigger
With default auditing Oracle stores Oracle username but not the application username. In order to store application username you need to set CLIENT IDENTIFIER for the application session which is connecting to the database.
Wednesday, September 28, 2011
Copying Files Over Remote Desktop
Start up a remote desktop dialog and set the local resources as desired using options button. After this you will be able to use the clipboard of local system on remote machine.
How To Use PROFILES To Limit User Resources
You have a group of users that do not always disconnect from the database when they are done, or, they leave their connection idle for long periods of time. You want to eliminate these connections to make more connections available to other users.
How to Audit Connect AS SYSDBA Using Oracle Server
Windows Systems
---------------
On Windows Systems, you can monitor audited connects to Oracle as an administrative
user (former connect INTERNAL as of 8.1.7 connections ' as sysdba ') in the
event viewer.
Tuesday, September 27, 2011
Application Tuning using Explain
The EXPLAIN facility can be used quickly and easily to
determine how the data is accessed (known as the access path) for
any given SQL statement, namely queries. The ability to modify
this access path can yield an incredible performance benefit.
SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES
Description
This script checks the current users Foreign Keys to make sure of the
following:
1) All the FK columns have indexes to prevent a possible locking
This script checks the current users Foreign Keys to make sure of the
following:
1) All the FK columns have indexes to prevent a possible locking
How to Collect Diagnostics for Database Hanging Issues
If you are encountering a database hanging situation, you need to take system
state dumps/hanganalyze so that Oracle Support can begin to diagnose the cause of the problem.
Whenever you take such dumps for a hang it is important to take at least 3 of
them a few minutes apart, on all instances of your database.
How To Connect Using A Sqlplus Preliminary Connection
There is an another way to connect to a database through Sqlplus when all other connection methods are hanging. Using a Sqlplus preliminary connection you will be able to connect to the database since no session is actually created,
Using DBMS_MONITOR
Using DBMS_MONITOR, Tracing has been enabled for diagnose and workload management based on, a specified client identifier or a hierarchical combination of service name, module name and action name. Also we have the facility to trace on session level.
Using TKPROF
The TKPROF facility accepts as input an SQL trace file and produces a formatted output file. Note that TKPROF can be run on individual or appended trace files to produce the formatted output file.
How to Enable SQL_TRACE for Another Session or in MTS Using Oradebug
The ORADEBUG utility can enable/disable setting the SQL tracing for another
user's session or an MTS session. To enable tracing for another session, the
Oracle process identifier (PID) or the Operating System processes identifier
(SPID) must be identified from v$process. This is an effective way of capturing
a SQL trace from a process which is already running. The output can be used to
analyze SQL related performance issues.
How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
Event 10046 is the standard method of gathering extended sql_trace information for Oracle sessions.
This is a special EVENT code. It can be used to signal Oracle to perform SQL_TRACE type
actions. The 10046 trace is the equivalent of setting SQL_TRACE=TRUE.The advantage of using
the event is that extra details may be output to the trace file depending on the level
specified with the event.
Monday, September 26, 2011
Investigating a Database Performance Issue
To investigate a slow performance problem, begin by deciding what diagnostics will be gathered. To do this, consider the following questions and take the appropriate action:-
Is the performance problem constant or does it occur at certain times of the day ?
Is the performance problem constant or does it occur at certain times of the day ?
Optimizing Joins
The first thing to remember about optimising the performance of joins is that the performance can decrease considerably as a 3rd or 4th or 5th table is added to the query. The more tables that are added, the more important it is to ensure that the query is properly tuned and tested.
Oracle database Performance Tuning
The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance. ne should do performance tuning for the following reasons:
- The speed of computing might be wasting valuable human time (users waiting for response);
Sunday, September 18, 2011
Desktop Heap Overview
Desktop heap is probably not something that you spend a lot of time thinking about, which is a good thing. However, from time to time you may run into an issue that is caused by desktop heap exhaustion, and then it helps to know about this resource. Let me state up front that things have changed significantly in Vista around kernel address space, and much of what I’m talking about today does not apply to Vista.
Tuesday, September 13, 2011
Schedule Killing sessions dynamically
You can kill session dynamically as below in a single instance but for the RAC you may face issues , so approach below can be used.
Monday, August 08, 2011
WUC-14 Object Cache Error: Specified object handle {0} not found in the cache.
WUC-14 was occurring when developer was trying to write a file with more than 10000 lines as below.
Monday, June 20, 2011
500 Internal Server Error
Client was getting following error while accessing the form application.
500 Internal Server Error
java.io.IOException: Too many open files at java.io.FileInputStream.open(Native Method) at java.io.FileInputStream.(FileInputStream.java:106) at java.io.FileInputStream.(FileInputStream.java:66) at java.io.FileReader.(FileReader.java:41) at oracle.forms.servlet.ConfigFileParser.(Unknown Source) at oracle.forms.servlet.FormsServlet.doRequest(Unknown Source) at
java.io.IOException: Too many open files at java.io.FileInputStream.open(Native Method) at java.io.FileInputStream.
Saturday, June 11, 2011
Finding FK for a Parent table
select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints
where constraint_type='R'
and r_constraint_name in (select constraint_name from all_constraints
where constraint_type in ('P','U') and table_name='T_LOOKUP');
Monday, June 06, 2011
ORA-19815: WARNING: db_recovery_file_dest_size
We were getting warning in Alertlog for a database although we were not using FRA.
ORA-19815: WARNING: db_recovery_file_dest_size of 6442450944 bytes is 99.78% used, and has 14326272 remaining bytes available.
Saturday, May 28, 2011
How to Delete a Windows Service in Windows
If you are a fan of tweaking your system and disabling services, you might find that over time your Windows Services list becomes huge and unwieldy with a large number of services in the list that will never be enabled.
Wednesday, April 27, 2011
TNS-12531: TNS:cannot allocate memory
Symptoms
On windows platform, new client connections to the database through listener fails, "TNS-12531: TNS:cannot allocate memory" error found in listener.log.
On windows platform, new client connections to the database through listener fails, "TNS-12531: TNS:cannot allocate memory" error found in listener.log.
Tuesday, April 26, 2011
How to Install Oracle 10.2.0.5 on MS Windows 7 / Windows 2008R2
Goal
Instructions to Install Oracle 10.2.0.5 on MS Windows 7 and MS Windows 2008R2.These instructions can be used for either the Oracle Database software, or the Oracle Client software.
Sunday, April 24, 2011
TG4MSQL and slow response
If you see the "HS Message to agent" wait event for a long time while querying the SQL Server, first of all check that tracing is enabled and if it is enabled , disable it and rerun the statement again as tracing may cause the poor response.
I met this situation with one of the environment today where a query was taking the 4 minutes and SQL Server team confirmed that query does not take the 2 seconds to execute on the SQL Server itself. I investigated and found that tracing was On with debug option , just I remarked the related line in the TG init file (eg; E:\oracle\product\10.2.0\tg_1\tg4msql\admin\inittg4msql.ora) as below
#HS_FDS_TRACE_LEVEL=debug
and query returned the results in few seconds rather than 4 minutes.
I met this situation with one of the environment today where a query was taking the 4 minutes and SQL Server team confirmed that query does not take the 2 seconds to execute on the SQL Server itself. I investigated and found that tracing was On with debug option , just I remarked the related line in the TG init file (eg; E:\oracle\product\10.2.0\tg_1\tg4msql\admin\inittg4msql.ora) as below
#HS_FDS_TRACE_LEVEL=debug
and query returned the results in few seconds rather than 4 minutes.
Monday, April 18, 2011
Notify Report success/failure by mail
You can change the report configuration to notify the report failure or success by mail as below. Set Notification tag and pluginparam in report server's configuration file . Failnote.txt and succnote.txt files are found under
MT_OH\reports\templates
MT_OH\reports\templates
Sunday, March 27, 2011
About Netbackup
NetBackup integrates the database backup and recovery capabilities of the Oracle Recovery Manager (RMAN) with the backup and recovery management capabilities of NetBackup. NetBackup for Oracle also lets you export and import Oracle data in XML format for long-term archival and retrieval.
Wednesday, March 16, 2011
Wait Event Enhancements in Oracle 10g
Oracle Database 10g Enhanced wait model [ID 245055.1] | |||||
Modified 26-JUN-2007 Type BULLETIN Status PUBLISHED |
PURPOSE
This bulletin outlines Enhanced wait model introduced in Oracle Database 10G.
SCOPE & APPLICATION
The Oracle Database 10g has many improvements for the wait reporting mechanism. These include:
- Blocking Session
- Classification of Events
- Break out important latch events
- Histogram
- Session and System Level Event Class Stats
- Combine V$SESSION_WAIT into V$SESSION
- Added V$SESSION_WAIT_HISTORY
- NEW "WAIT_CLASS" columns in V$EVENT_NAME
1. Blocking Session
Two new columns blocking_session and blocking_session_status have been added to V$SESSION view.
Column "blocking_session" would contain the session id of the resource holder for which a session is waiting for. Else it would contain Null.
Column "blocking_session_status" would contain the status of the value of the blocking_session column.
The Value of the columns could be:
The Value of the columns could be:
- VALID:- A valid Session ID is present in the Blocking Session column
- NO HOLDER:- which implies that there are no holders of this resource.
- UNKNOWN:- which implies that we could not figure out the holder.
- UNIMPLEMENTED:- which implies that the callback for the event has not been implemented
- GLOBAL:- which implies that the holder is a session on another instance.
Following query would be useful to find the blocking session.
SELECT sid, blocking_session, username, blocking_session_status
FROM v$session
ORDER BY blocking_session;
2. Classification of Events
All wait events have been classified into categories like contention wait/OS service wait/DB Service ,Wait/idle wait, etc.. This will enable the user to immediately find out whether the system is performing poorly due to excessive contention or a background not performing well or whether the Operating System does not have enough resources.
A category has many wait events assigned to it, so the below example shown displays the total waits reported for each class. The wait event class gives an overall view of a particular area. For example, the I/O wait category contains all wait events associated with disk I/O. As a rule, the wait event categories with the highest wait times and counts become the focus of tuning effort.
Example :
col wait_class format a30
SELECT e.wait_class#, e.wait_class,sum(s.total_waits), sum(s.time_waited)
FROM v$event_name e, v$system_event s
WHERE e.name = s.event
GROUP BY e.wait_class#,e.wait_class;
Classify the wait events into:
- Idle Waits: Whenever an Oracle process has no work to do this is an idle wait. For most processes this is because they are waiting on the user to provide a new SQL statement to execute.
- Application: These are waits caused by the way the application is designed. These include row lock waits, and table or other locks that are requested by the application either explicitly or implicitly (possibly due to DDL).
- Configuration: These are waits which occur in a badly configured system and weill be reduced dramatically as a result of proper tuning.
- Administrative: These are waits imposed by a privileged users by some action.
- Concurrency: These are waits that can not be tuned and will occur on a system with High Concurrency.
- Commit: This class only has log file sync. It deserves a special class because it is a necessary event and will be high and is supposed to be high on a system doing queries.
- Network: All waits due to network messaging delays belong here. They are supposed to point out network congestion or latency. They should not include think or processing time, only the time spent in the networking code and hardware.
- User I/O Waits: All waits for Disk I/O done by User queries or even SMON, MMON
- System I/O Waits: All waits for Disk I/O done by backgrnd processes like LGWR, DBWR, ARCH, RFS. But not SMON and MMON
- Scheduler: These are waits due to the resource manager
- Cluster: waits which will occur only in RAC mode.
- Other: All the wait events, which do not fit into one of the above classes clearly, or are not important to classify. By not important I mean those that wait for an insignificant amount of time or really do not fit into any one class.
3. Break out important latch events
Before Oracle Database 10g, it is not possible to see the latch waits on a session basis. When a session is waiting it waits on the latch free event, which does not tell much. One needs to see p1, which gives address, and find out which latch is it. Also, One does not know which are the sessions and which are getting the latch and resulting in contention.
Thus, the high contention in the database are breaking out separate wait events for certain latches which have known to be points of past. Some latches events will also have different wait classes. All the event names will have prefix of “latch: “ so that users can get all latch events by writing like ‘latch: %’.
Example:
SELECT event,p1,p2,p3
FROM v$session_wait
WHERE event like 'latch%';
4. Histogram
The following views have been introduced from Oracle Database 10G which would be useful to generate histogram for system events and I/Os at file level.
To collect histogram data in these views database parameter timed_statistics should be set to TRUE.
4.a V$EVENT_HISTOGRAM
The v$system_event view displays the number of waits, the maximum wait time, and the total time waited per wait event. This view that show wait event information do not offer information on the time of each wait event occurrence. A single very long wait can skew the information offered by this view, these waits might not be indicative of the system as a whole.
The v$event_histogram view remedies this. Using this view, you create a histogram showing the frequency of wait events for a range of durations. To provide you with more in depth data the v$event_histogram view shows the number of waits for a wait event over a range of time values. You use the v$event_histogram view to determine if the bottleneck is a regular or a unique problem.
This view has the following columns:
- event# :- The number assigned to the wait event. This corresponds to the event# column in the v$event_name table.
- Event:- The name of the wait event. This corresponds to the name column in the v$event_name table.
- wait_time_milli:- The time this bucket represents in ms. If DURATION= k then it represents waits of duration <=k not included in any smaller bucket. The values of DURATION will be 1, 2, 4, 8, …, 2^21, 2^22, UB4MAXVAL
- wait_count:- The number of waits of duration belonging to this bucket of the histogram
4.b V$FILE_HISTOGRAM
The v$filestat view displays statistics per data file. This information can be skewed due to a couple of very large I/O waits, thus these waits might not be indicative of the system as a whole.
V$FILE_HISTOGRAM displays a histogram of all single block reads on a per-file basis. The histogram has buckets of time intervals from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 2^21 ms, < 2^22 ms, >= 2^22 ms. The histogram can be used to determine if the bottleneck is a regular or a unique problem.The frequency of each wait event versus the length of the wait events would be seen using the information in the v$file_histogram view.
The following columns make up the v$file_histogram view:
- FILE#:- File number
- SINGLEBLKRDTIM_MILLI :-The time this bucket represents in ms. If DURATION= k then it represents waits of duration <=k not included in any smaller bucket. The values of DURATION will be 1, 2, 4, 8, …, 2^21, 2^22, UB4MAXVAL
- SINGLEBLKRDS :- Number of waits of the duration belonging to the bucket of the histogram
4.c V$TEMP_HISTOGRAM
V$TEMP_HISTOGRAM for temporary files. V$TEMP_HISTOGRAM displays a histogram of all single block reads on a per-tempfile basis. The histogram has buckets of time intervals from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 2^21 ms, < 2^22 ms, >= 2^22 ms.
- FILE# :- File number
- SINGLEBLKRDTIM_MILLI :- The time this bucket represents in ms. If DURATION= k then it represents waits of duration <=k not included in any smaller bucket. The values of DURATION will be 1, 2, 4, 8, …, 2^21, 2^22, UB4MAXVAL
- SINGLEBLKRDS :- Number of waits of the duration belonging to the bucket of the histogram.
5. Session and System Level Event Class Stats
The following views have been introduced from Oracle Database 10G, which display total wait wide instance and session.
5.a v$system_wait_class
The v$system_wait_class view displays the instance wide totals of the time waited and the number of times wait events of this class have occurred. Both of these totals are running totals started when the instance started.
The view consists of the following columns:
- wait_class# :- The wait class number
- wait_class :- The name given to the wait class when registered
- time_waited :- The amount of time spent in this wait by all sessions in the instance
- total_waits :- The number of times waits of this class occurred
From the output of the below query you can determine which classes of wait event you should investigate further.
SELECT wait_class#, wait_class, time_waited, total_waits
FROM v$system_wait_class
ORDR BY time_waited;
5.b v$session_wait_class
The v$session_wait_class view shows the time spent in various classes of wait event operations on a per session basis. The view includes the following columns:
- sid :- The session id (same as in v$sesstat)
- serial# :- The serial number
- wait_class# :- The wait class number
- wait_class :- The name given to the wait class when registered.
- time_waited :- The amount of time spent in this wait class by this session
- total_waits :- The number of times waits of this class occurred for this session
After determining that a wait event class is affecting performance (from the v$system_wait_class view) use v$session to determine the sessions that are experiencing the specific wait event class.
Use the below SQL statement to determine the sessions that have wait events associated with this class.
SELECT sid,serial#,time_waited,total_waits
FROM v$session_wait_class
WHERE wait_class# =
ORDER BY time_waited;
Note: Do not expect the wait_time and wait_count columns to add up to the total in v$system_wait_class since it is likely that some sessions would have already ended and therefore would not appear in the v$session_wait_class view.
6. Combine V$SESSION_WAIT into V$SESSION
In the previous releases to determine the sessions experiencing waits, we need to join the v$session_wait view with the v$session view.
In the Oracle Database 10g all wait event columns from v$session_wait have been added to v$session thus increasing performance by eliminating the overhead of joins.
7. Added V$SESSION_WAIT_HISTORY
In the previous releases, we cannot see the last few waits of a session. From Oracle Database 10g a new view V$Session_wait_history will allow us to see the last few wait events a session waited on.
The last 10 wait events that a session experienced can be displayed using the v$session_wait_history view. The session has to be currently active. Once the session ends this information is not available.
We can use the seq# column to sort the wait events into the order in which the wait events occurred for the session.
SELECT sid,seq#,event
FROM v$session_wait_history
WHERE sid = ;
8. NEW "WAIT_CLASS" columns in V$EVENT_NAME
The following columns have been added to the v$event_name view:
- wait_class# :- The wait class number
- wait_class :- The name of the class of wait events
Following query can check waits caused by I/O:
SELECT name, wait_class#, wait_class
FROM v$event_name
WHERE wait_class# in (10,11);
Related Products
|
Tuesday, March 15, 2011
DB Segments Used (Type wise)
Create Or Replace Force View Sys.Av_db_used_space_seg_type ("Total Used",
"Data part",
"Index part",
"LOB part",
"RBS part",
"TEMP part"
)
As
Select Sum (Bytes) / 1024 / 1024 "Total Used",
Sum (Decode (Substr (Segment_type, 1, 5),
'TABLE', Bytes / 1024 / 1024,
0
)
) "Data part",
Sum (Decode (Substr (Segment_type, 1, 5),
'INDEX', Bytes / 1024 / 1024,
0
)
) "Index part",
Sum (Decode (Substr (Segment_type, 1, 3),
'LOB', Bytes / 1024 / 1024,
0
)
) "LOB part",
Sum (Decode (Segment_type, 'ROLLBACK', Bytes / 1024 / 1024, 0)
) "RBS part",
Sum (Decode (Segment_type, 'TEMPORARY', Bytes / 1024 / 1024, 0)
) "TEMP part"
From Sys.Dba_segments;
"Data part",
"Index part",
"LOB part",
"RBS part",
"TEMP part"
)
As
Select Sum (Bytes) / 1024 / 1024 "Total Used",
Sum (Decode (Substr (Segment_type, 1, 5),
'TABLE', Bytes / 1024 / 1024,
0
)
) "Data part",
Sum (Decode (Substr (Segment_type, 1, 5),
'INDEX', Bytes / 1024 / 1024,
0
)
) "Index part",
Sum (Decode (Substr (Segment_type, 1, 3),
'LOB', Bytes / 1024 / 1024,
0
)
) "LOB part",
Sum (Decode (Segment_type, 'ROLLBACK', Bytes / 1024 / 1024, 0)
) "RBS part",
Sum (Decode (Segment_type, 'TEMPORARY', Bytes / 1024 / 1024, 0)
) "TEMP part"
From Sys.Dba_segments;
DB History
Create Or Replace Force View Sys.Av_db_update_history (Action_time,
Action,
Namespace,
Version,
Id,
Comments
)
As
Select "ACTION_TIME", "ACTION", "NAMESPACE", "VERSION", "ID", "COMMENTS"
From Registry$history;
Action,
Namespace,
Version,
Id,
Comments
)
As
Select "ACTION_TIME", "ACTION", "NAMESPACE", "VERSION", "ID", "COMMENTS"
From Registry$history;
How much DB undo generated
Create Or Replace Force View Sys.Av_db_undo_generated (Rundate,
Day,
Logswitch,
"REDO PER DAY (MB)"
)
As
Select Trunc (Completion_time) Rundate,
To_char (Trunc (Completion_time), 'DAY') Day, Count (*) Logswitch,
Round ((Sum (Blocks * Block_size) / 1024 / 1024)
) "REDO PER DAY (MB)"
From V$archived_log
Group By Trunc (Completion_time)
Order By 1 Desc;
Day,
Logswitch,
"REDO PER DAY (MB)"
)
As
Select Trunc (Completion_time) Rundate,
To_char (Trunc (Completion_time), 'DAY') Day, Count (*) Logswitch,
Round ((Sum (Blocks * Block_size) / 1024 / 1024)
) "REDO PER DAY (MB)"
From V$archived_log
Group By Trunc (Completion_time)
Order By 1 Desc;
Session blocking quiesce
Create Or Replace Force View Sys.Av_db_sess_blking_quiesc (Sid,
Sess_user,
Osuser,
Type,
Program,
Killstmt
)
As
Select Bl.Sid, User Sess_user, Osuser, Type, Program,
'Alter SYSTEM DISCONNECT Session '
|| ''''
|| Se.Sid
|| ','
|| Se.Serial#
|| ''' '
|| 'IMMEDIATE;' Killstmt
From V$blocking_quiesce Bl, V$session Se
Where Bl.Sid = Se.Sid;
DB overall efficiency
Create Or Replace Force View Sys.Av_db_overall_eff (Metric_name,
Mininum,
Maximum,
Average
)
As
Select Case Metric_name
When 'SQL Service Response Time'
Then 'SQL Service Response Time (secs)'
When 'Response Time Per Txn'
Then 'Response Time Per Txn (secs)'
Else Metric_name
End Metric_name,
Case Metric_name
When 'SQL Service Response Time'
Then Round ((Minval / 100), 2)
When 'Response Time Per Txn'
Then Round ((Minval / 100), 2)
Else Minval
End Mininum,
Case Metric_name
When 'SQL Service Response Time'
Then Round ((Maxval / 100), 2)
When 'Response Time Per Txn'
Then Round ((Maxval / 100), 2)
Else Maxval
End Maximum,
Case Metric_name
When 'SQL Service Response Time'
Then Round ((Average / 100), 2)
When 'Response Time Per Txn'
Then Round ((Average / 100), 2)
Else Average
End Average
From Sys.V_$sysmetric_summary
Where Metric_name In
('CPU Usage Per Sec', 'CPU Usage Per Txn',
'Database CPU Time Ratio', 'Database Wait Time Ratio',
'Executions Per Sec', 'Executions Per Txn',
'Response Time Per Txn', 'SQL Service Response Time',
'User Transaction Per Sec')
Order By 1;
Hard activities in DB
Create Or Replace Force View Sys.Av_db_hard_activities (Db_stat_name,
Time_secs,
Pct_time
)
As
Select Case Db_stat_name
When 'parse time elapsed'
Then 'soft parse time'
Else Db_stat_name
End Db_stat_name,
Case Db_stat_name
When 'sql execute elapsed time'
Then Time_secs - Plsql_time
When 'parse time elapsed'
Then Time_secs - Hard_parse_time
Else Time_secs
End Time_secs,
Case Db_stat_name
When 'sql execute elapsed time'
Then Round (100 * (Time_secs - Plsql_time) / Db_time,
2
)
When 'parse time elapsed'
Then Round (100 * (Time_secs - Hard_parse_time) / Db_time,
2)
Else Round (100 * Time_secs / Db_time, 2)
End Pct_time
From (Select Stat_name Db_stat_name,
Round ((Value / 1000000), 3) Time_secs
From Sys.V_$sys_time_model
Where Stat_name Not In
('DB time', 'background elapsed time',
'background cpu time', 'DB CPU')),
(Select Round ((Value / 1000000), 3) Db_time
From Sys.V_$sys_time_model
Where Stat_name = 'DB time'),
(Select Round ((Value / 1000000), 3) Plsql_time
From Sys.V_$sys_time_model
Where Stat_name = 'PL/SQL execution elapsed time'),
(Select Round ((Value / 1000000), 3) Hard_parse_time
From Sys.V_$sys_time_model
Where Stat_name = 'hard parse elapsed time')
Order By 2 Desc;
Time_secs,
Pct_time
)
As
Select Case Db_stat_name
When 'parse time elapsed'
Then 'soft parse time'
Else Db_stat_name
End Db_stat_name,
Case Db_stat_name
When 'sql execute elapsed time'
Then Time_secs - Plsql_time
When 'parse time elapsed'
Then Time_secs - Hard_parse_time
Else Time_secs
End Time_secs,
Case Db_stat_name
When 'sql execute elapsed time'
Then Round (100 * (Time_secs - Plsql_time) / Db_time,
2
)
When 'parse time elapsed'
Then Round (100 * (Time_secs - Hard_parse_time) / Db_time,
2)
Else Round (100 * Time_secs / Db_time, 2)
End Pct_time
From (Select Stat_name Db_stat_name,
Round ((Value / 1000000), 3) Time_secs
From Sys.V_$sys_time_model
Where Stat_name Not In
('DB time', 'background elapsed time',
'background cpu time', 'DB CPU')),
(Select Round ((Value / 1000000), 3) Db_time
From Sys.V_$sys_time_model
Where Stat_name = 'DB time'),
(Select Round ((Value / 1000000), 3) Plsql_time
From Sys.V_$sys_time_model
Where Stat_name = 'PL/SQL execution elapsed time'),
(Select Round ((Value / 1000000), 3) Hard_parse_time
From Sys.V_$sys_time_model
Where Stat_name = 'hard parse elapsed time')
Order By 2 Desc;
DB Global waits
Create Or Replace Force View Sys.Av_db_global_waits (Wait_class,
Total_waits,
Pct_waits,
Time_waited_secs,
Pct_time
)
Total_waits,
Pct_waits,
Time_waited_secs,
Pct_time
)
DB General Info
Create Or Replace Force View Sys.Av_db_gen_info (Db_info)
As
Select '01- ' || Banner Db_info
From Sys.V_$version
As
Select '01- ' || Banner Db_info
From Sys.V_$version
Data Files free space
Create Or Replace Force View Sys.Av_db_df_free_space (Tablespace_name,
File_name,
Allocated_mb,
Used_mb,
Free_space_mb
)
File_name,
Allocated_mb,
Used_mb,
Free_space_mb
)
Current session wait activity in DB
Create Or Replace Force View Sys.Av_db_cur_sess_wait_activity (Sid,
Username,
Wait_class,
Total_waits,
Time_waited_secs,
Killstmt
)
As
Select A.Sid, B.Username, A.Wait_class, A.Total_waits,
Round ((A.Time_waited / 100), 2) Time_waited_secs,
'Alter SYSTEM Kill Session '
|| ''''
|| B.Sid
|| ','
|| B.Serial#
|| ''' '
|| 'IMMEDIATE;' Killstmt
From Sys.V_$session_wait_class A, Sys.V_$session B
Where B.Sid = A.Sid
And B.Username Is Not Null
And A.Wait_class != 'Idle'
Order By 5 Desc;
Active Process in DB
Create Or Replace Force View Sys.Av_db_active_process (Username,
Osuser,
Disk_reads,
Buffer_gets,
Lockwait,
Pid,
Event,
Sql
)
Osuser,
Disk_reads,
Buffer_gets,
Lockwait,
Pid,
Event,
Sql
)
Blocking Waiting Sessions
Create Or Replace Force View Sys.Av_blocking_waiting_sess (Waiting_session,
Holding_session,
Lock_or_pin,
Address,
Mode_held,
Mode_requested
)
Holding_session,
Lock_or_pin,
Address,
Mode_held,
Mode_requested
)
Locked Object
Create Or Replace Force View Sys.Av_locked_obj (Sid,
Oracle_username,
Os_user_name,
Locked_mode,
Object_name,
Oracle_username,
Os_user_name,
Locked_mode,
Object_name,
Object Cache Locks
Create Or Replace Force View Sys.Av_lock_object_cache (Owner,
Name,
Db_link,
Namespace,
Name,
Db_link,
Namespace,
DDL Locks on Objects
Create Or Replace Force View Sys.Av_lock_ddl_on_obj (Sid,
Serial#,
Owner,
Obj_name,
ASM Diskgroup Info
Create Or Replace Force View Sys.Av_asm_diskgroup (Group_number,
Name,
Sector_size,
Block_size,
Allocation_unit_size,
State,
Type,
Total_mb,
Free_mb,
Required_mirror_free_mb,
Usable_file_mb,
Offline_disks,
Unbalanced,
Compatibility,
Database_compatibility
)
As
Select "GROUP_NUMBER", "NAME", "SECTOR_SIZE", "BLOCK_SIZE",
"ALLOCATION_UNIT_SIZE", "STATE", "TYPE", "TOTAL_MB", "FREE_MB",
"REQUIRED_MIRROR_FREE_MB", "USABLE_FILE_MB", "OFFLINE_DISKS",
"UNBALANCED", "COMPATIBILITY", "DATABASE_COMPATIBILITY"
From V$asm_diskgroup;
Name,
Sector_size,
Block_size,
Allocation_unit_size,
State,
Type,
Total_mb,
Free_mb,
Required_mirror_free_mb,
Usable_file_mb,
Offline_disks,
Unbalanced,
Compatibility,
Database_compatibility
)
As
Select "GROUP_NUMBER", "NAME", "SECTOR_SIZE", "BLOCK_SIZE",
"ALLOCATION_UNIT_SIZE", "STATE", "TYPE", "TOTAL_MB", "FREE_MB",
"REQUIRED_MIRROR_FREE_MB", "USABLE_FILE_MB", "OFFLINE_DISKS",
"UNBALANCED", "COMPATIBILITY", "DATABASE_COMPATIBILITY"
From V$asm_diskgroup;
How can I tell if a procedure/package is running?
Sometimes the installation of a new version of a database package "hangs" and eventually times out with a ORA-04021: timeout occurred while waiting to lock object". This is caused by another session that is currently executing the same package.
Sunday, March 13, 2011
Listing privileges recursively for Oracle users
This is a script that shows the hierarchical relationship between system privileges, roles and users.
FRM-92100 when running a long running report
Users were experiencing the FRM-92100 with IllegalArgumentException while running a long running report, after investigation it was found that Request Timeout (seconds) value was 600 only on OHS. So changing to 1500 resolved the issue.
RMAN Restore (Netbackup)
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN Backup using Netbackup
After Netbackup configuration on the Oracle Server, you can use rman to take backup on tape.
Saturday, March 12, 2011
How To Change ASM SYS PASSWORD ?
Things tried:
SQL> password
Changing password for SYS
Old password:
New password:
Retype new password:
ERROR:
ORA-00600: internal error code, arguments: [15051], [], [], [], [], [], [], []
Moving table(s) to a different tablespace
Q: What happens when u move a table to a different Tablespace?
Q: How can u move table to a different Tablespace which have long datatype?
Q: How can u move table to a different Tablespace with indexes?
Q: How can u move table to a different Tablespace which have long datatype?
Q: How can u move table to a different Tablespace with indexes?
ORA-02449 during tablespace drop
SQL> drop tablespace users including contents and datafiles ;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
Subscribe to:
Posts (Atom)