Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Shared Pool Management in 12c, What’s new!

Posted by FatDBA on January 17, 2018

Hi Everyone,

Few days while working on Shared Pool issue where we were getting ORA-4031 on one of the 11gR2 database i discovered something interesting and new regarding the SGA duration management. Here i wont discuss the problem that i faced and how we fixed but would try to show the architectural changes that has happened with 12c that can now fix these errors/issues.

Let me explain what are ‘Durations’ first. The shared pool is made up of a number of granules. The shared pool then split into sub-pools if you have a large enough SGA, and each sub-pool consists of a number of non-overlapping granules. In 11g each sub-pool also split into four sub-sub-pools known as durations.

What was there before 12c arrived
Starting from Oracle 10g each sub-pool in SGA was divided in to four durations.
Let’s check the distribution by generating the Heap Dump for shared pool, here i used oradebug with level 2 (This provides you the full SGA Summary or you can try with level 2050 to get full summary with contents).

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump 2
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8127.trc

.....
******************************************************
HEAP DUMP heap name="sga heap(1,0)"  desc=380030610
Total heap size    =218102664
Total free space   =  1066928
Total reserved free space   =  8439520
Unpinned space     = 38812528  rcr=11971 trn=17906
Permanent space    =208595160
HEAP DUMP heap name="sga heap(1,1)"  desc=380031e68
Total heap size    = 67108512
Total free space   =  2912528
Total reserved free space   =  1382816
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(1,2)"  desc=3800336c0
Total heap size    =167771280
Total free space   = 92743480
Total reserved free space   =  3852856
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(1,3)"  desc=380034f18
Total heap size    =268434048
Total free space   = 74547592
Total reserved free space   = 13497472
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(2,0)"  desc=380039e38
Total heap size    =201325536
Total free space   =    17200
Total reserved free space   =  8435920
Unpinned space     = 26474112  rcr=7934 trn=8094
Permanent space    =192871456
HEAP DUMP heap name="sga heap(2,1)"  desc=38003b690
Total heap size    = 83885640
Total free space   = 48723768
Total reserved free space   =  1035792
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(2,2)"  desc=38003cee8
Total heap size    =369096816
Total free space   =258674312
Total reserved free space   = 16982464
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(2,3)"  desc=38003e740
Total heap size    =218102664
Total free space   = 17202608
Total reserved free space   = 10966696
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(3,0)"  desc=380043660
Total heap size    =184548408
Total free space   =    13008
Total reserved free space   =  5061928
Unpinned space     = 26943408  rcr=4930 trn=9425
Permanent space    =179472608
HEAP DUMP heap name="sga heap(3,1)"  desc=380044eb8
Total heap size    = 67108512
Total free space   = 27568352
Total reserved free space   =     4744
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(3,2)"  desc=380046710
Total heap size    =352319688
Total free space   =233302736
Total reserved free space   = 15981216
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
HEAP DUMP heap name="sga heap(3,3)"  desc=380047f68
Total heap size    =385873944
Total free space   =143746536
Total reserved free space   = 19402616
Unpinned space     =        0  rcr=0 trn=0
Permanent space    =        0
.....
******************************************************

So above stats shows that we have three sub-pools [Sub pool (1,0), (1,1), (1,2), (1,3) ….. (3,0), (3,1), (3,2), (3,3)] of SGA Heaps with Four Durations each heap. And every duration has its own size, free space and reserved free space. This type of distribution possibly causes the ORA 4031 even when you have enough free space in other durations and this is what the actual cause in my earlier case, but here we won’t discuss how we fixed that.

Okay so now lets do the same with 12c database. Lets generate the Heap Dump for SGA in 12c database and see the distributions of durations here. Once again we will use the oradebug to dump heapdump with Level 2.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump heapdump 2
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/tunedb/tunedb/trace/tunedb_ora_11054.trc


******************************************************
HEAP DUMP heap name="sga heap(1,0)"  desc=0x60103678
 extent sz=0xfe0 alt=304 het=32767 rec=9 flg=0x82 opc=0
 parent=(nil) owner=(nil) nex=(nil) xsz=0x1000000 heap=(nil)
 fl2=0x24, nex=(nil), idx=1, dsxvers=1, dsxflg=0x0
 dsx first ext=0x73000000
 dsx empty ext bytes=0  subheap rc link=0x730000c0,0x730000c0

******************************************************
HEAP DUMP heap name="sga heap(1,3)"  desc=0x60107f80
 extent sz=0xfe0 alt=304 het=32767 rec=9 flg=0x82 opc=0
 parent=(nil) owner=(nil) nex=(nil) xsz=0x1000000 heap=(nil)
 fl2=0x24, nex=(nil), idx=1, dsxvers=1, dsxflg=0x0
 dsx first ext=0x7e000000
 dsx empty ext bytes=0  subheap rc link=0x7e0000c0,0x7e0000c0

Alright, so here we only have two groups of SGA durations – ‘Sub pool 1, duration 0’ and ‘Sub Pool 1 duration 3’ for improved sharability and to avoid ORA 4031 errors.

Hope It helps
Prashant Dixit

Advertisements

Posted in Advanced | Tagged: , , , | Leave a Comment »

How to troubleshoot, understand HW events & measure performance using PERF (Linux Profiler)! – Part 2

Posted by FatDBA on January 13, 2018

Repeat: I think I’ve mistakenly deleted the post, so re-posting the same.

Hey Folks,

Back with second edition of my previous post on troubleshooting performance issues using Linux in-built profiler named ‘perf’. Many of the users requested to write about few use cases situations on when and how to use this tool.

So, this post is all about discussing some cases that i have faced while working on few performance tuning projects and few of them are from the tests that I’ve performed on my Lab systems.

Okay coming back to the reason on why we need this, perf i mean ?
The answer is sometimes Oracle wait interface is not enough and you need to dig deeper inside the system to understand the problem. That point you have to use some third party, in-built dedicated tools for performance investigation. There perf might help you to understand what your resource intensive query is doing on OS layers.
So, using the tool you can monitor your process on what’s its doing!

And in case if you are using perf on Virtualised system, you might get error β€œperf.data file has no samplesβ€œ.
In order to fix it try with “-e cpu-clock” arguments to collect the sample data and then interpret the file.

Okay so one fine day, you saw a spike in server’s CPU consumption using TOP, Oratop, OEM, Scheduled scripts or by any possible monitoring techniques and you have identified the process and its other attributes.
Using the PID you have reached the SID, SQL_ID, SQL_TEXT and other statistics. So now you might want to analyze and understand the oracle’s execution.

 
Global Information
------------------------------
 Status              :  EXECUTING
 Instance ID         :  1
 Session             :  DIXIT (1:53089)
 SQL ID              :  71aa5ju8pwtf2
 SQL Execution ID    :  16777216
 Execution Started   :  01/09/2018 06:10:32
 First Refresh Time  :  01/09/2018 06:10:32
 Last Refresh Time   :  01/09/2018 06:13:03
 Duration            :  152s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@dixitlab.localdomain (TNS V1-V3)

Global Stats
========================================================
| Elapsed |   Cpu   |    IO    | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Gets  | Reqs | Bytes |
========================================================
|     163 |     150 |      115 |     5M | 648K |   5GB |
========================================================

SQL Plan Monitoring Details (Plan Hash Value=12102956)
===================================================================================================================================================================
| Id   |      Operation       | Name |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |       Activity Detail       | Progress |
|      |                      |      | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |         (# samples)         |          |
===================================================================================================================================================================
| -> 0 | SELECT STATEMENT     |      |         |       |       150 |     +2 |     1 |        0 |      |       |          |                             |          |
| -> 1 |   SORT AGGREGATE     |      |       1 |       |       150 |     +2 |     1 |        0 |      |       |          |                             |          |
| -> 2 |    TABLE ACCESS FULL | T294 |   96523 | 56397 |       151 |     +1 |     1 |      306 | 648K |   5GB |    99.34 | Cpu (69)                    |      31% |
|      |                      |      |         |       |           |        |       |          |      |       |          | db file sequential read (6) |          |
|      |                      |      |         |       |           |        |       |          |      |       |          | direct path read (126)      |          |
===================================================================================================================================================================

Above results points to high CPU Time (150 Seconds). Now to look more deeper in to things and to understand what exactly the session is doing.

[root@dixitlab ~]#  perf top -e cpu-clock -p 3505 

   PerfTop:     349 irqs/sec  kernel:42.7%  exact:  0.0% [1000Hz cpu-clock],  (target_pid: 3505)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

             samples  pcnt function                    DSO
             _______ _____ ___________________________ __________________________________________________

             1981.00 46.2% _raw_spin_unlock_irqrestore [kernel.kallsyms]
              906.00 21.1% _intel_fast_memcmp          /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
              270.00  6.3% kole_simple_string_match    /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle 
               96.00  2.2% copy_user_generic_unrolled  [kernel.kallsyms]
               96.00  2.2% kcbgtcr                     /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
               54.00  1.3% __intel_new_memset          /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle 
               37.00  0.9% __intel_ssse3_rep_memcpy    /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
               30.00  0.7% kghfrf                      /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
               28.00  0.7% kghalf                      /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
               27.00  0.6% kcbldio                     /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
               24.00  0.6% kdxbrs1                     /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
               24.00  0.6% kspgvc                      /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle
               21.00  0.5% kksMapCursor                /u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle



Results shows system was mostly busy processing kernel calls (kernel.kallsyms) and its function ‘_raw_spin_unlock_irqrestore‘ with 46% of its time spend using CPU cycles. It’s coming with huge overhead and with large samples and in general irq_restore shows up because re-enabling interrupts is costly, but this is not the real CPU consumption but is how the tool, Interrupt and system works in few of the Virtual systems – I was testing it on my Lab VM.

Here i would like to thank Tanel Poder for reviewing the document and highlighting the issue with the perf top command when executed inside a VM environment.

…… perf top that concluded _raw_spin_unlock_irqrestore taking lots of CPU time. I’ve been through this myself in past – and this is likely not actual CPU usage but rather how perf, OS and interrupts work in some VMs (basically measurement bias/errors by these tools). I assume that you tested in a VM? ……..

Next in the list is function/object ‘_intel_fast_memcmp‘ called by oracle with 21.1% which i believe is for fast memory compilations.

So the conclusion of the analysis:
Most of the CPU by process was spend processing kernel calls and for for fast memory compilations.

Purpose Revisit: This gives you a glimpse of what happens with the process calls and monitors a cpu-bound, database process is pass its time.

Hope It Helps
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , | Leave a Comment »

Using Flame Graphs to analyze performance & workloads: Part 1

Posted by FatDBA on January 13, 2018

Hi Guys,

Now after my last two posts on Perf tool/profiler, i guess this the right time to move ahead with interpretation on stack trace data.
Back with another post, this time as promised about ‘Flame Graphs’!
So, would like to first start with the very standard question – Why, what this Flame Graphs are ?

Flame Graphs is tool developed by Kernel/System Performance maestro Brendan Gregg to project or visualize strack traces for both user/all processes and kernel-level stacks for CPU, Off-CPU etc. You can use it on stacks/traces collected by various profiler i.e. perf, Dtrace, SystemTap etc.

In this post we will be using PERF for statistics collection and project them using Flame Graphs and will understand the complexity and the code paths of many Oracle’s internal functions. This being the very first edition for this subject we will start with basics and will first discuss about CPU graphs and try to understand why and where CPUs are busy using stack traces and identify hot code-paths.This can be a really helpful tool for fast identification of performance problems where conventional oracle performance tools failed.

Okay, let’s jump on how you will do it. First you have to download the toolkit (Download Link)
This tool generates results in SVG format using below three steps.

  • Stats Collection using any of the profiler e.g. Perf, Dtrace, Systemtap etc.
  • Compress or Fold your stats
  • Finally use the core (flamegraph.pl) script to generate the SVG file.

Okay, so now after enough description on tool, let’s start out first case – “CPU Flame Graphs for an expensive SQL Statement”.

Scenario: We have a resource intensive query “COUNT on one of the big table “ running from session with SPID 19280
So, we will be collecting process strack traces while the SQL statement was in run.

SQL> explain plan for select count(*) from dwh.**********_*** where ****TIMESTAMP > '23-JUN-16' and ******KEY > 789999999;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1112565023

------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |     1 |    14 |  2604K  (1)| 08:40:53 |
|   1 |  SORT AGGREGATE    |                     |     1 |    14 |            |          |
|*  2 |   TABLE ACCESS FULL| **************_**** |    39M|   524M|  2604K  (1)| 08:40:53 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("*****KEY">789999999 AND "*****TIMESTAMP">'23-JUN-16')

14 rows selected.

SQL>

Now collecting perf statistics for the process at low-frequency of 997 Hertz.

[root@dselimw5862 perftest]# perf record -a -g -F997 -p 19280 

perf record: Woken up 3926 times to write data ]
[ perf record: Captured and wrote 981.809 MB perf.data (9751081 samples) ]

This will create perf data file under the same directory.

[root@dselimw5862 perftest]# ls -ltrh
-rw-------. 1 root root 982M Jan 12 14:58 perf.data

Next, collapse the stacks using below

[root@dselimw5862 perftest]# /root/perftest/FlameGraph-master/stackcollapse-perf.pl 

Finally, time to render the SVG file.

[root@dselimw5862 perftest]# /root/perftest/FlameGraph-master/flamegraph.pl --title "Flame Graph: Selective data search on dwh.W6AUDITHISTORY_FULL Table" > ImageAuditHistory.svg 

Let’s open the SVG file in browser and see what’s it’s got!

Important Note: The x-axis reflects stack profile population, and the y-axis represents the stack depth

Here you will see many of the familiar Oracle specific functions/processes opiodr, opitsk, opiino, opidrv etc.
Okay so, If you take a look at the map, you will see few of the Linux processes spawned first which called Oracle Internal functions next which moved to the query execution (i.e SELECT FETCH -> GROUP BY SORT -> TABLE ACCESS) and then to other functions. For example you can see the execution time during the sampling was spent in kdstf0100101000km functions which was called by kdsttgr which is Kernel Data Scan Table Get Row.

So, now question ‘Which function is on CPU the most ??
The top edge shows who is on CPU directly, copy_user_generic_unrolled function which is used when there is no optimization on CPU level.
Remember Flame Graphs follows ancestry, means copy_user_generic_unrolled was called by __pread_nocancel and __pread_nocancel was called by ksfd_skgfqio and which called … and then it points to …

Next look little above from the bottom of the graph with three functions __libc_start_main, __pread_nocancel and kaf4reasrp1km. If we visually compare them it is clear that __pread_nocancel function was running more often than other two. We see one of the linux function __pread_nocancel() was sampled a lot during the query execution and found repeated 2 times in the graph. This is used by libc when a cancellation point (a POSIX threads concept) is not permitted.

In Short: So, the Flame Graphs might help you when you have a problem in hand ‘My Production Database had poor performance”.
It was a heavy CPU consumer, so i used to CPU profiler to see WHY ? – But do you thing reading those lengthy, messy, long, raw traces that easy. So, here at this point there is a need of some data representation tool to capture the problems and sources and to visualize them. Here comes Flame Graphs to picture, The hero! It also gives you flexibility to move your cursor to any of the object to understand how much percent the object was present during the sample time.

Well, in later editions for this subject i will try to cover more scenarios. The sole purpose of this post is to give audience an idea, a gist of the topic.

Hope It Helps
Prashant Dixit

Posted in Advanced | Tagged: , | Leave a Comment »

How to troubleshoot, understand HW events & measure performance using PERF (Linux Profiler)! – Part 1

Posted by FatDBA on January 8, 2018

Hi Pals,

Some time back i was part of a performance task-force for one of our customers in Czech where i did a complete full 360 degrees auditing and troubleshooting of their full database stack. But you know most of the times it not just the software or the database but the real problem lies underneath the OS layer.

Okay, so back to the discussion – So during the assignment at one point where i had to collect some statistics for the OS to prove my point that ‘Problem is not with the Database‘ but with something else, i had used few of the tools like DTrace, Linux Trace Toolkit or LTT, Systemtap and one of the profiler which i hardly used before ‘Perf‘.

Yes, so today’s topic of discussion is to give you some basic idea (Will cover the deep analysis using the tool in future) about the ‘Perf’ profiler which helps to dissect and understand hardware events and measures performance.

Okay, let me take a real time scenario where a sudden surge in CPU in a production environment raised questions.
During the initial probing of issues we have found that that about 25% of the CPU is consumed by SQLPLUS command with PID 6720, lets see what and why its doing that ….

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 6720 oracle    20   0  113m  14m 9.8m R 25.8  0.5   0:20.32 sqlplus   as sysdba

Lets first Sample on-CPU functions for the process ID on 99 Hertz using below command for PID 6720.

perf record -F 99 -p 6720 

This will produce a raw file named ‘perf.data’ under the same directory from where you called it.
Let it run for some time. You can cancel the execution by pressing CTRL+C.

[root@dixitlab perf]# ls -ltrh
total 12K
-rw-------. 1 root root 12K Jan  8 01:14 perf.data

Now lets see what’s there inside the raw file —- lets Dump raw contents from perf.data as hex for debugging.
For this we will use perf script with flag D, it will automatically considers the perf.data file present under the directory to show charts.


[root@dixitlab perf]# perf script -D

0x110 [0x48]: event: 1
.
. ... raw event: size 72 bytes
.  0000:  01 00 00 00 01 00 48 00 ff ff ff ff 00 00 00 00  ......H.........
.  0010:  00 00 00 00 00 00 00 00 ff ff ff 9f ff ff ff ff  ................
.  0020:  00 00 00 81 ff ff ff ff 5b 6b 65 72 6e 65 6c 2e  ........[kernel.
.  0030:  6b 61 6c 6c 73 79 6d 73 5d 5f 74 65 78 74 00 00  kallsyms]_text..
.  0040:  00 00 00 00 00 00 00 00                          ........
.
0x110 [0x48]: PERF_RECORD_MMAP -1/0: [0(0xffffffff9fffffff) @ 0xffffffff81000000]: [kernel.kallsyms]_text

0x158 [0x78]: event: 1
.
. ... raw event: size 120 bytes
.  0000:  01 00 00 00 01 00 78 00 ff ff ff ff 00 00 00 00  ......x.........
.  0010:  00 00 00 a0 ff ff ff ff ff bf 01 00 00 00 00 00  ................
.  0020:  00 00 00 00 00 00 00 00 2f 6c 69 62 2f 6d 6f 64  ......../lib/mod
.  0030:  75 6c 65 73 2f 32 2e 36 2e 33 39 2d 34 30 30 2e  ules/2.6.39-400.
.  0040:  32 39 37 2e 33 2e 65 6c 36 75 65 6b 2e 78 38 36  297.3.el6uek.x86
.  0050:  5f 36 34 2f 6b 65 72 6e 65 6c 2f 64 72 69 76 65  _64/kernel/drive
.  0060:  72 73 2f 6d 64 2f 64 6d 2d 6d 6f 64 2e 6b 6f 00  rs/md/dm-mod.ko.
.  0070:  00 00 00 00 00 00 00 00                          ........
.


0x2bf8 [0x68]: PERF_RECORD_MMAP 4387/4387: [0x7f6062943000(0x4c000) @ 0]: /u01/app/oracle/product/12.2.0/dbhome_1/lib/libons.so

0x2c60 [0x68]: event: 1
.
. ... raw event: size 104 bytes
.  0000:  01 00 00 00 02 00 68 00 23 11 00 00 23 11 00 00  ......h.#...#...
.  0010:  00 80 ba 62 60 7f 00 00 00 90 4d 00 00 00 00 00  ...b`.....M.....
.  0020:  00 00 00 00 00 00 00 00 2f 75 30 31 2f 61 70 70  ......../u01/app
.  0030:  2f 6f 72 61 63 6c 65 2f 70 72 6f 64 75 63 74 2f  /oracle/product/
.  0040:  31 32 2e 32 2e 30 2f 64 62 68 6f 6d 65 5f 31 2f  12.2.0/dbhome_1/
.  0050:  6c 69 62 2f 6c 69 62 6e 6e 7a 31 32 2e 73 6f 00  lib/libnnz12.so.
.  0060:  00 00 00 00 00 00 00 00                          ........
.
0x2c60 [0x68]: PERF_RECORD_MMAP 4387/4387: [0x7f6062ba8000(0x4d9000) @ 0]: /u01/app/oracle/product/12.2.0/dbhome_1/lib/libnnz12.so

0x2cc8 [0x68]: event: 1
.
. ... raw event: size 104 bytes
.  0000:  01 00 00 00 02 00 68 00 23 11 00 00 23 11 00 00  ......h.#...#...
.  0010:  00 10 2f 63 60 7f 00 00 00 c0 22 00 00 00 00 00  ../c`.....".....
.  0020:  00 00 00 00 00 00 00 00 2f 75 30 31 2f 61 70 70  ......../u01/app
.  0030:  2f 6f 72 61 63 6c 65 2f 70 72 6f 64 75 63 74 2f  /oracle/product/
.  0040:  31 32 2e 32 2e 30 2f 64 62 68 6f 6d 65 5f 31 2f  12.2.0/dbhome_1/
.  0050:  6c 69 62 2f 6c 69 62 69 70 63 31 2e 73 6f 00 00  lib/libipc1.so..
.  0060:  00 00 00 00 00 00 00 00                          ........
.
0x2cc8 [0x68]: PERF_RECORD_MMAP 4387/4387: [0x7f60632f1000(0x22c000) @ 0]: /u01/app/oracle/product/12.2.0/dbhome_1/lib/libipc1.so

0x2d30 [0x68]: event: 1
.
. ... raw event: size 104 bytes
.  0000:  01 00 00 00 02 00 68 00 23 11 00 00 23 11 00 00  ......h.#...#...
.  0010:  00 50 72 63 60 7f 00 00 00 20 07 00 00 00 00 00  .Prc`.... ......
.  0020:  00 00 00 00 00 00 00 00 2f 75 30 31 2f 61 70 70  ......../u01/app
.  0030:  2f 6f 72 61 63 6c 65 2f 70 72 6f 64 75 63 74 2f  /oracle/product/
.  0040:  31 32 2e 32 2e 30 2f 64 62 68 6f 6d 65 5f 31 2f  12.2.0/dbhome_1/
.  0050:  6c 69 62 2f 6c 69 62 6d 71 6c 31 2e 73 6f 00 00  lib/libmql1.so..
.  0060:  00 00 00 00 00 00 00 00                          ........
.
0x2d30 [0x68]: PERF_RECORD_MMAP 4387/4387: [0x7f6063725000(0x72000) @ 0]: /u01/app/oracle/product/12.2.0/dbhome_1/lib/libmql1.so

0x2d98 [0x78]: event: 1
.
. ... raw event: size 120 bytes
.  0000:  01 00 00 00 02 00 78 00 23 11 00 00 23 11 00 00  ......x.#...#...
.  0010:  00 c0 99 63 60 7f 00 00 00 90 3a 00 00 00 00 00  ...c`.....:.....
.  0020:  00 00 00 00 00 00 00 00 2f 75 30 31 2f 61 70 70  ......../u01/app
.  0030:  2f 6f 72 61 63 6c 65 2f 70 72 6f 64 75 63 74 2f  /oracle/product/
.  0040:  31 32 2e 32 2e 30 2f 64 62 68 6f 6d 65 5f 31 2f  12.2.0/dbhome_1/
.  0050:  6c 69 62 2f 6c 69 62 63 6c 6e 74 73 68 63 6f 72  lib/libclntshcor
.  0060:  65 2e 73 6f 2e 31 32 2e 31 00 00 00 00 00 00 00  e.so.12.1.......
.  0070:  00 00 00 00 00 00 00 00                          ........
.
0x2d98 [0x78]: PERF_RECORD_MMAP 4387/4387: [0x7f606399c000(0x3a9000) @ 0]: /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntshcore.so.12.1

0x2e10 [0x70]: event: 1
.
. ... raw event: size 112 bytes
.  0000:  01 00 00 00 02 00 70 00 23 11 00 00 23 11 00 00  ......p.#...#...
.  0010:  00 a0 f6 63 60 7f 00 00 00 30 65 03 00 00 00 00  ...c`....0e.....
.  0020:  00 00 00 00 00 00 00 00 2f 75 30 31 2f 61 70 70  ......../u01/app
.  0030:  2f 6f 72 61 63 6c 65 2f 70 72 6f 64 75 63 74 2f  /oracle/product/
.  0040:  31 32 2e 32 2e 30 2f 64 62 68 6f 6d 65 5f 31 2f  12.2.0/dbhome_1/
.  0050:  6c 69 62 2f 6c 69 62 63 6c 6e 74 73 68 2e 73 6f  lib/libclntsh.so
.  0060:  2e 31 32 2e 31 00 32 2e 00 00 00 00 00 00 00 00  .12.1.2.........
.
0x2e10 [0x70]: PERF_RECORD_MMAP 4387/4387: [0x7f6063f6a000(0x3653000) @ 0]: /u01/app/oracle/product/12.2.0/dbhome_1/lib/libclntsh.so.12.1

0x2e80 [0x70]: event: 1
.
. ... raw event: size 112 bytes
.  0000:  01 00 00 00 02 00 70 00 23 11 00 00 23 11 00 00  ......p.#...#...
.  0010:  00 20 a1 67 60 7f 00 00 00 40 0e 00 00 00 00 00  . .g`....@......
.  0020:  00 00 00 00 00 00 00 00 2f 75 30 31 2f 61 70 70  ......../u01/app
.  0030:  2f 6f 72 61 63 6c 65 2f 70 72 6f 64 75 63 74 2f  /oracle/product/
.  0040:  31 32 2e 32 2e 30 2f 64 62 68 6f 6d 65 5f 31 2f  12.2.0/dbhome_1/
.  0050:  6c 69 62 2f 6c 69 62 73 71 6c 70 6c 75 73 2e 73  lib/libsqlplus.s
.  0060:  6f 00 32 2e 31 00 32 2e 00 00 00 00 00 00 00 00  o.2.1.2.........
.
0x2e80 [0x70]: PERF_RECORD_MMAP 4387/4387: [0x7f6067a12000(0xe4000) @ 0]: /u01/app/oracle/product/12.2.0/dbhome_1/lib/libsqlplus.so

0x2ef0 [0x38]: event: 1
.
. ... raw event: size 56 bytes
.  0000:  01 00 00 00 02 00 38 00 23 11 00 00 23 11 00 00  ......8.#...#...
.  0010:  00 90 8f 09 ff 7f 00 00 00 10 00 00 00 00 00 00  ................
.  0020:  00 00 00 00 00 00 00 00 5b 76 64 73 6f 5d 00 70  ........[vdso].p
.  0030:  00 00 00 00 00 00 00 00                          ........
.

Now lets get back to the point where we had the process (PID: 6720) consuming highest CPU resources.

now profiling CPU’s with sample rate of 99 Hertz/second, -a for all CPU’s, -g for stack traces, sleep of 60 seconds for duration of run.

[root@dixitlab perf]# perf record -F 99 -a -g -- sleep 60 
[ perf record: Woken up 1 times to write data ]
[ perf record: Captured and wrote 0.388 MB perf.data (~16948 samples) ]

Now as i discussed above, it will generate a new perf.data under the directory.

[root@dixitlab perf]# ls -ltrh
total 768K
-rw-------. 1 root root 379K Jan  8 01:30 perf.data.old
-rw-------. 1 root root 386K Jan  8 01:31 perf.data

Next, we will interpret results from the newly created ‘perf.data’ file.
I have used ‘perf report -n –stdio’, you can simply use ‘perf report’ too.


[root@dixitlab perf]#
[root@dixitlab perf]# perf report -n --stdio 
# Events: 2K cpu-clock
#
# Overhead  Samples            Command          Shared Object                                    Symbol
# ........ ..........  ...............  .....................  ........................................
#
    50.05%       1001             sshd  [e1000]                [k] e1000_phy_read_status
                       |
                       --- e1000_phy_read_status
                          |
                          |--99.60%-- dev_hard_start_xmit
                          |          sch_direct_xmit
                          |          dev_queue_xmit
                          |          ip_finish_output
                          |          ip_output
                          |          ip_local_out
                          |          ip_queue_xmit
                          |          tcp_transmit_skb
                          |          tcp_write_xmit
                          |          __tcp_push_pending_frames
                          |          tcp_push
                          |          tcp_sendmsg
                          |          inet_sendmsg
                          |          sock_aio_write
                          |          do_sync_write
                          |          vfs_write
                          |          sys_write
                          |          system_call_fastpath
                          |          __write_nocancel
                           --0.40%-- [...]

     8.35%        167          swapper  [kernel.kallsyms]      [k] native_safe_halt
                    |
                    --- native_safe_halt
                        default_idle
                        cpu_idle
                        rest_init
                        start_kernel
                        x86_64_start_reservations
                        x86_64_start_kernel

     4.45%         89          sqlplus  [kernel.kallsyms]      [k] finish_task_switch
                    |
                    --- finish_task_switch
                        __schedule
                       |
                       |--87.64%-- __cond_resched
                       |          _cond_resched
                       |          mutex_lock
                       |          |
                       |          |--91.03%-- process_output_block
                       |          |          n_tty_write
                       |          |          tty_write
                       |          |          vfs_write
                       |          |          sys_write
                       |          |          system_call_fastpath
                       |          |          __write_nocancel
                       |          |
                       |           --8.97%-- process_output
                       |                     n_tty_write
                       |                     tty_write
                       |                     vfs_write
                       |                     sys_write
                       |                     system_call_fastpath
                       |                     __write_nocancel
                       |
                        --12.36%-- schedule
                                  sysret_careful
                                  |
                                  |--81.82%-- __write_nocancel
                                  |
                                   --18.18%-- __write_nocancel


.................
...........................
..................................

Note:
You can further sort results based on cpu, shared objects etc.
perf report –sort=cpu
perf report –sort=dso

Above results reflects the division. While taking a look at the tree, it shows a combination or a club of few OS Layer components (Starting from dev_hard_start_xmit) was sampled of time 50.05*99.60 = 49.84%
with parent sshd command and shared object [e1000] and is a physical read operation.

Rest of all might seem gibberish but needs time and patience to understand. I will try to cover the profiler in more detail with more use case scenarios in future. Till the time you can check the WIKI page of the tool.

Note: In case if you are using perf on VMWare system, you might get error “perf.data file has no samples“.
In order to fix it try using the ‘perf record’ option with -e cpu-clock arguments to collect the sample data and then use ‘perf report’ to interpret the file.

example:
perf record -F 99 -a -g -e cpu-clock — sleep 20


Hope It Helps
Prashant Dixit

Posted in Advanced | Tagged: , | 3 Comments »

Script to measure the Source & Contribution of any Wait Event within AWR snapshots.

Posted by FatDBA on January 1, 2018

🀘 Happy New Year Everyone! 🀘

So, here i am with the maiden post of this year.
Today i will discuss about a script that might be handy and useful while you are investigating any performance problem with the database.
In fact this is one of my favorite script which i always considers to run at a point when we have identified the leading wait event and the time-frame of the spike or for any transient variation in system behavior. This script helps you to measure the source and contribution or the impact of any specific wait event.

The script is a join between ASH views and historical views (dba_hist_active_sess_history AND dba_hist_snapshot) and provides you stats based on Average Number of Active Sessions (AAS) were waiting on this event during the period and with what total contribution in terms of percentage.

Lets work on a use case.
Suppose you have a system with huge User IOs happening, as conformed by any report or tool (AWR, ASH, Via any Script, OEM etc.)

For example using AWR we found one of the User IO wait class event ‘db file scattered read’ was leading the ‘Top Foreground Wait Events’ charts on a specific time when we have the high User IO load on system.

                                           Total Wait       Avg   % DB Wait
Event                                Waits Time (sec)      Wait   time Class
------------------------------ ----------- ---------- --------- ------ --------
db file scattered read              10,282       82.8    41.50ms  43.9 User I/O

So, now you have identified the leading wait event and the timeframe with maximum intensity or frequency, Lets call the script.

Script Inputs:
Example:

Enter value for event_class:
User I/O
Enter value for event_name: db file scattered read
Enter value for begin_snap: 193
Enter value for end_snap: 200
Enter value for dbid: 2896132084

WITH
events AS (
SELECT /*+ MATERIALIZE NO_MERGE */
       SUBSTR(TRIM(h.sql_id||' '||h.program||' '||
       CASE h.module WHEN h.program THEN NULL ELSE h.module END), 1, 128) source,
       h.dbid,
       COUNT(*) samples
  FROM dba_hist_active_sess_history h,
       dba_hist_snapshot s
 WHERE h.wait_class = TRIM('&Event_Class') AND h.event = TRIM('&Event_Name')
   AND h.snap_id BETWEEN &Begin_Snap AND &End_Snap
   AND h.dbid = &dbid
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
 GROUP BY
       h.sql_id,
       h.program,
       h.module,
       h.dbid
 ORDER BY
       3 DESC
),
total AS (
SELECT SUM(samples) samples,
       SUM(CASE WHEN ROWNUM > 15 THEN samples ELSE 0 END) others
  FROM events
)
SELECT e.source,
       e.samples,
       ROUND(100 * e.samples / t.samples, 1) percent,
       (SELECT DBMS_LOB.SUBSTR(s.sql_text, 1000, 1) FROM dba_hist_sqltext s WHERE s.sql_id = SUBSTR(e.source, 1, 13) AND s.dbid = e.dbid AND ROWNUM = 1) sql_text
  FROM events e,
       total t
 WHERE ROWNUM  0.1
 UNION ALL
SELECT 'Others',
       others samples,
       ROUND(100 * others / samples, 1) percent,
       NULL sql_text
  FROM total
 WHERE others > 0
   AND ROUND(100 * others / samples, 1) > 0.1;

Below is the output of the query.

SOURCE                                                                      SAMPLES      PERCENT       SQL_TEXT
------------------------------------------------------------------------  ---------- ---------------  -------------------------------
5av23g8w7f3ka sqlplus@dixitlab.localdomain (TNS V1-V3)                          1          45.23       select * from dixit.bigtab
9a7gbkahasj1a sqlplus@dixitlab.localdomain (TNS V1-V4)                          1           5          select * from dixit.gianttable
OTHERS                                                                          14         49.77

Okay so result shows one of the SQL statement with SQL id 5av23g8w7f3ka was responsible for more than 45% of these waits.
So, now you have the evidence and can start troubleshooting this specific statement to reduce the USER IOs.

Hope It Helps
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , | Leave a Comment »

All about Oracle 12c Database In-Memory!

Posted by FatDBA on December 27, 2017

Hi Mates,

Today i will discuss about the Database In-Memory option of Oracle 12c databases. I know i am little late to add about this feature but recently I’ve implemented the option for one of my customer in Latin Americas region and there I’ve got more exposure to understand it better and implement the feature.

So, lets start with most probably my last post of the year!

Recently i have got a chance to test and to benchmark the performance gains in one of out Pre-Prod environment. This feature applies on Tablespaces, Tables, MViews, (Sub) Partitions (Except objects owned by SYS, SYSTEM and SYSAUX). To understand the feature i would first like to shed some light on two of the ways how Oracle stores tables on both Disk and Memory using conventional ‘Row Format’ and with the all new In-Memory ‘Column’ format.

Row Arrangement: Is the same old traditional method to store data in row formats. This is best for OLTP systems as queries runs faster with this approach as it quickly fetches all of the columns in a record.
Column Based Arrangement: This way it stores records in a separate column store. This proves good for OLAP systems where large set of data is chosen but only for few number of columns.

Hence, based on above two methods or data arrangements in database it is clear that the row based method is best for DMLs and column based arrangement is good when selecting large portion of data, so both of the two methods have their own respective pros and cons. But starting from Oracle 12.1.0.2 we have the all new feature of ‘DB In-Memory’ which use best of both the approaches. I mean it uses both row and column arrangements to keep data in memory. Our smart optimizer automatically knows which query to route as per the workload (OLTP and for Analytical processing).

The In-Memory feature uses the IM Column store which is a new occupant of the SGA (In-Memory Area). This In-Memory Area is adjusted by a parameter INMEMORY_SIZE.

As far as IM Column Store it is filled by the information collected by worker processes e.g. w001, w002 etc.; each of the worker process updates the IM Compression Units or IMCUs.

So, enough the background and internals involved, lets jump to the real work and check how this thing practically works.
First check the minimum comparability of the database

COMPATIBLE = 12.1.0.0.0

Lets first enable the in-memory column store. There are many of the related parameters and are given below.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_adg_enabled                 boolean     TRUE
inmemory_clause_default              string
inmemory_expressions_usage           string      ENABLE
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     0
inmemory_query                       string      ENABLE
inmemory_size                        big integer 0
inmemory_trickle_repopulate_servers_ integer     1
percent
inmemory_virtual_columns             string      MANUAL
optimizer_inmemory_aware             boolean     TRUE

Before i show next logs on how to enable it, i would first like to show you the database startup details with no In-Memory enabled.

SQL> startup
ORACLE instance started.

Total System Global Area 1191182336 bytes
Fixed Size                  8620032 bytes
Variable Size             771753984 bytes
Database Buffers          402653184 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
SQL>

Okay now lets enable it!
Now a question might be asked about the criterion of adding objects to the pool. So in my opinion the best candidates would
be — Very hot data, large segments (not less than 1MB), collect these stats from sources like Oracle segment statistics, number of column scans, AWR reports etc. Analytical queries etc.

SQL> alter system set inmemory_size=250m scope=spfile;

System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1191182336 bytes
Fixed Size                  8620032 bytes
Variable Size             822085632 bytes
Database Buffers           83886080 bytes
Redo Buffers                8155136 bytes
In-Memory Area            268435456 bytes
Database mounted.
Database opened.

Now i will try to load some stuff in to the memory.

SELECT table_name,inmemory,inmemory_priority,inmemory_distribute,inmemory_compression,inmemory_duplicate 
FROM user_tables WHERE table_name='TEST1';

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
TEST1                DISABLED

00:14:42 SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     200278016          0 DONE                         >>>>>>> 1MB pool is used to store the column formatted data.
64KB POOL                     50331648          0 DONE                         >>>>>>> 64 KB pool is used to store the metadata about its residents.

Now i am moving the discussed table to in memory.

exec DBMS_INMEMORY.POPULATE(schema_name => dixit, table_name => 'TEST1');
or Using

00:15:01 SQL> alter table test1 inmemory;

Table altered.


00:17:59 SQL> SELECT table_name,inmemory,inmemory_priority,inmemory_distribute,inmemory_compression,inmemory_duplicate
FROM user_tables WHERE table_name='TEST1';

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
TEST1                ENABLED  NONE     AUTO            FOR QUERY LOW     NO DUPLICATE




00:20:17 SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     200278016          0 DONE
64KB POOL                     50331648          0 DONE


00:18:25 SQL> select owner, segment_name, populate_status from v$im_segments;

no rows selected

Above results shows that no segment is added to the pool. Now lets try to query the table and see the results again.

00:20:26 SQL> select count(*) from test1;

  COUNT(*)
----------
   1000000



00:20:48 SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     200278016          0 POPULATING
64KB POOL                     50331648          0 POPULATING


Okay now it's doing something as status from DONE has changed to POLULATING. Lets repeat the same command again.

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     200278016   49283072 DONE
64KB POOL                     50331648     458752 DONE

Okay its there in the pool now! Lets check the in memory area, what we have inside it.

00:23:56 SQL> select owner, segment_name, populate_status from v$im_segments;

OWNER      SEGMENT_NAME         POPULATE_STAT
---------- -------------------- -------------
DIXIT      TEST1                COMPLETED

Lets conform if table is fully populated we have to look at v$im_segments_detail which compares the number of blocks in In-Memory and in the TEST1 table.

SELECT m.inst_id, 
       m.blocksinmem, 
       m.datablocks 
FROM   v$im_segments_detail m, 
       user_objects o 
WHERE  m.dataobj = o.object_id 
AND    o.object_name = 'TEST1';

   INST_ID BLOCKSINMEM DATABLOCKS
---------- ----------- ----------
         1        6922       6922

Lets see if there is any changes captured in Execution plan of the query.

SQL> explain plan for select count(*) from test1;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 3896847026

-----------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    71   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE             |       |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| TEST1 |  1000K|    71   (2)| 00:00:01 |
-----------------------------------------------------------------------------

9 rows selected.

Now we have a new operation type added to the plan ‘TABLE ACCESS INMEMORY FULL’. It says that the object was accessed directly from the memory.

You can compare the response times with/without the In-Memory Store by simply disabling In-Memory functionality in your session:

ALTER SESSION SET inmemory_query = ENABLE;
ALTER SESSION SET inmemory_query = DISABLE;

You can anytime remove the objects from the pool. Other options to use with In memory.

SQL> ALTER TABLESPACE example INMEMORY;

Enabling the In-Memory attribute on the TEST1 table but excluding the β€œdest_id” column

SQL> ALTER TABLE TEST1 INMEMORY NO INMEMORY(dest_id);

Enabling the in memory option for table TEST1 and setting the priority to CRITICAL. With this option set on object (Other options are HIGH, MEDIUM, LOW), it will be populated immediately after the database is opened.

SQL> ALTER TABLE TEST1 INMEMORY PRIORITY CRITICAL;

There are few other options like Compression (Objects compressed during population) , Joins, Scans are also there and will be covered in a separate post or you can read the official documentation on in memory to understand them.

But i would like to discuss little about how the In-Memory option works in RAC environment.

In case of user querying the database in-memory in RAC, serial queries will only access a fraction of the data from its own node. IMCUs or IM Compression Units are not traveled using interconnect or using cache fusion. It fetches the remainder data from the disk itself.
Parallel execution helps as it starts multiple processes and ensures that at least one parallel server slave is allocated for each RAC instance. For that we have to set the parallel_degree_policy or Auto DOP to AUTO which makes the query coordinator ICMU aware and it (QC) automatically starts parallel server processes on correct server.

Now after all the discussion time to share my final words on the subject.
As we know that caching or pooling are the concepts there with Oracle RDBMS or with any of the databases from a very long time; then – why this in memory now ?
We already have few of the areas like KEEP/RECYCLE pools, Result cache etc. to keep cache data or results.
So my answer is that the Oracle In-Memory column store enables objects to load in memory in compressed columnar format which makes the scans to perform better that on-disk reads and leads to performance boosts. Use it when there is a SQL related performance issue and you’ve tried all tuning methods and each one of them are failed to fix the problem and yes If the In-Memory performance benefit can outperform the additional Oracle license costs.

Hope It Helps
Prashant Dixit

Posted in Advanced, Basics | Tagged: , | Leave a Comment »

Scripts: How to accurately calculate Database and Table growth.

Posted by FatDBA on December 26, 2017

Hi Pals,

Many of the times while doing sizing, capacity planning and requirement calculations you might have come across a point where you have to share database or table level growth stats to your customer for estimations. So – What should be the right and the accurate way or the method to track the database or table level growth in your databases ?

Today i will discuss about the ways to precisely calculate or track growth of your database we will surely come up with solutions like
– Do a join on V$DATAFILE, V$TEMPFILE, V$LOG and some help from DBA_FREE_SPACE.
– Else a different approach is using DBA_HIST views (DBA_HIST_SEG_STAT) in conjunction with dba_segments view.

As far as both of the approaches, second one (Using DBA_HIST views) is the most reliable,trusted and by far accurate way of tracking sizing information of the database But most of the DBA_HIST tables are part of Diagnostics Pack and should be accessed only if you have the license. So i advocate here another way which is manual method using customized PL/SQL code to monitor the growth trends of both your database and tables.

Here i would like to thank one of the best PL/SQL coder i know – Mr. Komal Chand Lilhare (Master Coder πŸ™‚ ) who is the original author behind these scripts and i have only made few additions and customization’s as per my need.

DB GROWTH JOB:

Step 1. Create table to capture DB growth

create table db_growth
as select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE% "
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
);

Step2. Create view to automate the capture process through DB jobs

create view v_db_growth
as select *
from
(
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
);


Step3. Create DB Job to capture the growth on regular interval

declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into db_growth select * from v_db_growth;commit;end;', sysdate, 'trunc(SYSDATE)+1', TRUE);
commit;
end;
/

Step4. Data analysis. It’s all your SQL skills how you want to see and present the data trend.

set linesize 130;
col what for a30;
col log_user for a12;
col interval for a30;

select job,what,log_user, to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "Sysdate", 
to_char(next_date,'dd-mon-yyyy hh24:mi:ss') "Next Date",
interval from user_jobs;


       JOB WHAT                           LOG_USER     Sysdate              Next Date            INTERVAL
---------- ------------------------------ ------------ -------------------- -------------------- ------------------------------
        23 begin insert into db_growth se SYS          26-dec-2017 23:18:18 17-dec-2017 00:00:00 trunc(SYSDATE)+1
           lect * from v_db_growth;commit
           ;end;

        24 begin insert into table_growth SYS          26-dec-2017 23:18:18 27-dec-2017 00:00:00 trunc(SYSDATE)+1
           _MB select * from v_table_grow
           th_MB;commit;end;




select * from db_growth order by 1;

SYSDATE   TOTAL_SIZE FREE_SPACE     FREE%
--------- ---------- ---------- ----------
15-DEC-17 167935.125 36946.0625 22.0001995
16-DEC-17 167935.125 36944.9375 21.9995296


select trunc("SYSDATE") Dt,round("TOTAL_SIZE") "TOTAL_SIZE(M)", round("TOTAL_SIZE"- "FREE_SPACE")"USED_SPACE(M)" , 
round("FREE_SPACE") "FREE_SPACE(M)"  
from sys.db_growth order by 1;

DT        TOTAL_SIZE(M) USED_SPACE(M) FREE_SPACE(M)
--------- ------------- ------------- -------------
15-DEC-17        167935        130989         36946
16-DEC-17        167935        130990         36945

TABLE GROWTH (IN MBs) SCRIPT:

Step 1. Check if table table_growth_MB is present in the database;

Step 2. Create a table and view which captures database tables which are more then 100MB in size, you can change it as per need.

create table table_growth_MB as 
SELECT trunc(sysdate) DT, owner,table_name, TRUNC(sum(bytes)/1024/1024) size_MB , 0 row_count
FROM (SELECT segment_name table_name, owner, bytes 
FROM dba_segments WHERE segment_type = 'TABLE' 
UNION ALL 
SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name 
AND   s.owner = i.owner AND   s.segment_type = 'INDEX' 
UNION ALL 
SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name 
AND   s.owner = l.owner AND   s.segment_type = 'LOBSEGMENT' 
UNION ALL 
SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name 
AND   s.owner = l.owner AND   s.segment_type = 'LOBINDEX')
where table_name = 'H304'
GROUP BY table_name, owner, trunc(sysdate)
HAVING SUM(bytes)/1024/1024 > 100 ;

Step 3.

create or replace view v_table_growth_MB as 
SELECT trunc(sysdate) DT, owner,table_name, TRUNC(sum(bytes)/1024/1024)  size_MB, 0 row_count
FROM (SELECT segment_name table_name, owner, bytes 
FROM dba_segments WHERE segment_type = 'TABLE' 
UNION ALL 
SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name 
AND   s.owner = i.owner AND   s.segment_type = 'INDEX' 
UNION ALL 
SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name 
AND   s.owner = l.owner AND   s.segment_type = 'LOBSEGMENT' 
UNION ALL 
SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name 
AND   s.owner = l.owner AND   s.segment_type = 'LOBINDEX')
where table_name = 'T312'
GROUP BY table_name, owner, trunc(sysdate) 
HAVING SUM(bytes)/1024/1024 > 100 ;

Step 4. Create a job which will run daily and capture the table growth.

declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into table_growth_MB select * from v_table_growth_MB;commit;end;', sysdate, 'trunc(SYSDATE)+1', TRUE);
commit;
end;
/

Step 5. Analyze the data, it’s all your SQL skill how you want to see and present the trend analysis.

– Job Details:

set linesize 130;
col what for a30;
col log_user for a12;
col interval for a30;

select job,what,log_user, to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "Sysdate", 
to_char(next_date,'dd-mon-yyyy hh24:mi:ss') "Next Date",
interval from user_jobs;


ALTER SESSION SET NLS_DATE_FORMAT='dd-Month-yyyy';

set pages 0
set lines 120
col DT for a20 
col owner for a20 
col table_name format a30
col size_MB format 999999.99
col row_count for 999999999
select trunc(DT), owner,table_name, size_MB , row_count from table_growth_MB 
where table_name like '%SAMPLE%'
order by 1;


select TABLE_NAME, SIZE_MB , DT from table_growth_MB  where trunc(dt) = trunc(sysdate-45) 
and  table_name in (select TABLE_NAME  from table_growth_MB T1 where dt> sysdate-2
and exists (select 1 from table_growth_MB T2 where trunc(T2.dt) = trunc(sysdate-45) and T2.table_name= T1.table_name and T1.SIZE_MB>T2.SIZE_MB+400))
union all
select TABLE_NAME, SIZE_MB , DT from table_growth_MB T1 where dt> sysdate-1
and exists (select 1 from table_growth_MB T2 where trunc(T2.dt) = trunc(sysdate-45) and T2.table_name= T1.table_name and T1.SIZE_MB>T2.SIZE_MB+400)
order by  TABLE_NAME, DT;

Hope It Helps
Prashant Dixit

Posted in Advanced | Leave a Comment »

MySQL – How to use LOAD DATA INFILE and INTO OUTFILE

Posted by FatDBA on December 20, 2017

Today i will discuss about the the useful but script/SQL based data export/import method in MySQL database that is – LOAD DATA INFILE and INTO OUTFILE.

Lets first create an export file/script for the table using SELECT … INTO OUTFILE, here you can specify the location of the export file.

mysql> select * from country into outfile 'countrycreate.sql';
Query OK, 109 rows affected (0.00 sec)

-rw-rw-rw-. 1 mysql mysql 3.6K Dec 20 01:07 countrycreate.sql

As there is no table definition captured using SELECT INTO OUTFILE way, so you should always ensure that you have a copy of the table definition for restoration of the file.

bash-4.1$ mysqldump -u root -p --no-data dixit country > /var/lib/mysql/dixit/countryschemadef.sql
Enter password:

-rw-rw-rw-. 1 mysql mysql 3.6K Dec 20 01:07 countrycreate.sql
-rw-r--r--. 1 mysql mysql 1.6K Dec 20 01:10 countryschemadef.sql

Lets see the contents of this newly created file.

bash-4.1$ more countryschemadef.sql
-- MySQL dump 10.13  Distrib 5.7.20, for Linux (x86_64)
--
-- Host: localhost    Database: dixit
-- ------------------------------------------------------
-- Server version       5.7.20

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `country`
--

DROP TABLE IF EXISTS `country`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `country` (
  `country_id` int(11) DEFAULT NULL,
  `country` text,
  `last_update` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-12-20  1:10:20

Lets create the new user and load the table data to it.


bash-4.1$ mysqladmin -u root -p create dixit2
Enter password:


bash-4.1$ mysql -u root -p dixit2  load data infile '/var/lib/mysql/dixit/countrycreate.sql' into table country;
Query OK, 109 rows affected (0.01 sec)
Records: 109  Deleted: 0  Skipped: 0  Warnings: 0

mysql>
mysql>
mysql> select count(*) from country;
+----------+
| count(*) |
+----------+
|      109 |
+----------+
1 row in set (0.00 sec)

All set!

Hope It Helps!
Prashant Dixit

Posted in Basics | Tagged: , | Leave a Comment »

Auto Stats Gathering in Oracle 12c & Something Interesting :)

Posted by FatDBA on December 14, 2017

Hi Fellas,
Starting from Oracle 12c there is a new feature added which collects the statistics when you perform the Bulk Loads when using any of the two methods:
– CREATE TABLE AS SELECT (CTAS)
– INSERT INTO … SELECT (Into an empty table using DPR or Direct Path Read).

SQL> explain plan for create table dixittab as select * from scottisdead;
Explained.
 
SQL> select * from table(DBMS_XPLAN.DISPLAY);
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 14312189
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |               |   500K|  8812K|   612   (1)| 00:00:01 |
|   1 |  LOAD AS SELECT                  | DIXITTAB      |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |               |   500K|  8812K|   371   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL             | SCOTTISDEAD   |   500K|  8812K|   371   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
10 rows selected. 

Above in execution plan you’ll see the new operation named “OPTIMIZER STATISTICS GATHERING” at ID 2.
Lets verify if the stats are collected.

SQL> select table_name, last_analyzed from user_tables where table_name = 'DIXITTAB';
 
TABLE_NAME       LAST_ANALYZED
---------------- -------------
DIXITTAB         12-DEC-17

Yup, stats were collected!
Same way stats will be auto collected during the other type of Bulk Load method (INSERT INTO .. SELECT).

There may be times when you want to disable this feature, situations like.
– Long/Huge Insert operations which is taking huge time on STATS GATHERING.
– With an extremely large dataset where you don’t want to collect stats.

In order to achieve that we have the option, with the use of a hint which instructs oracle to not gather table statistics.

SQL> create table dixittab as select /*+NO_GATHER_OPTIMIZER_STATISTICS */* from scottisdead;

Now, something interesting i would like to discuss …..
Is there any other condition when the stats won’t be collected automatically except barring it using NO_GATHER_OPTIMIZER_STATISTICS Hint ?

Lets try to do some conventional bulk loading using INSERT INTO .. SELECT method.
To do some tests – I am intentionally commenting few of the columns both the tables. I’ve commented DATE_VAL column of newly created table TABLE1 and DATE_VALUE of the table selected SAMPLE.


SQL> create table table1 (ident number, date_val date, text_val varchar2(4000));
Table created.


SQL> insert /*+ append */ into table1
(IDENT
--, DATE_VAL
, TEXT_VAL)
SELECT ID
--, DATE_VALUE
, TEXT_VALUE
FROM SAMPLE; 

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

Plan hash value: 1523099961
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | INSERT STATEMENT   |        |   100K|  2539K|   154   (1)| 00:00:01 |
|   1 |  LOAD AS SELECT    | TABLE1 |       |       |            |          |
|   2 |   TABLE ACCESS FULL| SAMPLE |   100K|  2539K|   154   (1)| 00:00:01 |
-----------------------------------------------------------------------------

9 rows selected.

😦 😦 Why, the auto stats gathering behavior not repeated this time ??

This happened because Oracle needs inclusion of all the columns of a table in order to kick in the OPTIMIZER STATISTICS GATHERING operation —> Let me show you what i said in above statement.

SQL> insert /*+ append */ into table1
(IDENT
, DATE_VAL
, TEXT_VAL)
SELECT ID
, DATE_VALUE
, TEXT_VALUE
FROM SAMPLE;  

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------

Plan hash value: 1523099961
-------------------------------------------------------------------------------------------
| Id  | Operation                        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |        |   100K|  3320K|   154   (1)| 00:00:01 |
|   1 |  LOAD AS SELECT                  | TABLE1 |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |        |   100K|  3320K|   154   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL             | SAMPLE |   100K|  3320K|   154   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------

10 rows selected.

Yes, the the stats were collected this time when we’ve included all the columns of the tables.
I haven’t seen any documentation on this restriction on this new feature of Oracle 12c. Hope Oracle adds this soon this to their documentation πŸ™‚ …..

Hope It Helps!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , | Leave a Comment »

DB Upgrade Failed! :( – Have to start it from the scratch, Really ??

Posted by FatDBA on December 4, 2017

Hi Mates,

Lets recall the list of per-requisites that we have to perform before doing a database upgrade!
Few of them i quickly recall are – Full RMAN Backup, Creating Guaranteed Restore points etc.

But are these options allows us to resume any failed upgrade activity ? — NO

Let’s go back to time before Oracle 12c (Ex: 10g, 11g etc.)

Resuming of failed upgrade was not possible and you have to recall the catupgrd.sql and open the database in UPGRADE mode but this will only start it from the beginning and won’t resume from the point it got failed last time. So same amount of time we be consumed once again for the upgrade

SQL> startup upgrade
SQL> spool /home/dixit/upgradedir/catupgrdscriptout.log
SQL> @?/rdbms/admin/catupgrd.sql

Now lets discuss about 12c, starting from 12c Release 1 we have the all new parallel upgrade utility (catctl.pl). For this you still have to open your database in UPGRADE mode and will be controlled by this new perl script.
If talk about restarting of failed upgrade, here we’ve got a small relief as you will be able to restart the upgrade from the phase where it was crashed/stopped using -P option.

Example:

Serial Phase #:56 [UPGR] Files:1 Time: 3s
******************* Migration ******************
Restart Phase #:57 Files: 1 Time: 0s
Serial Phase #:58 Files: 1 Time: 102s
Restart Phase #:59 Files: 1 Time: 1s
Serial Phase #:60 Files: 1 Time: 221s
Restart Phase #:61 Files: 1 Time: 0s
Serial Phase #:62 Files: 1 Time: 25s
Restart Phase #:63 Files: 1 Time: 0s
Serial Phase #:64 Files: 1 Time: 2s
Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/db_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0/db_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0/db_1/rdbms/admin -I /u01/app/oracle/product/12.1.0/db_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > catupgrd_datapatch_upgrade.log 2> catupgrd_datapatch_upgrade.err FAILED

As we see the upgrade failed at phase number 65, so we can restart the upgrade mentioning the phase number (-P) to resume it from that phase and upgrade process won’t repeat steps happened successfully.

$ORACLE_HOME/perl/bin/perl catctl.pl -p 65 -l /home/dixit/upgradedir/catupgrd.sql

******************* Migration ******************
Serial Phase #:65 Files: 1 Time: 28s
Serial Phase #:66 Files: 1 Time: 3921s
Serial Phase #:68 Files: 1 Time: 0s
Serial Phase #:69 Files: 1 Time: 57s
Serial Phase #:70 Files: 1 Time: 1027s
Serial Phase #:71 Files: 1 Time: 3s
Serial Phase #:72 Files: 1 Time: 0s
Serial Phase #:73 Files: 1 Time: 18s
Grand Total Time: 6459s
LOG FILES: (catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/JANI/upgrade/upg_summary.log
Grand Total Upgrade Time: [0d:0h:40m:39s]

Now, lets discuss the main objective of writing this new blog entry.
Starting from Oracle 12c Release 2 we have got more control over upgrade activities as with this latest release you will be able to resume the upgrade from where it got stopped last time. Oracle introduced a new flag -R with catctl.pl to achieve this, so don’t worry about the upgrade phases πŸ™‚

$ORACLE_HOME/perl/bin/perl catctl.pl -R -l /home/dixit/upgradedir/catupgrd.sql

Hope It Helps
Prashant Dixit

Posted in Advanced | Tagged: , , | Leave a Comment »

 
%d bloggers like this: