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.
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.
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
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;
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');
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 22.214.171.124, 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.
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:
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
select t1.c1, t2.c2
from t1, t2, t4
where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3;
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
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;
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
where ktuxecfl = 'DEAD'
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
transactions_per_rollback_segment integer 5
SQL> alter system set fast_start_parallel_rollback=HIGH;
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.
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
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.
# Creates a report, using statspack, of the long running sqls from database
# Feb-21-2018 - Elisson Almeida - Created.
### Environment setup
### Global variables
#tns-admin if needed otherwise comment out
echo "Usage: long_run_sql.sh [-h ] [ -r