Oracle re-indexing Shell script, SQL script sharing, oracleshell
Indexing is a powerful weapon to improve database query performance. Without an index, it is like a library without a book tag. It is more difficult to find a book you want than to climb to the sky. However, in the process of using indexes, especially in the case of batch DML, the corresponding fragments will be generated, and the B-tree height will change accordingly, therefore, you can reconstruct indexes with large changes to improve performance. N a long time ago, Oracle recommended that we regularly recreate indexes on tables with a height of 4 and deleted index entries accounting for at least 20% of the total number of existing index entries. However, Oracle strongly recommends that you do not rebuild indexes on a regular basis. For details, refer to the article: the necessity of re-indexing Oracle. However, it is necessary to re-build the index, but it is not recommended to regularly. This article provides a script for re-indexing for your reference.
1. re-create the index shell script
robin@SZDB:~/dba_scripts/custom/bin> more rebuild_unbalanced_indices.sh # +-------------------------------------------------------+# + Rebulid unblanced indices |# + Author : Leshami | # + Parameter : No |# +-------------------------------------------------------+#!/bin/bash # --------------------# Define variable# --------------------if [ -f ~/.bash_profile ]; then. ~/.bash_profilefiDT=`date +%Y%m%d`; export DTRETENTION=1LOG_DIR=/tmpLOG=${LOG_DIR}/rebuild_unbalanced_indices_${DT}.logDBA=Leshami@12306.cn# ------------------------------------# Loop all instance in current server# -------------------------------------echo "Current date and time is : `/bin/date`">>${LOG}for db in `ps -ef | grep pmon | grep -v grep |grep -v asm |awk '{print $8}'|cut -c 10-`do echo "$db" export ORACLE_SID=$db echo "Current DB is $db" >>${LOG} echo "===============================================">>${LOG} $ORACLE_HOME/bin/sqlplus -S /nolog @/users/robin/dba_scripts/custom/sql/rebuild_unbalanced_indices.sql>>${LOG}done;echo "End of rebuilding index for all instance at : `/bin/date`">>${LOG}# -------------------------------------# Check log file # -------------------------------------status=`grep "ORA-" ${LOG}`if [ -z $status ];then mail -s "Succeeded rebuilding indices on `hostname` !!!" ${DBA} <${LOG}else mail -s "Failed rebuilding indices on `hostname` !!!" ${DBA} <${LOG}fi# ------------------------------------------------# Removing files older than $RETENTION parameter # ------------------------------------------------find ${LOG_DIR} -name "rebuild_unb*" -mtime +$RETENTION -exec rm {} \;exit
2. re-create the SQL script called by the index
robin@SZDB:~/dba_scripts/custom/sql> more rebuild_unbalanced_indices.sql conn / as sysdbaset serveroutput on;DECLARE resource_busy EXCEPTION; PRAGMA EXCEPTION_INIT (resource_busy, -54); c_max_trial CONSTANT PLS_INTEGER := 10; c_trial_interval CONSTANT PLS_INTEGER := 1; pmaxheight CONSTANT INTEGER := 3; pmaxleafsdeleted CONSTANT INTEGER := 20; CURSOR csrindexstats IS SELECT NAME, height, lf_rows AS leafrows, del_lf_rows AS leafrowsdeleted FROM index_stats; vindexstats csrindexstats%ROWTYPE; CURSOR csrglobalindexes IS SELECT owner,index_name, tablespace_name FROM dba_indexes WHERE partitioned = 'NO' AND owner IN ('GX_ADMIN'); CURSOR csrlocalindexes IS SELECT index_owner,index_name, partition_name, tablespace_name FROM dba_ind_partitions WHERE status = 'USABLE' AND index_owner IN ('GX_ADMIN'); trial PLS_INTEGER; vcount INTEGER := 0;BEGIN trial := 0; /* Global indexes */ FOR vindexrec IN csrglobalindexes LOOP EXECUTE IMMEDIATE 'analyze index ' || vindexrec.owner ||'.'|| vindexrec.index_name || ' validate structure'; OPEN csrindexstats; FETCH csrindexstats INTO vindexstats; IF csrindexstats%FOUND THEN IF (vindexstats.height > pmaxheight) OR ( vindexstats.leafrows > 0 AND vindexstats.leafrowsdeleted > 0 AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) > pmaxleafsdeleted) THEN vcount := vcount + 1; DBMS_OUTPUT.PUT_LINE ( 'Rebuilding index ' || vindexrec.owner ||'.'|| vindexrec.index_name || '...'); <<alter_index>> BEGIN EXECUTE IMMEDIATE 'alter index ' || vindexrec.owner ||'.' || vindexrec.index_name || ' rebuild' || ' parallel nologging compute statistics' || ' tablespace ' || vindexrec.tablespace_name; EXCEPTION WHEN resource_busy OR TIMEOUT_ON_RESOURCE THEN DBMS_OUTPUT.PUT_LINE ( 'alter index - busy and wait for 1 sec'); DBMS_LOCK.sleep (c_trial_interval); IF trial <= c_max_trial THEN GOTO alter_index; ELSE DBMS_OUTPUT.PUT_LINE ( 'alter index busy and waited - quit after ' || TO_CHAR (c_max_trial) || ' trials'); RAISE; END IF; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('alter index err ' || SQLERRM); RAISE; END; END IF; END IF; CLOSE csrindexstats; END LOOP; DBMS_OUTPUT.PUT_LINE ('Global indices rebuilt: ' || TO_CHAR (vcount)); vcount := 0; trial := 0; /* Local indexes */ FOR vindexrec IN csrlocalindexes LOOP EXECUTE IMMEDIATE 'analyze index ' || vindexrec.index_owner||'.' || vindexrec.index_name || ' partition (' || vindexrec.partition_name || ') validate structure'; OPEN csrindexstats; FETCH csrindexstats INTO vindexstats; IF csrindexstats%FOUND THEN IF (vindexstats.height > pmaxheight) OR ( vindexstats.leafrows > 0 AND vindexstats.leafrowsdeleted > 0 AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) > pmaxleafsdeleted) THEN vcount := vcount + 1; DBMS_OUTPUT.PUT_LINE ( 'Rebuilding index ' || vindexrec.index_owner||'.'|| vindexrec.index_name || '...'); <<alter_partitioned_index>> BEGIN EXECUTE IMMEDIATE 'alter index ' || vindexrec.index_owner||'.' || vindexrec.index_name || ' rebuild' || ' partition ' || vindexrec.partition_name || ' parallel nologging compute statistics' || ' tablespace ' || vindexrec.tablespace_name; EXCEPTION WHEN resource_busy OR TIMEOUT_ON_RESOURCE THEN DBMS_OUTPUT.PUT_LINE ( 'alter partitioned index - busy and wait for 1 sec'); DBMS_LOCK.sleep (c_trial_interval); IF trial <= c_max_trial THEN GOTO alter_partitioned_index; ELSE DBMS_OUTPUT.PUT_LINE ( 'alter partitioned index busy and waited - quit after ' || TO_CHAR (c_max_trial) || ' trials'); RAISE; END IF; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ( 'alter partitioned index err ' || SQLERRM); RAISE; END; END IF; END IF; CLOSE csrindexstats; END LOOP; DBMS_OUTPUT.PUT_LINE ('Local indices rebuilt: ' || TO_CHAR (vcount));END;/exit;
3. Input log sample
Current date and time is : Sun Apr 20 02:00:02 HKT 2014 Current DB is SYBO2 ===============================================Rebuilding index GX_ADMIN.SYN_OUT_DATA_TBL_PK...Rebuilding index GX_ADMIN.IDX_TDBK_SPLNK_PARENT_REF...Rebuilding index GX_ADMIN.IDX_TDBK_SPLNK_CHILD_REF...Rebuilding index GX_ADMIN.PK_TRADE_BROKER_TBL...Rebuilding index GX_ADMIN.IDX_TDBK_INPUT_DATE... ................
4. Postscript
A. If there are multiple instances on the same server and each instance has the same schema, this script will patrol all instances and rebuild Based on the analyze results.
A. You should make adjustments as needed, such as the script path information.
B. Modify the schema name.
D. Adjust the degree of parallelism according to the system environment.
Write a shell script in linux to extract data with specified conditions from the oracle database and store the data in a text file.
Empno = 100 does not exist. It is changed to some data.
#! /Bin/bash
Result = $ (sqlplus-s 'Scott/tiger @ dbname' <EOF
Spool test.txt
Set pages 0
Set feed off
Set heading off;
Set feedback off;
Set verify off;
Set linesize 1000;
SELECT * FROM scott. emp where empno = 7369;
Spool off
EOF
)
Echo $ result
~
~
~
~
~
~
~
~
~
"Test. sh" 14L, 256C written
Oracle @ ****: ~> ./Test. sh
7369 smith clerk 7902 17-DEC-80 2240.06 20
Oracle @ ****: ~> More test.txt
7369 smith clerk 7902 17-DEC-80 2240.06
20
How can shell scripting be used with oracle?
Empno = 100 does not exist. It is changed to some data.
#! /Bin/bash
Result = $ (sqlplus-s 'Scott/tiger @ dbname' <EOF
Spool test.txt
Set pages 0
Set feed off
Set heading off;
Set feedback off;
Set verify off;
Set linesize 1000;
SELECT * FROM scott. emp where empno = 7369;
Spool off
EOF
)
Echo $ result
~
~
~
~
~
~
~
~
~
"Test. sh" 14L, 256C written
Oracle @ ****: ~> ./Test. sh
7369 smith clerk 7902 17-DEC-80 2240.06 20
Oracle @ ****: ~> More test.txt
7369 smith clerk 7902 17-DEC-80 2240.06
20