Advise: Setting underScore/Hidden Parameters

Please be aware no hidden parameters (parameters starting with “_”) should not be set unless recommended by Oracle explicitly or in case of a perfect match to the case.

This is explained on MOS Best Practices and Guidelines for Setting underScore/Hidden Parameter to Workaround and/or Resolve Customers Problems (Doc ID 2524119.1)

So, in case of any diagnostics you have to lead to setting it, SUGGESTED:

  • Get the explicit approval/agreement from Oracle.
  • Include evidence on the root cause for this setting. A comment should work, something like:
alter system set "_replace_virtual_columns"=false Comment= 'Workaround Doc ID 1512347.1' SID= '*' scope= both;

Cheers!

Oracle Script: hcheck.sql

Hi all,
Just documenting here, in case you don’t know this script:

Script to Check for Known Problems in oracle8i, oracle9i, oracle10g, Oracle 11g and Oracle 12c( Doc ID 136697.1 )

SQL> @hcheck.sql
HCheck Version 07MAY18 on 12-AUG-2019 21:51:32
----------------------------------------------
Catalog Version 12.2.0.1.0 (1202000100)
db_name: R360PD
Is CDB?: NO

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj                 ... 1202000100 <=  *All Rel* 08/12 21:51:32 PASS
.- MissingOIDOnObjCol          ... 1202000100 <=  *All Rel* 08/12 21:51:33 PASS
.- SourceNotInObj              ... 1202000100 <=  *All Rel* 08/12 21:51:33 PASS
.- OversizedFiles              ... 1202000100 <=  *All Rel* 08/12 21:51:33 PASS
.- PoorDefaultStorage          ... 1202000100 <=  *All Rel* 08/12 21:51:33 PASS
.- PoorStorage                 ... 1202000100 <=  *All Rel* 08/12 21:51:33 PASS
.- TabPartCountMismatch        ... 1202000100 <=  *All Rel* 08/12 21:51:33 PASS
.- OrphanedTabComPart          ... 1202000100 <=  *All Rel* 08/12 21:51:34 PASS
.- MissingSum$                 ... 1202000100 <=  *All Rel* 08/12 21:51:38 PASS
.- MissingDir$                 ... 1202000100 <=  *All Rel* 08/12 21:51:38 PASS
.- DuplicateDataobj            ... 1202000100 <=  *All Rel* 08/12 21:51:39 PASS
.- ObjSynMissing               ... 1202000100 <=  *All Rel* 08/12 21:55:56 PASS
.- ObjSeqMissing               ... 1202000100 <=  *All Rel* 08/12 21:55:57 PASS
.- OrphanedUndo                ... 1202000100 <=  *All Rel* 08/12 21:55:57 PASS
.- OrphanedIndex               ... 1202000100 <=  *All Rel* 08/12 21:55:57 PASS
.- OrphanedIndexPartition      ... 1202000100 <=  *All Rel* 08/12 21:55:57 PASS
.- OrphanedIndexSubPartition   ... 1202000100 <=  *All Rel* 08/12 21:55:57 PASS
.- OrphanedTable               ... 1202000100 <=  *All Rel* 08/12 21:56:00 PASS
.- OrphanedTablePartition      ... 1202000100 <=  *All Rel* 08/12 21:56:00 PASS
.- OrphanedTableSubPartition   ... 1202000100 <=  *All Rel* 08/12 21:56:00 PASS
.- MissingPartCol              ... 1202000100 <=  *All Rel* 08/12 21:56:04 PASS
.- OrphanedSeg$                ... 1202000100 <=  *All Rel* 08/12 21:56:04 PASS
.- OrphanedIndPartObj#         ... 1202000100 <=  *All Rel* 08/12 21:56:15 PASS
.- DuplicateBlockUse           ... 1202000100 <=  *All Rel* 08/12 21:56:15 PASS
.- FetUet                      ... 1202000100 <=  *All Rel* 08/12 21:56:17 PASS
.- Uet0Check                   ... 1202000100 <=  *All Rel* 08/12 21:56:17 PASS
.- SeglessUET                  ... 1202000100 <=  *All Rel* 08/12 21:56:18 PASS
.- BadInd$                     ... 1202000100 <=  *All Rel* 08/12 21:56:19 PASS
.- BadTab$                     ... 1202000100 <=  *All Rel* 08/12 21:56:21 PASS
.- BadIcolDepCnt               ... 1202000100 <=  *All Rel* 08/12 21:56:21 PASS
.- ObjIndDobj                  ... 1202000100 <=  *All Rel* 08/12 21:56:21 PASS
.- TrgAfterUpgrade             ... 1202000100 <=  *All Rel* 08/12 21:56:22 PASS
.- ObjType0                    ... 1202000100 <=  *All Rel* 08/12 21:56:22 FAIL

HCKE-0036: Bad OBJ$ entry with TYPE#=0 (Doc ID 1361015.1)
OBJ$ OBJ#=212606505 TYPE#=0 NAME=COMMENTS NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606501 TYPE#=0 NAME=CONTACT NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606496 TYPE#=0 NAME=HT360_CURRENCY_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=74768696 TYPE#=0 NAME=HT360_USER_ACCESS_MV NAMESPACE=66 Dblink=MDMPROD
OBJ$ OBJ#=183148682 TYPE#=0 NAME=HT360_USER_SUBS_PERM_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=186229204 TYPE#=0 NAME=HT360_USER_SUBS_PERM_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=115573869 TYPE#=0 NAME=HT360_USER_SUBS_PERM_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=212606499 TYPE#=0 NAME=HT360_USER_SUBS_PERM_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=212606631 TYPE#=0 NAME=TC_HT_SERVICE_DEL_ATTRIBUTES_V NAMESPACE=66
Dblink=EBSPROD
OBJ$ OBJ#=186229477 TYPE#=0 NAME=GDS360_PROPERTY_REMOTE NAMESPACE=66
Dblink=MDMPROD
OBJ$ OBJ#=148134057 TYPE#=0 NAME=MEDIA_ROI_INS NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606497 TYPE#=0 NAME=MEDIA_ROI_INS NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=75622441 TYPE#=0 NAME=CONTACT_REMOTE NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=197171249 TYPE#=0 NAME=CONTACT_REMOTE NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=148134085 TYPE#=0 NAME=CAMPAIGN_DMP NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606500 TYPE#=0 NAME=CAMPAIGN_DMP NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=148134088 TYPE#=0 NAME=INSERTION_CODE NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606503 TYPE#=0 NAME=INSERTION_CODE NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=148134080 TYPE#=0 NAME=INSERTION_DMP NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606504 TYPE#=0 NAME=INSERTION_DMP NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=148134083 TYPE#=0 NAME=INSERTION_ELEMENT NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=212606502 TYPE#=0 NAME=INSERTION_ELEMENT NAMESPACE=66 Dblink=EMCPROD
OBJ$ OBJ#=172793128 TYPE#=0 NAME=SUPPLIER_CONTACT_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=197171212 TYPE#=0 NAME=SUPPLIER_CONTACT_REMOTE NAMESPACE=66
Dblink=EMCPROD
OBJ$ OBJ#=183148695 TYPE#=0 NAME=PROPERTY_MAPPING NAMESPACE=66 Dblink=MDMPROD
OBJ$ OBJ#=154666744 TYPE#=0 NAME=PROPERTY_MAPPING NAMESPACE=66 Dblink=MDMPROD

.- BadOwner                    ... 1202000100 <=  *All Rel* 08/12 21:56:22 PASS
.- StmtAuditOnCommit           ... 1202000100 <=  *All Rel* 08/12 21:56:23 PASS
.- BadPublicObjects            ... 1202000100 <=  *All Rel* 08/12 21:56:23 PASS
.- BadSegFreelist              ... 1202000100 <=  *All Rel* 08/12 21:56:23 PASS
.- BadDepends                  ... 1202000100 <=  *All Rel* 08/12 21:56:25 PASS
.- CheckDual                   ... 1202000100 <=  *All Rel* 08/12 21:56:26 PASS
.- ObjectNames                 ... 1202000100 <=  *All Rel* 08/12 21:56:26 WARN

HCKW-0018: OBJECT name clashes with SCHEMA name (Doc ID 2363142.1)
Schema=TC_MONITOR JOB=TC_MONITOR.TC_MONITOR
Schema=TCDWSTAGE DATABASE LINK=PUBLIC.TCDWSTAGE

.- BadCboHiLo                  ... 1202000100 <=  *All Rel* 08/12 21:56:27 PASS
.- ChkIotTs                    ... 1202000100 <=  *All Rel* 08/12 21:56:32 PASS
.- NoSegmentIndex              ... 1202000100 <=  *All Rel* 08/12 21:56:32 PASS
.- BadNextObject               ... 1202000100 <=  *All Rel* 08/12 21:56:32 PASS
.- DroppedROTS                 ... 1202000100 <=  *All Rel* 08/12 21:56:33 PASS
.- FilBlkZero                  ... 1202000100 <=  *All Rel* 08/12 21:56:33 PASS
.- DbmsSchemaCopy              ... 1202000100 <=  *All Rel* 08/12 21:56:33 PASS .- OrphanedIdnseqObj           ... 1202000100 >  1201000000 08/12 21:56:33 PASS
.- OrphanedIdnseqSeq           ... 1202000100 >  1201000000 08/12 21:56:33 PASS
.- OrphanedObjError            ... 1202000100 >  1102000000 08/12 21:56:33 PASS
.- ObjNotLob                   ... 1202000100 <=  *All Rel* 08/12 21:56:34 PASS
.- MaxControlfSeq              ... 1202000100 <=  *All Rel* 08/12 21:56:34 PASS .- SegNotInDeferredStg         ... 1202000100 >  1102000000 08/12 21:56:34 PASS
.- SystemNotRfile1             ... 1202000100 >   902000000 08/12 21:56:35 PASS
.- DictOwnNonDefaultSYSTEM     ... 1202000100 <=  *All Rel* 08/12 21:56:35 PASS
.- OrphanTrigger               ... 1202000100 <=  *All Rel* 08/12 21:56:35 PASS
.- ObjNotTrigger               ... 1202000100 <=  *All Rel* 08/12 21:56:35 PASS 

---------------------------------------
12-AUG-2019 21:56:36  Elapsed: 304 secs
---------------------------------------
Found 26 potential problem(s) and 2 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not

PL/SQL procedure successfully completed.

Statement processed.

Complete output is in trace file:
/u01/app/oracle/diag/rdbms/r360pdlo/r360pd3/trace/r360pd3_ora_310651_HCHECK.trc

 

useful, right?

MySQL InnoDB Buffer

Hi all,
Do you have a MySQL server running somewhere? I’ve seen many and many MySQL servers running with the default configuration, even the mysql_secure_installation command being ignored. Let’s talk about a tip to tune your server.

innodb_buffer_pool_chunk_size=134217728

This config, tells the server how many memory it can use, the default (using 8.0 and 5.7) its 128MB, that’s way less then it could be, in general. I usually set about 75% of the total ram of the server IF you just have the database on that server. With that, you have enough memory to accommodate OS processes and the MySQL. You can set this in your my.cfn file.

Be aware this is not a silver bullet, and if your server has a lot of ram, let’s say > 100GB, if you set at 75%, you still have about 25GB free, and that’s way more than the SO needs. So it’s all a matter of your server memory size.

Make sure you review this point on next time!

AWR Scripts List

Hey,
You also always need to lookup for the right AWR Script to run, just like me?

Here is a quick summary that may help you (and myself) in future. 🙂

@?/rdbms/admin/awrrpt.sql      --> basic AWR report
@?/rdbms/admin/awrsqrpt.sql    --> Standard SQL statement Report
@?/rdbms/admin/awrddrpt.sql    --> Period diff on current instance
@?/rdbms/admin/awrrpti.sql     --> Workload Repository Report Instance (RAC)
@?/rdbms/admin/awrgrpt.sql     --> AWR Global Report (RAC)
@?/rdbms/admin/awrgdrpt.sql    --> AWR Global Diff Report (RAC)
@?/rdbms/admin/awrinfo.sql     --> Script to output general AWR information

Hope it helps!
Cheers!

Restore table statistics using dbms_stats

Hi all, another quick as reference. Table statistics as everyone knows are  very important when the CBO is creating the execution plan. When you have new statistics Oracle will invalid the current execution plans affected by them and create new execution plans based on the new statistics. Most of the time it gets right  if that causes your execution plan to change for worse?

In this situations dba_tab_stats_history   view and dbms_stats.restore_table_stats procedures are your friends.

To validate set and check and set the stats history:

select dbms_stats.get_stats_history_retention from dual; 
exec dbms_stats.alter_stats_history_retention(30);

On dba_tab_stats_history you can view the last statistics available:

select table_name, stats_update_time, from dba_tab_stats_history where table_name='TABLE1' and owner='USER' order by 2 desc;

Use the dbms_stats.restore_table_stats to restore the statistics:

exec dbms_stats.restore_table_stats(ownname=>'USER',tabname=>'TABLE1',AS_OF_TIMESTAMP=>'DATE from column STATS_UPDATE_TIME on dba_tab_stats_history');

Hope it helps!

Elisson Almeida

Oracle 11g Semantic Hints

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

Here is a quick summary I did for a client regarding new Semantic Hints on 11g..

11g New Semantic Hints

In Database version 11.2.0.1, Oracle introduced three new hints: IGNORE_ROW_ON_DUPKEY_INDEX, CHANGE_DUPKEY_ERROR_INDEX and RETRY_ON_ROW_CHANGE. They have different working ends but they all tries to avoid ORA-0001 in some specific situations. An important note is that, unlike almost all other hints, this hint has a semantic effect: it changes the actual behavior – not just the optimization – of the SQL. Oracle Docs says about this Hint:

“The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in “Hints” does not apply for these three hints.”

Some specifics about each one:

  • IGNORE_ROW_ON_DUPKEY_INDEX: “When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row.”
  • CHANGE_DUPKEY_ERROR_INDEX: “The CHANGE_DUPKEY_ERROR_INDEX hint provides a mechanism to unambiguously identify a unique key violation for a specified set of columns or for a specified index. When a unique key violation occurs for the specified index, an ORA-38911 error is reported instead of an ORA-001.”
  • RETRY_ON_ROW_CHANGE: “When you specify this hint, the operation is retried when the ORA_ROWSCN for one or more rows in the set has changed from the time the set of rows to be modified is determined to the time the block is actually modified.”

The use of those hints, however, is only recommended for pretty specific ends and should be avoided if possible. Not only for having some related bugs, but to add some complexity to known operations like bulk inserts and similar.

Other important hint is the Result Cache Hint, mentioned some months ago, under Result Cache Post.

Cheers!

Oracle SQL Join Factorization Transformation (JFT)

Hi All!
Following the line of last week post, let’s talk today about the JFT…

Oracle SQL Join Factorization Transformation (JFT)

The UNION operator is great for merging the results of multiple queries that return similar rowsets, essentially executing each query separately and merging the results together into a single result set. Oracle 11g release 2 come with improvements for SQL optimizer on how Oracle handles UNION ALL performance with the new join factorization transformation (JFT). The Join Factorization Transformation applies only to UNION ALL queries.

The following example show how the optimizer improves the performance of UNION ALL by dynamically re-writing a UNION ALL query into a more efficient form using an in-line view:

Original Query:

select t1.c1, t2.c2     
    from t1, t2, t3     
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2    
union all     
    select t1.c1, t2.c2     
    from t1, t2, t4     
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3;

Re-Written Query:

select t1.c1, VW_JF_1.item_2
     from t1, (select t2.c1 item_1, t2.c2 item_2
                    from t2, t3
                    where t2.c2 = t3.c2 and t2.c2 = 2
               union all
                    select t2.c1 item_1, t2.c2 item_2
                    from t2, t4
                    where t2.c3 = t4.c3) VW_JF_1
     where t1.c1 = VW_JF_1.item_1 and t1.c1 > 1;

Nice, right?
Cheers!

Oracle: “Invisible” Locks!

Have you ever heard:
“My session was killed. I’m trying again. There is no locks. But My SQL doesn’t run!”

By default, when a session is disconnected from Oracle, the SQLs uncommitted are undone (rollback). So, in case of a long routing there is a probably a rollback in place.

How Oracle rollback take that long? Well, it’s Oracle mechanism. The RDBMS basically assume you know what your are doing and start writing the new blocks so the commit will be very quick. Which, however, is bad in case of rollbacks.

How to check if this is your case?

select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD'
4 /

KTUXEUSN Time KTUXESIZ KTUXESTA
---------- -------------------- ---------- ----------------
31 07-APR-2018 16:48:53 17705 ACTIVE
48 07-APR-2018 16:48:53 108856 ACTIVE

Hm.. What can I do? Not much really, we need to wait the rollback to complete.
One thing that can be done to increase the rollback is changing the fast_start_parallel_rollback parameter, as per:

SQL> show parameters roll

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
SQL> alter system set fast_start_parallel_rollback=HIGH;

System altered.

Hope it helps!
Cheers!

11g SQL transformations (CBQT, CNT, JE, JPPD)

Hi All!
Let’s save some time to review those?
They usually very important when analysing query performance, specially on 10046 traces. I separated those were included on 11g, to have it as subset. Here it goes:

New 11g SQL transformations (CBQT, CNT, JE, JPPD)

The Oracle cost-based optimizer CBO is constantly being improved in its ability to re-write and transform sub-optimal SQL queries. In general way, CBO work by replacing operations by equivalent optimal when possible, changing clauses or re-grouping to take advantage on in indexes, eliminating subqueries by replacing with joins and not null tests and similar. In 11g new SQL transformations were implemented looking to continue improving this process. They are:

  • Cost-Based Query Transformation (CBQT): This is one of the core mechanisms in CBO, that tries different transformed and untransformed variants of the query and runs them through the costing algorithm. Improvements were done in 11g, specially related to clauses NOT IN into NOT EXISTS.
  • Count Transformation (CNT): If all rows have a value on this column (not null), Oracle CBO can simply count the number of rows. There is no need to actually retrieve the column value. This transformation changes count(col) to count(*).
  • Join Elimination (JE): Join elimination is a query transformation of the Oracle optimizer to remove joins that are not required in a query. Released on 10g, Join Elimination got several improvements and new mechanisms in 11g and even more in 12c.
  • Join Predicate PushDown (JPPD): Allows a view to be joined with index-based nested-loop join method, which may provide a more optimal alternative. The following types of views supports predicate pushdown: UNION ALL/UNION view, Outer-joined view, Anti-joined view, Semi-joined view, DISTINCT view and GROUP-BY view.

Nice?

Hope you enjoy it!

Statspack top queries script by elapsed time

Hi all,

I was engaged on a report request that I needed to gather to get the TOP SQL by elapsed time and using Statspack. I got those and than I was asked to it on the following week and on the following, and you may see when this is going. So I created a script which would give is a report and I would not have to do it manually ever again 🙂

Usage: long_run_sql.sh [-h ] [ -r 

Where:
If no parameters are used 120 minutes and 7 days will be used as default for History, 5 minutes for current running
-r = Set the time in minutes for the current running SQLs
-o = Specifies the minutes to be used for the long running SQLs History
-d = set the time period wanted for the execution history, default is 7 days
-h = Shows this help message

Might still have some bugs but it well enough to share here 🙂

You have some parameters that you need to change at the top, to suite your environment and of course have Statspack working and change the sqlplus connection line

Some tweaks are required as no 2 environments are never 100% alike. But still forth the work.

#!/bin/bash
#---------------------------------------------------------------------------
# Creates a report, using statspack, of the long running sqls from database
#
# History:
# Feb-21-2018 - Elisson Almeida - Created.
#---------------------------------------------------------------------------
#usage
### Environment setup
### Global variables
DIR=/home/oracle/scripts
LOG=/home/oracle/logs
DATE=$(date +%Y%m%d_%H%M%S)
NAME=long_running_sql_report
OUTPUT_FILE=${LOG}/${NAME}_${DATE}.log
ERROR_FILE=${LOG}/${NAME}.err
TMP_FILE=$DIR/$.tmp
CONFIG_EMAIL_TO=
PATH=
ORACLE_SID=
ORACLE_BASE=
ORACLE_HOME=
#tns-admin if needed otherwise comment out
#TNS_ADMIN
RUNNING=5
HISTORY=120
Help()
{
echo "Usage: long_run_sql.sh [-h ] [ -r 

Hope it helps!

Elisson Almeida