Home › Category Archives › Performance

Oracle Statistics

Statistics can be computed or estimated for a sample

ANALYZE TABLE/Index  Object_Name COMPUTE/ESTIMATE STATISTICS SAMPLE no ROWS/PERCENT;

EXEC DBMS_UTILITY.analyze_schema(‘SCHEMA_NAME’,’COMPUTE/ESTIMATE’, estimate_percent/estimate_rows =>);
Oracle recommends using the DBMS_STATS package for collecting the statistics rather than using Analyze directly.

 

EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats(‘SCHEMA_NAME’);
EXEC DBMS_STATS.gather_schema_stats(‘SCHEMA_NAME’, estimate_percent => 15);

 

To Compute Statistics for one partition of a table:
Analyze Table_Name Partition(Partition_Name) compute statistics;

DBA_OPTSTAT_OPERATIONS history of statistics operations performed at the schema and database level using the DBMS_STATS package.
DBA_USTATS Statistics collected on either table or index level
DBA_TAB_STATS_HISTORY history of table statistics modifications for all tables in the database. (saved only for 31 day)

Oracle Table Partition

Very Large tables and indexes can be partitioned in order to decompose the big object into smaller pieces.

This can have major impact on performance as well as maintenance and management activities.

There are mainly 3 data distribution methods

  • List Partition
  • Range Partition
  • Hash Partition

Each table can be partitioned using

  • Single Level Partition
  • Composite Partition

Each partition can be stored in different tablespace.

 

Reference Partition: This is supported in Oracle 11g, where the partitions will be based on foreign key  values.

Below are some examples on creating and administrating partitions

 

drop  TABLE TWOPARTITION;

CREATE TABLE TWOPARTITION
(
ID1    NUMBER,
Name1  VARCHAR2(100),
ID2    NUMBER,
Name2  VARCHAR2(100)
)
PARTITION BY RANGE (id1)
SUBPARTITION BY LIST (id2)
SUBPARTITION TEMPLATE
(SUBPARTITION sub_p_1 VALUES (1,2,3,4,5,6,7,8,9),
SUBPARTITION Sub_p_2 VALUES (DEFAULT)
)
(
PARTITION P1_1 VALUES LESS THAN (1000),
PARTITION P1_2 VALUES LESS THAN (MAXVALUE)
);

 

To check which tables are partitioned

Select * from user_part_tables;

To Get information about table partitions

select * from user_tab_partitions;

 

select * from user_tab_Subpartition;

User_Part_Key_Columns

User_SubPart_Key_Columns

User_Part_Col_Statistics

User_subPart_Col_Statistics

User_subpartition_templates

 

To Split one partition

alter table twopartition split partition p1_2 at (6000 ) into (partition p1_3, partition p1_rest);
alter table twopartition split partition p1_rest at (7000 ) into (partition p1_4, partition p1_rest);
alter table twopartition split partition p1_rest at (8000 ) into (partition p1_6, partition p1_rest);

To Merge 2 partitions

alter table twopartition merge partitions P1_3, P1_4 into  Partition P1_3N;
alter table twopartition merge partitions P1_6, P1_REST into  Partition P1_REST;
alter table twopartition merge partitions P1_3N, P1_REST into  Partition P1_2;

 

select * from user_tab_partitions;

you might find some tables named like BIN$SAVC these are from the recyclebin

select * from recyclebin;

to remove them use the Purge command

purge Table Partition BIN$SehH2WsDSUOowSrN47+PWw==$0;

or purge the complete recyclebin

PURGE RECYCLEBIN;

Truncate partition

ALTER TABLE twopartition
TRUNCATE PARTITION P_REST;

Select * from twopartition partition(P_REST);

VMWARE Performance Tuning

VMWARE Performance Tuning

1-      VM settings –> Option –> Version (is the same as the OS installed)

2-      VMWare tools installed and it install the required drivers

3-      Disconnect CD room if not in use

4-      Fade effect is displaying menu

a.       Appearance –> effect –> deselect (use the following transition effect)

5-      Enable hardware acceleration as it is by default is disabled

a.       Desktop –> properties –> settings –> advanced –> troubleshoot –> slide the hardware acceleration to full.