Oracle Performance Tuning-15

Performance Tuning

Optimizer mode. It will collect the statistics of the object.

Optimizer will 1.transform the query – parsed 2.estimate 3. Generate a plan.

1. Rule based optimizer (RBO) – deprecated from 10g onwards.
2. Cost based optimizer (CBO)

Keyword

RBO – Rule. 15 objects can be gathered.

CBO – Choose (or) All_rows, First_rows & first_rows_n. More than 256 objects can be gathered.

The cost based optimizer will always select the low cost. It may be an index scan or table scan, which is lower.

Parameters

Choose / All_rows – It will gather the stats for all the rows of the table.

First_rows – Only first row of the table

First_rows_n – The will gather the number of given rows. It mostly used for a larger tables.

Statistics

Optimizer statistics are a collection of data that describe more details about the database objects.

For an object, the following will be taken.

Cost – Number of resources, like CPU,RAM etc

Cardinal – No. of rows

Bytes- Size of the rows

DBblockgets- Reading data from DBC (Select)

Consistentgets- Reading data from DBC (DML)

Physical reads – Reading data from data file

Physical writes – Writing data to data file

Logical reads- DBblockgets + Consistentgets

 

Find out the current optimizer mode

Sql> show parameter optimizer_mode;

Sql> alter system set optimizer_mode =all_rows/first_rows/rule;

 

Sql> show parameter timed_statistics; – Default will be false

Sql> alter system set timed_statistics =true; – It will gather stats auto.

STATISTICS_LEVEL is set to TYPICAL or ALL, then true & BASIC, then false

How to gather stats

DB: level:

sql> exec dbms_stats.gather_database_stats;

Schema level – Mostly in real time.

sql> exec dbms_stats.gather_schema_stats (‘PRAPHU’, cascade => true); – cascade will gather indexes also.

Table: level:

sql> exec dbms_stats.gather_table_stats (‘PRAPHU’, ‘EMP’,cascade => true);

OR

sql> Analyze table emp compute statistcs; — Old version

 

Last stats analyzed for a table:

sql> select table_name,last_analyzed from dba_tables where owner= ‘PRABU’;

We mostly gather stats in non-business hours on daily basis. Stats are stored in system tablespace.

 

Query Tuning / SQL / Application Tuning:

Sql> Select * from emp where eno =101;

Methods to find the query problem:

1. Explain plan – Query level – Real time used
2. TK proof – Query level
3. Auto trace – Query level
4. AWR – Server level
5. DMVs – Server level
6. OEM – Need to write

Possible checks and fix

Code level:

1. Check the execution plan for the query by using anyone above methods
2. Check the index has been fulfilled
3. Check the stats are updated
4. Check the index fragmentation
5. Check the row chaining, Row migration & HWM

Server level:

1. Run the v$ view and DMV to find the problem like blocking, resource usages
2. Run the AWR, ADDM & ASH report and analysis the issue

 

Explain Plan:

It will display the execution of query, without actual run.

Sql> @?/rdbms/admin/utlxplan.sql; – It will create a plan table.

Sql> explain plan for select * from emp where eno=101; – It will save the data in plan_table

Sql> @?/rdbms/admin/utlxpls.sql;

OR

Sql> select * from table (dbms_xplan.display);

OR

Sql> select * from plan_table;

 

TK Prof

It’s a utility. It used to convert the trace to human readable file.

It will give the actual query execution details.

Keyword: TKprof

Sql> show parameter user_dump_dest;

Sql> alter session set sql_trace=true;

Alter system set – Permeant.

Alter session set – Temporary, only for a session.

ALTER SESSION SET SQL_TRACE = TRUE;

DBMS_SESSION.SET_SQL_TRACE(TRUE);

A DBA may enable tracing for another user’s session by using the following statement:

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);

 

Sql> select * from emp where eno=101; – Note the time and check the udump location.

Sql> alter session set sql_trace=flase; – Otherwise it will create a dump file for each query for a session.

$ TKprof /opt/oracle/diag/rdbms/prabu/trace/oracle123.trc /opt/output.txt – It will change the trace file to meaning full one.

$ vi /opt/output.txt

Analyze and apply the result to the following formula.

Formula: [1- Physical reads (PR)/(dbblockgets+consistentgets]*100

If the result is >90% good. < 90% bad query.

Auto Trace

It will trace all the queries.

Options:

autotrace on – Enables all options.

autotrace on explain – Displays returned rows and the explain plan.

autotrace on statistics – Displays returned rows and statistics.

autotrace trace explain – Displays the execution plan for a select statement without actually executing it. “set autotrace trace explain”

autotrace traceonly – Displays execution plan and statistics without displaying the returned rows. This option should be used when a large result set is expected.

 

Sql> set timing on;

01.40.00 sql> set autotrace on;

01.41.00 sql> analyze table emp compute statistics; – Optional

sql> select *from emp where eno =101;

Three output will display 1. Query output+ Statistics + table access full or index scan

We need to create an index for eno, the index was created still the output was slow.

 

Explain plan	Tkprof	Auto Trace
execution of query, without actual run	execution of query, actual run	Trace all query

 

Check the fragmentation:

When to rebuild: — https://blogs.oracle.com/sysdba/entry/when_to_rebuild_index

 

For that we need to validate the index structure.

Sql> analyze index ind_eno validate structure;

Sql> select [(del_lf_rows_len)/(lf_rows_len)]*100 from index_stats where name=’IND_ENO’;

< 20 % – Good & > 20% need to rebuild.

(OR)

Sql> select blevel from dba_indexes where index_name =’IND_ENO’;

< 3 % – Good & > 3% need to rebuild.

Sql> alter index IND_ENO rebuild;

OR

Sql> alter index ind_eno rebuild online;

Note: Currently if the index is used by a user, we must do an online rebuild. In case, if we have not checked, it will take more time to rebuild.

10G features:

ASSM- Automatic segment space management.

It’s a query tuning based feature. We can do a shrink online.

Sql> alter table emp enable row movement;

Sql> alter table emp shrink space;

We can shrink the table by using above two methods.  Internally, it will move the data block by block and there will be slowness.

Sql> alter table emp shrink space compact;  – Compact only work, when the table is not used by anyone.

Sql> alter table emp shrink space cascade;  – It will shrink both  table and indexes.

We can use advisory to use the better option.

Sql> select * from dba_advisor_logs;

Sql> alter table emp disable row movement;

Best practice: Use alter shrink.

 

Check the Row chaining:

http://www.dba-oracle.com/t_identify_chained_rows.htm

 

Sql> @?/rdbms/admin/utlchain.sql; – It will create a chained_rows table

Sql> analyze table emp list chained rows;

Sql> select count(*) from chained_rows;

0 – Good & >0 – Row chaining and migration problem.

(OR)

Sql> analyze table emp compute statistics;

Sql> select blocks,empty_blocks,chained_cnt from dba_tables where table_name=’EMP’;

Blocks – 0 good & >0 Below HWM problem

Empty_Blocks – 0 good & >0 Above HWM problem

Chained_cnt – 0 good & >0 Problem in RC & RM.

spool chain.lst;

set pages 9999;

column c1 heading “Owner”   format a9;

column c2 heading “Table”   format a12;

column c3 heading “PCTFREE” format 99;

column c4 heading “PCTUSED” format 99;

column c5 heading “avg row” format 99,999;

column c6 heading “Rows”    format 999,999,999;

column c7 heading “Chains”  format 999,999,999;

column c8 heading “Pct”     format .99;

set heading off;

select ‘Tables with migrated/chained rows and no RAW columns.’ from dual;

set heading on;

select

owner              c1,

table_name         c2,

pct_free           c3,

pct_used           c4,

avg_row_len        c5,

num_rows           c6,

chain_cnt          c7,

chain_cnt/num_rows c8

from dba_tables

where

owner not in (‘SYS’,’SYSTEM’)

and

table_name not in

(select table_name from dba_tab_columns

where

data_type in (‘RAW’,’LONG RAW’,’CLOB’,’BLOB’,’NCLOB’)

)

and

chain_cnt > 0

order by chain_cnt desc

;

Solution:

Sql> alter table emp deallocate unused; – Not needed, Since it will remove only the above HWM.

So, following is the methods of fix.

1. Move tables between tablespaces, then rebuild index. (The move will make an index to unusable)
2. Move table within tablespace, then rebuild
3. Use Query to recreate a table ‘create table as select * from’

1.

Sql> alter table emp move tablespace tools;

Sql> alter index ind_eno rebuild;

OR

Sql> alter index ind_eno rebuild tablespace tools; – The index will move and rebuild.

2. Mostly used in real time

Check we have good free space in the tablespace.

Sql> alter table emp move;

Sql> alter index ind_eno rebuild;

OR

Sql> alter index ind_eno rebuild tablespace tools;

 

Note: We need to do this in maintenance window.  From 10g onwards we can do this online.

Sql> create table emp1 as select * from emp;

Sql> drop table emp;

Sql> rename table emp1 to emp;

Note: The dependencies will not move, we need to manually create them.

 

Rebuild Index	Coalesce	Shrink
Exclusive lock	Online	Lock
Recreates, 2*space	shuffles, to get empty block	Shrink empty blocks
Variation size	Same index size	Variation size

 

Oracle Advisory for Rebuild, Shrink & Coalesce

https://docs.oracle.com/cd/E18283_01/server.112/e17120/schema003.htm

 

variable id number;

begin

declare

name varchar2(100);

descr varchar2(500);

obj_id number;

begin

name:=’Manual_Employees’;

descr:=’Segment Advisor Example’;

 

dbms_advisor.create_task (

advisor_name     => ‘Segment Advisor’,

task_id          => :id,

task_name        => name,

task_desc        => descr);

 

dbms_advisor.create_object (

task_name        => name,

object_type      => ‘TABLE’,

attr1            => ‘HR’,

attr2            => ‘EMPLOYEES’,

attr3            => NULL,

attr4            => NULL,

attr5            => NULL,

object_id        => obj_id);

 

dbms_advisor.set_task_parameter(

task_name        => name,

parameter        => ‘recommend_all’,

value            => ‘TRUE’);

 

dbms_advisor.execute_task(name);

end;

end;