-- (c) Kyle Hailey 2007 OPTS=${1:-2} # -1 - drop REPO tables # 0 - create TABLES, # 1 - create package ash on target # 2 - create Tables and packages # package is created locally on TARG, tables are created remotely on REPO # local schema to install package, should be SYS SCHEMA=sys PW="sys as sysdba" TARG_HOST=`hostname` TARG_SID=$ORACLE_SID TARG_HOME=$ORACLE_HOME # REPOSITORY - need schema/passsword and HOST and SID REPO_SCHEMA=ash REPO_PW=ash REPO_SID=cdb REPO_HOST=cont01 REPO_PORT=1521 # number of partitions, based on day of week Sun =1 Sat=7 nparts=7 # HOW TO USE: # -- collect data 30 times at 1 sec intervals # exec ash.print(1,30); # # -- collect data into v$ash every sec for an hour # -- Oracle job runs this every hour # variable job number # exec dbms_job.submit(:job,'ash.collect(1,3600);',sysdate,'trunc(sysdate+(1/(24)),''HH'')'); # -- need the commit to actually submit the job # commit; # # -- remove job # -- exec dbms_job.remove(:job); # # -- look at data # select sample_time, session_id, session_state from v$ash; # -- count samples taken # select count(*) from v$ash; # x=1 CREATE=ashcr.sql ASHPKG=ashpkg.sql DROP=ashdr.sql DBL="@REPO" DBLINK="ashdbl.sql" echo "" > $CREATE echo "" > $DROP # ============== CREATE REPO ======================= if [ $OPTS -eq 0 -o $OPTS -eq 2 -o $OPTS -eq -1 ]; then # get database id for use in script DBID=`sqlplus -s "\$SCHEMA/\$PW" << EOF set heading off set feedback off select dbid from sys.v\\$database; EOF` # get rid of the carraige return in the var value if there is one DBID=`echo $DBID | awk '{print $1}'` echo "DBID $DBID" SDATE=`sqlplus -s "\$SCHEMA/\$PW" << EOF set heading off set feedback off select to_char(sysdate+1,'DD-MM-YYYY') from dual; EOF` TNS="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$REPO_HOST)(PORT=$REPO_PORT)))(CONNECT_DATA=(SID=$REPO_SID)))" # create V$ASH_SQLTEXT, V$ASH_SQLSTATS, V$ASH_OBJS for i in 1; do echo " drop table v\$ash_params_all; drop table v\$ash_all; drop table v\$ash_sqlplans_all; drop table v\$ash_sqltxt_all; drop table v\$ash_sqlstats_all; drop table v\$ash_objs_all; drop table v\$ash_event_names_all ; drop table v\$ash_users_all ; drop table v\$ash_latch_all ; drop table dbids; drop view v\$ash ; drop view v\$ash_params_all ; drop view v\$ash_latch ; drop view v\$ash_event_names ; drop view v\$ash_sqlplans; drop view v\$ash_sqltxt; drop view v\$ash_sqlstats; drop view v\$ash_objs; drop view v\$ash_users;" done >> $DROP for i in 1; do echo " create or replace package ashpkg is -- g_offset is how many days in the future to add a new partion g_offset number := 1; g_ash_table varchar2(100) := 'V\$ASH_ALL'; procedure add_subpartition (p_dbid varchar2); procedure part_management (p_table_name varchar2, p_history number); procedure range_partition_job(p_sysdate date default sysdate); end ashpkg; / show err; create or replace package body ashpkg is ----------------------------------------------------------------- -- Create a new subpartition for the most current range partition ----------------------------------------------------------------- procedure add_subpartition (p_dbid varchar2) is l_text varchar2(200); l_seq_val number; -------------------------------------------------------------- -- Only create new subpartitions for the current range partition -------------------------------------------------------------- cursor table_parts is select partition_name from (select partition_name from user_tab_partitions where table_name = g_ash_table order by partition_name desc ) where rownum < 3 ; begin for t in table_parts loop select ash_part_seq.nextval into l_seq_val from dual; l_text := 'alter table '||g_ash_table||' modify partition '||t.partition_name||' add subpartition sp_'||p_dbid||'_'||l_seq_val||' values ('||p_dbid||')'; execute immediate l_text; end loop; exception when others then insert into v\$ashlog (action, message,result) values ('ADD SUBPARTITION', l_text,'E'); commit; raise_application_error(-20010,'Subpartition addition for '|| p_dbid||' errored.'); end; ------------------------------------------------------------------ -- Manage the partitions, drop and add based on p_history. -- New partitions are created based on the last partition, including -- all subpartitions for the last partition. ------------------------------------------------------------------ procedure part_management(p_table_name varchar2, p_history number) is l_text varchar2(1000); l_seq_val number; l_max_part varchar2(100); l_cur_part varchar2(100); cursor table_parts is select partition_name from (select partition_name, rownum rn from (select partition_name from user_tab_partitions where table_name = p_table_name order by 1 desc) ) where rn > p_history; cursor table_subparts is select subpartition_name, partition_name, high_value from user_tab_subpartitions where table_name = g_ash_table and partition_name = (select max(partition_name) from user_tab_partitions where table_name = g_ash_table) order by partition_name desc; begin ----------------------------------------------------------------- -- Drop partitions that are past the p_history limit ----------------------------------------------------------------- for part in table_parts loop l_text := 'alter table ' || p_table_name|| ' drop partition ' || part.partition_name; execute immediate l_text; end loop; ----------------------------------------------------------------- -- Add new partition based on last created partition, including -- the subpartitions. ----------------------------------------------------------------- select max(partition_name) , 'P_'||to_char(sysdate+g_offset,'YYYY_MM_DD') into l_max_part, l_cur_part from user_tab_partitions where table_name = g_ash_table; if (l_max_part < l_cur_part) then l_text := 'alter table '|| p_table_name|| ' add partition P_'|| to_char(sysdate + g_offset,'YYYY_MM_DD')||' values less than (to_date(''' || to_char(sysdate + g_offset + 1,'yyyy-mm-dd') || ''',''yyyy-mm-dd''))'; l_text := l_text||'('; for t in table_subparts loop select ash_part_seq.nextval into l_seq_val from dual; l_text := l_text|| 'subpartition SP_'||t.high_value||'_'|| l_seq_val|| ' values ('||t.high_value||'),'; end loop; l_text := rtrim(l_text, ','); l_text := l_text||')'; execute immediate l_text; else insert into v\$ashlog (action, message,result) values ('ADD PARTITION', 'Range partition already exists for '||trunc(sysdate + g_offset),'E'); end if; exception when others then insert into v\$ashlog (action, message,result) values ('ADD PARTITION', l_text,'E'); commit; RAISE_APPLICATION_ERROR(-20010,'Range partition errored for '|| sysdate + g_offset||'.'); end; -------------------------------------------------------------------- -- Partition management job, add and dropping of partitions -------------------------------------------------------------------- procedure range_partition_job(p_sysdate date default sysdate) is x number; begin dbms_job.submit ( job => x ,what => 'ashpkg.part_management('''||g_ash_table||''',7);' ,next_date => to_date(to_char(p_sysdate,'dd/mm/yyyy hh24:mi:ss'),'dd/mm/yyyy hh24:mi:ss') ,interval => 'trunc(sysdate)+1' ,no_parse => TRUE ); sys.dbms_output.put_line('Job Number: ' || to_char(x)); exception when others then insert into v\$ashlog (action, message,result) values ('RUN JOB', 'Run job: '||to_char(x), 'E'); commit; RAISE_APPLICATION_ERROR(-20010,'Range partition errored for '||sysdate||'.'); end; end ashpkg; / show err " > $ASHPKG echo " create table v\$ash_all ( dbid number, sample_time date, session_id number, session_state varchar2(20), session_serial# number, user_id number, sql_address varchar2(20), sql_id number, sql_opcode number, session_type number, event# number, seq# number, p1 number, p2 number, p3 number, wait_time number, current_obj# number, current_file# number, current_block# number, program varchar2(64), module number, action number, FIXED_TABLE_SEQUENCE number, sample_id number ) PARTITION BY RANGE (SAMPLE_TIME) SUBPARTITION BY LIST (DBID) SUBPARTITION TEMPLATE (SUBPARTITION S_$DBID VALUES ($DBID)) (PARTITION P_1 VALUES LESS THAN (to_date('$SDATE','DD-MM-YYYY')) ) ; create index v\$ash_i on v\$ash_all(sample_time) local; create sequence ash_part_seq start with 1 increment by 1 nocache; create table v\$ashlog (start_time date default sysdate, action varchar2(100), result char(1), message varchar2(1000)); create table v\$ash_sqlplans_all ( statement_id varchar2(30), timestamp date, remarks varchar2(80), operation varchar2(30), options varchar2(255), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, distribution varchar2(30), cpu_cost numeric, io_cost numeric, temp_space numeric, access_predicates varchar2(4000), filter_predicates varchar2(4000), hash_value number, child_number number, dbid number); create index v\$ash_sqlplans_i on v\$ash_sqlplans_all (dbid, statement_id); create table v\$ash_params_all ( dbid number, name varchar2(64), value varchar2(512)); create unique index v\$ash_params_i on v\$ash_params_all ( dbid , name ); create table v\$ash_event_names_all ( dbid number, event# number, name varchar2(64)); create unique index v\$ash_event_names_i on v\$ash_event_names_all ( dbid , event# ); create table v\$ash_users_all ( dbid number, username varchar2(30), user_id number); create unique index v\$ash_users_i on v\$ash_users_all (dbid, user_id); create table v\$ash_sqltxt_all ( dbid number, address raw(8), hash_value number, command_type number, piece number, sql_text varchar(64), last_found date); create unique index v\$ash_sqltxt_i on v\$ash_sqltxt_all (dbid, hash_value, piece); create table v\$ash_sqlstats_all ( dbid number, sample_time date, address raw(8), hash_value number, executions number, elapsed_time number, rows_processed number); create table v\$ash_objs_all ( dbid number, object_id number, owner varchar2(30), object_name varchar2(128), subobject_name varchar2(30), object_type varchar2(18)); create unique index v\$ash_objs_i on v\$ash_objs_all (dbid, object_id); create table dbids ( dbid number, host varchar2(30), home varchar2(100), sid varchar2(10), ashseq number ); create unique index dbids_i on dbids ( host,sid,home); create view v\$ash as select * from v\$ash_all; create view v\$ash_params as select * from v\$ash_params_all; create view v\$ash_event_names as select * from v\$ash_event_names_all; create view v\$ash_objs as select * from v\$ash_objs_all; create view v\$ash_sqltxt as select * from v\$ash_objs_all; create view v\$ash_sqlstats as select * from v\$ash_objs_all; create view v\$ash_users as select * from v\$ash_users_all; create view v\$ash_sqlplans as select * from v\$ash_sqlplans_all;" done >> $CREATE # create DATABASE LINK echo 'DROP DATABASE LINK "REPO";' > $DBLINK for i in 1 ; do echo 'CREATE DATABASE LINK "REPO"' echo "CONNECT TO \"$REPO_SCHEMA\"" echo "IDENTIFIED BY \"$REPO_PW\"" echo 'USING' echo "'$TNS';" done >> $DBLINK fi # ============ Drop REPO ===================== if [ $OPTS -eq -1 ]; then echo "DROPPING ........." echo "sqlplus $REPO_SCHEMA/$REPO_PW@${TNS} " sqlplus $REPO_SCHEMA/$REPO_PW@${TNS} << EOF @${DROP} exit EOF fi # ============ Create REPO ===================== if [ $OPTS -eq 0 -o $OPTS = 2 ]; then echo "sqlplus $REPO_SCHEMA/$REPO_PW@${TNS} " echo "CREATING ........." sqlplus $REPO_SCHEMA/$REPO_PW@${TNS} << EOF @${CREATE} @${ASHPKG} ! echo 'exec ashpkg.add_subpartition(${DBID});' exec ashpkg.add_subpartition(${DBID}); exit EOF fi # ============ Create Target Sampling Package ===================== if [ $OPTS -eq 1 -o $OPTS -eq 2 ]; then sqlplus "$SCHEMA/$PW" << EOF @${DBLINK} -- don't drop incase it's used by a currently running collection -- otherwise start reusing the same values --drop sequence ashseq; create sequence ashseq ; /* don't think this is needed anymore -- the following two lines are a hack -- I need the v$ash%rowtype but it's on the REPO -- I could try using rowtype across a dblink but havne't yet -- if there already is a v$ash view then this a problem drop view v\$ash ; create view v\$ash as select * from $REPO_SCHEMA.v\$ash_all${DBL} where rownum < 1; */ /* this view could be change in many ways it might be good to get program from v$process it might be good to add machine */ drop view v\$ashnow; create view v\$ashnow as Select d.dbid, sysdate sample_time, s.indx "SESSION_ID", decode(w.ksusstim, 0,'WAITING','ON CPU') "SESSION_STATE", s.ksuseser "SESSION_SERIAL#", s.ksuudlui "USER_ID", s.ksusesql "SQL_ADDRESS", s.ksusesqh "SQL_ID" , s.ksuudoct "SQL_OPCODE" /* aka SQL_OPCODE */, s.ksuseflg "SESSION_TYPE" , w.ksussopc "EVENT# ", w.ksussseq "SEQ#" /* xksuse.ksuseseq */, w.ksussp1 "P1" /* xksuse.ksusep1 */, w.ksussp2 "P2" /* xksuse.ksusep2 */, w.ksussp3 "P3" /* xksuse.ksusep3 */, w.ksusstim "WAIT_TIME" /* xksuse.ksusetim */, s.ksuseobj "CURRENT_OBJ#", s.ksusefil "CURRENT_FILE#", s.ksuseblk "CURRENT_BLOCK#", s.ksusepnm "PROGRAM", s.ksuseaph "MODULE_HASH", /* ASH collects string */ s.ksuseach "ACTION_HASH", /* ASH collects string */ s.ksusefix "FIXED_TABLE_SEQUENCE" /* FIXED_TABLE_SEQUENCE */ from sys.x\$ksuse s , sys.x\$ksusecst w, v\$database d where s.indx != ( select distinct sid from v\$mystat where rownum < 2) and bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.indx = w.indx and ( ( /* status Active - seems inactive & "on cpu"=> not on CPU */ w.ksusstim != 0 and /* on CPU */ bitand(s.ksuseidl,11)=1 /* ACTIVE */ ) or w.ksussopc not in /* waiting and the wait event is not idle */ ( select event# from v\$event_name where lower(name) in ( 'null event', 'lock manager wait for remote message', 'pipe get', 'queue messages', 'rdbms ipc message', 'virtual circuit status', 'queue monitor wait' ) or name like 'Str%' or name like '%slave%' or name like 'PX%' or name like '%time%' or name like '%message from%' ) ); CREATE OR REPLACE PACKAGE ash AS -- PROCEDURE print (sleep number, loops number) ; PROCEDURE get_all ; PROCEDURE get_objs ; PROCEDURE get_users ; PROCEDURE get_params ; PROCEDURE get_sqltxt ; PROCEDURE get_sqlstats ; PROCEDURE get_sqlplans ; PROCEDURE get_event_names ; PROCEDURE collect (sleep number, loops number) ; PROCEDURE purge (part number) ; PROCEDURE purgeall ; FUNCTION get_dbid return number ; PROCEDURE set_dbid ; END ash; / show errors CREATE OR REPLACE PACKAGE BODY ash AS FUNCTION get_dbid return number is l_dbid number; begin select dbid into l_dbid from sys.v\$database; return l_dbid; -- return 1; end get_dbid; PROCEDURE get_users is l_dbid number; begin select dbid into l_dbid from sys.v\$database; insert into ${REPO_SCHEMA}.v\$ash_users_all${DBL} (dbid, username, user_id) select l_dbid,username,user_id from dba_users; commit; end get_users; PROCEDURE get_params is l_dbid number; begin select dbid into l_dbid from sys.v\$database; insert into ${REPO_SCHEMA}.v\$ash_params_all${DBL} ( dbid, name, value) select l_dbid,name,value from gv\$parameter; commit; end get_params; PROCEDURE set_dbid is l_dbid number; begin select dbid into l_dbid from sys.v\$database; insert into ${REPO_SCHEMA}.dbids${DBL} (dbid, host, home,sid,ashseq) values (l_dbid,'${TARG_HOST}','${TARG_HOME}','${TARG_SID}',0); commit; end set_dbid; PROCEDURE purgeall is PRAGMA AUTONOMOUS_TRANSACTION; begin for i in 1..$nparts loop purge(i); end loop; end purgeall; PROCEDURE get_event_names is l_dbid number; begin l_dbid:=get_dbid; insert into ${REPO_SCHEMA}.v\$ash_event_names_all${DBL} ( dbid, event#, name) select l_dbid, event#, name from gv\$event_name; end get_event_names; PROCEDURE get_objs is l_dbid number; begin l_dbid:=get_dbid; -- get object info for top 20 sql in ASH over the last hour insert into ${REPO_SCHEMA}.v\$ash_objs_all${DBL} (dbid, object_id, owner, object_name, subobject_name, object_type ) select l_dbid, o.object_id, o.owner, o.object_name, o.subobject_name, o.object_type from dba_objects o where object_id in ( select current_obj# from ( select count(*) cnt, CURRENT_OBJ# from ${REPO_SCHEMA}.v\$ash_all${DBL} where l_dbid = dbid and current_obj# > 0 and sample_time > (sysdate - 1/24) group by current_obj# order by cnt desc ) where rownum < 21) and object_id not in (select object_id from ${REPO_SCHEMA}.v\$ash_objs_all${DBL} where l_dbid = dbid) ; commit; end get_objs; PROCEDURE get_all is begin get_sqltxt; commit; get_sqlstats; commit; get_objs; commit; get_sqlplans; commit; end get_all; PROCEDURE get_sqlplans is l_dbid number; begin l_dbid:=get_dbid; -- get sql stats for top 20 sql in ASH over the last hour insert into ${REPO_SCHEMA}.v\$ash_sqlplans_all${DBL} ( STATEMENT_ID , TIMESTAMP , REMARKS , OPERATION , OPTIONS , OBJECT_NODE , OBJECT_OWNER , OBJECT_NAME , OBJECT_INSTANCE , OBJECT_TYPE , OPTIMIZER , SEARCH_COLUMNS, ID , PARENT_ID , POSITION , COST , CARDINALITY, BYTES , OTHER_TAG , PARTITION_START , PARTITION_STOP , PARTITION_ID , OTHER , DISTRIBUTION , CPU_COST , IO_COST , TEMP_SPACE , ACCESS_PREDICATES , FILTER_PREDICATES , DBID ) select ADDRESS, sysdate, 'REMARKS', OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, 0, 'OBJECT_TYPE', OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID, POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, l_dbid from gv\$sql_plan sql where sql.hash_value in ( select hash_value from ${REPO_SCHEMA}.v\$ash_sqltxt_all${DBL} where l_dbid = dbid ); --hash_value, --child_number, commit; end get_sqlplans; PROCEDURE get_sqlstats is l_dbid number; begin l_dbid:=get_dbid; -- get sql stats for top 20 sql in ASH over the last hour insert into ${REPO_SCHEMA}.v\$ash_sqlstats_all${DBL} ( dbid, sample_time, address, hash_value, executions, elapsed_time, rows_processed) select l_dbid, sysdate, sql.address, sql.hash_value, sql.executions, sql.elapsed_time, sql.rows_processed from gv\$sql sql where sql.hash_value in ( select hash_value from ${REPO_SCHEMA}.v\$ash_sqltxt_all${DBL} where l_dbid = dbid ); commit; end get_sqlstats; PROCEDURE get_sqltxt is l_dbid number; begin l_dbid:=get_dbid; -- get sqltext for top 20 sql in ASH over the last hour update ${REPO_SCHEMA}.v\$ash_sqltxt_all${DBL} set last_found = sysdate where hash_value in ( select sql_id from ( select count(*) cnt, sql_id from ${REPO_SCHEMA}.v\$ash_all${DBL} where l_dbid = dbid and sql_id != 0 and sample_time > (sysdate - 1/24) group by sql_id order by cnt desc ) where rownum < 21); insert into ${REPO_SCHEMA}.v\$ash_sqltxt_all${DBL} ( dbid, address, hash_value, command_type, piece, sql_text, last_found) select l_dbid, sqlt.address, sqlt.hash_value, sqlt.command_type, sqlt.piece, sqlt.sql_text, sysdate from gv\$sqltext sqlt where sqlt.hash_value in ( select sql_id from ( select count(*) cnt, sql_id from ${REPO_SCHEMA}.v\$ash_all${DBL} where l_dbid = dbid and sql_id != 0 and sample_time > (sysdate - 1/24) group by sql_id order by cnt desc ) where rownum < 21) and hash_value not in ( select hash_value from ${REPO_SCHEMA}.v\$ash_sqltxt_all${DBL} where l_dbid = dbid); commit; end get_sqltxt; PROCEDURE purge(part number) is PRAGMA AUTONOMOUS_TRANSACTION; l_text varchar2(200); begin l_text:='truncate table ${REPO_SCHEMA}v\$ash_'|| to_char(part)||'${DBL} reuse storage'; dbms_output.put_line(l_text); execute immediate l_text; end purge; /* PROCEDURE print(sleep number, loops number) is ash_rec v\$ash%rowtype; cursor ash_cur return v\$ash%ROWTYPE is select a.*, ashseq.nextval sample_id from v\$ashnow a; begin for i in 1..loops loop open ash_cur; loop fetch ash_cur into ash_rec; exit when ash_cur%notfound; dbms_output.put_line(ash_rec.sample_time||' '|| to_char(ash_rec.session_id)||' '|| ash_rec.session_state); end loop; dbms_lock.sleep(sleep); close ash_cur; end loop; end print; */ PROCEDURE collect(sleep number, loops number) is ash_rec ${REPO_SCHEMA}.v\$ash${DBL}%rowtype; l_dbid number; cpart number := -1; /* current partition number */ part number := 1; /* new partition number */ npart number := $nparts; /* current partition number */ cur_ashseq number := 0; cursor ash_cur return ${REPO_SCHEMA}.v\$ash${DBL}%rowtype is select a.*, cur_ashseq sample_time from v\$ashnow a; begin l_dbid:=get_dbid; for i in 1..loops loop select ashseq.nextval into cur_ashseq from dual; -- update ${REPO_SCHEMA}.dbids${DBL} set ashseq = cur_ashseq where dbid = l_dbid; dbms_output.put_line('loop # '||to_char(i)); -- change partitions every day of the week 1-7 , SUN = 1 select to_number(to_char(sysdate,'D')) into part from dual; if part != cpart then -- don't purge the first time around incase data exists from previous run if cpart != -1 then purge(part); end if; cpart:=part; end if; open ash_cur; loop fetch ash_cur into ash_rec; exit when ash_cur%notfound; -- ie if sample_id not great than 1 then its not real data, don't insert if ash_rec.sample_id > 1 then dbms_output.put_line('insert into part '||to_char(part)); insert into ${REPO_SCHEMA}.v\$ash${DBL} values ash_rec; end if; end loop; close ash_cur; commit; dbms_lock.sleep(sleep); end loop; end collect; END ash; / show errors exec ash.set_dbid; exec ash.get_event_names; exec ash.get_users; exec ash.get_params; exit EOF fi # TODO # add more logging to v$ashlog # add logging to v$ashlog from Targets # # on targets have reconnect code # get latchnames from targets # # have purge routinge for sqltxt, sqlstats by date # do we need to partion them? # # # set up local indexing for partitions instead of global # # # export a target over a time period # makes me think of the baseline stuff