when jobs run slow from application

-


set pages 9999 lines 300

col OPEN_MODE for a30

col HOST_NAME for a30

col INSTANCE_START_TIME for a20

col UPTIME for a20

col DATABASE_STATUS for a15

col VERSION for a10

col LOG_MODE for a10

col INSTANCE_NAME for a10

col DATABASE_ROLE for a10

select INST_ID, INSTANCE_NUMBER, INSTANCE_NAME, HOST_NAME, OPEN_MODE, version, DB_VERSION, DATABASE_STATUS,

DATABASE_ROLE, LOGINS, log_mode,

to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "instance_start_time",

to_char(sysdate - STARTUP_TIME) "UPTIME",

to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "CURRENT_TIME"

from v$database, gv$instance;


SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial||''' IMMEDIATE;' FROM v$session where sid=973;

ALTER SYSTEM KILL SESSION '1921,26176,@2' IMMEDIATE;

SELECT sid, serial#, inst_id, username, status FROM gv$session WHERE username='EPCAUTH03OWNER';

SELECT 'ALTER SYSTEM KILL SESSION '''||inst_id||','||sid||','||serial||''' immediate;' FROM gv$session WHERE status='INACTIVE' and username='EPCAUTH03OWNER';

exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'PKS_SERVS_DLY', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);

exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'MKT_AUTH', cascade => TRUE, estimate_percent =>  DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);

exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'ALA_SERV_CODE_DLY', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);

exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'SPA_TO_PROCESS_OND', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);

exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'CTEI_EAI_SERVICES_DLY', cascade => TRUE, estimate_percent =>  DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);

exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'CNSNT_WHITE_LST_DLY', cascade => TRUE, estimate_percent =>  DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial||''' IMMEDIATE;' FROM v$session where sid=973;

ALTER SYSTEM KILL SESSION '1921,26176,@2' IMMEDIATE;

SELECT sid, serial#, inst_id, username, status FROM gv$session WHERE username='EPCAUTH03OWNER';

SELECT 'ALTER SYSTEM KILL SESSION '''||inst_id||','||sid||','||serial||''' immediate;'  FROM gv$session WHERE status='INACTIVE' and username='EPCAUTH03OWNER';

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

db server : bfrcpdb-wc-a1-21p.sys.comcast.net

db Name :


Long running query issue for machine name spcouch-wc-a2p.sys.comcast.net


* Verify the session details and note the TR. If after some time it got completed inform team

  if not ask OPS team to kill the job and gather the stats for tables involved in that particular SQL

  Once done ask OPS team to Rerun the job then it should get complete.


SELECT SID, SERIAL#, OPNAME, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM GV$SESSION_LONGOPS

WHERE OPNAME NOT LIKE 'aggregate%'

  AND TOTALWORK != 0

  AND SOFAR <> TOTALWORK;


PatchOnDemand EPC refresh monitoring know issue:


set lines 200


column USERNAME format a20

column osuser format a20

column WAIT_CLASS format a10

column EVENT format a26

col osuser format a20

select INST_ID, SID, SERIAL#, SQL_ID, USERNAME, osuser, LAST_CALL_ET, WAIT_CLASS, SECONDS_IN_WAIT from gv$session where machine='spcouch-wc-a2p.sys.comcast.net' and status='ACTIVE';


column USERNAME format a20

column osuser format a20

column WAIT_CLASS format a10

column EVENT format a26

col osuser format a20

select INST_ID, SID, SERIAL#, SQL_ID, USERNAME, osuser, LAST_CALL_ET, WAIT_CLASS, SECONDS_IN_WAIT from gv$session where username='EPCAUTH03OWNER';


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


EPCAUTH03OWNER


SELECT SID, Serial#, UserName, Status, SchemaName, LogonTime  FROM GV$Session WHERE Status='ACTIVE' AND UserName IS NOT NULL;



exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'PKS_SERVS_DLY', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);


exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'MKT_AUTH', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);


exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'ALA_SERV_CODE_DLY', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);


exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'SPA_TO_PROCESS_OND', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8); 


exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'CTEI_EAI_SERVICES_DLY', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);


exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'CNSNT_WHITE_LST_DLY', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);




ELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial||''' IMMEDIATE;' FROM v$session where sid=973;


ALTER SYSTEM KILL SESSION '1921,26176,@2' IMMEDIATE;


SELECT sid, serial#, inst_id, username, status FROM gv$session WHERE username='EPCAUTH03OWNER';


SELECT 'ALTER SYSTEM KILL SESSION '''||inst_id||','||sid||','||serial||''' immediate;' FROM gv$session WHERE status='INACTIVE'   AND username='EPCAUTH03OWNER';

  

 SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM GV$Session WHERE Status='ACTIVE' AND UserName IS NOT NULL;


select INST_ID,SID,SERIAL#,SQL_ID,USERNAME,osuser,LAST_CALL_ET,WAIT_CLASS,SECONDS_IN_WAIT from gv$session where SQL_ID='8zz8bhrwawn6';


select SQL_FULLTEXT from gv$sql where sql_id='84349c04ftas';


select INST_ID, SID, SERIAL#, SQL_ID, ELAPSED_SECONDS, TIME_REMAINING, MESSAGE  from gv$session_longops  where time_remaining>0 and sql_id='8zz8bhbrswwn6';


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

      GATHER STATS :

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


exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'PKS_SERVS_DLY', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);


exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'MKT_AUTH', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);


exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'ALA_SERV_CODE_DLY', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);


exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'SPA_TO_PROCESS_OND', cascade => TRUE, estimate_percent =>  DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);


exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'CTEI_EAI_SERVICES_DLY', cascade => TRUE, estimate_percent =>  DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);


exec dbms_stats.gather_table_stats(ownname => 'EPCAUTH03OWNER', tabname => 'CNSNT_WHITE_LST_DLY', cascade => TRUE, estimate_percent =>  DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 8);


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


SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial||''' IMMEDIATE;' FROM v$session where sid=973;


ALTER SYSTEM KILL SESSION '1921,26176,@2' IMMEDIATE;


SELECT sid, serial#, inst_id, username, status FROM gv$session WHERE username='EPCAUTH03OWNER';


SELECT 'ALTER SYSTEM KILL SESSION '''||inst_id||','||sid||','||serial||''' immediate;' FROM gv$session WHERE status='INACTIVE' AND username='EPCAUTH03OWNER';



db server : bfrcpdb-wc-a1-21p.sys.comcast.net

db Name :


Long running query issue for machine name spcouch-wc-a2p.sys.comcast.net


* Verify the session details and note the TR. If after some time it got completed inform team   if not ask OPS team to kill the job and gather the stats for tables involved in that particular SQL   Once done ask OPS team to Rerun the job then it should get complete.


SELECT SID, SERIAL#, OPNAME, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM GV$SESSION_LONGOPS WHERE OPNAME NOT LIKE 'aggregate%'   AND TOTALWORK != 0   AND SOFAR <> TOTALWORK;


PatchOnDemand EPC refresh monitoring know issue:


set lines 200

column USERNAME format a20

column osuser format a20

column WAIT_CLASS format a10

column EVENT format a26

col osuser format a20

select INST_ID, SID, SERIAL#, SQL_ID, USERNAME, osuser, LAST_CALL_ET, WAIT_CLASS, SECONDS_IN_WAIT from gv$session where machine='spcouch-wc-a2p.sys.comcast.net' and status='ACTIVE';


column USERNAME format a20

column osuser format a20

column WAIT_CLASS format a10

column EVENT format a26

col osuser format a20

select INST_ID, SID, SERIAL#, SQL_ID, USERNAME, osuser, LAST_CALL_ET, WAIT_CLASS, SECONDS_IN_WAIT from gv$session where username='EPCAUTH03OWNER';


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


EPCAUTH03OWNER


SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM GV$Session WHERE Status='ACTIVE' AND UserName IS NOT NULL;


col name for a40

select INST_ID, CON_ID, NAME, OPEN_MODE, RESTRICTED FROM GV$PDBS ORDER BY INST_ID, CON_ID;


Sql> select name,cause,type,message,status from pdb_plug_in_violations order by name;


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

                            From all nodes

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


tnsping dbsmp/milloetp* (DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = smleprd-chr-a3-ac.sys.comcast.net)(PORT = 1555))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = itmg3_spp)))


tnsping dbsmp/milloetp* (DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = smleprd-chr-a3-ac.sys.comcast.net)(PORT = 1555))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pentaho_prdclds.sys.comcast.net)))


(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (HOST=cqodb-ar-bs-bgrd.sys.comcast.net)(PORT=1555))) (CONNECT_DATA=(SERVICE_NAME=STDBMY_RW)))

Comments

Post a Comment

Popular posts from this blog

MY NOTEPAD

-
set lines 200 pages 200; col END_TIME for a20; col ELAPSED_TIME for a25; col START_TIME for a20 SELECT T.SESSION_KEY, T.INPUT_TYPE, T.STATUS, T.SUM_BACKUP_PIECES_IN_GB, T.START_TIME, T.END_TIME, T.SECONDS ELAPSED_TIME FROM ( SELECT SESSION_KEY, INPUT_TYPE, STATUS, OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB, TO_CHAR (START_TIME, 'dd/mm/yyyy hh24:mi') START_TIME, TO_CHAR (END_TIME, 'dd/mm/yyyy hh24:mi') END_TIME, TIME_TAKEN_DISPLAY SECONDS FROM V$RMAN_BACKUP_JOB_DETAILS WHERE INPUT_TYPE='ARCHIVELOG' ORDER BY SESSION_KEY DESC) T WHERE ROWNUM <= 1; ================ ASM disk group free space ============== set sqlblanklines on set lines 180 set pages 999le col "Group Name" form a25 col "Disk Name" form a30 col "State" form a15 ...
Read more

Oracle OEM Holistic Patch

-
  Oracle OEM Holistic Patch  The Concept Starting with Oracle Enterprise Manager (OEM) 13.5 RU24 (October 2024), Oracle introduced the Holistic Patch for OEM — a unified patching model that consolidates several separate updates into a single coordinated package. Previously, administrators had to patch: * The OMS and Agents with the quarterly Release Update (RU). * OEM Application via the System Patch Bundle (SPB). * WebLogic CPU and JDK manually. There was an element of complication because of the need to keep the solution homogeneous. With the Holistic Patch, the last two steps are merged into a single patch bundle. Now we have a new Holistic patch for OEM that is released with each new OMS Release Update. Prerequisites Apply RU 24 Patch  PATCH 36761595  /later on all OMSes before applying Oct Holistic Patch   PATCH 37305292             (OR) Apply RU 25 Patch  PATCH 37057635  /later on all OMSes before applying ...
Read more

Upgrade Oracle 19c DB to 21c using Auto Upgrade utility inprogress

-
Image
  Upgrade Oracle 11g to 21c using AutoUpgrade utility: How do I upgrade Oracle database to 21c?  install the 21c oracle binaries into the server . Oracle Virtual Bix  Oracle Linux 7 Oracle Database 19c currently running Upgrading 19c database to 21c directly Oracle Database 19c on premises is available – and one of the key features is the new Auto Upgrade utility. In the following days I will publish several blog posts explaining and showcasing the new Auto Upgrade. What is the Auto Upgrade? The Oracle Database Auto Upgrade utility is a new tiny little command line tool which allows you to upgrade your databases in an unattended way. I call it the Hands-Free Upgrade. The new Auto Upgrade utility in Oracle 19che idea of the tool is to run the prechecks against multiple databases, fix 99% of the potential issues, set a restore point in case something goes wrong – and then upgrade your databases. And of course, do the post upgrade, recompilation and time zone adjustment. The...
Read more