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!

Oracle memory usage on Linux / Unix

Hi all,

So one of the most important things that we need to do when setting up a new server or checking the capacity of the server is to see how much memory Oracle is using.

When checking the capacity there are some practical things that always help me to get a fast glimpse of the system:

  • When opening topas and hitting M you will see this below
Topas Monitor for host: SERVER1 Interval: 2 Sat Dec 8 03:39:59 2019
================================================================================
REF1 SRAD TOTALMEM INUSE FREE FILECACHE HOMETHRDS CPUS
--------------------------------------------------------------------------------
0 0 59.8G 59.6G 212.3 16.3G 528 0-15
1 1 61.4G 61.2G 188.8 15.7G 536 16-31

On the memory session you will see 3 categories, INUSE, FREE and FILECACHE. There you may see what is being using for what but there is not much granularity there.

  • When using top you have this summary below
top - 11:48:08 up 119 days, 10:18, 1 user, load average: 26.76, 26.16, 25.95
Tasks: 1936 total, 38 running, 1898 sleeping, 0 stopped, 0 zombie
Cpu(s): 79.3%us, 1.1%sy, 0.0%ni, 15.1%id, 4.3%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 263750172k total, 219075656k used, 44674516k free, 797476k buffers
Swap: 16773116k total, 505760k used, 16267356k free, 88055108k cached

Same you have a high level usage. So here comes the question:

How are you to prove that you have a memory shortage?

I often use vmstat on Linux looking on the columns si and so equals to 0 (swap in and swap out) and when the free command, the free column you will also have no or very low swap being used

/home/oracle> vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
15 3 505760 44896608 797480 88062288 0 0 7037 1304 0 0 29 2 61 8 0
16 1 505760 44922964 797480 88062320 0 0 432272 144314 38784 31348 41 2 52 5 0
14 2 505760 44943072 797480 88062320 0 0 468904 155424 32676 27522 34 1 60 5 0
15 2 505760 44943032 797480 88062328 0 0 431032 144275 32596 27469 34 1 60 5 0
15 2 505760 44920136 797480 88062352 0 0 396232 145052 30772 26657 32 1 62 6 0
19 1 505760 44928576 797480 88062360 0 0 429360 160158 33640 28012 36 1 58 5 0
15 3 505760 44935340 797480 88062368 0 0 477232 161849 28393 21423 41 1 53 5 0
17 1 505760 44924744 797480 88062368 0 0 515265 160212 27478 20578 40 1 54 5 0
16 1 505760 44921596 797480 88062368 0 0 495408 159304 25458 19548 37 1 58 5 0
18 1 505760 44918144 797480 88062384 0 0 552880 168895 28203 22774 38 1 56 5 0
15 2 505760 44922344 797480 88062392 0 0 546920 160463 25321 19151 37 1 58 5 0
16 4 505760 44921544 797480 88062400 0 0 571544 153810 25429 20011 36 1 58 5 0
16 1 505760 44919620 797480 88062400 0 0 577552 160004 27132 20111 40 1 54 5 0
19 2 505760 44360240 797480 88062400 0 0 584969 155553 29467 22145 41 2 52 5 0
/home/oracle> free
total used free shared buffers cached
Mem: 263750172 219060896 44689276 91608 797480 88062464
-/+ buffers/cache: 130200952 133549220
Swap: 16773116 505760 16267356

To check a process specific memory usage (RSS) I often use ps along with other commands to calculate the process memory for a specific process id as below:

/home/oracle> ps -eo rss,pid,euser,lstart,args:100 --sort %mem | grep -v grep | grep 35796 | awk '{printf $1/1024 "MB"; $1=""; print }'| sort
19.6016MB 35796 oracle Sat Sep 8 02:43:54 2018 ora_lg00_ORC1
34.957MB 32340 oracle Sat Jan 5 11:50:09 2019 oracleORC1 (LOCAL=NO)

RSS is resident memory, but when comes to shared memory like the Oracle SGA the methods above could be miss leading – not to say wrong – but as Oracle memory is shared we may see double counting on the results. I sometimes use pmap to check a process memory as well when available

/home/oracle> pmap 35796
35796: ora_lg00_ORC1
total 0K

But, still when checking a server wide scope, do you want to keep doing manual work and lots of math? I don’t think so. 🙂

That’s why when I came across SMEM made my life a lot easier. It is a python script which gives you a nice breakdown of the memory usage and without the miss leading double counting.

You can see the commands and processes and their memory:

[root@srv01 smem-1.4]# ./smem -trk | head
PID User Command Swap USS PSS RSS
4829 root /opt/stackdriver/collectd/s 444.0K 4.0G 4.0G 4.0G
5647 oracle asm_gen0_+ASM 50.1M 424.4M 425.0M 437.8M
16512 oracle rman software/product/11.2. 0 172.9M 173.7M 177.8M
85107 oracle ora_n001_db01 42.3M 147.8M 147.8M 185.8M
85103 oracle ora_n000_db01 42.4M 146.5M 146.6M 184.6M
85109 oracle ora_n002_db01 42.2M 145.6M 145.6M 183.5M
85111 oracle ora_n003_db01 42.1M 145.1M 145.2M 183.1M
7287 oracle ora_dia0_db01 1.6M 68.6M 68.8M 107.8M

As well the overall server per user:

root@srv01 smem-1.4]# ./smem -turk 
User Count Swap USS PSS RSS oracle 1358 4.8G 7.8G 8.0G 76.6G 
root 43 12.0M 4.1G 4.1G 4.2G user1 10 0 321.0M 328.0M 369.2M 
nobody 2 96.0K 2.1M 2.3M 6.0M user2 2 0 684.0K 1.7M 7.7M 
user4 2 0 632.0K 1.7M 7.9M user4 1 72.0K 536.0K 540.0K 2.1M 
ntp 1 424.0K 332.0K 368.0K 2.4M 
smmsp 1 1.3M 160.0K 298.0K 1.9M 
rpc 1 336.0K 68.0K 73.0K 1.7M 
rpcuser 1 808.0K 4.0K 16.0K 1.9M 
--------------------------------------------------- 
1422 4.8G 12.2G 12.5G 81.3G

Hope it helps, see you next time!

Oracle 11g / 12c identified by values – set an Oracle password to its current value without knowing the password

This post will be a quick one. I needed to set a Oracle password as it was about to expire but the client did not want to change the password itself.

The simplest way to get the encrypted password was:

select name, password from sys.user$ where name=‘USER’;

But after Oracle 11g/12c seems a better option for security ends to use DBMS_METADATA.get_ddl or select the spare4 column on sys.user$

select name, spare4 from sys.user$ where name=‘USER’;

or

set long 9999999
set longc 9999999
select dbms_metadata.get_ddl('USER','DBSNMP') from dual;

And how to set this password back to the user?

alter user [USER] identified by values '[encrypted password]';

 

Hope it helps!

MySQL: Sed for Scripts using “Show” from Command Line

Hi all,
So, a pretty basics one today… But useful to have handy. How to script an output from  mysql -B -e?

Easy, by using SED. Ok, by replacements are always tricky considering the line braking and etc. So, here goes an example with show tables:

Original Output.

[root@greporasrv ~]# mysql sbtest -B -e 'show tables'
Tables_in_sbtest
sbtest1
sbtest10
sbtest2
sbtest3
sbtest4
sbtest5
sbtest6
sbtest7
sbtest8
sbtest9
Cool, now with all in one line:
[root@greporasrv ~]# mysql sbtest -B -e 'show tables'|sed ':a;N;$!ba;s/\n/ /g'
Tables_in_sbtest sbtest1 sbtest10 sbtest2 sbtest3 sbtest4 sbtest5 sbtest6 sbtest7 sbtest8 sbtest9

Great, so let’s put some useful code on it:

[root@greporasrv ~]# mysql sbtest -B -e 'show tables'|sed ':a;N;$!ba;s/\n/ engine=innodb; \n alter table /g'
Tables_in_sbtest engine=innodb;
alter table sbtest1 engine=innodb;
alter table sbtest10 engine=innodb;
alter table sbtest2 engine=innodb;
alter table sbtest3 engine=innodb;
alter table sbtest4 engine=innodb;
alter table sbtest5 engine=innodb;
alter table sbtest6 engine=innodb;
alter table sbtest7 engine=innodb;
alter table sbtest8 engine=innodb;
alter table sbtest9 engine=innodb;

Hope you can use for your needs.

Cheers!

Amazon EC2: X11 Forwarding After Sudo SSH Session

Hello all!

So, now with more use of resources like Cloud servers, more and more silent instalations are being done, right? Myself, I do it in silent always I can.

What if I need to export X. Plus, if I need to export it from user oracle, but I can only login with ec2-user, as usual?

Here is the process for that:

1) Connect to AWS EC2 instance

[user@securehost ~]$ ssh -X ec2-user@ipaddress
Last login: Fri Dec 7 14:41:41 2018 from grepora.srv.com
__| __|_ )
 _| ( / Amazon Linux AMI
 ___|\___|___|
https://aws.amazon.com/amazon-linux-ami/2018.03-release-notes/
13 package(s) needed for security, out of 16 available
Run "sudo yum update" to apply all updates.

2) Test xclock works from ec2-user

[ec2-user@ipaddress ~]$ xclock
Warning: Missing charsets in String to FontSet conversion
^C

3) Show all magic cookie

[ec2-user@ipaddress ~]$ xauth list
ipaddress/unix:12 MIT-MAGIC-COOKIE-1 7e53e7600ff4177d7bbc66bde0a1b1ca
ipaddress/unix:11 MIT-MAGIC-COOKIE-1 e3d1a8915484c929ef3e809b047e6352
ipaddress/unix:10 MIT-MAGIC-COOKIE-1 07b3de3093cef835c19239ea952231b7

4) Show DISPLAY variable

[ec2-user@ipaddress ~]$ env|grep DISPLAY
DISPLAY=localhost:10.0

5) Create /tmp/xauth based on current DISPLAY variable

[ec2-user@ipaddress ~]$ xauth list | grep unix`echo $DISPLAY | cut -c10-12` > /tmp/xauth
[ec2-user@ipaddress ~]$ ll /tmp/xauth ; cat /tmp/xauth 
-rw-rw-r-- 1 ec2-user ec2-user 78 Dec 7 14:47 /tmp/xauth
ipaddress/unix:10 MIT-MAGIC-COOKIE-1 07b3de3093cef835c19239ea952231b7

6) Sudo to oracle

[ec2-user@ipaddress ~]$ sudo su - oracle
Last login: Fri Dec 7 14:43:12 UTC 2018 on pts/0

7) Add and Verify xauth

[oracle@ipaddress ~]$ xauth add `cat /tmp/xauth`
[oracle@ipaddress ~]$ xauth list
ipaddress/unix:10 MIT-MAGIC-COOKIE-1 07b3de3093cef835c19239ea952231b7

8) Verify and Add DISPLAY variable

[oracle@ipaddress ~]$ env|grep DISPLAY
[oracle@ipaddress ~]$ export DISPLAY=localhost:10.0

9) Test xclock works from oracle

[oracle@ipaddress ~]$ xclock
Warning: Missing charsets in String to FontSet conversion
^C
[oracle@ipaddress ~]$

Now you should be able to see the clock and so other graphical resources, like DBCA and so on.

Hope it helps!

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

Managing listener.log and log.xml with Linux Logrotate

Hi all,
To manage Oracle trace files the way to go is ADRCI. You can see this post from Matheus if you have not read it yet.

In the last part of the script we have a small bash code to configure the ADRCI on all databases running on a server

You could add:

adrci exec="set home $1;purge -age 10080 -type ALERT";

In this case the age parameter is in minutes but still you would be required to run it periodically which could be another script in crontab to be managed.

SO the solution that I found to be best as it takes leverage from an existing solution is called logrotate.

Logrotate is a Unix/Linix based program that helps as its name says, rotate any file that you need. You just need to create a configuration file and place it on /etc/logrotate.d on most Linuxes distributions.

But when you have a server with several databases and listerners and more, it starts to get a bit tedious and time consuming to create this manually.

In this post on the Pythian Blog, will find how to create but it does not handle the listener.log nor the log.xml so I added this piece here

for L in `\ps -ef | grep tnslsnr | grep -v grep | sed s'/-.*$//g' | awk '{print $NF}'`
do
OUT=${DEST}/"logrotate_xml_"${L}
LSRN_LOG=`lsnrctl status ${L} | grep "Listener Log File" | awk '{print $NF}'`
echo ${LSRN_LOG%.*}"*" " {" > ${OUT}
cat << ! >> ${OUT}
daily
rotate 1
compress
notifempty
}
!
echo ${OUT} has been generated
done

for L in `\ps -ef | grep tnslsnr | grep -v grep | sed s'/-.*$//g' | awk '{print $NF}'`
do
	OUT=${DEST}/"logrotate_xml_"${L}
	LSRN_LOG=`lsnrctl status ${L} | grep "Listener Log File" | awk '{print $NF}'`
	echo ${LSRN_LOG%.*}"*.xml" " {"		>  ${OUT}
	cat << !			>> ${OUT}
	daily
	rotate 1
	compress
}
!
	echo ${OUT} has been generated
done

Using logroate really helps on the managing on Oracle related files which are not done by ADRCI.

Hope it helps,

Elisson

Disabling PL/SQL Warnings

Hi all!
So, the DBA keep insisting that the Procedure need to compile without warnings? Easy!
This is actually a nice option if you are compiling a code in a client and don’t want to show that your code has warnings, which is kind of ok, once it’s almost impossible to code without warnings.

And this is not even new. Have a look on this documentation from 10.2.

Ok, so how to do it?

ALTER SESSION SET plsql_warnings = 'disable:all';

Have a look in the example below:

SQL> CREATE OR REPLACE PROCEDURE plw5001
  2  IS
  3     a   BOOLEAN;
  4     a   PLS_INTEGER;
  5  BEGIN
  6     a := 1;
  7     DBMS_OUTPUT.put_line ('Will not compile?');
  8  END plw5001;
  9  /
Warning: Procedure created with compilation errors.

SQL>
SQL> SHOW ERRORS
Errors for PROCEDURE PLW5001:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/4 PLW-05001: previous use of 'A' (at line 3) conflicts with this
use

6/4 PL/SQL: Statement ignored
6/4 PLS-00371: at most one declaration for 'A' is permitted
SQL>
SQL> ALTER SESSION SET plsql_warnings = 'disable:all';

Session altered.

SQL>
SQL> CREATE OR REPLACE PROCEDURE plw5001
2 IS
3 a BOOLEAN;
4 a PLS_INTEGER;
5 BEGIN
6 DBMS_OUTPUT.put_line ('Will not compile?');
7 END plw5001;
8 /

Procedure created.

 

Script to Setup ADRCI Policies

Hi all!
So, today just sharing some useful scripts to configure, set and check on databases and users.

This is specially useful for environments with several databases under different users, considering a possible server consolidation strategy.

# Script to Check on Current Configuration:

[root@greporasrv ~]# cat adrci-check.sh
su - $1 -c 'export ORAENV_ASK=NO ; ORACLE_SID=$2 ; . oraenv ; for f in $(adrci exec="show homes" | grep -v "ADR Homes:" | grep -v "clients") ; do adrci exec="set home $f; show control;" ; done'

# Script to Set New Configuration
On this example: (SHORTP_POLICY = 168, LONGP_POLICY = 720).

[root@greporasrv ~]# cat adrci-set.sh
ORAUSER=$1
export SID=$2
su - $ORAUSER -c 'export ORAENV_ASK=NO ; ORACLE_SID='$SID' ; . oraenv ; for f in $(adrci exec="show homes" | grep -v "ADR Homes:" | grep -v "clients") ; do adrci exec="set home $f; set control \(SHORTP_POLICY = 168, LONGP_POLICY = 720\);" ; done'

# To run them informing OSUSER and SID:

./adrci-check.sh OWNER SID
./adrci-set.sh OWNER SID

# Master one: Script to set for all DBs/Users:

[root@greporasrv ~]# cat adrci-gen.sh
for h in $(grep -v "^#" /etc/oratab | awk 'BEGIN { FS=":";} {if (NF) print $2}' | sort -u)
do
ORAOWN=`ls -ld $h | cut -d " " -f 3`
# validate user?
# get a SID to use for this home
SID=`grep $h /etc/oratab | grep -v '^\*:' | cut -d ":" -f 1 | tail -1`

# Generating the code to check ADRCI settings on this OH
./adrci-check.sh $ORAOWN $SID

# Generating the code to change ADRCI settings on this OH
./adrci-set.sh $ORAOWN $SID
done

Nice, right?
Hope it helps. Cheers!