Skip to main content

Posts

SQL ID Baseline

set linesize 200 pagesize 0 SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'&sql_id', format=>'ALLSTATS LAST +cost +bytes +outline +PEEKED_BINDS +adaptive')); select * from table(dbms_xplan.display_awr(sql_id=>'&sql_id', format=>'ALLSTATS LAST +cost +bytes +outline +PEEKED_BINDS +adaptive')); select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle','&plan_name','typical')); col begin_interval_time for a30 col end_interval_time for a30 select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot order by snap_id asc / select snap_id,BEGIN_INTERVAL_TIME, startup_time from dba_hist_snapshot where trunc(end_interval_time) > (SELECT trunc(NEXT_DAY(sysdate,'FRIDAY')) -14 FROM dual) AND trunc(end_interval_time) 6hxw283cyw0ub and plan_hash_value for good plan that we want to force is 95728747.*/ Follow below steps to create sql baseline for sql_id STEP...
2 comments
Read more
Recent posts

Start & Stop Container & Pluggable DB

Start and stop the Pluggable & Container Database 1. Check the current session container. Note: I am login in CDB$ROOT container. SQL> show con_name CON_NAME -------------- CDB$ROOT Start and Stop pluggable database You should login in the pluggable or CDB$ROOT container. PDB shutdown can be managed from CDB$ROOT or by own PDB login. First step we will explain from CDB$ROOT connectivity. Second step will explain from direct PDB connectivity. 1. From CDB$ROOT container -- Check status from CDB$ROOT col name for a10 Select name,open_mode from v$pdbs; NAME OPEN_MODE ---------- ---------- PDB$SEED READ ONLY XEPDB1 READ WRITE PDB2 READ WRITE --Shutdown the PDB2 database alter pluggable database PDB2 close immediate; Pluggable database altered. ---Note: Shutdown change OPEN_MODE value to MOUNTED. --Start the PDB2 database alter pluggable database PDB2 open; Pluggable database altered. 2. From PDB direct connectivity --check you are in PDB show con_name CON...
Post a Comment
Read more

Create Pluggable seed database

oracle@localhost ~ $ sqlplus SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 20 09:28:54 2022 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 MDB READ WRITE NO SQL> CREATE PLUGGABLE DATABASE MDB2 ADMIN USER PDBADMIN IDENTIFIED BY "monu" DEFAULT TABLESPACE USERS DATAFILE '/u01/app/oracle/oradata/ORCLPDB/MDB2/users01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED FILE_NAME_CONVERT=('/u01/app/oracle/oradata/ORCLPDB/pdbseed/','/u01/app/oracle/oradata/ORCLPDB/MDB2/'); 2 CREATE PLUGGABLE DATABASE MDB2 ADMIN USER PDBADMIN IDENTIFIED BY "monu" DEFAULT TABLESPACE USERS DATA...
Post a Comment
Read more

Pratik Kumar DBA - SQL History

  ORACLE SQL History .... define sql_id='7jycxu86n60qh' set lines 1000 pages 9999 col instance_number FOR 9999 HEA 'Inst' col end_time HEA 'End Time' col plan_hash_value HEA 'Plan|Hash Value' col rows_per_exec HEA 'Rows Per Exec' col et_secs_per_exec HEA 'Elap Secs|Per Exec' col cpu_secs_per_exec HEA 'CPU Secs|Per Exec' col io_secs_per_exec HEA 'IO Secs|Per Exec' col cl_secs_per_exec HEA 'Clus Secs|Per Exec' col ap_secs_per_exec HEA 'App Secs|Per Exec' col cc_secs_per_exec HEA 'Conc Secs|Per Exec' col pl_secs_per_exec HEA 'PLSQL Secs|Per Exec' col ja_secs_per_exec HEA 'Java Secs|Per Exec' col executions_total FOR 999,999 HEA 'Execs|Total' select 'gv$dba_hist_sqlstat' source,h.instance_number, to_char(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time, to_char(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH2...
Post a Comment
Read more