-- (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: # set serveroutput on # execute dbms_output.enable(1000000); # -- print data 10 times at 1/10 sec intervals # exec ash.print(.1,10); # -- 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 jon # -- 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_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_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), dbid number); 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_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 ; -- 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; 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 ) 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 ( 'queue monitor wait', 'null event', 'pl/sql lock timer', 'px deq: execution msg', 'px deq: table q normal', 'px idle wait', 'sql*net message from client', 'sql*net message from dblink', 'dispatcher timer', 'lock manager wait for remote message', 'pipe get', 'pmon timer', 'queue messages', 'rdbms ipc message', 'slave wait', 'smon timer', 'virtual circuit status', 'wakeup time manager', 'i/o slave wait', 'jobq slave wait', 'queue monitor wait', 'SQL*Net message from client' ) ) ); CREATE OR REPLACE PACKAGE ash AS -- PROCEDURE print (sleep number, loops number) ; PROCEDURE get_all ; PROCEDURE get_objs ; PROCEDURE get_users ; 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} select l_dbid,username,user_id from dba_users; commit; end get_users; PROCEDURE set_dbid is l_dbid number; begin select dbid into l_dbid from sys.v\$database; insert into ${REPO_SCHEMA}.dbids${DBL} 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} 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} 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 ) 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} 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 ); 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} 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 insert into ${REPO_SCHEMA}.v\$ash_sqltxt_all${DBL} 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 sample_time > (sysdate - 1/24) group by sql_id order by cnt ) 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 v\$ash%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; 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