Performance Question and Answers
Documents Created by : Bharat Gali
Created date : 01-Apr-2007
Database Version : 10g
Last Revised :30-Jan-2008
*********************************************************************************
Question 1
1. Currently, commercial database engines incorporate many features that assume large disk secondary storage areas. These are two basic strategies for improving the I/O performance:
1) Avoid disk I/O whenever possible, or 2) make the physical reads and writes more efficient.When they do occur. Briefly outline a few mechanisms or techniques that are useful for implementing these two strategies.
Bharat Gali reply:
Potential reasons for more disk I/O is
1) Bad SQL and PL/SQL.
2) Missing indexes, full table scans, row chaining, data fragmentation, etc
1) Find the largest amount of physical reads and logical reads by query.
Simple way to find Queries having more physical read
---------------
select disk_reads, sql_text
from v$sqlarea
where disk_reads > 10000
order by disk_reads desc;
----------------
Simple way to find Queries having more logical reads.
-----------
select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 200000
order by buffer_gets desc;
-----------
Multiples approaches must be made to cover all types of queries
.
Below listed few approaches.
1) Check the tables in the queries for proper indexes creations.
2) Check for tables, indexes and other objects for recent statistics (This can be done using
dbms_stats package)
3) Pin (Cache) most used PL/SQL statements into memory
syntax: Dbms_shared_pool.keep(object_name).
4) Avoid using database function in select statements as much as possible.
Ex select sales_no,get_vendor_name(sales_no)
from sales_transactions
where sales_date = sysdate
In the above query get_vendor_name is a function.
5) Avoid full table scans for data retrievals as much as possible.
Some times if data in the table is huge and select criteria for retrieval is more. Then even
thought the table column having indexes this will not help. In such cases create table
partitions.( Some times there will be other dis-advantages for table partitions check for
business cases before creating table partitions).
6) Use Merge statements (Available in 9i and 10g only) for huge updates and deletes
Merge statements uses very less I/O.
Example Merge statements
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status);
Your Question
2. Most commercial database systems use variable-sized rows to avoid wasted space. However as rows change size through update activity they may no longer fit in their original block, migrating to other blocks, or even requiring several blocks to store large rows. How does this problem affect performance? Which features of the Oracle DBMS allow the designer to lessen such performance impacts.
Bharat Gali Answer.
The above problem technically defined as row chaining in Oracle. Excessive row chaining can cause a dramatic increase in disk I/O because several I/O’s are required to fetch the block instead of one single I/O.
Row chaining can be avoid using more value for PCTFREE parameter at the time of table
creation in oracle.
Sample syntax
------
CREATE TABLE CTX_TEMP_WWV_HELP
(
SUBSCRIBER_ID NUMBER
TEXTKEY VARCHAR2(64 BYTE),
CONID NUMBER,
SCORE NUMBER
)
TABLESPACE PORTAL
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 16K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
3.
The Oracle DBMS relies on shared memory for interprocess communications within the database engine. In addition, the shared memory is divided into separate areas, such as the database buffer cache, redo log buffer, shared pool (library cache and data dictionary cache), java pool and large pool. Why is shared memory a good choice of for communications? Are there any potential drawbacks?
I have no Clue with this question.
Partial answer I saw in the internet.
---------------------
Why is shared memory a good choice of for communications?
Shared memory allows processes to access common structures and data by placing them in shared memory segments. It's the fastest form of Interprocess Communication (IPC) available since no kernel involvement occurs when data is passed between the processes. In fact, data does not need to be copied between the processes.
Oracle uses shared memory segments for the Shared Global Area (SGA) which is an area of memory that is shared by Oracle processes. The size of the SGA has a significant impact to Oracle's performance since it holds database buffer cache and much more.