One of the crucial performance parameter for Golden gate extract process is TRANLOGOPTIONS which controls the way that it interacts with the transaction log. You can use multiple TRANLOGOPTIONS statements in the same parameter file, or you can specify multiple options within the same TRANLOGOPTIONS statement.
There are lot of performance related options i.e. INCLUDEAUX (AUX trails when reading audit trails), DBLOGREADERBUFSIZE etc. that you can use with TRANLOGOPTIONS parameter, but recently I’d tried one of the tuning parameter PERFORMANCEPROFILE with our medium intensity workload. It can be set to HIGH and MEDIUM (default). It helps achieve better performance by grouping the parameters that affect performance. Once the performance profile is set up, this option automatically configures the applicable parameters, to achieve the desired throughput and latency.
We’d used this parameter in one of our 21c (21.7.0) GG installation with TRANLOGOPTIONS PERFORMANCEPROFILE HIGH, but immediately we’d started seeing spikes in extract’s latency. This was might be because it increases the Extract’s read buffer size to 8MB and the rule to purge the extract read buffer is either when the buffer is full or there is no ingress records for 0.2 seconds. Therefore, any uninterrupted workload with Extract consumption rate below 8MB will result in integrated Extract latency to exceed 1 second.
We’d checked with Oracle support and as a quick temporary solution they’d suggested to not use PERFORMANCEPROFILE parameter with HIGH flag, as the Extract consumption/intake rates are below specific value, such as ~15 MB/sec to get ~0.5 second extract response times. Hence we’d set the buffer size to one-third of the redo generation rate in MB/sec to get ~0.3 second maximum Extract latency. They also marked this as Bug 33772499 for GG 21c for July 2022 release.
Trace File Analyzer (TFA) is one of the popular Oracle provided toolkit that is very useful when you are dealing with any ORA error and want to pack incident files or when you want to monitor your database using oratop (now part of tfa), or want to run orachk (part of tfa) for a health check of your entire Oracle software stack for issue detection. It also comes with support tools like OSWatcher, process watcher and darda which we all are very much familiar. Today’s post is about one of my favorite TFA utilities option/flag ‘Summary’ which is not that commonly used, but is one of the cool things that we you can do with the TFA.
For this post I have used TFA version 21.2.0.0.0, I suggest to use latest version as it comes with bug fixes and lot of new features.
Feature : ‘summary‘ This flag/function of TFA provides you a detailed (high level) real time status summary of your full DB system. It collects stats of your CRS, ASM, ACFS, Database, Patching Info, Network, Listeners and Operating System. Once you execute ‘summary’ mode within TFA command line (TFACTL), it will take you to tfactl_summary mode or SUMMARY Command-Line Interface (CLI) from where you can select from multiple options to view component wise statistics. The best part is you can even save the report in the form of HTML page (using option q) which is more fancy and easy to read version, otherwise text based is the default version.
Alright lets get started, I am going to call the ‘summary’ option and will see what is there for us.
Now when all statistics summaries are collected, this will automatically take us to the SUMMARY Command-Line Interface (tfactl_summary>) where if we use ‘list’ option will bring the entire chart of options to choose from (see above all 11 different areas). Next I am going to select option 3 to get ASM_OVERVIEW from tfactl_summary CLI which when selected will further take you to another linked CLI that is tfactl_summary_asmoverview> which will further gives you details of your entire cluster, like in my case I have node machines as a part of this cluster.
So basically the flow will be something like this tfactl —> Use summary command and enter into tfactl_summary —> Choose option from the list using ‘list’ command and enter into option related CLI (i.e. tfactl_summary_asmoverview>) and choose option or node to view stats.
Please take a look below for better understanding.
You can even save your output into an HTML file which is great for a quick view and very easy to present and transfer too. The option to generate report into HTML format is by calling “summary -overview -html” command within TFACTL and use option ‘q’ to save reports from tfactl_summary> CLI. Let’s see how to do that.
[root@nodeabc ~]#
[root@nodeabc ~]# tfactl
WARNING - AHF Software is older than 180 days. Please consider upgrading AHF to the latest version using ahfctl upgrade.
tfactl> summary -overview -html
Executing Summary in Parallel on Following Nodes:
Node : nodeabc
Node : nodexyz
LOGFILE LOCATION : /u01/app/grid/oracle.ahf/data/repository/suptools/nodeabc/summary/root/20221012121321/log/summary_command_20221012121321_nodeabc_82905.log
Component Specific Summary collection :
- Collecting CRS details ... Done.
- Collecting ASM details ... Done.
- Collecting ACFS details ... Done.
- Collecting DATABASE details ... Done.
- Collecting PATCH details ... Done.
- Collecting LISTENER details ... Done.
- Collecting NETWORK details ... Done.
- Collecting OS details ... Done.
- Collecting TFA details ... Done.
- Collecting SUMMARY details ... Done.
Remote Summary Data Collection : In-Progress - Please wait ...
- Data Collection From Node - nodexyz .. Done.
Prepare Clusterwide Summary Overview ... Done
cluster_status_summary
COMPONENT STATUS DETAILS
+-----------+---------+---------------------------------------------------------------------------------------------------+
CRS PROBLEM .-----------------------------------------------.
| CRS_SERVER_STATUS : ONLINE |
| CRS_STATE : ONLINE |
| CRS_INTEGRITY_CHECK : FAIL |
| CRS_RESOURCE_STATUS : OFFLINE Resources Found |
'-----------------------------------------------'
ASM PROBLEM .-------------------------------------------------------.
| ASM_DISK_SIZE_STATUS : WARNING - Available Size < 20% |
| ASM_BLOCK_STATUS : PASS |
| ASM_CHAIN_STATUS : PASS |
| ASM_INCIDENTS : PASS |
| ASM_PROBLEMS : PASS |
'-------------------------------------------------------'
ACFS OFFLINE .-----------------------.
| ACFS_STATUS : OFFLINE |
'-----------------------'
DATABASE PROBLEM .-----------------------------------------------------------------------------------------------.
| ORACLE_HOME_DETAILS | ORACLE_HOME_NAME |
+----------------------------------------------------------------------------+------------------+
| .------------------------------------------------------------------------. | OraDB19000_home1 |
| | PROBLEMS | INCIDENTS | DB_BLOCKS | DATABASE_NAME | STATUS | DB_CHAINS | | |
| +----------+-----------+-----------+---------------+---------+-----------+ | |
| | PROBLEM | PROBLEM | PASS | FATDBA1 | PROBLEM | PROBLEM | | |
| '----------+-----------+-----------+---------------+---------+-----------' | |
'----------------------------------------------------------------------------+------------------'
PATCH OK .----------------------------------------------.
| CRS_PATCH_CONSISTENCY_ACROSS_NODES : OK |
| DATABASE_PATCH_CONSISTENCY_ACROSS_NODES : OK |
'----------------------------------------------'
LISTENER OK .-----------------------.
| LISTNER_STATUS : OK |
'-----------------------'
NETWORK OK .---------------------------.
| CLUSTER_NETWORK_STATUS : |
'---------------------------'
OS OK .-----------------------.
| MEM_USAGE_STATUS : OK |
'-----------------------'
TFA OK .----------------------.
| TFA_STATUS : RUNNING |
'----------------------'
SUMMARY OK .------------------------------------.
| SUMMARY_EXECUTION_TIME : 0H:1M:44S |
'------------------------------------'
+-----------+---------+---------------------------------------------------------------------------------------------------+
### Entering in to SUMMARY Command-Line Interface ###
tfactl_summary>list
Components : Select Component - select [component_number|component_name]
1 => overview
2 => crs_overview
3 => asm_overview
4 => acfs_overview
5 => database_overview
6 => patch_overview
7 => listener_overview
8 => network_overview
9 => os_overview
10 => tfa_overview
11 => summary_overview
tfactl_summary>q
### Exited From SUMMARY Command-Line Interface ###
--------------------------------------------------------------------
REPOSITORY : /u01/app/grid/oracle.ahf/data/repository/suptools/nodeabc/summary/root/20221012121321/nodeabc
HTML REPORT : <REPOSITORY>/report/Consolidated_Summary_Report_20221012121321.html
--------------------------------------------------------------------
tfactl>
This is how the HTML report looks like, the summary TFA report.
Todays post is a quick one about the difference that I have noticed in one of the extended execution plan section ‘Query block registry‘ between Oracle 19c (19.8) and Oracle 21c (21.3). I am not going to explain about query blocks etc. here as I’ve already made few blog posts on those topics in the past, this one is about the difference that you will observe between two said database versions for QBR section in execution plans.
First I am going to use the option/flag ‘qbregistry‘ (for Query block registry info) in Oracle database version 19.16, and next will repeat same steps in Oracle 21.3. Query block registy information can also be collect from the 10053 optimizer traces, but I always notice that one’s there in CBO traces are more repetitive that what you see as a concise version through execution plans with ‘qbregistry‘ option.
So, I have already set the playground, for testing purpose, created two sample tables and have written two outer join queries. One for each table. Then combining the results of these using union all.
Above ‘Query Block Registry’ XML translates to something like this SET$1 NULL_HALIAS|SET$1 SEL$1 BRICKS|SEL$1|TOYS|SEL$1 SEL$2 BRICKS|SEL$2|TOYS|SEL$2
Considering we have a two SELECT statements, one for each table, internally optimizer has created two query blocks SEL$1 and SEL$2, one for each of the select. Here its using a hint alias name ‘NULL_HALIAS‘, and points to both of the two SELECT statements used in the original query.
Next, lets execute the same statement in Oracle 21c (21.3.0) version and see the difference in QBR section.
--
-- In Oracle 21.3 Database
--
-- Skipping few sections to have more clarity about discussed topic
SQL*Plus: Release 21.0.0.0.0 - Production on Sat Oct 8 23:57:12 2022
Version 21.3.0.0.0
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'+alias +outline +qbregistry'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 731550672
...
.....
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1
2 - SEL$1
3 - SEL$1 / "TOYS"@"SEL$1"
4 - SEL$1 / "BRICKS"@"SEL$1"
5 - SEL$2
7 - SEL$2 / "BRICKS"@"SEL$2"
8 - SEL$2 / "TOYS"@"SEL$2"
Outline Data
-------------
......
Predicate Information (identified by operation id):
---------------------------------------------------
.....
Query Block Registry:
---------------------
SEL$1 (PARSER) [FINAL]
SEL$2 (PARSER) [FINAL]
SET$1 (PARSER) [FINAL]
SQL>
Here with 21c (21.3), first thing is its no more coming in the form of an XML, The curious part out of the entire output is the ‘Query Block Registry‘ where the [FINAL] is the transformation that is chosen by the CBO. This assures that time was used on a query block which has been selected for an optimal plan.
Recently I was doing an analysis on a slothful database where I had to generate multiple AWR reports to cover almost 12 hours of the problem period. I usually go with few of the SQL scripts or AWR generator tools for my trend analysis, but I was little lucky in making unexpected and fortunate discovery on Metalink, found a cool Doc ID 2857522.1 which explains about an Oracle provided perl script to generate AWR report time series in any given timeframe. The script works with RDBMS 12.1 and later.
The script generates all the AWR reports with [begin snapid:end snapid] equal to [n,n+1] with n falling into a given time interval. The script is very handy and interactive, gives you option to choose between standard Text or HTML format, report type (RAC or Non-RAC type reports). You can call it in both command line or interactive mode.
This script generates a timeseries of awr report for a given database in a awr repository. It connects to database via oracle sid on the db server or via tnsalias. To run the script just execute B . To connect via oracle sid do not specify username password and tnsalias. In order to have sixty minutes time-series reports do not specify frequency if awr snapshot is executed every 60 minutes (default awr setting), set frequency 2 if awr snapshot is executed every 30 mins , 4 if awr snapshot is executed every 15 mins and so on. Leaving frequency blank will generate a series based on the awr snapshot frequency. All reports generated during execution will be saved in dumpdir, if the directory does not exist it will be automatically created.
By default awr reports will be generated in text format. To generate pluggable database awr reports connect to database by specifying username , password and tns alias of the pluggable db.
-- Call perl script awrdmp.pl to run the AWR extraction.
[oracle@fatdba ~]$ perl ./awrdmp.pl
Enter usrname: -
Enter password: -
Enter tnsalias: -
Enter frequency -
Enter mode
(text/html) - text
CONNECTED AS SYSDBA
RDBMS VERSION: 19.0.0.0.0
---- ---------- ------- ---------- ---------- -------------------- ------
NUM DBID INSTID DBNAME INSTID MACHINE CONTID
---- ---------- ------- ---------- ---------- -------------------- ------
0 2511273110 2 DIXITD fat2 racnode2 0
1 2511273110 1 DIXITD fat1 racnode1 0
Enter database num: [0,1] -: 0
0 2511273110 2 DIXITD fat2 racnode2
RANGE AVAILABLE IN REPOSITORY FOR DBID 2511273110 INST 2:
------------------------------------------------------
[191 04-SEP-22 07.58.34.180 AM : 420 05-SEP-22 06.28.18.307 AM]
Enter the minimum date interval (DD/MM/YYYY) -: 04/09/2022
Enter the maximum date interval (DD/MM/YYYY) -: 05/09/2022
GENERATING FILES
[ 12 %] writing file : report_2_DIXITD_191_192.text
--
--
--
-- In case if want to execute it in command line format.
perl awrdmp.pl --batch --freq 1 --instid 1 --dbid 2511273110 --dbn DIXITD --begin 04/09/2022 --end 05/09/2022 --rac --mode html
--
--
--
-- Output under dumpdir directory.
ls -ltr ./dumpdir
[...]
-rw-r--r-- 1 oracle oinstall 145147 Sep 05 14:16 report_1_DIXITD_196_197.text
-rw-r--r-- 1 oracle oinstall 159775 Sep 05 14:16 report_1_DIXITD_197_198.text
-rw-r--r-- 1 oracle oinstall 157100 Sep 05 14:16 report_1_DIXITD_198_199.text
-rw-r--r-- 1 oracle oinstall 148216 Sep 05 14:16 report_1_DIXITD_199_200.text
-rw-r--r-- 1 oracle oinstall 144003 Sep 05 14:16 report_1_DIXITD_200_201.text
-rw-r--r-- 1 oracle oinstall 146216 Sep 05 14:16 report_1_DIXITD_201_202.text
Tales From A Lazy Fat DBA 