# remove .xml files older than 1 month
54 08 * * * $HOME/bin/purge_adr.sh &> /dev/null
cat $HOME/bin/purge_adr.sh
#!/bin/bash
# purge XML files over 30 days old
export GRID_HOME=/u01/app/grid
export PATH=$GRID_HOME/bin:/bin:$PATH
SCRIPT_LOG=/home/grid/bin/purge_adr.log
SCRIPT_AGE=44640
date >> $SCRIPT_LOG
HOMES="diag/tnslsnr/irac01/listener_scan1
diag/tnslsnr/irac01/listener
diag/tnslsnr/irac01/mgmtlsnr
diag/tnslsnr/irac01/asmnet1lsnr_asm
diag/tnslsnr/irac01/listener_test"
for h in $HOMES
do
adrci_home=$h
echo $adrci_home
echo 'purging from : ' $adrci_home >> $SCRIPT_LOG
adrci exec="set homepath ${adrci_home}; purge -age ${SCRIPT_AGE}" -type ALERT >> $SCRIPT_LOG
done
exit
Monday 22 April 2024
Oracle Purge xml files
Oracle xml file
Wednesday 3 April 2024
runInstaller -silent -deinstall REMOVE_HOMES
Oracle 19c Deinstall Utility on OL/RHEL 8 fails with "ERROR:null" (Doc ID 2738856.1)
./runInstaller -silent -deinstall REMOVE_HOMES={"/u01/app/oracle/product/19.0.0/dbhome_1"}
[INS-04013] The argument [-deinstall] is not supported. Run /u01/app/oracle/product/19.0.0/dbhome_1/deinstall/deinstall for deinstallation.
use deinstall
on oracle 19c!!!!!!!!!!!!!!!!!
[oracle@oracledb dbhome_1]$ export CV_ASSUME_DISTID=OL7
[oracle@oracledb dbhome_1]$ ./deinstall
-bash: ./deinstall: Is a directory
[oracle@oracledb dbhome_1]$
[oracle@oracledb dbhome_1]$
[oracle@oracledb dbhome_1]$ cd d
data/ dbjava/ dbs/ deinstall/ demo/ diagnostics/ dmu/ drdaas/ dv/
[oracle@oracledb dbhome_1]$ cd d
data/ dbjava/ dbs/ deinstall/ demo/ diagnostics/ dmu/ drdaas/ dv/
[oracle@oracledb dbhome_1]$ cd deinstall/
[oracle@oracledb deinstall]$ ./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /u01/app/oraInventory/logs/
############ ORACLE DECONFIG TOOL START ############
######################### DECONFIG CHECK OPERATION START #########################
## [START] Install check configuration ##
Checking for existence of the Oracle home location /u01/app/oracle/product/19.0.0/dbhome_1
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
## [END] Install check configuration ##
Network Configuration check config START
Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check2024-04-02_02-48-23PM.log
Network Configuration check config END
Database Check Configuration START
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check2024-04-02_02-48-23PM.log
Use comma as separator when specifying list of values as input
Specify the list of database names that are configured in this Oracle home []: Database Check Configuration END
######################### DECONFIG CHECK OPERATION END #########################
####################### DECONFIG CHECK OPERATION SUMMARY #######################
Oracle Home selected for deinstall is: /u01/app/oracle/product/19.0.0/dbhome_1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2024-04-02_02-48-20-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2024-04-02_02-48-20-PM.err'
######################## DECONFIG CLEAN OPERATION START ########################
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_clean2024-04-02_02-48-23PM.log
Network Configuration clean config START
Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_clean2024-04-02_02-48-23PM.log
De-configuring backup files...
Backup files de-configured successfully.
The network configuration has been cleaned up successfully.
Network Configuration clean config END
######################### DECONFIG CLEAN OPERATION END #########################
####################### DECONFIG CLEAN OPERATION SUMMARY #######################
#######################################################################
############# ORACLE DECONFIG TOOL END #############
Using properties file /tmp/deinstall2024-04-02_02-47-44PM/response/deinstall_2024-04-02_02-48-20-PM.rsp
Location of logs /u01/app/oraInventory/logs/
############ ORACLE DEINSTALL TOOL START ############
####################### DEINSTALL CHECK OPERATION SUMMARY #######################
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2024-04-02_02-48-20-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2024-04-02_02-48-20-PM.err'
######################## DEINSTALL CLEAN OPERATION START ########################
## [START] Preparing for Deinstall ##
Setting LOCAL_NODE to oracledb
Setting CRS_HOME to false
Setting oracle.installer.invPtrLoc to /tmp/deinstall2024-04-02_02-47-44PM/oraInst.loc
Setting oracle.installer.local to false
## [END] Preparing for Deinstall ##
Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START
Detach Oracle home '/u01/app/oracle/product/19.0.0/dbhome_1' from the central inventory on the local node : Done
Delete directory '/u01/app/oracle/product/19.0.0/dbhome_1' on the local node : Done
The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/u01/app/oracle/product/version/db_1'.
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END
## [START] Oracle install clean ##
## [END] Oracle install clean ##
######################### DEINSTALL CLEAN OPERATION END #########################
####################### DEINSTALL CLEAN OPERATION SUMMARY #######################
Successfully detached Oracle home '/u01/app/oracle/product/19.0.0/dbhome_1' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/19.0.0/dbhome_1' on the local node.
Oracle Universal Installer cleanup was successful.
Review the permissions and contents of '/u01/app/oracle' on nodes(s) 'oracledb'.
If there are no Oracle home(s) associated with '/u01/app/oracle', manually delete '/u01/app/oracle' and its contents.
Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################
############# ORACLE DEINSTALL TOOL END #############
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='48vf4pg4g55 10' 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)
;
Subscribe to:
Posts (Atom)
