1.2 S ITUACIÓN ACTUAL
1.2.1 Aeropuerto de Lima
1.2.1.4 Pilares estratégicos
Rebuild Indexes
When I first started doing DBA work, I was thrilled to find the "analyze index .... validate structure" command. This command puts information about a specific index in the view index_stats. The problem with using this command is that while the analyze is running, the index is locked. This prompted me look for other signs that an index needs to be rebuilt. Now that I am working under a tight space constraint, I've come back to that first "cool" command I learned.
This tells a lot about the index, but what interests me is the space the index is taking, what percentage of that is really being used, and what space is unusable because of delete actions. Remember that when rows are deleted, the space is not re-used in the index. Let's check one:
analyze index john.APPTHIST_CURR_STAT_FK validate structure;
select btree_space,pct_used,del_lf_rows_len from index_stats;
BTREE_SPACE PCT_USED DEL_LF_ROWS_LEN --- --- 19889296 43 5374551
So we see this index is only using 43 percent of the almost 19M allocated to it and that it holds over 5M of space that it cannot use because of deletes. This is a candidate to rebuild. Of course, we don't want to rebuild one at a time. You can use the following script to rebuild all of them automatically::
set serveroutput on declare
v_MaxHeight integer := 3;
v_MaxLeafsDeleted integer := 20;
v_Count integer := 0;
--Cursor to Manage NON-Partitioned Indexes cursor cur_Global_Indexes is
select index_name, tablespace_name from user_indexes
where partitioned = 'NO';
--Cursor to Manage Current Index cursor cur_IndexStats is
select name, height, lf_rows as leafRows, del_lf_rows as leafRowsDeleted
select name, height, lf_rows as leafRows, del_lf_rows as leafRowsDeleted from index_stats;
v_IndexStats cur_IndexStats%rowtype;
--Cursor to Manage Partitioned Indexes cursor cur_Local_Indexes is
select index_name, partition_name, tablespace_name from user_ind_partitions
where status = 'USABLE';
begin
DBMS_OUTPUT.ENABLE(1000000);
/* Global or Standard Indexes Section */
for v_IndexRec in cur_Global_Indexes loop
begin
dbms_output.put_line('before analyze ' || v_IndexRec.index_name);
execute immediate 'analyze index ' || v_IndexRec.index_name || ' validate structure';
dbms_output.put_line('After analyze ');
(v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then begin
dbms_output.put_line('Rebuilding index ' || v_IndexRec.index_name || ' with ' || to_char(v_IndexStats.height) || ' height and '
|| to_char(trunc(v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows)) || ' % LeafRows');
/*
--- Commented line was needed for Oracle 9i
--- On 10g Oracle now automatically collects statistics during index creation and rebuild execute immediate 'alter index ' || v_IndexRec.index_name ||
dbms_output.put_line('The index ' || v_IndexRec.index_name || ' WAS NOT ANALYZED');
end;
end loop;
dbms_output.put_line('Global or Standard Indexes Rebuilt: ' || to_char(v_Count));
v_Count := 0;
/* Local indexes Section */
for v_IndexRec in cur_Local_Indexes loop
execute immediate 'analyze index ' || v_IndexRec.index_name ||
' partition (' || v_IndexRec.partition_name ||
') validate structure';
open cur_IndexStats;
fetch cur_IndexStats into v_IndexStats;
if cur_IndexStats%found then
if (v_IndexStats.height > v_MaxHeight) OR
(v_IndexStats.leafRows > 0 and v_IndexStats.leafRowsDeleted > 0 AND
(v_IndexStats.leafRowsDeleted * 100 / v_IndexStats.leafRows) > v_MaxLeafsDeleted) then v_Count := v_Count + 1;
dbms_output.put_line('Rebuilding Index ' || v_IndexRec.index_name || '...');
/* execute immediate 'alter index ' || v_IndexRec.index_name ||
' rebuild' ||
' partition ' || v_IndexRec.partition_name ||
' parallel nologging compute statistics' ||
' tablespace ' || v_IndexRec.tablespace_name;
*/
end if;
end if;
close cur_IndexStats;
end loop;
dbms_output.put_line('Local Indexes Rebuilt: ' || to_char(v_Count));
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL ;
end;
/
Make a Script
The drawback you will see when working with index_stats is that it only holds one row at a time. So we will first create a table to hold the results from this view:
create table t_ind_used_size (owner varchar2(30) ,name varchar2(30)
,name varchar2(30) ,btree_space number(12) ,pct_used number(3) ,del_len number(12) ,dt date )
tablespace xxx
storage (initial 256k next 256k pctincrease 0) pctused 80 pctfree 0;
Now I know that what I want to do is to check each index for a given owner:
declare
v_stmt varchar2(100);
cursor c1 is
select owner,index_name from dba_indexes where owner = 'JOHN';
begin
for line in c1 loop
v_stmt := 'analyze index '||line.owner||'.'||line.index_name||
' validate structure';
execute immediate v_stmt;
insert into t_ind_used_size
(owner,name,btree_space,pct_used,del_len,dt)
select line.owner,name,btree_space,pct_used,del_lf_rows_len,sysdate from index_stats;
if mod(c1%rowcount,100)=0 then commit;
end if;
end loop;
commit;
end;
/
Our cursor gives us all of the indexes for this owner. You can also take the "where" clause off the cursor and get all indexes.For each index, we create the analyz e statement and then execute it using dynamic SQL. The results from this analyz e statement are then put into our table for reference later.
Remember that the "analyz e" will lock the index, so be sure to run this operation during off- hours. I have 372 indexes taking 2564M of space, and this script takes 7:40 to complete. Not too bad.
Now Let 's Use t he Inf ormat ion
So we have gathered all of this information. We can just look at it to get an overview with:
variable block_size number;
begin
select value into :block_size from v$parameter where name = 'db_block_size';
end;
/
select * from t_ind_used_size
where btree_space > :block_size order by pct_used DESC;
Notice that I am only interested in the indexes that are taking more than one block of space. Any indexes currently taking one block cannot be improved,
Notice that I am only interested in the indexes that are taking more than one block of space. Any indexes currently taking one block cannot be improved, no matter what percentage is being used. I have 176 rows from this query with the indexes making the least efficient use of space at the bottom of the result set.
You will notice that we have the date in the table, too, so we can compare over time with the following:
select a.owner,a.name,a.dt,a.pct_used,b.dt,b.pct_used from t_ind_used_size a, t_ind_used_size b
where a.owner = b.owner and a.name = b.name and a.pct_used>1.1*b.pct_used and a.dt >= (b.dt - 7);
This would show us the indexes that have dropped their percent used by more than 10 percent during the last seven days. Here we are assuming that you would run this periodically (daily or weekly).
I am not so much interested in the change over time than in the use of space right now. We want to reclaim space, so we will rebuild all of the indexes that have too much unused space. For "too much," I have chosen indexes that are using less than 75 percent of their space held or that have more than one block of delete space that is unusable.
My "where" clause for this is:
select count(1)
from t_ind_used_size a, dba_indexes b where btree_space > :block_size
and (pct_used < 75 or del_len > :block_size) and a.owner = b.owner and a.name = b.index_name order by pct_used;
COUNT(1) ---46
I join my table with dba_indexes so I can get more information on how the index is created. We now have 46 indexes that are candidates for a rebuild.
For each index we want to; rebuild, analyz e to get new statistics, and then remove the index from the t_ind_used_siz e table so we don't do it again. It will take me forever to alter each one manually so we make a script to do it for us:
select 'alter index '||a.owner||'.'||a.name||
' rebuild tablespace '||b.tablespace_name||chr(10)||
'storage (initial '||initial_extent||
' next '||next_extent||
' pctincrease '||pct_increase||') pctfree 0 nologging;'||chr(10)||
'analyze index '||a.owner||'.'||a.name||
' compute statistics;'||chr(10)||
'delete t_ind_used_size where name = '''||a.name||
''' and owner = '''||a.owner||''';'||chr(10)||
'commit;'
from t_ind_used_size a, dba_indexes b where btree_space > :block_size
and (pct_used < 75 or del_len > :block_size) and a.owner = b.owner and a.name = b.index_name order by pct_used;
This gives us 46 rows like:
alter index JOHN.APPTHIST_CURR_STAT_FK rebuild tablespace LOCAL1M_IDX storage (initial 1048576 next 1048576 pctincrease 0) pctfree 0 nologging;
analyze index JOHN.APPTHIST_CURR_STAT_FK compute statistics;
delete t_ind_used_size where name = 'APPTHIST_CURR_STAT_FK' and owner = 'JOHN';
commit;
So you see, we will rebuild in the same tablespace, analyz e, delete the row, and move on to the next.
Problem Solved
This takes care of the two problems I started with. I know when to rebuild my large indexes based on the percent used and delete space. If there is only lookup activity against a large index, I'll never rebuild it once it is the right siz e.
I can also take care of those active indexes that are holding deleted space that is unusable.
You can pick any numbers for your limits but a block of deleted space and 75 percent usage seemed reasonable to me. I don't want to be rebuilding all indexes every week.
This script can, of course, just be added to you weekly processing. Just spool out the output and then execute that created script. With this, when you again start getting tight for space, you know you really are tight.
HINTS
You should first get the explain plan of your SQL and determine what changes can be done to make the code operate without using hints if possible. However, Oracle hints such as ORDERED, LEADING, INDEX, FULL, and the various AJ and SJ Oracle hints can tame a wild optimizer and give you optimal performance.
Some suggestions:
- Use ALIASES for the tablenames in the hints.
- Ensure tables containst up-to-date statistics
- Syntax: /*+ HINT HINT ... */ (In PLSQL the space between the '+' and the first letter of the hint is vital so /*+ ALL_ROWS */ is fine but /*+ALL_ROWS */ will cause problems
Here is a list of all the Hints:
Oracle Hint Meaning
+ Must be immediately after comment indicator, tells Oracle this is a list of hints.
ALL_ROWS Use the cost based approach for best throughput.
CHOOSE Default, if statistics are available will use cost, if not, rule.
FIRST_ROWS Use the cost based approach for best response time.
RULE Use rules based approach; this cancels any other hints specified for this statement.
Access Method Oracle Hints:
CLUSTER(table) This tells Oracle to do a cluster scan to access the table.
FULL(table) This tells the optimizer to do a full scan of the specified table.
HASH(table) Tells Oracle to explicitly choose the hash access method for the table.
HASH_AJ(table) Transforms a NOT IN subquery to a hash anti-join.
ROWID(table) Forces a rowid scan of the specified table.
INDEX(table [index]) Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.
INDEX_ASC (table [index]) Same as INDEX only performs an ascending search of the index chosen, this is functionally identical to the INDEX statement.
INDEX_DESC(table [index]) Same as INDEX except performs a descending search. If more than one table is accessed, this is ignored.
INDEX_COMBINE(table
index) Combines the bitmapped indexes on the table if the cost shows that to do so would give better performance.
INDEX_FFS(table index) Perform a fast full index scan rather than a table scan.
MERGE_AJ (table) Transforms a NOT IN subquery into a merge anti-join.
AND_EQUAL(table_name
index_Name1) This hint causes a merge on several single column indexes. Two must be specified, five can be.
NL_AJ Transforms a NOT IN subquery into a NL anti-join (nested loop).
HASH_SJ(t1, t2) Inserted into the EXISTS subquery; This converts the subquery into a special type of hash join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
MERGE_SJ (t1, t2) Inserted into the EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
NL_SJ Inserted into the EXISTS subquery; This converts the subquery into a special type of nested loop join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
Oracle Hints for join orders and transformations:
ORDERED This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join.
STAR Forces the largest table to be joined last using a nested loops join on the index.
STAR_TRANSFORMATION Makes the optimizer use the best plan in which a start transformation is used.
FACT(table) When performing a star transformation use the specified table as a fact table.
NO_FACT(table) When performing a star transformation do not use the specified table as a fact table.
PUSH_SUBQ This causes nonmerged subqueries to be evaluated at the earliest possible point in the execution plan.
REWRITE(mview) If possible forces the query to use the specified materialized view, if no materialized view is specified, the system chooses what it calculates is the appropriate view.
NOREWRITE Turns off query rewrite for the statement, use it for when data returned must be concurrent and can�t come from a materialized view.
USE_CONCAT Forces combined OR conditions and IN processing in the WHERE clause to be transformed into a compound query using the UNION ALL set operator.
NO_MERGE (table) This causes Oracle to join each specified table with another row source without a sort-merge join.
NO_EXPAND Prevents OR and IN processing expansion.
Oracle Hints for Join Operations:
USE_HASH (table) This causes Oracle to join each specified table with another row source with a hash join.
USE_NL(table) This operation forces a nested loop using the specified table as the controlling table.
USE_MERGE(table,[table,]) This operation forces a sort-merge-join operation of the specified tables.
DRIVING_SITE The hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.
LEADING(table) The hint causes Oracle to use the specified table as the first table in the join order.
Oracle Hints for Parallel Operations:
[NO]APPEND This specifies that data is to be or not to be appended to the end of a file rather than into existing free space. Use only with INSERT commands.
NOPARALLEL (table This specifies the operation is not to be done in parallel.
PARALLEL(table, instances) This specifies the operation is to be done in parallel.
PARALLEL_INDEX Allows parallelization of a fast full index scan on any index.
Other Oracle Hints:
CACHE Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list when the table is full table scanned.
NOCACHE Specifies that the blocks retrieved for the table in the hint are placed at the least recently used end of the LRU list when the table is full table scanned.
[NO]APPEND For insert operations will append (or not append) data at the HWM of table.
UNNEST Turns on the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to FALSE.
Turns off the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set
NO_UNNEST to TRUE.
PUSH_PRED Pushes the join predicate into the view.
ALL_ROWS:
This is the cost-based approach designed to provide the best overall throughput and minimum resource consumption. It's the default option of Oracle
select /*+ ALL_ROWS */ COMPANY.Name from COMPANY, SALES
where COMPANY.Company_ID = SALES.Company_ID and SALES.Period_ID =3
and SALES.Sales_Total>1000;
This example will usually execute NESTED LOOPS. The ALL_ROWS forces the optimizer to use a MERGE JOIN.
AND-EQUAL:
Causes merge scans of two to five single-column indexes.:
select /*+ AND-EQUAL COMPANY$CITY, COMPANY$STATE */Name, City, State from COMPANY
where City = 'Roanoke' and State = 'VA';
CLUSTER:
Requests a cluster scan of the table_name:
/*+ CLUSTER(table) */
FIRST_ROWS:
This hint is the opposite of ALL_ROWS. It tells the the optimizer to return the rows as fast as it can, even if it needs to perform more I/O operations:
select /*+ FIRST_ROWS */ COMPANY.Name from COMPANY, SALES
where COMPANY.Company_ID = SALES.Company_ID and SALES.Period_ID =3
and SALES.Sales_Total>1000;
FULL:
It performs a FULL ACCESS to the table. Yoy may want to use it if you know that the distribution of the data is not good.
select /*+ FULL(COMPANY) */ Name, City, State from COMPANY
where City = 'Roanoke' and State = 'VA';
HASH:
Causes a hash scan
/*+ HASH(table) */
INDEX(table_name index_name):
It can be used in 3 different ways:
1. If only one index is mentioned, it will use that index
2. If you mention more than one index, the optimizer will decide which one to use.
3. If you mention just a table, the optimizer will decide wich index to use on that table.
select /*+ INDEX(COMPANY) */ Name, City, State from COMPANY
where City = 'Roanoke' and State = 'VA';
INDEX_ASC(table_name index_name):
It will use the indicated index in ASC order.
INDEX_DESC(table_name index_name):
It will use the indicated index in DESC order.
NO_MERGE:
This hint is used in a view to prevent it from being merged into a parent query.
NOCACHE
This hint causes the table CACHE option to be bypassed.
ORDERED:
Requests that the tables should be joined in the order that they are specified (left to right). For example, if you know that a state table has only 50 rows, you may want to use this hint to make state the driving table.
ROWID:
Requests a rowid scan of the specified table.
RULE:
Indicates that the rule-based optimizer should be invoked (sometimes due to the absence of table statistics)
select /*+ RULE */ COMPANY.Name from COMPANY, SALES
where COMPANY.Company_ID = SALES.Company_ID and SALES.Period_ID =3
and SALES.Sales_Total>1000;
USE_HASH (table_name1 table_name2):
Requests a hash JOIN against the specified tables.
USE_NL (table_name):
Requests a nested loop operation with the specified table as the driving table.
select /*+ USE_NL(COMPANY) */ COMPANY.Name
from COMPANY, SALES
where COMPANY.Company_ID = SALES.Company_ID and SALES.Period_ID =3
and SALES.Sales_Total>1000;
USE_MERGE:
It is the opposite of ISE_NL. It tells the optimizer to use a MERGE JOIN between the tables mentioned there.
select /*+ USE_MERGE(COMPANY, SALES) */ COMPANY.Name from COMPANY, SALES
where COMPANY.Company_ID = SALES.Company_ID and SALES.Period_ID =3
and SALES.Sales_Total>1000;
*****************************************
** Parallel Execution **
** Note: Oracle ignores parallel **
** hints on a temporary table. **
*****************************************
/*+ APPEND */
/*+ NOAPPEND */
Specifies that data is simply appended (or not) to a table; existing free space is not used. Use these hints only following the INSERT keyword.
/*+ NOPARALLEL(table) */
Disables parallel scanning of a table, even if the table was created with a PARALLEL clause.
/*+ PARALLEL(table)
/*+ PARALLEL(table integer) */
Lets you specify parallel execution of DML and queries on the table; integer specifies the desired degree of parallelism, which is the number of parallel
threads that can be used for the operation. Each parallel thread may use one or two parallel execution servers. If you do not specify integer, Oracle
computes a value using the PARALLEL_THREADS_PER_CPU parameter. If no parallel hint is specified, Oracle uses the existing
computes a value using the PARALLEL_THREADS_PER_CPU parameter. If no parallel hint is specified, Oracle uses the existing