PostgreSQL 13 pg_stat_statements

Overview

Today, we will discuss, how to configure pg_stat_statements in PostgreSQL 13 for Performance improvement strategies

The pg_stat_statements module provides a means for tracking the planning and execution statistics of all SQL statements executed by a server. The module must be loaded by adding pg_stat_statements to shared_preload_libraries in PostgreSQL. conf, because it requires additional shared memory.

Let's start

We will run below SQL Query for pg_stat_statements findings.

SQL Query (Make sure that you have opened and connected with your database using PgAdmin 4 Tool)

SQL

SELECT * FROM pg_stat_statements ORDER BY Total_time DESC;

Error Message

As we can see, it's generating an Error message with the description "ERROR: relation "pg_stat_statements" does not exist at character 17

How to resolve this Error

First of all, we have to install an Extension for pg_stat_statements for further work, so we will type the below SQL command in PgAdmin 4 Query Editor

CREATE Extension pg_stat_statements;

Now, as we can see the problem is not resolved yet, so after digging out for finding the root cause

Following PostgreSQL.conf entries are required to be added for proper functionality

Open PostgreSQL.conf and add these lines

# Configuration for pg_stat_statements

shared_preload_libraries = 'pg_stat_statements'

#compute_query_id = on

pg_stat_statements.max = 10000

pg_stat_statements.track = all

Restart PostgreSQL services from Services.msc

Now the problem is still there, what is exactly happening with this Error message.

Looking into Log File

After reading PostgreSQL documentation, i found these lines which are explaining clearly about this error message

pg_stat_statements for Postgres 13 does not contain: total_time, min_time, max_time, mean_time, stddev_time columns

https://www.postgresql.org/docs/13/pgstatstatements.html

Modify the above Query/SQL statement

SQL Query

SELECT t2.rolname, t3.datname, queryid, calls
, ( total_plan_time + total_exec_time ) / 1000 as total_time_seconds
, ( min_plan_time + min_exec_time ) / 1000 as min_time_seconds, ( max_plan_time + max_exec_time ) / 1000 as max_time_seconds
,( mean_plan_time + mean_exec_time ) / 1000 as mean_time_seconds
,( stddev_plan_time + stddev_exec_time ) / 1000 as stddev_time_seconds
, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written
, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read
, temp_blks_written, blk_read_time / 1000 as blk_read_time_seconds, blk_write_time / 1000 as blk_write_time_seconds
FROM pg_stat_statements t1 JOIN pg_roles t2 ON (t1.userid=t2.oid)
JOIN pg_database t3 ON (t1.dbid=t3.oid) WHERE t2.rolname != 'rdsadmin'
AND queryid IS NOT NULL;

Conclusion

So in this article, we have learned how to configure pg_stat_statements in PostgreSQL 13.

Useful Links

https://github.com/prometheus-community/postgres_exporter/issues/541

https://stackoverflow.com/questions/31021174/pg-stat-statements-enabled-but-the-table-does-not-exist

https://www.postgresql.org/docs/current/pgstatstatements.html

MySQL table row count information_schema

Overview

Diagram 1.1

Finding Row count with Database Tables

Second Part of the attached image

Conclusion

How to apply Oracle Hrglobal Legislative Patch

HRMS Global Steps

• Set Apps environment and enable Maintenance Mode.

                . ./apps/apps_st/appl/TEST_paytest.en

• Enable Maintenance Mode using adadmin command

              Choose 5 and 1 Options Enable Maintenance Mode and finally press Enter Key twice       

• Download Unzip and Apply Patch p11072961_R12.FND.B_R12_LINUX.zip

• Download Unzip and Apply Patch p16000686_R12.HR_PF.B_R12_LINUX.zip 

• Run Data Install

• Select Options 1I (I stands for Install)

13c OMS Schedule Backup

Overview

Let's configure Oracle 13c OMS that is running with the following details:-

Operating System        Oracle Linux 6.10 (el6uek.x86_64)

Database SID               testdb

Database Version         Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Oracle 13c OMS Ver   Oracle Enterprise Manager Cloud Control 13c Release 3

Following is the script for taking weekly cold backup for 13c OMS Server in a Linux environment

Step 1

Create a script with cold.sh as per my sample script

Script

echo

echo

date

echo

echo "Shutting Down OMS Services of 13c Monitoring Cloud Control for Cold Backup ......."

echo

echo

echo

export ORACLE_HOME=/u01/db/dbhome_1

export ORACLE_SID=testdb

export OMS_HOME=/u01/cloud/middleware

export AGENT_HOME=/u01/cloud/agent/agent_13.3.0.0.0

$OMS_HOME/bin/emctl stop oms -all

/u01/cloud/agent/agent_13.3.0.0.0/bin/emctl stop agent

echo "Shutting Down Database and Listener Services of 13c Monitoring Cloud Control for Cold Backup ......."

echo

echo

echo

#$ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF1

#shutdown immediate;

#EOF1

#sqlplus "/ as sysdba" <<EOF1

#shutdown immediate;

#EOF1

#$ORACLE_HOME/bin/lsnrctl stop

date

echo

sleep 10;

date

echo "All Services are down for 13c Monitoring Cloud Control Cold Backup ......."

echo

echo

echo

date

echo "Starting Tar Backup for 13c Monitoring Cloud Control with Database"

echo

echo

echo

AS1=Oracle13c-OMS-TAR_$(date +%d-%m-%Y).tgz

#AS2=Oracle13c-DB-TAR_$(date +%d-%m-%Y).tgz

#AS3=Oracle13c-DBInventory-TAR_$(date +%d-%m-%Y).tgz

echo

/bin/tar -cvzf /u01/backup/cold/$AS1 /u01/cloud

#/bin/tar -cvzf /u01/backup/cold/$AS2 /u01/db

#/bin/tar -cvzf /u01/backup/cold/$AS3 /u01/oraInventory

echo

echo

date

echo

echo "Backup Successfully Completed for 13c Monitoring Cloud Control"

echo "Starting 13c Monitoring Cloud Control Services ......."

$ORACLE_HOME/bin/lsnrctl start

$ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF1

startup;

EOF1

$OMS_HOME/bin/emctl start oms

/u01/cloud/agent/agent_13.3.0.0.0/bin/emctl start agent

date

echo "All Services are Up after 13c Monitoring Cloud Control Cold Backup Completion ......."

echo

echo

Step2

Create a crontab schedule job for running the above script on a weekly basis

01 00 * * 6 /home/oracle/backup/scripts/cold.sh > /home/oracle/backup/logs/cold.log

Conclusion

This script will run at 00:01 on Saturday Night, you may change it accordingly as per your desired time.

Migrate WebLogic 12c steps in Linux

Overview

Here I am going to share with you how we can rename WebLogic 11g Instance for cloning purposes or as per any other requirement.

Example

We have 2 x Linux Servers

Server 1 = prodserver.domain.com

Server 2 = testserver.domain.com

Step 1

Edit file vi /oracle/domains/FRDomain/config/fmwconfig/servers/AdminServer/applications/em/META-INF/emoms.properties

And change

1.       oracle.sysman.emSDK.svlt.ConsoleServerHost=testserver.domain.com

2.       oracle.sysman.emSDK.svlt.ConsoleServerName=testserver.domain.com:7001_Management_Service

Step 2

Open file /oracle/domains/FRDomain/servers/WLS_REPORTS/tmp/_WL_user/reports_11.1.2/uxabaw/configuration/rwservlet.properties and change host name entry.

Step 3

Edit Start and Stop Web logic scripts

Open file /oracle/domains/FRDomain/bin/startWebLogic.sh and edit host name entry.

Open file /oracle/domains/FRDomain/bin/stopWebLogic.sh and edit host name entry.

Open file /oracle/domains/FRDomain/bin/startManagedWebLogic.sh and edit host name entry.

Open file /oracle/domains/FRDomain/bin/stopManagedWebLogic.sh and change host name entry.

Step 4

Verify both scripts

1)      Start Weblogic using script and Form and Report Services.

2)      Stop Weblogic using script and Form and Report Services.

Conclusion

In this way, we can make a Test Server for Weblogic Forms and Reports environment.

Microsoft SQL Server and PostgreSQL have some similar data types while others are not. Before migration, it is important to keep in mind the right types representing:

 SQL Query to get Row Count For all Tables in SQL Server

 

Postgresql Count Rows in All Tables