Here are the few performance improvement techniques for your oracle database(There are many ways to do the improvement and it depends on the database size, usage, clustering etc..)
1. Implement Parallel degree:
Overview and Benefits of using PX:
Using parallel operations enables multiple processes to work together simultaneously to resolve a single SQL statement.
Consider a full table scan, rather than having a single process to execute it (serially), Oracle can create multiple processes to scan the table in parallel.
Degree of Parallelism (DOP) is the number of processes used to perform the task on a table. The degree can be set while creating the table or while writing the query using hints.
As an example, if we use DOP as 4 for a particular table, then Oracle uses 4 processes to run and 1 process to coordinate the query. This case differs, if we use some type of sort in the query. Oracle will then use 4 more processes to sort them as well.
Oracle supports parallelization for both DDL and DMLs.
Oracle can parallelize the following operations on a table:
1. Select
2. Insert, Update, Delete (with an exception Parallelized only for partitions)
3. Merge
4. Create table as select * ….
5. Create and Rebuild Index
The following operations can also be parallelized:
1. Select distinct
2. Group by
3. Order by
4. Not in
5. Union/Union All
6. Aggregate functions
7. Nested Loops
Parallel execution is enabled by default, Oracle Database computes defaults for these parameters based on the value at database startup of CPU_COUNT and PARALLEL_THREADS_PER_CPU
Starting Oracle 10g onwards, we have a few oracle parameters deprecated with reference to the Parallel execution: PARALLEL_AUTOMATIC_TUNING
Steps needed to implement
1. From the AWR snapshots we can gether information about parallel execution:
PARALLEL_EXECUTION_MESSAGE_SIZE
PARALLEL_MAX_SERVERS
The parameter PARALLEL_MAX_SERVER Specifies the maximum number of parallel execution processes and parallel recovery processes
PARALLEL_MAX_SERVERS is calculated with the following formula:
= CPU_COUNT X PARALLEL_THREADS_PER_CPU X (2 if PGA_AGGREGATE_TARGET > 1 OR 1) X 5
For ex:
= 48 X 2 X 2 X 5
= 960
2. PARALLEL_ADAPTIVE_MULTIUSER parameter is set to TRUE (by default) from 10g onwards; indicate whether the DOP should change with the load. I couldn’t gather this information from the snapshots. Not sure if this is set to false?
3. If parallelism is used then we can set the DOP based on the query complexity
4. We may use NOLOGGING clause to generate less redos? (If the redo generation isn’t that important)
5. Once these parameters are set, we can implement DOP using following methods:
• At the statement level with hints and with the PARALLEL clause
INSERT /*+ PARALLEL(tbl_ins,2) */ INTO tbl_ins
SELECT /*+ PARALLEL(tbl_sel,4) */ * FROM tbl_sel;
• At the session level by issuing the ALTER SESSION FORCE PARALLEL statement
ALTER SESSION FORCE PARALLEL QUERY;
• At the table level in the table's definition
Create table ORDER_LINE_ITEMS
(Invoice_Number NUMBER (12) not null,
Invoice_date DATE not null) parallel 4;
Create table ORDER_LINE_ITEMS
(Invoice_Number NUMBER (12) not null,
Invoice_date DATE not null) parallel 4
as
select /*+parallel (OLD_ORDER_LINE_ITEM,4) */*
From OLD_TABLE_ITEMS;
At the index level in the index's definition
Create index ORDER_KEY on ORDER_LINE_ITEMS (Order_Id, Item_Id)
tablespace idx1
storage (initial 10m next 1m pctincrease 0)
parallel (parallel 5 ) NOLOGGING;
6. If DOP is not specified while running the query, then Oracle takes this value based on CPU_COUNT and PARALLEL_THREADS_PER_CPU as well as makes sure that number of processes does not exceed PARALLEL_MAX_SERVERS.
7. Hence if we have ample number of parallel processes then more queries can be used for parallelism.
8. We need to consider sizing of SHARE_POOL_SIZE since parallel execution needs more memory compared to the serial execution and hence appropriate sizing of Shared pool is very necessary. In our system, we have SGA_TARGET and SGA_MAX_SIZE set which means AUTOMATIC SHARED MEMORY MANAGEMENT is in place. ASMM will take care of the sizing of the memory parameters internally, Appropriate sizes of SGA_TARGET is very critical.
2. Reduce SQL*Net round-trip
Overview and Benefits of using SDU:
DBA can change the frequency and size of network packets using the following (In oracle 10g):
1. SDU, QUEUE_SIZE in tnsnames.ora and listener.ora
2. DEFAULT_SDU_SIZE, tcp.nodelay in sqlnet.ora
Number of round trips can also be reduced by setting ARRAYSIZE in SQL*Plus.
SDU is a buffer that Oracle Net uses to place data before transmitting it across the network. Oracle Net sends the data in the buffer either when requested or when it is full.
The idea of SDU is to resize the network packets sent over network, thereby accommodating more data at once and eventually reducing number of trips.
The ARRAYSIZE gives the amount of records your application fetches at once (like SQL*Plus), using which more rows can be fetched from the database at one time.
Steps needed to implement
1. Increase SDU if it hasn’t been tuned already. The value ranges from 512 to 32767 bytes
SDU needs to be configured at both Client and the Server. SDU can be configured for particular service using tnsnames.ora and listener.ora as explained below:
To configure the client, set the SDU size in the following places:
sqlnet.ora File
For global configuration on the client side, configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file:
DEFAULT_SDU_SIZE=32767
Connect Descriptors
For a particular connect descriptor, you can override the current settings in the client side sqlnet.ora file. In a connect descriptor, you specify the SDU parameter for a description.
sales.us.acme.com=
(DESCRIPTION=
(SDU=11280)
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com))
)
SDU size applies to all Oracle Net protocols.
To configure the database server, set the SDU size in the following places:
sqlnet.ora File
Configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file:
DEFAULT_SDU_SIZE=32767
If using shared server processes, set the SDU size in the DISPATCHERS parameter as follows:
DISPATCHERS="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp))(SDU=8192))"
If using dedicated server processes for a database that is registered with the listener through static configuration in the listener.ora file, you can override the current setting in sqlnet.ora:
SID_LIST_listener_name=
(SID_LIST=
(SID_DESC=
(SDU=8192)
(SID_NAME=sales)))
Reference: http://download.oracle.com/docs/cd/B13789_01/network.101/b10775/performance.htm
2. Set the value of DEFAULT_SDU_SIZE in sqlnet.ora . If the DEFAULT_SDU_SIZE parameter is not configured in the sqlnet.ora file, then the default SDU for the client and a dedicated server is 2048 bytes, while for a shared server the default SDU is 32767 bytes
3. ARRAYSIZE – Number of rows fetched per network trip. Default is 15 and valid values are 1 to 5000.
SQL> set arraysize 100;
1. Implement Parallel degree:
Overview and Benefits of using PX:
Using parallel operations enables multiple processes to work together simultaneously to resolve a single SQL statement.
Consider a full table scan, rather than having a single process to execute it (serially), Oracle can create multiple processes to scan the table in parallel.
Degree of Parallelism (DOP) is the number of processes used to perform the task on a table. The degree can be set while creating the table or while writing the query using hints.
As an example, if we use DOP as 4 for a particular table, then Oracle uses 4 processes to run and 1 process to coordinate the query. This case differs, if we use some type of sort in the query. Oracle will then use 4 more processes to sort them as well.
Oracle supports parallelization for both DDL and DMLs.
Oracle can parallelize the following operations on a table:
1. Select
2. Insert, Update, Delete (with an exception Parallelized only for partitions)
3. Merge
4. Create table as select * ….
5. Create and Rebuild Index
The following operations can also be parallelized:
1. Select distinct
2. Group by
3. Order by
4. Not in
5. Union/Union All
6. Aggregate functions
7. Nested Loops
Parallel execution is enabled by default, Oracle Database computes defaults for these parameters based on the value at database startup of CPU_COUNT and PARALLEL_THREADS_PER_CPU
Starting Oracle 10g onwards, we have a few oracle parameters deprecated with reference to the Parallel execution: PARALLEL_AUTOMATIC_TUNING
Steps needed to implement
1. From the AWR snapshots we can gether information about parallel execution:
PARALLEL_EXECUTION_MESSAGE_SIZE
PARALLEL_MAX_SERVERS
The parameter PARALLEL_MAX_SERVER Specifies the maximum number of parallel execution processes and parallel recovery processes
PARALLEL_MAX_SERVERS is calculated with the following formula:
= CPU_COUNT X PARALLEL_THREADS_PER_CPU X (2 if PGA_AGGREGATE_TARGET > 1 OR 1) X 5
For ex:
= 48 X 2 X 2 X 5
= 960
2. PARALLEL_ADAPTIVE_MULTIUSER parameter is set to TRUE (by default) from 10g onwards; indicate whether the DOP should change with the load. I couldn’t gather this information from the snapshots. Not sure if this is set to false?
3. If parallelism is used then we can set the DOP based on the query complexity
4. We may use NOLOGGING clause to generate less redos? (If the redo generation isn’t that important)
5. Once these parameters are set, we can implement DOP using following methods:
• At the statement level with hints and with the PARALLEL clause
INSERT /*+ PARALLEL(tbl_ins,2) */ INTO tbl_ins
SELECT /*+ PARALLEL(tbl_sel,4) */ * FROM tbl_sel;
• At the session level by issuing the ALTER SESSION FORCE PARALLEL statement
ALTER SESSION FORCE PARALLEL QUERY;
• At the table level in the table's definition
Create table ORDER_LINE_ITEMS
(Invoice_Number NUMBER (12) not null,
Invoice_date DATE not null) parallel 4;
Create table ORDER_LINE_ITEMS
(Invoice_Number NUMBER (12) not null,
Invoice_date DATE not null) parallel 4
as
select /*+parallel (OLD_ORDER_LINE_ITEM,4) */*
From OLD_TABLE_ITEMS;
At the index level in the index's definition
Create index ORDER_KEY on ORDER_LINE_ITEMS (Order_Id, Item_Id)
tablespace idx1
storage (initial 10m next 1m pctincrease 0)
parallel (parallel 5 ) NOLOGGING;
6. If DOP is not specified while running the query, then Oracle takes this value based on CPU_COUNT and PARALLEL_THREADS_PER_CPU as well as makes sure that number of processes does not exceed PARALLEL_MAX_SERVERS.
7. Hence if we have ample number of parallel processes then more queries can be used for parallelism.
8. We need to consider sizing of SHARE_POOL_SIZE since parallel execution needs more memory compared to the serial execution and hence appropriate sizing of Shared pool is very necessary. In our system, we have SGA_TARGET and SGA_MAX_SIZE set which means AUTOMATIC SHARED MEMORY MANAGEMENT is in place. ASMM will take care of the sizing of the memory parameters internally, Appropriate sizes of SGA_TARGET is very critical.
2. Reduce SQL*Net round-trip
Overview and Benefits of using SDU:
DBA can change the frequency and size of network packets using the following (In oracle 10g):
1. SDU, QUEUE_SIZE in tnsnames.ora and listener.ora
2. DEFAULT_SDU_SIZE, tcp.nodelay in sqlnet.ora
Number of round trips can also be reduced by setting ARRAYSIZE in SQL*Plus.
SDU is a buffer that Oracle Net uses to place data before transmitting it across the network. Oracle Net sends the data in the buffer either when requested or when it is full.
The idea of SDU is to resize the network packets sent over network, thereby accommodating more data at once and eventually reducing number of trips.
The ARRAYSIZE gives the amount of records your application fetches at once (like SQL*Plus), using which more rows can be fetched from the database at one time.
Steps needed to implement
1. Increase SDU if it hasn’t been tuned already. The value ranges from 512 to 32767 bytes
SDU needs to be configured at both Client and the Server. SDU can be configured for particular service using tnsnames.ora and listener.ora as explained below:
To configure the client, set the SDU size in the following places:
sqlnet.ora File
For global configuration on the client side, configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file:
DEFAULT_SDU_SIZE=32767
Connect Descriptors
For a particular connect descriptor, you can override the current settings in the client side sqlnet.ora file. In a connect descriptor, you specify the SDU parameter for a description.
sales.us.acme.com=
(DESCRIPTION=
(SDU=11280)
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com))
)
SDU size applies to all Oracle Net protocols.
To configure the database server, set the SDU size in the following places:
sqlnet.ora File
Configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file:
DEFAULT_SDU_SIZE=32767
If using shared server processes, set the SDU size in the DISPATCHERS parameter as follows:
DISPATCHERS="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp))(SDU=8192))"
If using dedicated server processes for a database that is registered with the listener through static configuration in the listener.ora file, you can override the current setting in sqlnet.ora:
SID_LIST_listener_name=
(SID_LIST=
(SID_DESC=
(SDU=8192)
(SID_NAME=sales)))
Reference: http://download.oracle.com/docs/cd/B13789_01/network.101/b10775/performance.htm
2. Set the value of DEFAULT_SDU_SIZE in sqlnet.ora . If the DEFAULT_SDU_SIZE parameter is not configured in the sqlnet.ora file, then the default SDU for the client and a dedicated server is 2048 bytes, while for a shared server the default SDU is 32767 bytes
3. ARRAYSIZE – Number of rows fetched per network trip. Default is 15 and valid values are 1 to 5000.
SQL> set arraysize 100;
