Posts

Showing posts from November, 2023

Tracking Child Sqlid / Session Id executed By Plsql and Procedure using program_id

  Its not easy to track and tune  Child sql that are  executed internally as part  of plsql.  Unfortunately  Sqlid attached execution plan  and Sql tuning advisor doesnt support on sqlid for plsql hence we need to identify  Child sql that are  executed internally as part  of plsql and tune child sqlid . While this execution is in-progress, we could easily track the SQL that is currently executing along with its line no# with in the package. The PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID from V$SESSION would tell us the name of the package and method that is currently executing. Select owner,object_name,object_type,object_id  from dba_objects  where object_name in( 'MYPKG' ); OWNER      OBJECT_NAM  OBJECT_TYPE           OBJECT_ID ---------- ---------- -------------------- ---------- ABDUL      MYPKG      PACKAGE BODY      ...
Post a Comment
Read more

Shared Pool Utilization

  SET  LONG   10000000 ;  SET LONGC 10000000 ;  SET LINESIZE 32767 ;  SET PAGESIZE 0 ;  SET TRIMSPOOL ON ; SPOOL sahred_pool.html SELECT DBMS_PERF.REPORT_PERFHUB FROM DUAL; SPOOL OFF;   SELECT 1                                                                   NOPR,        TO_CHAR (A.INST_ID)                                                 INST_ID,        A.USERS                                                             USERS,        TO_CHA...
Post a Comment
Read more

Tracking the SQL and its line no from PL/SQL

  When we use PL/SQL for processing the data, it is easy to track each SQL that is executing and its line no# associated with the PL/SQL units. Let’s start with a demo.  create or replace package mypkg   2  as   3     procedure p1;   4     procedure p2;   5  end;   6  / Package created.  create or replace package body mypkg   2  as   3     procedure p1 as   4     begin   5             for x in (select b1.owner,b2.username   6                     from all_objects b1, all_users b2)   7             loop   8                     null;   9             end loop;  10     end;  11  12     procedure p2 as  13     begin ...
Post a Comment
Read more

Oracle Segment Growth

 dba_hist_seg_stat_usage.sql -- **************************************************** -- This tablespace growth report requires AWR license -- **************************************************** set feedback on select * from (select c.tablespace_name,c.segment_name “object name”,b.object_type, sum(space_used_delta) / 1024 / 1024 “Growth (MB)” from     dba_hist_snapshot sn,    dba_hist_seg_stat a,    dba_objects b,    dba_segments c where     begin_interval_time > trunc(sysdate) – &days_back and     sn.snap_id = a.snap_id and     b.object_id = a.obj# and     b.owner = c.owner and     b.object_name = c.segment_name and     c.owner =’XXXX’ group by     c.tablespace_name,    c.segment_name,    b.object_type) order by 3 asc;  
Post a Comment
Read more

Oracle Transaction History to check growth

   How To Query Transaction Count in Oracle   SELECT BEGIN_TIME, END_TIME, TXNCOUNT, ( (END_TIME - BEGIN_TIME) * 24 * 60 * 60) DIFFSECOND, TO_CHAR ( (TXNCOUNT / ( (END_TIME - BEGIN_TIME) * 24 * 60 * 60)), '999999.99') TRANXPERSECOND FROM V$UNDOSTAT WHERE     BEGIN_TIME >= TO_DATE('20.09.2014 00:00','DD.MM.YYYY HH24:MI') AND BEGIN_TIME <= TO_DATE('25.09.2014 00:00','DD.MM.YYYY HH24:MI') AND (TXNCOUNT / ( (END_TIME - BEGIN_TIME) * 24 * 60 * 60)) > 50 ORDER BY 1;   -- Transaction per hour  alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';  set pages 9999; column c1 heading "Start|Time" format a30; column c2 heading "End|Time" format a15; column c3 heading "Total|Undo|Blocks|Used" format 9,999,999; column c4 heading "Total|Number of|Transactions|Executed" format 999,999; column c5 heading "Longest|Query|(sec)" format 999,999; column c6 heading "Highest|Concurrent|Tran...
Post a Comment
Read more

SQL Performance Report sqlperf.sql

 A sql script, which is written by Marcel Jank for gathering the sql performance report, I found it really helpful to gather stat. The script will ask sql_id and will generate an HTML report for that sql. Below is the sql script written by Marcel.  ------------------------------------------------------------------------------------------------------------------------------------------------------ --      Retrieve a lot of information about query performance, based on sql_id -- --      Script      sqlperf.sql (formerly known as sql_sql_id_html.sql) --      Run as      DBA -- -- https://dbarohit.blogspot.com/2014/04/sql-performance-report.html --      Purpose     This script will retrieve a lot of info about a query, based on sql_id and create a HTML report -- --      Input       sql_id, child_number -- --      Author...
Post a Comment
Read more