Sharing some PL/SQL Developer config settings/code

[aotte]

Hi,

As Marco said when the forum started, it is to share, so I thought some of my session and browser extender queries might be worthwhile for more people than just me. Let me know if you see issues, or easy improvements for them:

Session queries:

-- Long Operations
SELECT
   TRIM(slo.OPNAME) oprtn_name,
   TRIM(slo.TARGET) trgt_name,
   slo.totalwork| |' - '| |slo.sofar| |' = '| |(slo.totalwork-slo.sofar) unts_left,
   TRIM(slo.units) unts,
   (slo.ELAPSED_SECONDS+slo.TIME_REMAINING)| |' - '| |slo.ELAPSED_SECONDS| |' = '| |slo.TIME_REMAINING time_left,
   slo.START_TIME strt_dttm,
   slo.LAST_UPDATE_TIME last_updt_dttm
FROM v$session_longops slo
WHERE slo.sid = :sid AND
      slo.serial# = :serial# AND
      (
      (slo.totalwork-slo.sofar) <> 0 OR
      slo.START_TIME > (SYSDATE - INTERVAL '1' HOUR)
      )
ORDER BY
   strt_dttm DESC

-- Parallel degree
SELECT
   COUNT(DISTINCT ps.server_set) srvr_set_cnt,
   AVG(ps.DEGREE) * COUNT(DISTINCT ps.server_set) dgr_rcvd_cnt,
   AVG(ps.req_degree) * COUNT(DISTINCT ps.server_set) dgr_rqstd_cnt
FROM gv$px_session ps
WHERE ps.qcsid = :sid AND
      ps.qcserial# = :serial#

-- Parallel Slaves
SELECT
   s.sid sssn_id,
   s.username usr_name,
   s.osuser os_usr,
   ps.server_group srvr_grp,
   ps.server_set srvr_set,
   ps.server# srvr#,
   s.logon_time lgn_time
FROM gv$px_session ps,
     v$session s
WHERE ps.qcsid = :sid AND
      ps.sid = s.sid AND
      ps.qcsid <> ps.sid
ORDER BY
   s.sid

-- Temp space usage
SELECT
      su.TABLESPACE tbl_spc,
      SUM(su.EXTENTS) extnt_cnt,
      SUM(su.EXTENTS)*32 mb_dsk_spc_cnt,
      ROUND(SUM(su.EXTENTS)*32/1024,2) gb_dsk_spc_cnt
FROM
   v$sort_usage su
WHERE su.session_addr = :saddr AND
      su.CONTENTS = 'TEMPORARY'
GROUP BY
   su.TABLESPACE

Browser Extender:
Had a few for partitions, which I lost due to a re-image of my machine. Will post those when I have recreated them (Hmm, can I say back-up?)

PL/SQL Developer Browser Extender Command

[MAIN]
NAME=Gather Statistics
CAPTION=Last analyzed on #field_0
OTYPE=Index,Table,User
OOWNER=%
ONAME=%
SEPARATOR_ABOVE=N
SEPARATOR_BELOW=Y
LOAD_AND_EXECUTE=Y
LOAD_COMMAND=Y
LOAD_AFTER=REPLACE
LOAD_WIN_TYPE=1
PRIVILEGE_REQ=N
PRIVILEGE="ANALYZE ANY"
ORACLE_VERSION_REQ=N
ORACLE_VERSION=7.0.0
ONCLICK_CONFIRM=N
ONCLICK_CONFIRM_MSG=
ONCLICK_SPLASH=N
ONCLICK_SPLASH_MSG=Analyzing #otype #object...
ONCLICK_SPLASH_MSG_BL=Y
ONCLICK_SPLASH_MSG_AFTER=Done.
ONCLICK_SPLASH_DELAY=2
ONCLICK_IGNORE_EXCEPTIONS=N
ONPOPUP_IGNORE_EXCEPTIONS=N

[ONCLICK]
DECLARE
   -- Let the browser extender tell us what we are doing
   c_object_type_name CONSTANT VARCHAR2(30) := '#otype';
   c_qualified_object_name CONSTANT VARCHAR2(61) := '#object';
   c_object_owner CONSTANT VARCHAR2(30) := '#oowner';
   c_object_name CONSTANT VARCHAR2(30) := '#oname';
   c_user_name CONSTANT VARCHAR2(30) := '#username';
   c_caption CONSTANT VARCHAR2(32767) := '#caption';

   -- Browser Extender supported object types
   c_object_index CONSTANT VARCHAR2(30) := 'INDEX';
   c_object_table CONSTANT VARCHAR2(30) := 'TABLE';
   c_object_user CONSTANT VARCHAR2(30) := 'USER';

   -- Some generic constants
   c_module_name CONSTANT VARCHAR2(42) := 'BE Analyze '| |c_object_type_name;
   c_action_name CONSTANT VARCHAR2(32) := SUBSTR(c_qualified_object_name,1,32);

   -- Statistics specific constants
   c_estimate_percent CONSTANT NUMBER := 10;
   c_cascade CONSTANT BOOLEAN := TRUE;
   c_degree CONSTANT NUMBER := 4;

BEGIN
   -- Show people what we are doing
   DBMS_APPLICATION_INFO.SET_MODULE(c_module_name,c_action_name);

   CASE c_object_type_name
      WHEN c_object_table THEN
         DBMS_STATS.GATHER_TABLE_STATS(
            OWNNAME          => c_object_owner,
            TABNAME          => c_object_name,
            ESTIMATE_PERCENT => c_estimate_percent,
            CASCADE          => c_cascade,
            DEGREE           => c_degree);
      WHEN c_object_index THEN
         DBMS_STATS.GATHER_INDEX_STATS(
            OWNNAME          => c_object_owner,
            INDNAME          => c_object_name,
            ESTIMATE_PERCENT => c_estimate_percent,
            DEGREE           => c_degree);
      WHEN c_object_user THEN
         DBMS_STATS.GATHER_SCHEMA_STATS(
            OWNNAME          => c_object_name,
            -- A sample percentage of at least 1, but half of what
            -- is done for individual objects
            ESTIMATE_PERCENT => ROUND(c_estimate_percent/2,0)+1,
            CASCADE          => c_cascade,
            DEGREE           => c_degree);
      ELSE
         RAISE NO_DATA_FOUND;
   END CASE;

END;

[ONPOPUP]
SELECT
   NVL(TO_CHAR(last_analyzed,'YYYYMMDD@HH24MI'),'Sint Juttemis') field_0
FROM
   sys.all_tables all_tbl
WHERE
   all_tbl.owner = '#oowner' AND
   all_tbl.table_name = '#oname' AND
   '#otype' = 'TABLE'
UNION
SELECT
   NVL(TO_CHAR(last_analyzed,'YYYYMMDD@HH24MI'),'Sint Juttemis') field_0
FROM
   sys.all_indexes all_idx
WHERE
   all_idx.owner = '#oowner' AND
   all_idx.index_name = '#oname' AND
   '#otype' = 'INDEX'
UNION
SELECT
   NVL(TO_CHAR(MAX(last_analyzed),'YYYYMMDD@HH24MI'),'Sint Juttemis') field_0
FROM
   sys.all_tables all_tbl
WHERE
   all_tbl.owner = '#oowner' AND
   '#otype' = 'USER'

Hope these are of help to some people.

------------------
Hakuna Matata,

Arnoud.

[This message has been edited by aotte (edited 30 May 2003).]

 

[Marco Kalter]

Thanks

------------------
Marco Kalter
Allround Automations