Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Sunday 18 February 2024

How to change Scheduler maintenance windows ?


How to change Scheduler maintenance windows?   ..

from web https://connor-mcdonald.com/2020/08/07/modifying-scheduler-windows/ set linesize 500 pagesize 400 col WINDOW_NAME for a25 col REPEAT_INTERVAL for a70 col DURATION for a25 col SCHEDULE_OWNER for a14 select SCHEDULE_OWNER ,window_name, repeat_interval, duration from dba_scheduler_windows --where 1=1 order by WINDOW_NAME
;


SCHEDULE_OWNER WINDOW_NAME               REPEAT_INTERVAL                                                        DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
               MONDAY_WINDOW             freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
               TUESDAY_WINDOW            freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
               WEDNESDAY_WINDOW          freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
               THURSDAY_WINDOW           freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
               FRIDAY_WINDOW             freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
               SATURDAY_WINDOW           freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                   +000 20:00:00 *
               SUNDAY_WINDOW             freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                   +000 20:00:00 *
               WEEKNIGHT_WINDOW          freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0  +000 08:00:00
               WEEKEND_WINDOW            freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                    +002 00:00:00 *

9 rows selected.



declare
   x sys.odcivarchar2list :=  sys.odcivarchar2list('SATURDAY');
 BEGIN
 
 for i in 1 .. x.count
 
 loop
   DBMS_SCHEDULER.disable(name => 'SYS.'||x(i)||'_WINDOW', force => TRUE);

   DBMS_SCHEDULER.set_attribute(
     name      => 'SYS.'||x(i)||'_WINDOW',
     attribute => 'REPEAT_INTERVAL',
     value     => 'FREQ=WEEKLY;BYDAY='||substr(x(i),1,3)||';BYHOUR=03;BYMINUTE=0;BYSECOND=0');

   DBMS_SCHEDULER.set_attribute(
     name      => 'SYS.'||x(i)||'_WINDOW',
     attribute => 'DURATION',
     value     =>  numtodsinterval(60, 'minute'));

   DBMS_SCHEDULER.enable(name=>'SYS.'||x(i)||'_WINDOW');
 end loop;
 END;
 /

 
 output !!!
 
 declare
   x sys.odcivarchar2list :=  sys.odcivarchar2list('SATURDAY');
 BEGIN

SQL> SQL>   2    3    4    5   for i in 1 .. x.count
  6
  7   loop
  8     DBMS_SCHEDULER.disable(name => 'SYS.'||x(i)||'_WINDOW', force => TRUE);
  9
 10     DBMS_SCHEDULER.set_attribute(
 11       name      => 'SYS.'||x(i)||'_WINDOW',
 12       attribute => 'REPEAT_INTERVAL',
 13       value     => 'FREQ=WEEKLY;BYDAY='||substr(x(i),1,3)||';BYHOUR=03;BYMINUTE=0;BYSECOND=0');

   DBMS_SCHEDULER.set_attribute(
     name      => 'SYS.'||x(i)||'_WINDOW',
     attribute => 'DURATION',
     value     =>  numtodsinterval(60, 'minute'));

   DBMS_SCHEDULER.enable(name=>'SYS.'||x(i)||'_WINDOW');
 end loop;
 END;
 / 14   15   16   17   18   19   20   21   22   23

PL/SQL procedure successfully completed.

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


from 

 SCHEDULE_OWNER WINDOW_NAME               REPEAT_INTERVAL                                                        DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
               
               SATURDAY_WINDOW           freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                   +000 20:00:00 *
               


 to 
 
 SCHEDULE_OWNER WINDOW_NAME               REPEAT_INTERVAL                                                        DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
              SATURDAY_WINDOW           FREQ=WEEKLY;BYDAY=SAT;BYHOUR=03;BYMINUTE=0;BYSECOND=0                  +000 01:00:00
               

Tuesday 13 February 2024

How to Create a SQL Patch to add Hints ?


Oracle     How to Create a SQL Patch to add Hints ?  

How to Create a SQL Patch to add Hints to Application SQL Statements (Doc ID 1931944.1) want use sql patch on below sql for hint select/*+ opt_param('_optimizer_extended_cursor_sharing_rel' 'none')*/ select d.deptno, d.dname, max(sal) from emp e , dept d where e.deptno = d.deptno and d.deptno> 10 group by d.deptno,d.dname; set linesize 400 col sql_text for a50 select SQL_ID,SQL_TEXT from gv$sql where 1=1 and SQL_TEXT like '%and d.deptno> 10%'; SQL_ID SQL_TEXT ------------- -------------------------------------------------- fzsf6kw7q2vxt select d.deptno, d.dname, max(sal) from emp e , de pt d where e.deptno = d.deptno and d.deptno> 10 gr oup by d.deptno,d.dname from web !! declare v1 varchar2(128); begin v1 := dbms_sqldiag.create_sql_patch( sql_id => 'g2z10tbxyz6b0', name => 'validate_fk', -- hint_text => 'ignore_optim_embedded_hints' -- hint_text => 'parallel(a@sel$1 8)' -- worked -- hint_text => 'parallel(8)' -- worked -- hint_text => q'{opt_param('_fast_full_scan_enabled' 'false')}' -- worked hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}' ); dbms_output.put_line(v1); end; / SET SERVEROUTPUT ON DECLARE v_sql_id VARCHAR2 (13) := ''; v_patch_name VARCHAR2 (30); v_hint VARCHAR2 (4096) := 'NO_QUERY_TRANSFORMATION(@"SEL$1")'; BEGIN v_patch_name := DBMS_SQLDIAG.create_sql_patch (sql_id => v_sql_id, hint_text => v_hint); DBMS_OUTPUT.put_line (v_patch_name); END; / === set serveroutput on declare v1 varchar2(128); begin v1 := dbms_sqldiag.create_sql_patch( sql_id => 'fzsf6kw7q2vxt', name => 'optimizer_extended_cursor_sharing_rel', hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}' ); dbms_output.put_line(v1); end; / set linesize 400 col sql_text for a50 col name for a37 select name, status, created, sql_text from dba_sql_patches where name='optimizer_extended_cursor_sharing_rel'; set linesize 400 set numf 99999999999999999999999999 select SQL_ID,CHILD_NUMBER,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE,plan_hash_value plan_hash,EXACT_MATCHING_SIGNATURE from v$sql where sql_id = 'fzsf6kw7q2vxt'; SQL_ID CHILD_NUMBER SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE PLAN_HASH EXACT_MATCHING_SIGNATURE ------------- ------------ --------------- ------------------------------------- ------------------------------------- --------------------------- --------------------------- fzsf6kw7q2vxt 0 2006461124 9282672672555810008 fzsf6kw7q2vxt 2 optimizer_extended_cursor_sharing_rel SQL_PLAN_81npdmnqyng6s61f3d804 2006461124 9282672672555810008 col OUTLINE_HINTS for a40 select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints from xmltable('/outline_data/hint' passing (select xmltype(comp_data) xml from sys.sqlobj$data where signature = 9282672672555810008) ) x; OUTLINE_HINTS ---------------------------------------- opt_param('_optimizer_extended_cursor_sh aring_rel' 'none') If needed patch can be disabled using EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('optimizer_extended_cursor_sharing_rel', 'STATUS', 'DISABLED'); If you want to drop the patch, EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name=> 'optimizer_extended_cursor_sharing_rel'); === Test ---- declare v1 varchar2(128); primary:sys@IBRAC-ibrac2 sqlplus> 2 3 begin v1 := dbms_sqldiag.create_sql_patch( 4 5 sql_id => 'fzsf6kw7q2vxt', 6 name => 'optimizer_extended_cursor_sharing_rel', 7 hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}' 8 ); 9 dbms_output.put_line(v1); 10 end; 11 / optimizer_extended_cursor_sharing_rel PL/SQL procedure successfully completed. set linesize 400 col sql_text for a50 col SQL_PROFILE for a15 col SQL_PATCH for a40 col SQL_PLAN_BASELINE for a35 select SQL_ID,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE,SQL_TEXT from v$sql where 1=1 --and SQL_TEXT like '%and d.deptno> 10%' and sql_id='fzsf6kw7q2vxt' ; SQL_ID SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE SQL_TEXT ------------- --------------- ---------------------------------------- ----------------------------------- -------------------------------------------------- fzsf6kw7q2vxt select d.deptno, d.dname, max(sal) from emp e , de pt d where e.deptno = d.deptno and d.deptno> 10 gr oup by d.deptno,d.dname fzsf6kw7q2vxt optimizer_extended_cursor_sharing_rel SQL_PLAN_81npdmnqyng6s61f3d804 select d.deptno, d.dname, max(sal) from emp e , de pt d where e.deptno = d.deptno and d.deptno> 10 gr set linesize 400 col sql_text for a50 col name for a37 col CATEGORY for a15 select name, status, created,category, sql_text from dba_sql_patches where name='optimizer_extended_cursor_sharing_rel'; NAME STATUS CREATED SQL_TEXT -------------------------------------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------------- -------------------------------------------------- optimizer_extended_cursor_sharing_rel ENABLED 13-FEB-24 02.57.04.000000 AM select d.deptno, d.dname, max(sal) from emp e , dept d where e.deptno = d.deptno and d.deptno> 10 group by d.deptno,d.dname SELECT CAST (EXTRACTVALUE (VALUE (x), '/hint') AS VARCHAR2 (500)) AS outline_hints FROM XMLTABLE ( '/outline_data/hint' PASSING (SELECT xmltype (comp_data) xml FROM sys.sqlobj$data WHERE signature = (SELECT signature FROM dba_sql_patches WHERE name = 'optimizer_extended_cursor_sharing_rel'))) x; OUTLINE_HINTS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ opt_param('_optimizer_extended_cursor_sharing_rel' 'none') set pagesize 300 select sql_id,address, hash_value ,count(*) from v$sql where 1=1 and SQL_ID='fzsf6kw7q2vxt' group by sql_id,address, hash_value --having count(*)>20 ; SQL_ID ADDRESS HASH_VALUE COUNT(*) ------------- ---------------- ---------- ---------- fzsf6kw7q2vxt 00000000CC66CA60 258043833 2 You can view all sql patches: select name,category,status,sql_text from dba_sql_patches; define sql_id='fzsf6kw7q2vxt' set numf 99999999999999999.99 linesize 400 col CHILD_NUMBER for 99999 col SQL_PATCH for a37 col SQL_PLAN_BASELINE for a37 select --SQL_ID sql_id1, SQL_ID,CHILD_NUMBER,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE, plan_hash_value plan_hash, executions, PX_SERVERS_EXECUTIONS pxe, cpu_time/1000 cpu_ms, elapsed_time/1000 ela_ms, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_ela, IO_INTERCONNECT_BYTES/1024/1024/1024 io_inter_gb, PHYSICAL_READ_BYTES/1024/1024/1024 PHYSICAL_READ_GB, PHYSICAL_READ_BYTES/1024/1024/decode(nvl(executions,0),0,1,executions) PIO_MB_PE, buffer_gets/decode(nvl(executions,0),0,1,executions) LIOS_PE, disk_reads/decode(nvl(executions,0),0,1,executions) PIOS_PE from gv$sql where 1=1 and sql_id = ('&sql_id') order BY inst_id, sql_id, hash_value, child_number,SQL_ID,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE ; SQL_ID CHILD_NUMBER SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE PLAN_HASH EXECUTIONS PXE CPU_MS ELA_MS AVG_ELA IO_INTER_GB PHYSICAL_READ_GB PIO_MB_PE LIOS_PE PIOS_PE ------------- --------------------- --------------- ------------------------------------- ------------------------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- fzsf6kw7q2vxt .00 2006461124.00 1.00 .00 28.01 32.25 .03 .00 .00 .01 208.00 1.00 fzsf6kw7q2vxt 2.00 optimizer_extended_cursor_sharing_rel SQL_PLAN_81npdmnqyng6s61f3d804 2006461124.00 1.00 .00 12.58 13.70 .01 .00 .00 .02 5.00 2.00 set numformat 99999999999999999999999999 select EXACT_MATCHING_SIGNATURE from v$sql where sql_id = 'fzsf6kw7q2vxt'; EXACT_MATCHING_SIGNATURE --------------------------- 2640606212120450132 Then pass the signature to below query to get the hints: col OUTLINE_HINTS for a40 select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints from xmltable('/outline_data/hint' passing (select xmltype(comp_data) xml from sys.sqlobj$data where signature = 9282672672555810008) ) x; col outline_hints for a40 select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints from xmltable('/outline_data/hint' passing (select xmltype(comp_data) xml from sys.sqlobj$data where signature = 9282672672555810008) ) x; define sql_id='fzsf6kw7q2vxt' set numf 99999999999999999.99 linesize 400 col CHILD_NUMBER for 99999 col SQL_PATCH for a37 col SQL_PLAN_BASELINE for a37 select --SQL_ID sql_id1, SQL_ID,CHILD_NUMBER,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE, plan_hash_value plan_hash, executions, PX_SERVERS_EXECUTIONS pxe, cpu_time/1000 cpu_ms, elapsed_time/1000 ela_ms, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_ela, IO_INTERCONNECT_BYTES/1024/1024/1024 io_inter_gb, PHYSICAL_READ_BYTES/1024/1024/1024 PHYSICAL_READ_GB, PHYSICAL_READ_BYTES/1024/1024/decode(nvl(executions,0),0,1,executions) PIO_MB_PE, buffer_gets/decode(nvl(executions,0),0,1,executions) LIOS_PE, disk_reads/decode(nvl(executions,0),0,1,executions) PIOS_PE --,SQL_ID, --SQL_PROFILE, --SQL_PATCH,SQL_PLAN_BASELINE from gv$sql where 1=1 and sql_id = ('&sql_id') order BY inst_id, sql_id, hash_value, child_number,SQL_ID,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE ; SQL_ID CHILD_NUMBER SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE PLAN_HASH EXECUTIONS PXE CPU_MS ELA_MS AVG_ELA IO_INTER_GB PHYSICAL_READ_GB PIO_MB_PE LIOS_PE PIOS_PE ------------- ------------ --------------- ------------------------------------- ------------------------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- fzsf6kw7q2vxt 0 2006461124.00 1.00 .00 28.01 32.25 .03 .00 .00 .01 208.00 1.00 fzsf6kw7q2vxt 2 optimizer_extended_cursor_sharing_rel SQL_PLAN_81npdmnqyng6s61f3d804 2006461124.00 1.00 .00 12.58 13.70 .01 .00 .00 .02 5.00 2.00 define sql_id='fzsf6kw7q2vxt' col PLAN_TABLE_OUTPUT for a200 SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&sql_id', cursor_child_no => 2, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID fzsf6kw7q2vxt, child number 2 ------------------------------------- select d.deptno, d.dname, max(sal) from emp e , dept d where e.deptno = d.deptno and d.deptno> 10 group by d.deptno,d.dname Plan hash value: 2006461124 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | O/1/M | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 5 (100)| | | | | | 1 | HASH GROUP BY | | 1 | 20 | 5 (20)| 00:00:01 | 1088K| 1088K| 1/0/0| |* 2 | HASH JOIN | | 14 | 280 | 4 (0)| 00:00:01 | 1743K| 1743K| 1/0/0| |* 3 | TABLE ACCESS FULL| DEPT | 3 | 39 | 2 (0)| 00:00:01 | | | | | 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 | | | | -------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / D@SEL$1 4 - SEL$1 / E@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "D"@"SEL$1") FULL(@"SEL$1" "E"@"SEL$1") LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1") USE_HASH(@"SEL$1" "E"@"SEL$1") USE_HASH_AGGREGATION(@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("E"."DEPTNO"="D"."DEPTNO") 3 - filter("D"."DEPTNO">10) Note ----- - SQL patch "optimizer_extended_cursor_sharing_rel" used for this statement <<<<<<<<<<<<<<<<<<< sql is using sql patch - SQL plan baseline SQL_PLAN_81npdmnqyng6s61f3d804 used for this statement - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 57 rows selected.

====


set linesize 150 pagesize 300
define sql_id='9s45v5rhut05y'
define plan_hash_value=3134758917

--SET LINESIZE 200 PAGESIZE 100
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(sql_id=>'&sql_id',plan_hash_value=>&plan_hash_value ,format=>'ALL +ALIAS +COST +BYTES +predicate +note +adaptive +report +outline'));


====
===========================================
 below hint for Bind mismatch(33)? and test 
/*+ opt_param('_optimizer_use_feedback' 'false')
================================================



https://github.com/tanelpoder/tpt-oracle/blob/master/nonshared2.sql
	 
	 define 2='48vf4pg4g5510'
set linesize 200 	 
col REASON_XML for a40
col REASON for a20
define 1=100
	 
	 COL nonshared_sql_id HEAD SQL_ID FOR A13
COL nonshared_child HEAD CHILD# FOR A10
COL nonshared_reason_and_details HEAD REASON FOR A60 WORD_WRAP
COL reason_xml FOR A100 WORD_WRAP &1
col REASON for a20
BREAK ON nonshared_sql_id 

SELECT 
    '&2' nonshared_sql_id
  , EXTRACTVALUE(VALUE(xs), '/ChildNode/ChildNumber') nonshared_child
  , EXTRACTVALUE(VALUE(xs), '/ChildNode/reason') || ': ' ||  EXTRACTVALUE(VALUE(xs), '/ChildNode/details') nonshared_reason_and_details
  , VALUE(xs) reason_xml
FROM TABLE (
    SELECT XMLSEQUENCE(EXTRACT(d, '/Cursor/ChildNode')) val FROM (
        SELECT 
            --XMLElement("Cursor", XMLAgg(x.extract('/doc/ChildNode')))
            -- the XMLSERIALIZE + XMLTYPE combo is included for avoiding a crash in qxuageag() XML aggregation function
            XMLTYPE (XMLSERIALIZE( DOCUMENT XMLElement("Cursor", XMLAgg(x.extract('/doc/ChildNode')))) ) d
        FROM 
            v$sql_shared_cursor c
          , TABLE(XMLSEQUENCE(XMLTYPE('<doc>'||c.reason||'</doc>'))) x
        WHERE
          c.sql_id = '&2' and c.child_number < 5
    )
) xs
/

Bug 31211220 – High version count (cursor leaks) due to bind_equiv_failure (Doc ID 31211220.8)
SQL's Are Not Getting Shared due to BIND_EQUIV_FAILURE in 12.2 (Doc ID 2635456.1)


Monday 29 January 2024

How to set fix control ?

 






Bug 31211220 – High version count (cursor leaks) due to bind_equiv_failure (Doc ID 31211220.8)
SQL's Are Not Getting Shared due to BIND_EQUIV_FAILURE in 12.2 (Doc ID 2635456.1)



alter system set "_fix_control"='17443547:ON';
  
  

set linesize 300 pagesize 300
col fix_control_value 	for a18 
col sql_feature 		for a30

select bugno,value,
case
when value=1 then 'fix_control_on'
when value=0 then 'fix_control_off'
end as fix_control_value,
optimizer_feature_enable,
sql_feature,
is_default,
event,
con_id,
description 
from v$system_fix_control 
where bugno=17443547
;



     BUGNO      VALUE FIX_CONTROL_VALUE  OPTIMIZER_FEATURE_ENABLE  SQL_FEATURE                    IS_DEFAULT      EVENT     CON_ID DESCRIPTION
---------- ---------- ------------------ ------------------------- ------------------------------ ---------- ---------- ---------- ----------------------------------------------------------------
  17443547          1 fix_control_on     12.2.0.1                  QKSFM_CURSOR_SHARING_17443547           1          0          1 Adaptive Cursor Sharing for single bind constant expressions



   sqlplus> alter system set "_fix_control"='17443547:OFF';

System altered.

  
  
    BUGNO      VALUE FIX_CONTROL_VALUE  OPTIMIZER_FEATURE_ENABLE  SQL_FEATURE                    IS_DEFAULT      EVENT     CON_ID DESCRIPTION
---------- ---------- ------------------ ------------------------- ------------------------------ ---------- ---------- ---------- ----------------------------------------------------------------
  17443547          0 fix_control_off    12.2.0.1                  QKSFM_CURSOR_SHARING_17443547           0          0          1 Adaptive Cursor Sharing for single bind constant expressions


  ===

  to check fix control on prod and standby
  
col DB_UNIQUE_NAME for a15
select max(SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME')) "DB_UNIQUE_NAME",count(*) "fix_control_on" from v$system_fix_control  where 1=1 and value=1 ;



col DB_UNIQUE_NAME for a15
select max(SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME')) "DB_UNIQUE_NAME",count(*) "fix_control_off" from v$system_fix_control  where 1=1 and value=0 ;



col DB_UNIQUE_NAME for a15
col "v$parameter" for 99999
select max(SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME')) "DB_UNIQUE_NAME",count(*) "v$parameter" from v$parameter ;

  

SET LINESIZE 300
COLUMN name          FORMAT A30
COLUMN current_value FORMAT A30
COLUMN sid           FORMAT A8
COLUMN spfile_value  FORMAT A30
col DB_UNIQUE_NAME for a15
SELECT p.name,
SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME') as DB_UNIQUE_NAME,
       i.instance_name AS sid,
       p.value AS current_value,
       sp.sid,
       sp.value AS spfile_value      
FROM   v$spparameter sp,
       v$parameter p,
       v$instance i
WHERE  1=1
and sp.name   = p.name
--AND    sp.value != p.value
and sp.name like '%fix_control%';


  


Sunday 17 December 2023

PLAN_TABLE_OUTPUT User has no SELECT privilege on V$SESSION





SQL> show user
USER is "SCOTT"


select * from table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION



*****

as sys or system 
grant below to scott !!

GRANT SELECT ON v_$session TO scott ;
GRANT SELECT ON v_$sql_plan_statistics_all TO scott ;
GRANT SELECT ON v_$sql_plan TO scott ;
GRANT SELECT ON v_$sql TO scott ;



===




select /*+ domtest */ count(*), max(col2) from t1 where flag = :n;

  COUNT(*) MAX(COL2)
---------- --------------------------------------------------
     49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  45sygvgu8ccnz, child number 0
-------------------------------------
select /*+ domtest */ count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49999 |  1464K|   273   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"=:N)


19 rows selected.

Tuesday 12 December 2023

How to Enable Oracle Change Tracking (BCT)?

How to Enable Oracle Change Tracking (BCT)? ===
 

SHOW PARAMETER DB_CREATE_FILE_DEST   File will create here 
 

--To Enable : 
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING ;
 
--To check 
col filename for a70
select filename,status from v$block_change_tracking;
 
====
--to disable:
alter database disable block change tracking; 
 
--To check
col filename for a70
select filename,status from v$block_change_tracking;


or

set linesize 400
col FILENAME for a70
col NAME for a15
col BCT_STATUS for a15
col MB for 99999.99
select inst_id, dbid, name, db_unique_name, open_mode, log_mode, flashback_on, switchover_status, database_role,CONTROLFILE_TYPE,filename, BCT_STATUS, bytes/1024/1024 MB
             from (select inst_id, dbid, name, db_unique_name, open_mode, log_mode, flashback_on, switchover_status, database_role,CONTROLFILE_TYPE  from gv$database),
                  (select filename, status BCT_STATUS, bytes    from   v$block_change_tracking)
;



Friday 1 December 2023

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

ORA-01139: RESETLOGS option only valid after an incomplete database recovery




[oracle@wcp12cr2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Dec 1 05:41:46 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<




https://anuj-singh.blogspot.com/2021/10/rman-restore-database.html

Check the health of datafile !!! :)

set linesize 300 pagesize 100
col inst_id                      for 9999999                 heading 'Instance #'
col file_nr                      for 9999999                 heading 'File #'
col file_name                    for A70                     heading 'File name'
col checkpoint_change_nr         for 99999999999999          heading 'Checkpoint #'
col checkpoint_change_time       for A20                     heading 'Checkpoint time'
col last_change_nr               for 99999999999999          heading 'Last change #'
col SCNStatus for a15
SELECT
 fe.inst_id,
fe.CON_ID,  ---- for >12c
 fe.fenum file_nr,
 fn.fnnam file_name,
 TO_NUMBER (fe.fecps) checkpoint_change_nr,
 fe.fecpt checkpoint_change_time,
fe.fests last_change_nr,
NVL2(fe.fecps, '<-good ----------------------------------------------------------------------="" --------------------="" ----------------="" ---------------="" ----------="" --------="" -------="" ----="" -="" 03:27:07="" 05:41:46="" 0="" 10="" 11="" 12.1.0.1.0="" 12="" 12c="" 13="" 18="" 1982="" 1="" 2013="" 2023="" 2270166="" 2="" 3="" 4="" 5="" 64bit="" 65535="" 6="" 7="" 8="" 9="" advanced="" all="" analytics="" and="" application="" as="" below="" bitand="" by="" c="" change="" checkpoint="" con_id="" connected="" copyright="" database="" db="" dec="" decode="" ecover="" edition="" enterprise="" fe.fedup="" fe.fefnh="fn.fnnum" fe.fenum="" fe.fepax="0)" fe.festa="" fe.fetsn="" fe="" file="" fn.fnflg="" fn.fnfno="fe.fenum" fn.fnnam="" fn.fntyp="4" fn="" fri="" from="" ias_opss.dbf="" iasactivities.dbf="" iasjive.dbf="" iaswebcenter.dbf="" iau.dbf="" instance="" is="" kccfe="" kccfn="" last="" lus:="" max="" mds.dbf="" name="" not="" null="" ocs.dbf="" olap="" on="" online="" ood="" option="" options="" or="" oracle.="" oracle="" oradata="" orcl="" order="" partitioning="" production="" real="" recover="" release="" reserved.="" rights="" rows="" scn="" scnstatus="" selected.="" sql="" sqlplus="" status="" svctbl.dbf="" sysaux01.dbf="" sysdba="" system01.dbf="" system="" testing="" the="" this="" time="" to:="" to="" try="" undotbs01.dbf="" users01.dbf="" wcp12cr2="" webcenter_portlet.dbf="" where="" with="" x=""> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery




SQL> recover database until cancel;
Media recovery complete.


SQL> alter database open resetlogs;

Database altered.

Thursday 23 November 2023

Oracle Schema Size

Oracle schema size  ... 



DEFINE schema_name = 'XXXX'
set linesize 300

col schema_size_gb  for 9999999.99

SELECT sum(sizegb) schema_size_gb FROM (
-- tablesize !!!
--SELECT *  FROM (
SELECT
owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024/1024 AS sizegb,tablespace_name, extents, initial_extent,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_MB
FROM (
-- Tables
SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,segment_name AS table_name, bytes,tablespace_name, extents, initial_extent
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
-- Indexes
SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,i.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
--LOB Segments
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
-- LOB Indexes
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX'
)
WHERE owner in UPPER('&schema_name')
)
--WHERE total_table_MB > 10
--ORDER BY total_table_MB DESC, MB DESC
/


====


with count 

set linesize 100 pagesize 200 
col Owner for a28
select obj.owner "Owner", obj_cnt "Objects",decode(seg_size, NULL, 0, seg_size) "size GB"
from ( select owner, count(*) obj_cnt from dba_objects group by owner) obj,
     ( select owner, ceil(sum(bytes)/1024/1024/1024) seg_size from dba_segments group by owner) segment
where obj.owner = segment.owner(+)
order by 3 desc, 2 desc, 1;

Oracle DBA

anuj blog Archive