# 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
Anuj Singh Oracle DBA
Search This Blog
Total Pageviews
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)
Oracle DBA
anuj blog Archive
- ► 2011 (362)