Jenkins in Oracle Database Task Automation

 Jenkins in Oracle Database Task Automation

Now we’re ready to finish setting up the node via the Jenkins UI. In Jenkins, go to Manage Jenkins, then Manage Nodes, then click New Node. Here you can give your agent node a name, then select Permanent Agent and click OK. There are a variety of options you can use here to customize your node. All we care about right now is the Launch Method.

Important Points

 Important points to be noted from daily activities:-

While performing Re-org/oracle home movement on a production database:-  

    i)While performing oracle-home movement of a production database, please add the TNS of both primary and standby databases to the new homes on both the PRIMARY and STANDBY side.

    ii)While performing re-org/table/index/lob/table partitions movement , monitor the RECO disk group closely , flashback log also accumulated. If DR-sync-up is not happening, RECO disk group may get full.

------------------------------------------------------------------------------------------------------

If you are unable to drop the tablespace due to BIN$ objects in dba_indexes, then try to drop those indexes, if you are unable to drop them, then drop the BIN$ constraints of that table first from dba_constraints then drop the BIN$ indexes.

select sum(bytes) from v$sgastat where pool = 'shared pool';

The sharedpool holds many other structures that are outside the scope of the corresponding parameter. The SHARED_POOL_SIZE is typically the largest contributor to the shared pool as reported by the SUM(BYTES), but it is not the only contributor.

In Oracle 10g, the SHARED_POOL_SIZE parameter controls the size of the shared pool, whereas in Oracle9i and before, it was just the largest contributor to the shared pool. You should review your 9i and before actual shared pool size (based on V$SGASTAT) and use that figure to set your SHARED_POOL_SIZE parameter in Oracle 10g and above.

SELECT DBTIMEZONE AS "Database Time Zone", SESSIONTIMEZONE AS "Session Time Zone"

FROM dual;

SQL_TUNING

 SQL_TUNING

Turning on 10046 tracing for the sql:

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Or if the sql is already running you can turn the trace on for this sql_id:
How to trace sql id:
1. ALTER SYSTEM SET EVENTS sql_trace [sql: sql_id=3s1yukp05bzg7] bind=true, wait=true';
2. execute the query
3. alter system set events 'sql_trace off';
4. Find the trace
You do not need to leave the trace on, just collect for 10 minutes.

Explain plan of a sql query:

SET VERIFY OFF

set echo off

set pagesize 400

set linesize 300

set long 4999

set longc 4999

explain plan for 

SELECT * from soe.employee;

select * from table(dbms_xplan.display);

----------invisible index demo------------------------------

SET VERIFY OFF

set echo off

set pagesize 400

set linesize 300

set long 4999

set longc 4999

explain plan for 

SELECT * /*+ use_invisible_indexes */ from soe.employee;

select * from table(dbms_xplan.display);

--------------Indexes presence on a table------------------

SELECT aic.index_owner, aic.table_name, aic.INDEX_NAME, listagg(aic.column_name,',') within group (order by aic.column_position) cols FROM all_ind_columns aic where  aic.table_name='TABLE_NAME' group by aic.index_owner, aic.table_name, aic.INDEX_NAME order by aic.index_owner, aic.table_name ;

----------SQL Tracing-------------

1. Set the below trace at session level to get trace :

alter session set tracefile_identifier='06502';

alter session set events '06502 trace name errorstack level 3';

2. Run the plsql block and generate the error.

3. To close the trace set the following

alter session set events '06502 trace name context off';

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

---------Query execution time check-----------

------this query will give the avg execution time per each execution ---taking calculations from gv$sql executions count and elapsed time

WITH

p AS (

SELECT plan_hash_value

  FROM gv$sql_plan

 WHERE sql_id = TRIM('&&sql_id.')

   AND other_xml IS NOT NULL

 UNION

SELECT plan_hash_value

  FROM dba_hist_sql_plan

 WHERE sql_id = TRIM('&&sql_id.')

   AND other_xml IS NOT NULL ),

m AS (

SELECT plan_hash_value,

       SUM(elapsed_time)/SUM(executions) avg_et_secs

  FROM gv$sql

 WHERE sql_id = TRIM('&&sql_id.')

   AND executions > 0

 GROUP BY

       plan_hash_value ),

a AS (

SELECT plan_hash_value,

       SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs

  FROM dba_hist_sqlstat

 WHERE sql_id = TRIM('&&sql_id.')

   AND executions_total > 0

 GROUP BY

       plan_hash_value )

SELECT p.plan_hash_value,

       ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs

  FROM p, m, a

 WHERE p.plan_hash_value = m.plan_hash_value(+)

   AND p.plan_hash_value = a.plan_hash_value(+)

 ORDER BY

       avg_et_secs NULLS LAST;

--------------------------------------------------------------

---------sql plan changes app --------

set lines 200

set pagesize 200

col execs for 999,999,999

col avg_etime for 999,999.999 heading avg_exec|time(s)

col avg_lio for 999,999,999.9

col begin_interval_time for a30

col node for 99999

col SQL_PROFILE for a45

set verify off

col PLAN_HASH_VALUE for 9999999999 heading 'Plan Hash'

col PARSING_SCHEMA_NAME for a10 heading 'Parsing Schema'

col END_INTERVAL_TIME for a30

SELECT STAT.SNAP_ID,STAT.SQL_ID, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, round(ELAPSED_TIME_DELTA/1000000,2) exec_sec, SS.END_INTERVAL_TIME,SQL_PROFILE FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID = TXT.DBID AND SS.DBID = STAT.DBID AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND STAT.SNAP_ID = SS.SNAP_ID AND  STAT.INSTANCE_NUMBER = 1 AND SS.BEGIN_INTERVAL_TIME >= sysdate-7 AND UPPER(STAT.SQL_ID) =  upper('&sqlid') ORDER BY stat.snap_id desc

/

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,

nvl(executions_delta,0) execs,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,SQL_PROFILE

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where upper(sql_id) like upper('&&sqlid')

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and executions_delta > 0

order by 1,2,begin_interval_time 

/

PROMPT +-------------------------------------------------+

PROMPT | Execution times of the various plans in history |

PROMPT +-------------------------------------------------+

set lines 200 pages 200

col execs for 999,999,999

col etime for 999,999,999.9

col avg_etime for 999,999.999

col avg_cpu_time for 999,999.999

col avg_lio for 999,999,999.9

col avg_pio for 9,999,999.9

col begin_interval_time for a30

col node for 99999

--break on plan_hash_value on startup_time skip 1

select sql_id, plan_hash_value,

sum(execs) execs,

sum(etime) etime,

sum(etime)/sum(execs) avg_etime,

sum(cpu_time)/sum(execs) avg_cpu_time,

sum(lio)/sum(execs) avg_lio,

sum(pio)/sum(execs) avg_pio

from (

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,

nvl(executions_delta,0) execs,

elapsed_time_delta/1000000 etime,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,

buffer_gets_delta lio,

disk_reads_delta pio,

cpu_time_delta/1000000 cpu_time,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,

(cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where sql_id =  '&&sqlid'

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and executions_delta > 0

)

group by sql_id, plan_hash_value

order by 5

/

=====================check plan diff sql id

set linesize 200

set pagesize 40

col sql_plan_hash_value for 9999999999 heading 'Sql|Plan Hash'

col rows_processed for 999999999 heading 'Rows|Processed'

col SORTS for 9999999

set verify off

col last_load for a19

col plan_hash_value for 9999999999 heading "Plan|Hash Value"

select plan_hash_value,to_char(LAST_LOAD_TIME,'DD-MON-YY HH24:MI:SS') last_load,SORTS,FETCHES,EXECUTIONS,PARSE_CALLS,DISK_READS,DIRECT_WRITES,BUFFER_GETS,ROWS_PROCESSED,HASH_VALUE,OBJECT_STATUS from gv$sqlarea where SQL_ID = '&&sqlid';

PROMPT +---------------------------------------+

PROMPT | &&sqlid Query Last 5 plan history    

PROMPT +---------------------------------------+

set lines 200

set pagesize 200

col snap_id for 999999

col instance_number for 9999

col execs for 999,999,999

col avg_etime for 999,999.999

col avg_lio for 999,999,999

col SQL_PROFILE for a32

col begin_interval_time for a26

col node for 99999

--define sqlid=&1

set verify off

select * from (select ss.snap_id, ss.instance_number node, to_char(begin_interval_time,'DD-MON-YY HH24:MI:SS') Begin_Interval, sql_id, plan_hash_value,

nvl(executions_delta,0) execs,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,SQL_PROFILE

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where sql_id ='&&sqlid'

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and executions_delta > 0

--and begin_interval_time > sysdate-1/24

order by begin_interval_time desc,1, 2)

where rownum <= 5

/

PROMPT +---------------------------------------+

PROMPT | &&sqlid Avg Exec Plan History

PROMPT +---------------------------------------+

set lines 200 pages 200

col execs for 999,999,999

col etime for 999,999,999 heading 'Exec_Time(sec)'

col avg_etime for 999,990.999 heading 'Avg |Exec_Time(sec)'

col avg_cpu_time for 999,999.999

col avg_lio for 999,999,999 heading 'Avg | Logical IO'

col avg_pio for 9,999,999  heading 'Avg | Physical IO'

col begin_interval_time for a30

col node for 99999

select sql_id, plan_hash_value,

sum(execs) execs,

sum(etime) etime,

sum(etime)/sum(execs) avg_etime,

sum(cpu_time)/sum(execs) avg_cpu_time,

sum(lio)/sum(execs) avg_lio,

sum(pio)/sum(execs) avg_pio

from (

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,

nvl(executions_delta,0) execs,

elapsed_time_delta/1000000 etime,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,

buffer_gets_delta lio,

disk_reads_delta pio,

cpu_time_delta/1000000 cpu_time,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,

(cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where sql_id =  '&&sqlid'

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and executions_delta > 0

)

group by sql_id, plan_hash_value

order by 5

/

==================================

------------processing sql id ------------------------

set linesize 200

set pagesize 40

col sql_plan_hash_value for 9999999999 heading 'Sql|Plan Hash'

col rows_processed for 999999999 heading 'Rows|Processed'

set verify off

col last_load for a19 heading "Last Load Time"

col plan_hash_value for 9999999999 heading "Plan|Hash Value"

select plan_hash_value,to_char(LAST_LOAD_TIME,'DD-MON-YY HH24:MI:SS') last_load,SORTS,FETCHES,EXECUTIONS,PARSE_CALLS,DISK_READS,DIRECT_WRITES,BUFFER_GETS,ROWS_PROCESSED,HASH_VALUE,OBJECT_STATUS from gv$sqlarea where SQL_ID = '&&sqlid';

=========================================

=====stale stat sql id ============

set lines 500

col table_owner for a15

col table_name for a30

col partition_name for a30

select distinct b.table_owner, b.table_name, b.partition_name, b.inserts, b.updates, b.deletes, b.TRUNCATED,c.STALE_STATS,

to_char(b.timestamp, 'mm/dd/yyyy hh24:mi') timestamp, to_char(c.last_analyzed, 'mm/dd/yyyy hh24:mi') last_analyzed,

c.num_rows

from (select distinct sql_id, object#, object_name, object_owner from gv$sql_plan where sql_id = '&&sqlid' UNION select distinct sql_id, object#, object_name, object_owner from dba_hist_sql_plan where sql_id = '&&sqlid') a

, sys.dba_tab_modifications b, dba_tab_statistics c

where a.sql_id = '&&sqlid'

and  a.OBJECT_OWNER = b.table_owner

and  a.OBJECT_NAME = b.table_name

and  b.table_owner = c.owner

and  b.table_name  = c.table_name

and  NVL(b.partition_name,'NONE') = NVL(c.partition_name,'NONE')

and b.table_name is not null

order by b.table_owner, b.table_name, b.partition_name;

========================================================

======Plan change REport ==============

with samples as 

 (select *

  from dba_hist_sqlstat st

  join dba_hist_snapshot sn

  using (snap_id, instance_number) 

  where 

  --  sql_id='sqlid'

-- parsing_schema_name = 'schema'

  --and module  'DBMS_SCHEDULER' -- no sql tuning task

   begin_interval_time between sysdate - '&num_days' and sysdate

  and executions_delta > 0),

 

/* just statements that had at least 2 different plans during that time */

  sql_ids as 

   (select sql_id,

    count(distinct plan_hash_value) plancount

    from samples

    group by sql_id

    having count(distinct plan_hash_value) > 2),

/* per combination of sql_id and plan_hash_value, elapsed times per execution */

    plan_stats as 

     (select sql_id,

      plan_hash_value,

      min(parsing_schema_name),

      count(snap_id) snap_count,

      max(end_interval_time) last_seen,

      min(begin_interval_time) first_seen,

      sum(executions_delta) total_execs,

      sum(elapsed_time_delta) / sum(executions_delta) elapsed_per_exec_thisplan

      from sql_ids

      join samples

      using (sql_id)

      group by sql_id, plan_hash_value),

/* how much different is the elapsed time most recently encountered from other elapsed times in the measurement interval? */

      elapsed_time_diffs as 

       (select p.*,

        elapsed_per_exec_thisplan - first_value(elapsed_per_exec_thisplan)

          over(partition by sql_id order by last_seen desc) elapsed_per_exec_diff,

        (elapsed_per_exec_thisplan - first_value(elapsed_per_exec_thisplan)

          over(partition by sql_id order by last_seen desc)) / elapsed_per_exec_thisplan elapsed_per_exec_diff_ratio

        from plan_stats p),

/* consider just statements for which the difference is bigger than our configured threshold */

        impacted_sql_ids as 

         (select *

          from elapsed_time_diffs ),

/* for those statements, get all required information */

          all_info as

           (select sql_id,

            plan_hash_value,

        --    parsing_schema_name,

            snap_count,

            last_seen,

first_seen,

total_execs,

            round(elapsed_per_exec_thisplan / 1e6, 2) elapsed_per_exec_thisplan,

            round(elapsed_per_exec_diff / 1e6, 2) elapsed_per_exec_diff,

            round(100 * elapsed_per_exec_diff_ratio, 2) elapsed_per_exec_diff_pct,

            round(max(abs(elapsed_per_exec_diff_ratio))

              over(partition by sql_id), 2) * 100 max_abs_diff,

            round(max(elapsed_per_exec_diff_ratio) over(partition by sql_id), 2) * 100 max_diff,

            'select * from table(dbms_xplan.display_awr(sql_id=>''' || sql_id ||

            ''', plan_hash_value=>' || plan_hash_value || '));' xplan

            from elapsed_time_diffs

            where sql_id in (select sql_id from impacted_sql_ids))

/* format the output */

            select 

             a.sql_id,

plan_hash_value,

            -- parsing_schema_name,

             a.snap_count,

total_execs,

     to_char(a.elapsed_per_exec_thisplan, '999999.99') elapsed_per_exec_thisplan,

             to_char(a.elapsed_per_exec_diff, '999999.99') elapsed_per_exec_diff,

             to_char(a.elapsed_per_exec_diff_pct, '999999.99') elapsed_per_exec_diff_pct,

to_char(first_seen, 'dd-mon-yy hh24:mi') first_seen,

to_char(last_seen, 'dd-mon-yy hh24:mi') last_seen

             --xplan

             from all_info a where sql_id in (select distinct sql_id from all_info where elapsed_per_exec_diff_pct < -50)

             order by sql_id, elapsed_per_exec_diff_pct;

=============================

=====explain plan from sql_id==============

SELECT t.*

FROM gv$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t 

WHERE s.sql_id='&&sql_id';

=============================

Stale Stat check query

select distinct b.table_owner, b.table_name, b.partition_name, b.inserts, b.updates, b.deletes, b.TRUNCATED,c.STALE_STATS,

to_char(b.timestamp, 'mm/dd/yyyy hh24:mi') timestamp, to_char(c.last_analyzed, 'mm/dd/yyyy hh24:mi') last_analyzed,

c.num_rows

from (select distinct sql_id, object#, object_name, object_owner from gv$sql_plan where sql_id = '&&sqlid' UNION select distinct sql_id, object#, object_name, object_owner from dba_hist_sql_plan where sql_id = '&&sqlid') a

, sys.dba_tab_modifications b, dba_tab_statistics c

where a.sql_id = '&&sqlid'

and  a.OBJECT_OWNER = b.table_owner

and  a.OBJECT_NAME = b.table_name

and  b.table_owner = c.owner

and  b.table_name  = c.table_name

and  NVL(b.partition_name,'NONE') = NVL(c.partition_name,'NONE')

and b.table_name is not null

order by b.table_owner, b.table_name, b.partition_name;

+++++++++++++++++++++++++++++++++++++++++++++++

To check unindexed foreign key columnn in database;

column columns format a30 word_wrapped

column table_name format a15 word_wrapped

column constraint_name format a15 word_wrapped

select table_name, constraint_name,

cname1 || nvl2(cname2,','||cname2,null) ||

nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||

nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||

nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)

columns

from ( select b.table_name,

b.constraint_name,

max(decode( position, 1, column_name, null )) cname1,

 max(decode( position, 2, column_name, null )) cname2,

 max(decode( position, 3, column_name, null )) cname3,

 max(decode( position, 4, column_name, null )) cname4,

 max(decode( position, 5, column_name, null )) cname5,

 max(decode( position, 6, column_name, null )) cname6,

 max(decode( position, 7, column_name, null )) cname7,

 max(decode( position, 8, column_name, null )) cname8,

 count(*) col_cnt

 from (select substr(table_name,1,30) table_name,

 substr(constraint_name,1,30) constraint_name,

 substr(column_name,1,30) column_name,

 position

 from user_cons_columns ) a,

 user_constraints b

 where a.constraint_name = b.constraint_name

 and b.constraint_type = 'R'

 group by b.table_name, b.constraint_name

 ) cons

 where col_cnt > ALL

 ( select count(*)

 from user_ind_columns i,

 user_indexes ui

 where i.table_name = cons.table_name

 and i.column_name in (cname1, cname2, cname3, cname4,

 cname5, cname6, cname7, cname8 )

 and i.column_position <= cons.col_cnt

 and ui.table_name = i.table_name

 and ui.index_name = i.index_name

 and ui.index_type IN ('NORMAL','NORMAL/REV')

 group by i.index_name

 );

Blocking session:

============

select

(select username from v$session where sid=a.sid) blocker,

a.sid,

' is blocking ',

(select username from v$session where sid=b.sid) blockee,

b.sid

from v$lock a, v$lock b

where a.block = 1

and b.request > 0

 and a.id1 = b.id1

 and a.id2 = b.id2;

ALTER SESSION SET EVENTS '10730 trace name context forever level [1, 2, 3]';

perfstats_query:

SELECT

    SYSDATE,

    sql_id,

    sql_fulltext,

    hash_value,

    parsing_schema_name,

    module,

    first_load_time,

    last_active_time,

    parse_calls,

    executions,

    round(cpu_time / (executions * 1000000) ) AS cputime,

    round(user_io_wait_time / (executions * 1000000) ) AS iowait,

    round(elapsed_time / (executions * 1000000),2 ) AS elaptimesecs

FROM

    gv$sqlarea

WHERE

    executions != 0

    AND parsing_schema_name NOT IN (

        'SYS',

        'SYSTEM',

        'DBSNMP',

        'PERFSTATS'

    )

        AND module NOT IN (

        'SQL Developer',

        'Toad'

    )

    and round(elapsed_time/(executions*1000000))>3

========

col BEGIN_INTERVAL_TIME for a30

select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME 

select snap_id,instance_number inst_id,sql_id,plan_hash_value,parsing_schema_name,EXECUTIONS_TOTAL,EXECUTIONS_DELTA,ELAPSED_TIME_TOTAL,ELAPSED_TIME_DELTA from DBA_HIST_SQLSTAT where sql_id=TRIM('&&sql_id.');

 select sql_id,plan_hash_value,elapsed_time,executions from gv$sql where sql_id =TRIM('&&sql_id.');

select Inst_id,SQL_FULLTEXT,SQL_ID,EXECUTIONS,ELAPSED_TIME from gv$sqlarea where sql_id =TRIM('&&sql_id.') ;

SELECT STAT.SNAP_ID,STAT.SQL_ID, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME,elapsed_time_total,executions_total,elapsed_time_delta, nvl(executions_delta,0) executions_delta ,round(ELAPSED_TIME_DELTA/1000000,2) exec_sec, SS.END_INTERVAL_TIME,SQL_PROFILE FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS 

WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID = TXT.DBID AND SS.DBID = STAT.DBID AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND STAT.SNAP_ID = SS.SNAP_ID AND  STAT.INSTANCE_NUMBER = 1 AND SS.BEGIN_INTERVAL_TIME >= sysdate-10 AND UPPER(STAT.SQL_ID) =  upper('&sqlid') ORDER BY stat.snap_id desc

/