SQL Shared Area - BIND MISMATCH

select reason_not_shared, count(*) cursors, count(distinct sql_id) sql_ids
from v$sql_shared_cursor
unpivot(val for reason_not_shared in(
 UNBOUND_CURSOR,SQL_TYPE_MISMATCH,OPTIMIZER_MISMATCH,OUTLINE_MISMATCH,
  STATS_ROW_MISMATCH,LITERAL_MISMATCH,FORCE_HARD_PARSE,EXPLAIN_PLAN_CURSOR,
  BUFFERED_DML_MISMATCH,PDML_ENV_MISMATCH,INST_DRTLD_MISMATCH,SLAVE_QC_MISMATCH,
  TYPECHECK_MISMATCH,AUTH_CHECK_MISMATCH,BIND_MISMATCH,DESCRIBE_MISMATCH,
  LANGUAGE_MISMATCH,TRANSLATION_MISMATCH,BIND_EQUIV_FAILURE,INSUFF_PRIVS,
  INSUFF_PRIVS_REM,REMOTE_TRANS_MISMATCH,LOGMINER_SESSION_MISMATCH,INCOMP_LTRL_MISMATCH,
  OVERLAP_TIME_MISMATCH,EDITION_MISMATCH,MV_QUERY_GEN_MISMATCH,USER_BIND_PEEK_MISMATCH,
  TYPCHK_DEP_MISMATCH,NO_TRIGGER_MISMATCH,FLASHBACK_CURSOR,ANYDATA_TRANSFORMATION,
  PDDL_ENV_MISMATCH,TOP_LEVEL_RPI_CURSOR,DIFFERENT_LONG_LENGTH,LOGICAL_STANDBY_APPLY,
  DIFF_CALL_DURN,BIND_UACS_DIFF,PLSQL_CMP_SWITCHS_DIFF,CURSOR_PARTS_MISMATCH,
  STB_OBJECT_MISMATCH,CROSSEDITION_TRIGGER_MISMATCH,PQ_SLAVE_MISMATCH,TOP_LEVEL_DDL_MISMATCH,
  MULTI_PX_MISMATCH,BIND_PEEKED_PQ_MISMATCH,MV_REWRITE_MISMATCH,ROLL_INVALID_MISMATCH,
  OPTIMIZER_MODE_MISMATCH,PX_MISMATCH,MV_STALEOBJ_MISMATCH,FLASHBACK_TABLE_MISMATCH,
  LITREP_COMP_MISMATCH,PLSQL_DEBUG,LOAD_OPTIMIZER_STATS,ACL_MISMATCH,
  FLASHBACK_ARCHIVE_MISMATCH,LOCK_USER_SCHEMA_FAILED,REMOTE_MAPPING_MISMATCH,LOAD_RUNTIME_HEAP_FAILED,
  HASH_MATCH_FAILED,PURGED_CURSOR,BIND_LENGTH_UPGRADEABLE,USE_FEEDBACK_STATS
))
where val = 'Y'
group by reason_not_shared

order by 2 desc, 3, 1;

---->See output as below and see the issues with BIND MISMATCH and other MISMATCH

BIND_MISMATCH 111124 300

LANGUAGE_MISMATCH 6181 3023

AUTH_CHECK_MISMATCH 5073 2613

TRANSLATION_MISMATCH 5069 2611

BIND_LENGTH_UPGRADEABLE 1663 863

BIND_EQUIV_FAILURE 181 51

BIND_UACS_DIFF 69 69

OPTIMIZER_MISMATCH 51 45

LOAD_OPTIMIZER_STATS 33 29

Solution --To clean the queries periodically every 4 hours or so, you can write job also with procedure to execute in regular interval with sharable_mem > 400MB as an example.

SELECT *
          FROM   v$sqlarea
          WHERE  version_count > 100
          ORDER BY sharable_mem desc;

-->Take top 5 shareble_memory sql ids and purge them to release shared pool area (SGA).

 select 'exec sys.dbms_shared_pool.purge ('''||address||','||hash_value||''', ''C'');'

from v$sqlarea
where sql_id='656u1muc0d4h0';

-->Run below to purge

exec sys.dbms_shared_pool.purge ('0700010300A34620,2550567424', 'C');

--We also implemented below in 11gr2 in db

alter system set “_optimizer_use_feedback” = false scope=both;

alter system set "_optimizer_adaptive_cursor_sharing"=false scope=both;

alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;

--Extra - to check _ underscore hidden parameter values

col parameter for a35

col "Session value" for a10

col "Instance value" for a10

SELECT x.ksppinm "Parameter", Y.ksppstvl "Session Value",Z.ksppstvl "Instance Value" FROM x$ksppi X, x$ksppcv Y, x$ksppsv Z WHERE x.indx = Y.indx AND x.indx = z.indx AND x.ksppinm like '_optimizer%' order by x.ksppinm;

----What is Cardinality feedback introduced in 11gr2
Cardinality Feedback (Oracle Database 10g to 11g Change)
Cardinality feedback is an enhancement made to the Optimizer in Oracle Database 11g Release
2. Cardinality feedback compares cardinality estimates used to derive the plan with the actual
cardinality seen in the first execute. If the estimate is 2X off, the cursor is marked for hard parse
next time around. The cardinality information seen at first execute is supplied at the next hard
parse thus allowing the Optimizer an opportunity to improve on the plan now that it knows more
about the actual cardinality seen in the query. If cardinality feedback is used, it is displayed in the
section of the execution plan. Cardinality feedback works for predicates on tables, indexes and
group by clauses. It does not help for cardinality mis-estimates for joins. Feedback is not
persistent on disk, it resides in memory only. The Optimizer will need to “relearn” something if the
database is shutdown and restarted. (Related to _optimizer_use_feedback parameter.)

ORA-20005: object statistics are locked (stattype = ALL) - Solution

After creation index as below gather index giving error

 create index VIJ.VIJ_SALES_INFO_IDX1 on VIJ.VIJ_SALES_INFO(VALUE1) ;

EXEC DBMS_STATS.gather_index_stats('VIJ', 'VIJ_SALES_INFO_IDX1');

--Error while gather_index_stats--------

Error starting at line : 5 in command -

BEGIN DBMS_STATS.gather_index_stats('VIJ', 'VIJ_SALES_INFO_IDX1'); END;

Error report -

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 18701

ORA-06512: at "SYS.DBMS_STATS", line 18724

ORA-06512: at line 1

Resolution :

Cause

SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='VIJ_SALES_INFO' and owner='VIJ';

You would get STATTYPE_LOCKED  as value - ALL

STATTYPE_LOCKED – ALL stats are locked for the table
STATTYPE_LOCKED – NULL stats are not locked

EXEC DBMS_STATS.unlock_table_stats('VIJ','VIJ_SALES_INFO');

After unlock table stats , able to do gather index stats.

EXEC DBMS_STATS.gather_index_stats('VIJ', 'VIJ_SALES_INFO_IDX1');

PL/SQL procedure successfully completed.

FAQ - On Materialized Views and DBMS_JOB Jobs

 Refer Oracle Support Note - FAQ: On Materialized Views and DBMS_JOB Jobs (Doc ID 1365482.1)

Oracle expdp - impdp tricks

Getting started

1) Create expimpuser user (In place of expimpuser you can give any name or use existing database user name, don't use sys)

   and give connect, resource to expimpuser

CREATE USER expimpuser IDENTIFIED BY passwowrd123 DEFAULT TABLESPACE APPS_TS;

GRANT CONNECT, RESOURCE TO expimpuser;

2) Create physical directory on the system like /u01/expimpdir -change path accroding to your system.

mkdir /u01/expimpdir

chmod -R 777 /u01/expimpdir

3) Create DBA dump directory where yyo would be placining all exmport dmp filies.

CREATE OR REPLACE DIRECTORY DATA_PUMP AS '/u01/expimpdir';

SELECT * FROM DBA_DIRECTORIES;

GRANT READ,WRITE ON DIRECTORY DATA_PUMP TO expimpuser;

GRANT READ,WRITE ON DIRECTORY DATA_PUMP TO system;

Dirrerent export scenarios

1) Export full schema from source to target server

  Lets say you want to export all the hr user data 

  GRANT  DATAPUMP_EXP_FULL_DATABASE  TO  hr;

  GRANT READ,WRITE ON DIRECTORY DATA_PUMP TO hr;

    ->If using container architectre use @PDB1(PDB1 is name of pluggable db) to connect to specific pluggable database.

%U would append numbers to dump directory once the filesize reaches 100GB.

Use PARALLEL to make it fast and compress to reduce the dump file size.

expdp hr/h#rc#01@PDB1 directory=DATA_PUMP dumpfile=HR_PDB1_%U.dmp 

COMPRESS=Y filesize=100G PARALLEL=5 logfile=EXPDP_HR_PDB1_20Jan23.log

-> Non PDB , don't use @PDB1 (PDB1 is name of pluggable db)

expdp hr/h#rc#01@PDB1 directory=DATA_PUMP dumpfile=HR_PDB1_%U.dmp 

COMPRESS=Y filesize=100G PARALLEL=5 logfile=EXPDP_HR_PDB1_20Jan24.log

->To import , make user hr user exists in new database , copy the dump files to target server /u01/expimpdir

impdp hr/h#rc#01@PDB1 directory=DATA_PUMP dumpfile=HR_PDB1_%U.dmp 

PARALLEL=5 logfile=IMPDP_HR_PDB1_20Jan24.log

2) Export View as table

expdp system/passwowrd##@PDB1  directory=DATA_PUMP dumpfile=HR_HR_VIEW1_%U.dmp 

COMPRESS=Y filesize=100G views_as_tables=HR.HR_VIEW PARALLEL=5 logfile=OHR_HR_VIEW1_17Jan24.log

Exclude indexes

Monitor jobs using

 select * from DBA_DATAPUMP_JOBS;

---Tips 

Always use METRICS=YES

and LOGTIME=ALL

--Monitoring

Monitor a Data Pump process in V$SESSION_LONGOPS

$ expdp ... job_name=DUMPJOB

SQL> select sid, serial#, sofar, totalwork

from v$session_longops

where opname = 'DUMPJOB' and sofar != totalwork;

sofar Shows how much work in MB has been done so far in relation to totalwork

totalwork Shows the total amount of work in MB

---Current status

SELECT sl.sid, sl.serial#, sl.sofar, sl.totalwork, dp.owner_name, dp.state, dp.job_mode

     FROM v$session_longops sl, v$datapump_job dp

     WHERE sl.opname = dp.job_name

     AND sl.sofar != sl.totalwork;

select * from dba_datapump_jobs;

--Run time details

SELECT b.username, a.sid, b.opname, b.target,

            round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING,

            to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time

     FROM v$session_longops b, v$session a

     WHERE a.sid = b.sid      ORDER BY 6;

--sofar work done

SELECT sl.sid, sl.serial#, sl.sofar, sl.MESSAGE,sl.totalwork, dp.owner_name, dp.state, dp.job_mode

FROM v$session_longops sl, v$datapump_job dp

WHERE sl.opname = dp.job_name;

Oracle Tuning Advisor - Running SQL Tuning advisor steps

 1. Create Tuning Task

 

DECLARE l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => '4us78thunhn00',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 500,

                          task_name   => '4us78thunhn00_tuning_task1',

                          description => 'Tuning task1 for statement 4us78thunhn00');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

 

 

SQL> begin   

DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => '4us78thunhn00_tuning_task1', parameter => 'TIME_LIMIT', value => 15000);   

end;   

PL/SQL procedure successfully completed. 

 

2. Execute Tuning task:

 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '4us78thunhn00_tuning_task1');

 

3. Get the Tuning advisor report.

set long 65536

set longchunksize 65536

set linesize 600

set pagesize 9000

select dbms_sqltune.report_tuning_task('4us78thunhn00_tuning_task1') from dual;

 

 

4. Get list of tuning task present in database:

 

We can get the list of tuning tasks present in database from DBA_ADVISOR_LOG

 

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;

5. Drop a tuning task:

execute dbms_sqltune.drop_tuning_task('4us78thunhn00_tuning_task1');