Oracle Performance Tuning

BASICS

1. What is Performance Tuning?

Ans: Making optimal use of system using existing resources called performace tuning.

2. Types of Tunings?

Ans: 1. CPU Tuning 2. Memory Tuning 3. IO Tuning 4. Application Tuning 5. Databse Tuning

3. What Mailny Database Tuning contains?

Ans: 1. Hit Ratios 2. Wait Events

3. What is an optimizer?

Ans: Optimizer is a mechanizm which will make the execution plan of an sql statement

4. Types of Optimizers?

Ans: 1. RBO(Rule Based Optimizer) 2. CBO(Cost Based Optimzer)

5. Which init parameter is used to make use of Optimizer?

Ans: optimizer_mode= rule----RBO cost---CBO choose--------First CBO otherwiser RBO

6. Which optimizer is the best one?

Ans: CBO

7. What are the pre requsited to make use of Optimizer?

Ans: 1. Set the optimizer mode 2. Collect the statistics of an object

8. How do you collect statistics of a table?

Ans: analyze table emp compute statistics or analyze table emp estimate statistics

9. What is the diff between compute and estimate?

Ans: If you use compute, The FTS will happen, if you use estimate just 10% of the table will be read

10. What wll happen if you set the optimizer_mode=choose?Ans: If the statistics of an object is available then CBO used. if not RBO will be used

11. Data Dictionay follows which optimzer mode?

Ans: RBO

12. How do you delete statistics of an object?

Ans: analyze table emp delete statistics

13. How do you collect statistics of a user/schema?

Ans: exec dbms_stats.gather_schema_stats(scott)

14. How do you see the statistics of a table?

Ans: select num_rows,blocks,empty_blocks from dba_tables where tab_name='emp'

15. What are chained rows?

Ans: These are rows, it spans in multiple blocks

16. How do you collect statistics of a user in Oracle Apps?

Ans: fnd_stats package

17. How do you create a execution plan and how do you see?Ans: 1. @?/rdbms/admin/utlxplan.sql --------- it creates a plan_table 2. explain set statement_id='1' for select * from emp; 3. @?/rdbms/admin/utlxpls.sql -------------it display the plan

18. How do you know what sql is currently being used by the session?

Ans: by goind v$sql and v$sql_area

19. What is a execution plan?

Ans: Its a road map how sql is being executed by oracle db?

20. How do you get the index of a table and on which column the index is?

Ans: dba_indexes and dba_ind_columns

21. Which init paramter you have to set to by pass parsing?

Ans: cursor_sharing=force

22. How do you know which session is running long jobs?

Ans: by going v$session_longops

23. How do you flush the shared pool?

Ans: alter system flush shared_pool

24. How do you get the info about FTS?

Ans: using v$sysstat

25. How do you increase the db cache?

Ans: alter table emp cache

26. Where do you get the info of library cache?

Ans: v$librarycache

27. How do you get the information of specific session?

Ans: v$mystat

28. How do you see the trace files?

Ans: using tkprof --- usage: tkprof allllle.trc llkld.txt

29. Types of hits?

Ans: Buffer hit and library hit

30. Types of wait events?

Ans: cpu time and direct path read

Advanced

1. A tablespace has a table with 30 extents in it. Is this bad? Why or why not?
Level: Intermediate
Expected answer: Multiple extents in and of themselves aren't bad. However if you also have chained rows this can hurt performance.

2. How do you set up tablespaces during an Oracle installation?
Level: Low
Expected answer: You should always attempt to use the Oracle Flexible Architecture standard or another partitioning scheme to ensure proper separation of SYSTEM, ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX segments.

3. You see multiple fragments in the SYSTEM tablespace, what should you check first?
Level: Low
Expected answer: Ensure that users don't have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.

4. What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
Level: Intermediate
Expected answer: Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.

5. What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans?
Level: High
Expected answer: Oracle almost always reads in 64k chunks. The two should have a product equal to 64 or a multiple of 64.

6. What is the fastest query method for a table?
Level: Intermediate
Expected answer: Fetch by rowid

7. Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?
Level: High
Expected answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

8. When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad, how do you correct it?
Level: Intermediate
Expected answer: If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort are parameter is the SORT_AREA_SIZe parameter.

9. When should you increase copy latches? What parameters control copy latches?
Level: high
Expected answer: When you get excessive contention for the copy latches as shown by the "redo copy" latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.

10. Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed?
Level: Low
Expected answer: You can look in the init.ora file for an indication of manually set parameters. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view.

Partitioning in Oracle

Oracle Partitioning is a separately licensed option of the Oracle database that is only available with the Enterprise Edition of the database. Partitioning allows DBAs to split large tables into more manageable "sub-tables", called partitions, to improve database performance, manageability and availability.

Partitioning is basically a divide-and-conquer approach to scale to very large database sizes. It improves manageability by allowing partitions to be added, loaded, indexed and removed while the database is on-line and users are working against these tables. Partitioning can potentially make the database faster the bigger it gets.

Oracle Partitioning was first introduced in Oracle 8 in 1997.

Oracle 10g R2 allowed more than 1 million partitions per table. It also introduced multidimensional pruning and fast table drops.

Starting with Oracle 11g, Oracle provides a partition advisor to assist customers to choose the best partitioning method for their data.

Some of the advantages offered:

§  Partitions can be stored in different tablespaces.

§  Partitions can be added/ removed while users are working. DBAs can perform maintenance without having to bring down an entire table (import/ export/ load/ etc).

§  Data can be selected from targeted partitions without having to scan all partitions for rows (partition pruning). The SQL optimizer will bypass partitions that don't contain data pertinent to the query being solved.

Available partitioning types:

§  Range partitioning (introduced in Oracle 8)

§  Hash partitioning (introduced in Oracle 8i)

§  Composite partitioning (introduced in Oracle 8i)

§  List partitioning (introduced in Oracle 9i)

§  Interval partitioning (introduced in Oracle 11g)

§  System partitioning (introduced in Oracle 11g)

§  Reference partitioning (introduced in Oracle 11g)

Partition compression

Starting from Oracle 11g individual partitions can be compressed:

§  Compression as high as 3.5 to 1 is possible

§  Compressed tables now support: DML Statements, Add and Drop Column, Partition level COMPRESS or NOCOMPRESS.

§  ALTER TABLE... COMPRESS;

§  ALTER TABLE... NOCOMPRESS;

§  Table compression now supported for OLTP

As we know that in 10g we were able to compress the whole table neither it was apply to individual partition but in 11g we can use compress on partition level. Let's check the compression feature for partitions.

SQL>CREATE TABLE test

           (sno       NUMBER(6),

            last_name VARCHAR2(30),

            salary    NUMBER(6))

          PARTITION BY RANGE (salary)

           INTERVAL  (5000)

          (

     PARTITION p1 VALUES LESS THAN (5000)   COMPRESS,

     PARTITION p2 VALUES LESS THAN (10000)  NOCOMPRESS,

     PARTITION p3 VALUES LESS THAN (15000)  COMPRESS,

     PARTITION p4 VALUES LESS THAN (20000)) NOCOMPRESS;

SQL> SELECT table_name, partition_name, compression

 2  FROM user_tab_partitions

 3  WHERE table_name='TEST';

TABLE_NAME      PARTITION_NAME              COMPRESS

--------------- --------------------------- --------------

TEST            P1                          ENABLED

TEST            P2                          DISABLED

TEST            P3                          ENABLED

TEST            P4                          DISABLED

We can define compression for whole partition by using single keyword like this.

SQL> CREATE TABLE test

 2              (sno       NUMBER(6),

 3               last_name VARCHAR2(30),

 4               salary    NUMBER(6))

 5          COMPRESS

 6             PARTITION BY RANGE (salary)

 7              INTERVAL (5000)

 8             (

 9        PARTITION p1 VALUES LESS THAN (5000) ,

10        PARTITION p2 VALUES LESS THAN (10000),

11        PARTITION p3 VALUES LESS THAN (15000),

12        PARTITION p4 VALUES LESS THAN (20000));

Table created.

SQL> SELECT table_name, partition_name, compression

 2  FROM user_tab_partitions

 3  WHERE table_name='TEST';

TABLE_NAME                     PARTITION_NAME                COMPRESS

------------------------------ ----------------------------- -------------

TEST                           P1                            ENABLED

TEST                           P2                            ENABLED

TEST                           P3                            ENABLED

TEST                           P4                            ENABLED

How does one enable partition support for a database?

Partitioning is only available with Oracle Enterprise Edition as a cost option (you need to buy licenses before you can use it).

Start the Oracle installer and check if "Oracle Partitioning" is installed. If it is, you can just start using it.

If not, you will get error ORA-00439: feature not enabled: Partitioning. If you get this error, upgrade to Enterprise Edition and/or install the partitioning option.

What partitioning types does Oracle support?

Oracle support the following partitioning methods:

§  Range partitioning - data is mapped to partitions based on a range of column values (usually a date column)

§  Hash partitioning - data is mapped to partitions based on a hashing algorithm, evenly distributing data between the partitions.

§  List partitioning - data is mapped to partitions based on a list of discrete values.

§  Interval partitioning - data is mapped to partitions based on an interval

§  Composite partitioning - combinations of the above methods

§  Range-Hash partitioning - data is partitioned by range, then hashed to sub-partitions.

§  Range-List partitioning - data is partitioned by range, then to sub-partitions based on a value list.

§  Range-range partitioning

§  List-range partitioning

§  List-hash partitioning

§  List-list partitioning

§  Interval partitioning, an extension of range partitioning. New partitions are automatically added.

§  System partitioning, application controlled partitioning.

§  Reference partitioning, partitioned like a parent table (referenced in a referential constraint).

How do I know if an object is partitioned or not?

§  A simple query against the data dictionary will show if a given table or index is partitioned or not:

§  SELECT * FROM dba_part_tables;

§  SELECT * FROM dba_part_indexes;

 

How does one list the partitions of a partitioned object?

One can list the partitions of a table with:

SELECT * FROM dba_tab_partitions WHERE table_name = '<table_name>';

One can show the partitions of an given index with:

SELECT * FROM dba_ind_partitions WHERE index_name = '<index_name>';

Oracle Data Pump

Oracle Data Pump is a feature of Oracle Database 10g that enables very fast bulk data and metadata movement between Oracle databases. Oracle Data Pump provides new high-speed, parallel Export and Import utilities (expdp and impdp) as well as a Web-based Oracle Enterprise Manager interface. 

Data Pump Export and Import utilities are typically much faster than the original Export and Import Utilities. A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is 15-45 times fast than original Import. 
Data Pump jobs can be restarted without loss of data, whether or not the stoppage was voluntary or involuntary. 
Data Pump jobs support fine-grained object selection. Virtually any type of object can be included or excluded in a Data Pump job. 
Data Pump supports the ability to load one instance directly from another (network import) and unload a remote instance (network export). 
Data Pump Export (expdp) :- 

For this example, once your export your database before that you must be give privilege on this user. If you need to export you can give " EXP_FULL_DATABASE " and if you need import you can give " IMP_FULL_DATABASE " 

SQL> CONNECT SYS/BABU@KEYSTONE AS SYSDBA 
Connected. 

SQL> GRANT CREATE ANY DIRECTORY TO ORTHONOVC16; 

Grant succeeded. 

SQL> CREATE OR REPLACE DIRECTORY OTHOC16 AS 'D:\ORTHOC16'; 

Directory created. 

SQL> GRANT READ,WRITE ON DIRECTORY OTHOC16 TO ORTHONOVC16; 

Grant succeeded. 

SQL> GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE TO ORTHONOVC16; 

Grant succeeded. 

Table level Export :- 

SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE tables=G_USER DIRECTORY=OTHOC16 DUMPFILE=ORTHO_G_USER.DMP LOGFILE=ORTHOLOG.LOG 


The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables. 

Schema level export :- 

SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE SCHEMAS=ORTHONOVC16 DIRECTORY=OTHOC16 DUMPFILE=ORTHONOVC16.DMP LOGFILE=ORTHONOVC16.LOG 
DataBase level export :- 
SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE FULL=Y DIRECTORY=OTHOC16 DUMPFILE=DBORTHO.DMP LOGFILE=DBORTHO.LOG 
Other export's : 

Exclude = View, Proceudre, Function , Constraint , Index 

Include = Table: " in ( 'emp') " 

Content = ALL (by default ) / data_only / metadata_only 

Estimate_Only = Before export your dumpfile you can estimate your dumpfile size using the bellow 

parameter " ESTIMATE_ONLY = Y "