AI Engineer - My Proof-of-Concept RAG Application

A Retrieval-Augmented Generation (RAG) application is an AI framework that enhances Large Language Models (LLMs) by integrating a targeted information retrieval mechanism. This allows LLMs to access and query domain-specific, external data beyond their pre-trained knowledge base, leading to more accurate, up-to-date, and contextually grounded responses. 

To demonstrate this concept, I developed a command-line Python utility named llm_chat.py. The application acts as a specialized AI Assistant for Database Administrators (DBAs). It takes a user’s plain-text query, converts it into a vector embedding, and queries a PostgreSQL database powered by the pgvector extension to pull the top 10 most relevant historical DBA kowledge base event  posts. It then passes both the question and the retrieved contextual data to Gemini to generate two distinct responses: a step-by-step instructional answer and an executive narrative summary.

How the RAG Architecture Works 

Standard Large Language Models generate answers using only the general knowledge present in their training data. This program implements true RAG by breaking the response pipeline into three distinct phases:

1. Retrieval: Instead of querying the model directly, the application first retrieves exact, domain-specific knowledge (matching historical event titles and IDs) from a PostgreSQL vector database using cosine distance searching (<=>).
2. Augmentation: It augments the prompt by inserting those retrieved database logs directly into the CONTEXT block of the system prompt. 
3. Generation: Finally, the LLM generates an answer strictly grounded in your private, real-world DBA data - mitigating generic answers and preventing hallucinations. 

Example Outputs 

Example 1: Software Installation Query

(.venv) C:\Users\dsun\Denis_files\python_proj\ai_apps\dbaets_pgvector>python llm_chat.py
Ask a question to your expert DBA AI Assistant: (ctrl-c to exit)

how to install Oracle 19c software?
Your query: how to install Oracle 19c software?

========== Your DBA AI will answer your questions based on the following context ==================
event_id:   3241  score:0.140  title: Install Oracle Database 19c software on Linux 9
event_id:   2757  score:0.266  title: Solaris 11.4 install oracle-database-preinstall-19c 
event_id:   2774  score:0.288  title: New Oracle 19c - Grid Install Error / Remediation  on Redhat Linux 8.4
event_id:   2762  score:0.294  title: Upgrade a 12.2 RAC database to 19.3
event_id:   2765  score:0.315  title: Oracle GI 19.3 on Linux 8 installation issue and resolution
event_id:   2771  score:0.333  title: Cloning An Oracle 19c Oracle Home through gold image
event_id:   2763  score:0.347  title: Oracle Database 19.0.0.0.0 is certified on Linux x86-64 Red Hat Enterprise Linux 8 Update 0+
event_id:   2596  score:0.351  title: oracle 19c multitenant license notes
event_id:   2634  score:0.368  title: check required linux os packages for Oracle software
event_id:   2866  score:0.374  title: clone a 19c database using rman duplicate from active database 


======= How to do things as a DBA =========================================================
Question: how to install Oracle 19c software?
Answer:
1. **OS Prep:** Use `oracle-database-preinstall-19c` package (if available for your OS) to install required packages. Manually install if needed.
2. **Download:** Download Oracle 19c software from Oracle.
3. **Extract:** Unzip the downloaded software.
4. **Run Installer:** Execute `runInstaller` from the extracted directory.
5. **Follow Prompts:** Choose installation options. For RAC, install Grid Infrastructure first.
6. **Root Scripts:** Run scripts as root when prompted.
7. **Verify:** Check logs for errors after installation.

   Consider Gold Image cloning for faster deployments.


======= Your DBA AI is giving summarization ==================================================
Question: how to install Oracle 19c software?
Answer:
DBA tasks involve installing Oracle 19c on Linux/Solaris, addressing installation errors, upgrading RAC databases, cloning Oracle homes/databases, verifying OS package requirements, and working with multitenant licenses.

Example 2: Performance Tuning Query

(.venv) C:\Users\dsun\Denis_files\python_proj\ai_apps\dbaets_pgvector>python llm_chat.py
Ask a question to your expert DBA AI Assistant: (ctrl-c to exit)

how to add hint to tune sql?
Your query: how to add hint to tune sql?

========== Your DBA AI will answer your questions based on the following context ==================
event_id:   1775  score:0.286  title: postgresql pg_hint_plan -- controls execution plan with hinting phrases in comment of special form
event_id:   3183  score:0.287  title: General advice for developer to tune sql
event_id:    216  score:0.315  title: Suggest tuning a query by adding hint to skip a particular index - NO_INDEX(smc SMC_IDX_RELEASE_DATE)
event_id:   3290  score:0.367  title: mtasCronOptPurge_request.sh - tuning a delete statement adding hints
event_id:   1908  score:0.369  title: STRAIGHT_JOIN sql tuning
event_id:   3028  score:0.371  title: Run SQL tuning advisor for 1r8xa9fumhq2d
event_id:   2093  score:0.377  title: My SQL Tuning Primer
event_id:    248  score:0.392  title: about query 4xx9vuzyj1m71 - RE Dev SQL tuning opportunity- RE Introscope Alert Message
event_id:    245  score:0.394  title: SQL -2rg6vs7y2kr64 - takes long time - fixed by restoring old stats to generate a better [execution plan] [sql tuning]
event_id:   3303  score:0.396  title: spfprdsc - 11trc93bakbv0 sql tuning


======= How to do things as a DBA =========================================================
Question: how to add hint to tune sql?
Answer:
To tune SQL using hints (like `NO_INDEX` or `STRAIGHT_JOIN`), embed them within a special comment block in your SQL:

/*+ NO_INDEX(table_alias index_name) */

For example:

SELECT /*+ NO_INDEX(smc SMC_IDX_RELEASE_DATE) */ * FROM smc WHERE ...

This tells the optimizer to skip `SMC_IDX_RELEASE_DATE` on table `smc`. Remember to test thoroughly! Consider also using SQL Tuning Advisor for systematic optimization.


======= Your DBA AI is giving summarization ==================================================
Question: how to add hint to tune sql?
Answer:
The DBA tuned SQL queries using hints within comments. Examples include skipping indexes (NO_INDEX) and forcing join order (STRAIGHT_JOIN). The DBA also used SQL Tuning Advisor and restored old statistics to improve execution plans. Several specific queries were tuned.

Prompt Engineering & Future Enhancements 

Notice how applying two different prompt templates to the exact same retrieved context yields dramatically different outputs tailored to specific roles: 

Instructional Prompt (Prompt 1):

Designed to act as an expert senior mentor explaining practical, step-by-step procedures to junior DBAs.

prompt = f"""
INSTRUCTIONS:
You are an expert Oracle database administrator, based on the context provided, you can explain how to do things to any junior to mid-level DBAs.
Please limit your answer within 2000 characters

CONTEXT:
{context}

QUESTION:
{query}

ANSWER:
"""

Summarization Prompt (Prompt 2): 

Designed to synthesize historical enterprise activities into a high-level operational narrative.

prompt = f"""
INSTRUCTIONS:
Based on the context provided, you can summarize and give a narrative about what are the tasks or activities DBA performed.
Please limit your answer within 2000 characters

CONTEXT:
{context}

QUESTION:
{query}

ANSWER:
"""

Currently, this proof of concept extracts embeddings exclusively from Event Titles in the DBAETS knowledge base. In future iterations, I plan to chunk and generate vector embeddings from both the Event Titles and full Event Descriptions/Body Content. Combining title metadata with rich document content will significantly increase context granularity, enabling Gemini to produce even deeper, more accurate diagnostic guidance. 

Summary 

This proof of concept demonstrates how easily a local enterprise dataset - such as DBAETS event posts - can be transformed into an intelligent operational assistant using RAG architecture. By pairing vector similarity search in PostgreSQL (pgvector) with the reasoning capabilities of Gemini, llm_chat.py bridges the gap between static enterprise documentation and interactive, multi-purpose guidance. Whether generating actionable technical steps for junior staff or summarizing complex administrative histories for management, RAG transforms raw relational data into an indispensable knowledge retrieval tool.

AI Engineer - Embedding Generation from Gemini API and Storing in PostgreSQL

In this post, I first demonstrate that sample Python programs have been developed and tested to do the following two tasks: 

1. Create embeddings from sample source data using the Germini API 

2. Use PostgreSQL as a vector database and store embeddings data in it using pgvector. 

Then I demonstrate similarity search in PostgreSQL with pgvector. 

1. Source data preparation: 

 From the DBAETS application `events` table in the PostgreSQL, dumping ` event_id` and `title` data into a csv file. Total 2021 records.

 \copy ( select event_id, title from events order by 1 ) to 'events_title.csv' with csv 

 The `events` table stores data about database administration events, each entry represents a certain kind of DBA task or activity. The `title` of the `event` table is trivial, but the purpose of using it as the source of content is to make me familiar with the embedding creation and storing process. The end goal is to use the `description` column of the `events` table, which contains a detailed description of the event, therefore providing the useful domain knowledge for DBAs.

 2. Embedding generation 

The python program emb_to_csv.py is developed to generate embedding from source csv file and generate a csv file including embeddings of `title`. Gemini API is used with model: ‘text-embedding-004’, which generates vectors with 768 dimensions .

==============  screenshot run the program ==============
(.venv) C:\Users\dsun\Denis_files\python_proj\ai_apps\dbaets_pgvector>python emb_to_csv_2.py
   event_id                                              title
0         3  PREP CALL for the 2pm actual call to discuss r...
1         4                   Query Certification / 04-11-2012
2         5        [DemoApp] [Migration] -  RD175935 - Tonight
3         6  RE: myhostpd3:testprod3 ALERT_LOG Error: [SR 3-...
4         7                    space add in mrosple/mxdsacdd02
total batches : 22 batch size: 100 total records: 2120
process batch no: 0
loop: 0 event_id: 3
loop: 10 event_id: 13
loop: 20 event_id: 24
loop: 30 event_id: 34
loop: 40 event_id: 44
loop: 50 event_id: 54
loop: 60 event_id: 64
loop: 70 event_id: 74
loop: 80 event_id: 84
loop: 90 event_id: 94
process batch no: 1
loop: 100 event_id: 104
loop: 110 event_id: 114
loop: 120 event_id: 124
loop: 130 event_id: 134
loop: 140 event_id: 144
loop: 150 event_id: 155
loop: 160 event_id: 165
loop: 170 event_id: 175
loop: 180 event_id: 185
loop: 190 event_id: 195
process batch no: 2
loop: 200 event_id: 205
…
(ommitting ..)

I intentionally used some small batch size and sleep 200 seconds between batches to avoid Gemini API free tier rate limit, if I don't do that, I could end up with errors "RATE_LIMIT_EXCEEDED". 

3. Embedding loacding 

The python program emb_csv_to_pgvector.py is developed to load the embeddings from csv to a PostgreSQL table. 

4. Similarity search using SQL 

 The following query calculates vector distances using the <#> negative inner product operator to compare event titles against a target event (event_id = 2690). It retrieves the target event's self-similarity score alongside the top 10 most similar distinct events, returning their event IDs, titles, and similarity scores in a single combined result.

etsdb=> -- top event titles that are mostly similar to first title
etsdb=> (
etsdb(> select event_id, title,  embedding <#> embedding as similarity
etsdb(> from event_title_embeddings
etsdb(> where event_id=2690
etsdb(> )
etsdb-> union all
etsdb-> (
etsdb(> select t.event_id , t.title , (t.embedding <#> i.embedding ) as similarity
etsdb(> from event_title_embeddings t join event_title_embeddings i
etsdb(> on t.event_id != i.event_id
etsdb(> and i.event_id=2690
etsdb(> order by similarity
etsdb(> limit 10
etsdb(> )
etsdb-> ;
 event_id |                              title                               |     similarity
----------+------------------------------------------------------------------+---------------------
     2690 | Master note for operation support - mydb2                        | -0.9999982118606567
     2431 | Master note for operation support - mydb1 BVC                    | -0.8375328183174133
     2629 | Master note for operation support - omrtppdb OEM repostory       | -0.8193813562393188
     2444 | Master note for operation support - iexdb                        | -0.7917341589927673
     2450 | mydb4 database support master notes                              | -0.7660208344459534
     2459 | master note for operation support - mydb12sc APP1                | -0.766010582447052
     2876 | Operations Master notes for EV6V- mmypos on dbhost41/42/43       | -0.6405361294746399
     2953 | Master Notes -APP2                                               | -0.6208126544952393
     2937 | SCM master notes                                                 | -0.6061583161354065
     2275 | mydb1 database related doc                                       | -0.6051017045974731
     2879 | Master Notes - APP1 - mydb3                                      | -0.6019692420959473
(11 rows)

In summary, I successfully established an end-to-end embedding pipeline using the Gemini text-embedding-004 model and PostgreSQL with pgvector. By generating 768-dimensional vector embeddings from DBA event titles and querying them using inner product distance (<#>), the setup demonstrated accurate semantic similarity retrieval across database task records. This foundational work sets the stage for scaling up to full event descriptions and implementing a robust domain-specific vector search system.

AI Engineer - Starting My Journey

Recently, I have come across a youtube video that is quite inspiring. It explains what AI Engineers do and how to develop AI applications in PostgreSQL. It made me believe I can develop something useful as if I am an AI Engineer! At least I know Python and PostgreSQL reasonably well.

One of the functionalities of the DBAETS application I developed is it is a knowledge base for DBAs. From time to time, I post notes there about database related activities,  such as troubleshooting, performance tuning and so on and so forth. My ambition is to develop a Q&A RAG application for DBAs to ask questions and get answers utilizing the knowledge-base data in the DBAETS.

What is RAG?

"In the field of AI, RAG stands for Retrieval-Augmented Generation. It's a framework that combines the strengths of traditional information retrieval systems with generative large language models (LLMs). RAG enhances AI responses by retrieving relevant information from external sources, such as databases or web pages, and using that information to augment the LLM's generation process. This approach helps create more accurate, up-to-date, and relevant text outputs. "

Let the journey begin! And I will post any meaningful progress along the way.

As of today, I have achieved three things as described in the following:

•  I turned my PostgreSQL db as a vector database by installing the pgvector extension

– installation of pgvector

pgdbhost003.mycompany.com, as root

cd /u01/stage

git clone --branch v0.8.0 https://github.com/pgvector/pgvector.git

cd pgvector

make

make install 

– enable the extension

postgres=# \c etsdb

You are now connected to database "etsdb" as user "postgres".

etsdb=# CREATE EXTENSION vector;

CREATE EXTENSION

etsdb=#

– create a table with `vector` data type

etsdb=# create table items(id bigserial primary key, embedding vector(3));

CREATE TABLE

– insert 

etsdb=# INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

INSERT 0 2

– query

etsdb=# SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

 id | embedding

----+-----------

  1 | [1,2,3]

  2 | [4,5,6]

As you can see I have successfully turned my PostgreSQL database into a vector store.

• I am able to call Gemini API

In the following example script, I ask a LLM model what an AI Engineer do through an API

(venv311_prac) C:\Users\dsun\Denis_files\python_proj\ragtest\ragtest>type test_googleapi.py
# ref : https://github.com/google-gemini/cookbook
# ref : https://github.com/googleapis/python-genai
#
# RAG:
#
# https://medium.com/@saurabhgssingh/understanding-rag-building-a-rag-system-from-scratch-with-gemini-api-b11ad9fc1bf7
#
#
# type of services
#   ** Gemini Developer API
#   ** Vertex AI
# pip install google-genai
#
import os
from google import genai
from google.genai import types
client = genai.Client(api_key=os.getenv("GEMINI_API_KEY"))
response = client.models.generate_content(
    model="gemini-2.0-flash-001",
    contents="Explain what an AI Enigeer do?",
)
print(response.text)

Run the program:

(venv311_prac) C:\Users\dsun\Denis_files\python_proj\ragtest\ragtest>python test_googleapi.py

An AI Engineer is a professional who focuses on **applying artificial intelligence (AI) and machine learning (ML) models to solve real-world problems.** They bridge the gap between theoretical AI research and practical implementation. Think of them as the builders and architects of AI systems.

Here's a breakdown of what an AI Engineer typically does:

**1. Data Handling and Preparation:**

*   **Data Collection:** Gathering data from various sources, including databases, APIs, and other data stores.

*   **Data Cleaning and Preprocessing:**  Cleaning messy data (handling missing values, outliers, and inconsistencies) to make it suitable for training ML models. This is a crucial step as the quality of the data directly impacts the model's performance.

*   **Data Transformation:** Converting data into a format that can be easily processed by ML algorithms. This might involve feature scaling, encoding categorical variables, and creating new features.

*   **Data Augmentation:** Generating synthetic data to increase the size and diversity of the training dataset, which can improve model robustness.

**2. Model Development and Training:**

*   **Model Selection:** Choosing the appropriate AI/ML model based on the problem, data characteristics, and desired outcome (e.g., regression, classification, clustering, or a specific deep learning architecture).

*   **Model Training:**  Using the prepared data to train the selected model. This involves tuning the model's parameters to optimize its performance.

*   **Model Evaluation:**  Assessing the trained model's performance using various metrics and techniques (e.g., accuracy, precision, recall, F1-score, ROC curves).

*   **Hyperparameter Tuning:** Optimizing the model's hyperparameters (parameters that are not learned during training) to improve its performance. This often involves using techniques like grid search or Bayesian optimization.

**3. Model Deployment and Maintenance:**

*   **Model Deployment:**  Putting the trained model into a production environment where it can be used to make predictions on new data. This could involve deploying the model to a cloud platform, a server, or an embedded device.

*   **Model Monitoring:** Tracking the model's performance in production to ensure it continues to perform as expected.

*   **Model Retraining:**  Retraining the model with new data to keep it up-to-date and prevent performance degradation (model drift).

*   **Infrastructure Management:** Managing the infrastructure required to run the AI system, including servers, databases, and cloud resources.

**4. Collaboration and Communication:**

*   **Collaboration:** Working closely with data scientists, software engineers, and other stakeholders to build and deploy AI solutions.

*   **Communication:**  Effectively communicating technical concepts and findings to both technical and non-technical audiences.

*   **Documentation:**  Creating and maintaining documentation for the AI system, including the model architecture, training process, and deployment instructions.

**Key Skills for AI Engineers:**

*   **Programming:** Strong proficiency in programming languages like Python (most common), Java, or C++.

*   **Machine Learning:**  A solid understanding of machine learning algorithms and techniques.

*   **Deep Learning:**  Experience with deep learning frameworks like TensorFlow, PyTorch, or Keras.

*   **Data Science Fundamentals:** Knowledge of data analysis, data visualization, and statistical modeling.

*   **Data Engineering:**  Skills in data wrangling, data cleaning, and data pipelining.

*   **Cloud Computing:**  Experience with cloud platforms like AWS, Azure, or Google Cloud.

*   **DevOps:** Familiarity with DevOps practices like continuous integration and continuous delivery (CI/CD).

*   **Software Engineering:**  Understanding of software development principles, design patterns, and testing methodologies.

*   **Problem-Solving:**  Strong analytical and problem-solving skills.

*   **Communication:**  Excellent communication and collaboration skills.

**In summary, an AI Engineer is a hands-on role that requires a blend of programming, machine learning, and software engineering skills. They are responsible for building, deploying, and maintaining AI systems that solve real-world problems.** They are not just theorists; they are builders who bring AI models to life.

•  I can generate embeddings:

(venv311_prac) C:\Users\dsun\Denis_files\python_proj\ragtest\ragtest>type gen_emb.py

from google import genai

import os

client = genai.Client(api_key=os.getenv("GEMINI_API_KEY"))

result = client.models.embed_content(

        model="gemini-embedding-exp-03-07",

        contents="What is the meaning of life?")

print(result.embeddings)

(venv311_prac) C:\Users\dsun\Denis_files\python_proj\ragtest\ragtest>python gen_emb.py

[ContentEmbedding(values=[-0.022372285, -0.004451784, 0.013473644, -0.053762246, -0.020569915, 0.011864573, 0.015185799, 0.006950965, 0.03180835, 0.007074574, 0.027503368, -0.00600613, -0.014889315, 0.03269886, 0.12054204, 0.019322146, 0.000517173, 0.0045754807, -0.00856155, -0.01532448, 0.015616342, -0.008661197, -0.017454486, 0.0099245, -0.015551475, 0.012284064, 0.020809751, -0.0037114064, 0.025106275, 0.008105811, 0.020252233, 0.0019548477, -0.010780675, 0.027334962, -0.017213175, -0.011735542, 0.009507163, -0.015499457, -0.013591795, 0.0138707, -0.022853972, -0.009638755, -0.0034423112, -0.018855078, 0.018475225, -0.010515843, 0.015031793, -0.042978574, -0.013993226, 0.007916359, -0.012274015, 0.011758872, -0.010251529, -0.15881006, 0.016281825, 0.0103672175, -0.006364179, -0.009997806, -0.025991082, -0.027687864, -0.008586312, -0.014933809, -0.007584574, -0.021427568, 0.008805106, -0.009369353, -0.02012641, 0.011695516, 0.0020037016, 0.012606018, -0.01629937, 0.015133599, -0.005364407, -0.013935832, 0.0065723164, 0.014414399, 0.016648233, -0.009122886, 0.0006896179, -0.0042646583, 0.0001541545, -0.01034019, -0.025387881, -0.01852225, -0.005335359, 0.013288918, -0.008168338, -0.005782173, 0.017077502, -0.0015941358, 0.027414756, 0.00015515718, 0.019269329, 0.026197737, -0.015433854, -0.016303977, -0.0048079626, -0.013850489, 0.018751703, 0.0037166988, -0.029702129, -0.018824589, 0.0037240938, -0.00461113, 0.0010804693, 0.0029576228, 0.020787966, 0.014922842, 0.010893234, -0.00839888, 0.016467841, -0.011249123, -0.014664661, -9.935649e-06, -0.016037786, -0.17255014, 0.011858983, -0.0148698045, -0.019271476, 0.02731455, 0.029812986, 0.0046130605, -0.012905329, 0.010795695, -0.0144493915, 0.0009820901, 0.02241333 .....  ]

In summary, I have successfully enabled vector capabilities in a PostgreSQL database and demonstrated the ability to call the Gemini API. This blog post serves as a starting point of my exploration toward developing practical AI solutions.

High-Concurrency Database Load Testing in Oracle Using Native PL/SQL

High-Concurrency Database Load Testing in Oracle Using Native PL/SQL

Building a Lightweight, In-Database Benchmarking Framework with DBMS_SQL and DBMS_SCHEDULER

Simulating high-concurrency database workloads often relies on external testing tools such as JMeter, HammerDB, or custom application drivers. While effective, external tools introduce network latency variations, connection pool overhead, and complex infrastructure setup.

When your objective is to evaluate pure database execution performance—such as engine efficiency, latch contention, or indexing strategies under heavy stress—executing the load directly inside the database engine provides the cleanest, most repeatable results.

This post details a pure PL/SQL methodology for conducting high-concurrency database load testing in Oracle using built-in packages.

Architectural Overview of the Methodology

The native load-testing framework consists of three core components:

1. The Workload Engine (DBMS_SQL): Executes targeted SQL statements inside a high-throughput loop, leveraging dynamic cursor parsing, variable binding, and randomized test data to prevent soft/hard parsing bottlenecks.
2. The Concurrency Orchestrator (DBMS_SCHEDULER): Spawns and manages asynchronous background worker threads, allowing effortless scaling across multiple CPU cores.
3. The Observability Engine (job_logs): Captures real-time metrics including execution start times, completion times, and total iterations per job thread.

Step 1: High-Efficiency Query Execution with DBMS_SQL

To measure true execution efficiency without incurring the overhead of PL/SQL static SQL context switching or hard parsing, we utilize the DBMS_SQL package.

By opening a dynamic cursor once, parsing the statement skeleton, and binding randomized search criteria during loop iterations, we isolate execution performance while mimicking realistic application behavior.

create or replace procedure run_sql_proc(p_iteration number, p_desc varchar2 default '') 

is 

  curid number; 

  sqltext varchar2(4000); 

  ret number; 

  l_counter number := 0; 

  l_mtn test.leadlist.mtn%type; 

  l_jobid number; 

begin

   -- Log execution start and obtain tracking ID

   insert into job_logs(job_id, executions, stime) 

   values (job_logs_seq.nextval, p_iteration, sysdate) 

   returning job_id into l_jobid;

   

   -- Define parameterized SQL statement

   sqltext := 'select /*test1*/ * from test.leadlist where mtn = :mtn';

   curid   := DBMS_SQL.OPEN_CURSOR; 

   

   loop 

       if l_counter > p_iteration then 

          exit; 

       end if;

       

       -- Generate random bind values to simulate realistic access patterns

       l_mtn := 'mtn' || to_char(round(10000 * dbms_random.value())); 

|---|

       DBMS_SQL.PARSE(curid, sqltext, DBMS_SQL.NATIVE); 

       DBMS_SQL.BIND_VARIABLE(curid, 'mtn', l_mtn); 

       ret := DBMS_SQL.EXECUTE_and_fetch(curid); 

       

       l_counter := l_counter + 1; 

  end loop; 

 

  dbms_sql.close_cursor(curid); 

 

  -- Log execution completion

  update job_logs 

  set etime = sysdate, description = 'Done - ' || p_desc 

  where job_id = l_jobid; 

end; 

/

Key Technical Considerations in run_sql_proc:

• Bind Variable Injection (:mtn): Prevents library cache lock contention by avoiding unique unparameterized SQL strings.
• Random Data Generation (DBMS_RANDOM): Simulates dynamic multi-user key lookups across the target table dataset.
• Single-Fetch Optimization (EXECUTE_and_fetch): Executes and fetches index lookup results in a single call to minimize overhead.

Step 2: Programmatic Concurrency Control via DBMS_SCHEDULER

To simulate multi-user workload patterns without maintaining open client terminal sessions, we leverage DBMS_SCHEDULER. The following submission script accepts a substitution variable (&&p_thread) to instantiate an exact number of parallel background jobs.

declare 

  l_counter number := 0; 

  l_msg varchar2(200); 

  l_job_action varchar2(400); 

begin 

  loop 

     l_counter := l_counter + 1; 

     if l_counter > &&p_thread then 

        exit; 

     end if;

     l_msg := 'local2-thread-' || &&p_thread || '_' || to_char(l_counter); 

     l_job_action := 'begin run_sql_proc(1000000,''' || l_msg || ''' ); commit; end;';

     dbms_scheduler.create_job(

         job_name   => 'RUN_SQL_JOB_' || to_char(l_counter), 

         job_type   => 'PLSQL_BLOCK', 

         job_action => l_job_action, 

         enabled    => true, 

         auto_drop  => true, 

         comments   => 'run_sql_job'

     );

     

     -- Optionally pin job threads to specific RAC instances

     dbms_scheduler.set_attribute('RUN_SQL_JOB_' || to_char(l_counter), 'INSTANCE_ID', '1');

  end loop; 

end; 

/

undefine p_thread

Step 3: Parametric Scalability Testing Execution Model

To evaluate database scalability under stress, tests should be executed in geometric thread increments: 1, 2, 4, 8, 16, 32, 64, and 128 threads.

Each thread runs a designated workload loop (e.g., 1,000,000 iterations per job). By adjusting &&p_thread, performance engineers can pinpoint exact saturation thresholds, latch bottlenecks, and throughput limits.

Test Environment Workflow:

1. Truncate or initialize the telemetry logging table (job_logs).
2. Execute the scheduler submission script with p_thread = 1.
3. Wait for job completion and collect timing metrics.
4. Repeat for higher thread counts (2, 4, 8, 16, 32, 64, 128).

Step 4: Tracking Metrics with Custom Instrumentation

A key requirement for database benchmarking is accurate timing. By capturing execution timestamps directly inside the procedure surrounding the workload loop, network latency and client-side logging delays are completely eliminated.

Performance Metrics Captured

• Average Job Completion Time (Minutes): Indicates how scaling concurrency impacts single-thread latency.
• Queries Per Second (QPS): Total aggregate database throughput across all active concurrent worker threads.

$$QPS = \frac{\text{Total Iterations Across All Threads}}{\text{Max Elapsed Duration (Seconds)}}$$

---

Secondary Case Study: Evaluating Partitioned Index Performance

To demonstrate this methodology in practice, the framework was deployed on an Oracle test database   to benchmark local versus global partitioned index access paths.

Test Dataset Setup

• Table: TEST.LEADLIST (Daily interval-partitioned table)
• Volume: 140,000 rows across 1,682 partitions
• Access Pattern: Index lookup by MTN key (select * from test.leadlist where mtn = :mtn)

 CREATE TABLE "TEST"."LEADLIST"

   (    "LEADLISTID" VARCHAR2(20),

        "MTN" VARCHAR2(10),

        "CREATEDATE" DATE DEFAULT sysdate,

        "LASTMODFIEDUSER" VARCHAR2(20)

   ) PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"

  PARTITION BY RANGE ("CREATEDATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))

 (PARTITION "PART_MIN"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED

  PCTFREE 1 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ) ;

insert into test.leadlist

(leadlistid, mtn, createdate,lastmodfieduser)

select

'aa' || to_char(level) ,

'mtn' ||  to_char(round(10000 * dbms_random.value())),

sysdate - 1682 * dbms_random.value(),

'user123'

from dual

connect by level <=140000

;

create index test.leadlist_ix1 on test.leadlist(mtn) local parallel 8;

alter index test.leadlist_ix1 noparallel;

create index test.leadlist_ix1 on test.leadlist(mtn) parallel 4;

alter index test.leadlist_ix1 noparallel;

Test Results Data

 Fig. 1 Average Job completion time vs Number  of Threads

Fig. 2 QPS vs Number  of Threads

Concurrent Threads	Global Index Avg Time (min)	Local Index Avg Time (min)	Global Index Total QPS	Local Index Total QPS
1 Thread	1.3	6.9	~15,000	~3,000
8 Threads	1.3	6.7	~100,000	~20,000
32 Threads	1.8	7.2	~290,000	~75,000
64 Threads	2.7	7.7	~402,000 (Peak)	~138,000
128 Threads	5.4	10.0	~396,000	~212,000

Benchmark Results Analysis & Observations

1. Completion Time Scaling: At 64 concurrent threads, jobs running against the Global Index completed in an average of 2.7 minutes, compared to 7.7 minutes for jobs using the Local Index.
2. Throughput Saturation: The Global Index configuration reached peak performance at 64 concurrent threads (~402,000 QPS). Beyond 64 threads, throughput leveled off due to CPU resource saturation.
3. Index Access Efficiency: Because the query predicate did not include the partition key (CREATEDATE), the Local Index required checking 1,682 individual index partitions per lookup, causing higher logical read overhead compared to a single B-tree lookup in the Global Index.

Summary

By combining DBMS_SQL for efficient dynamic SQL execution and DBMS_SCHEDULER for concurrent background process orchestration, database engineers can build a powerful, zero-dependency load testing framework directly inside Oracle. This native methodology provides precise, isolated performance insights free from network noise or third-party tool overhead.

PostgreSQL - Sampling pg_stat_statements

Have you ever needed to identify the top queries executed in PostgreSQL over the last 30 seconds or 5 minutes? 

In Oracle, you can conveniently retrieve this information using an ASH report. In PostgreSQL, the pg_stat_statements view is analogous to Oracle's v$sqlarea, as it stores query execution statistics since the last reset. Because these statistics are cumulative, answering the question requires sampling pg_stat_statements twice—at the beginning and end of a chosen interval—and then calculating the delta metrics.

I developed a SQL script to implement this concept by leveraging PostgreSQL's temporary table feature. The script samples pg_stat_statements twice over a specific interval (10 seconds in the example below), saves the results into temporary tables, and runs a query to identify the top SQL statements based on total execution time. 

The sample script named pss.sql is shown as follows:

someip.vpc.myco.com:/misc/denis/pgcheck [] $ cat sql/pss.sql

create temp table tmp_pss_ as select 1 as snap_id, now() as sample_time, d.* from  pg_stat_statements  d where 1=0;

insert into tmp_pss_ select 1, now(),   d.* from  pg_stat_statements  d ;
select pg_sleep(10);
insert into tmp_pss_ select 2, now(),   d.* from  pg_stat_statements  d ;


\pset format wrapped
\pset columns 150
\x


select usename, queryid, query_text, duration_s, num_calls, num_rows, total_elapsed_time_ms,
    case num_calls
        when 0 then total_elapsed_time_ms
               else  total_elapsed_time_ms/num_calls end     as ms_per_call
    , (num_blk_hits + num_blk_read)/nullif(num_calls,0)      as logical_reads_per_call
    , 100*num_blk_hits/nullif(num_blk_hits + num_blk_read,0) as hit_percent
from
(
        select u.usename, b.queryid
               , b.query  as  query_text
               , extract ( epoch from (e.sample_time - b.sample_time) ) as duration_s
               , e.calls - b.calls as num_calls
               , e.rows - b.rows as num_rows
               , e.shared_blks_hit - b.shared_blks_hit as num_blk_hits
               , e.shared_blks_read - b.shared_blks_read as num_blk_read
               , round(e.total_time - b.total_time) as total_elapsed_time_ms
        from ( select * from tmp_pss_ where snap_id=1 ) b join
             ( select * from tmp_pss_ where snap_id=2 ) e on e.userid=b.userid and  e.queryid=b.queryid and e.dbid=b.dbid join
              pg_user u on u.usesysid=b.userid
        order by total_elapsed_time_ms desc limit 10
) t;

\x

Here is a demonstration of the 10-second sampling script running against a production Aurora PostgreSQL RDS instance (note: real names have been masked):

someip.vpc.myco.com:/misc/denis/pgcheck [] $ pgconn.sh ini/appa_prusr.ini  sql/pss.sql
---------------------------Your connection inputs---------------------------
Endpoint: vcm-appa-east1a-postgre-prod-tpa-2.cxocijv8i513.us-east-1.rds.amazonaws.com
Port    : 5432
User    : appadbusr1
Database: prusrprdrds
---------------------------------------------------------------------------



------------------- You run the following sql statments ------------
create temp table tmp_pss_ as select 1 as snap_id, now() as sample_time, d.* from  pg_stat_statements  d where 1=0;

insert into tmp_pss_ select 1, now(),   d.* from  pg_stat_statements  d ;
select pg_sleep(10);
insert into tmp_pss_ select 2, now(),   d.* from  pg_stat_statements  d ;


\pset format wrapped
\pset columns 150
\x


select usename, queryid, query_text, duration_s, num_calls, num_rows, total_elapsed_time_ms,
    case num_calls
        when 0 then total_elapsed_time_ms
               else  total_elapsed_time_ms/num_calls end     as ms_per_call
    , (num_blk_hits + num_blk_read)/nullif(num_calls,0)      as logical_reads_per_call
    , 100*num_blk_hits/nullif(num_blk_hits + num_blk_read,0) as hit_percent
from
(
        select u.usename, b.queryid
               , b.query  as  query_text
               , extract ( epoch from (e.sample_time - b.sample_time) ) as duration_s
               , e.calls - b.calls as num_calls
               , e.rows - b.rows as num_rows
               , e.shared_blks_hit - b.shared_blks_hit as num_blk_hits
               , e.shared_blks_read - b.shared_blks_read as num_blk_read
               , round(e.total_time - b.total_time) as total_elapsed_time_ms
        from ( select * from tmp_pss_ where snap_id=1 ) b join
             ( select * from tmp_pss_ where snap_id=2 ) e on e.userid=b.userid and  e.queryid=b.queryid and e.dbid=b.dbid join
              pg_user u on u.usesysid=b.userid
        order by total_elapsed_time_ms desc limit 10
) t;

\x
------------------- end  -------------------------------------------


Timing is on.
SELECT 0
Time: 8.773 ms
INSERT 0 4816
Time: 12.022 ms
 pg_sleep
----------

(1 row)

Time: 10004.895 ms (00:10.005)
INSERT 0 4952
Time: 11.980 ms
Output format is wrapped.
Target width is 150.
Expanded display is on.
-[ RECORD 1 ]----------+-----------------------------------------------------------------------------------------------------------------------------
usename                | prusradm
queryid                | 2553995311
query_text             | (SELECT * FROM PRUSR.COMP_REVENUE_NONCONTRACT WHERE  ACCOUNT_NUM= I_ACCT_NUM AND   COMP_TYPE = ? ORDER BY CRTD_TIMESTAMP)
duration_s             | 10.017147
num_calls              | 30
num_rows               | 75
total_elapsed_time_ms  | 55
ms_per_call            | 1.83333333333333
logical_reads_per_call | 171
hit_percent            | 100
-[ RECORD 2 ]----------+-----------------------------------------------------------------------------------------------------------------------------
usename                | prusradm
queryid                | 3783259900
query_text             | select * from prusr.SEL_ACCT_DTL2($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) as result
duration_s             | 10.017147
num_calls              | 38
num_rows               | 38
total_elapsed_time_ms  | 45
ms_per_call            | 1.18421052631579
logical_reads_per_call | 17
hit_percent            | 100
-[ RECORD 3 ]----------+-----------------------------------------------------------------------------------------------------------------------------
usename                | prusradm
queryid                | 274367736
query_text             | (Select                                                                                                                     +
                       | A.ACCOUNT_NUM ,                                                                                                             +
                       | ACCOUNT_NAME,                                                                                                               +
                       | ACCOUNT_TYPE ,                                                                                                              +
                       | CONTACT_FIRST_NAME       ,                                                                                                  +
                       | CONTACT_LAST_NAME       ,                                                                                                   +
                       | CONTACT_NUM ,                                                                                                               +
                       | EMAIL_ID        ,                                                                                                           +
                       | PARENT_ID        ,                                                                                                          +
                       | FIBER_READY_FLAG         ,                                                                                                  +
                       | COMP_POINT ,                                                                                                                +
                       | BILLING_POINT   ,                                                                                                           +
                       | ADDR_TYPE_FLAG,                                                                                                             +
                       | COMP_ACCOUNT_ID ,                                                                                                           +
                       | BILLING_ACCOUNT_ID      ,                                                                                                   +
                       | CAN     ,                                                                                                                   +
                       | MASTER_ORDER_NUM        ,                                                                                                   +
                       | A.MSTR_AGREEMENT_NUM,                                                                                                       +
                       | ORDER_ID        ,                                                                                                           +
                       | ACCOUNT_STATUS  ,                                                                                                           +
                       | VENDOR_ADDRESS_TYPE,                                                                                                        +
                       | ACCOUNT_ACTIVE_DATE,                                                                                                        +
                       | ACCOUNT_ACTVN_STATUS,                                                                                                       +
                       | ACCOUNT_VALDN_STATUS,                                                                                                       +
                       | AGREEMENT_SOURCE        ,                                                                                                   +
                       | STATUS  ,                                                                                                                   +
                       | CONTACT_NUM_EXT ,                                                                                                           +
                       | ACCOUNT_ADDRESS_ID,                                                                                                         +
                       | ADDRESS_TYPE    ,                                                                                                           +
                       | STREET_ADDRESS_1,                                                                                                           +
                       | STREET_ADDRESS_2        ,                                                                                                   +
                       | UNIT_TYPE       ,                                                                                                           +
                       | UNIT_VALUE,                                                                                                                 +
                       | CITY    ,                                                                                                                   +
                       | STATE   ,                                                                                                                   +
                       | ZIP_CODE        ,                                                                                                           +
                       | W9_FORM_ID,                                                                                                                 +
                       | VENDOR_ID       ,                                                                                                           +
                       | VENDOR_SEQ_NUM  ,                                                                                                           +
                       | LEASING_OFFICE_FLAG ,                                                                                                       +
                       | WF.VENDOR_ID AS LO_VENDOR_ID            ,                                                                                   +
                       | WF.VENDOR_SEQ_NUM AS LO_VENDOR_SEQ_NUM,                                                                                     +
                       | A.COMP_PROJECT_CODE AS PROJECT_CODE,                                                                                        +
                       | DIFF_PRD_STATUS,                                                                                                            +
                       | CONTRACT_UNITS,                                                                                                             +
                       | MDU_PROPERTY_ID,                                                                                                            +
                       | BILL_CONTACT_FIRST_NAME  ,                                                                                                  +
                       | BILL_CONTACT_LAST_NAME  ,                                                                                                   +
                       | BILL_CONTACT_NUM ,                                                                                                          +
                       | BILL_EMAIL_ID,                                                                                                              +
                       | BILL_CONTACT_NUM_EXT,                                                                                                       +
                       | MARKETING_STATUS,                                                                                                           +
                       | STATE_CODE,                                                                                                                 +
                       | ACTIVE_TO_PAY_COMM,                                                                                                         +
                       | COMMISSION_START_DATE,                                                                                                      +
                       | COMMISSION_END_DATE,                                                                                                        +
                       | COMMISSION_SCHEDULE,                                                                                                        +
                       | (SELECT COUNT(SRVC_ADDR_ID) FROM PRUSR.SERVICE_ADDRESS SA WHERE A.ACCOUNT_NUM=SA.HOA_ACCOUNT_NUM                            +
                       | AND VALDN_CODE >= ? AND SA.VALDN_CODE <= ?) AS VALID_ADDRESS_COUNT                                                          +
                       | FROM PRUSR.ACCOUNT A                                                                                                        +
                       | LEFT JOIN PRUSR.ACCOUNT_ADDRESS AD ON A.ACCOUNT_NUM=AD.ACCOUNT_NUM                                                          +
                       | LEFT JOIN PRUSR.W9_FORM WF ON A.ACCOUNT_NUM=WF.ACCOUNT_NUM                                                                  +
                       | WHERE A.ACCOUNT_NUM=I_ACCOUNT_NUM                                                                                           +
                       | order by ADDRESS_TYPE desc)
duration_s             | 10.017147
num_calls              | 38
num_rows               | 19
total_elapsed_time_ms  | 42
ms_per_call            | 1.10526315789474
logical_reads_per_call | 158
hit_percent            | 100
-[ RECORD 4 ]----------+-----------------------------------------------------------------------------------------------------------------------------
usename                | prusradm
queryid                | 1011339641
query_text             | select sv4.C41 ,sv4.O_SQLCODE ,sv4.O_SQLSTATE ,sv4.O_MESSAGE From  PRUSR.SEL_COMM_DTL_V4(I_ACCOUNT_NUM) sv4
duration_s             | 10.017147
num_calls              | 36
num_rows               | 36
total_elapsed_time_ms  | 18
ms_per_call            | 0.5
logical_reads_per_call | 9
hit_percent            | 100
-[ RECORD 5 ]----------+-----------------------------------------------------------------------------------------------------------------------------
usename                | prusradm
queryid                | 1590377131
query_text             | SELECT TERM_DATE AS AGREEMENT_END_DATE ,                                                                                    +
                       |         (TERM_DATE - cast($1 as interval)) AS DUEL_MODE_PERIOD_BEGIN_DATE,                                                  +
                       |         (TERM_DATE+ cast($2 as interval)) AS SERVICE_TERMINATION_DATE                                                       +
                       |         FROM                                                                                                                +
                       |         PRUSR.TEMP_TERM_AGREEMENT WHERE ACCOUNT_NUM=$3 UNION SELECT                                                         +
                       |         DATE_AGREEMENT_TERMINATED AS AGREEMENT_END_DATE ,                                                                   +
                       |         (DATE_AGREEMENT_TERMINATED -cast($4 as interval) ) AS                                                               +
                       |         DUEL_MODE_PERIOD_BEGIN_DATE,                                                                                        +
                       |         (DATE_AGREEMENT_TERMINATED+cast($5 as interval)) AS SERVICE_TERMINATION_DATE FROM                                   +
                       |         PRUSR.TEMP_TERM_AGREEMENT_HIST                                                                                      +
                       |         WHERE ACCOUNT_NUM= $6 UNION SELECT                                                                                  +
                       |         DATE_REQUESTED AS AGREEMENT_END_DATE                                                                                +
                       |         ,                                                                                                                   +
                       |         (DATE_REQUESTED -cast($7 as interval)) AS DUEL_MODE_PERIOD_BEGIN_DATE,                                              +
                       |         (DATE_REQUESTED+cast($8 as interval)) AS SERVICE_TERMINATION_DATE FROM                                              +
                       |         PRUSR.TEMP_TERM_AGREEMENT_FALLOUT WHERE ACCOUNT_NUM= $9
duration_s             | 10.017147
num_calls              | 19
num_rows               | 0
total_elapsed_time_ms  | 12
ms_per_call            | 0.631578947368421
logical_reads_per_call | 68
hit_percent            | 100
-[ RECORD 6 ]----------+-----------------------------------------------------------------------------------------------------------------------------
usename                | prusradm
queryid                | 1137626341
query_text             | select sv5.C41, sv5.C7 ,sv5.O_SQLCODE ,sv5.O_SQLSTATE ,sv5.O_MESSAGE From  PRUSR.SEL_COMM_DTL_V4(I_ACCOUNT_NUM) sv5
duration_s             | 10.017147
num_calls              | 10
num_rows               | 10
total_elapsed_time_ms  | 3
ms_per_call            | 0.3
logical_reads_per_call | 8
hit_percent            | 100
-[ RECORD 7 ]----------+-----------------------------------------------------------------------------------------------------------------------------
usename                | prusradm
queryid                | 3486729767
query_text             | SELECT GRACE_PERIOD FROM PRUSR.AGREEMENT WHERE MSTR_AGREEMENT_NUM IN (SELECT MSTR_AGREEMENT_NUM FROM PRUSR.ACCOUNT WHERE ACC.
                       |.OUNT_NUM=$1)
duration_s             | 10.017147
num_calls              | 38
num_rows               | 38
total_elapsed_time_ms  | 2
ms_per_call            | 0.0526315789473684
logical_reads_per_call | 7
hit_percent            | 100
-[ RECORD 8 ]----------+-----------------------------------------------------------------------------------------------------------------------------
usename                | prusradm
queryid                | 168909306
query_text             | SELECT AP.* FROM PRUSR.AGREEMENT_PRODUCTS                                                                                   +
                       |                         AP , PRUSR.ACCOUNT A WHERE A.MSTR_AGREEMENT_NUM =                                                   +
                       |                         AP.MSTR_AGREEMENT_NUM AND A.ACCOUNT_NUM = $1 AND AP.ACTIVE                                          +
                       |                         = ? ORDER                                                                                           +
                       |                         BY PRODUCT_ID
duration_s             | 10.017147
num_calls              | 19
num_rows               | 0
total_elapsed_time_ms  | 1
ms_per_call            | 0.0526315789473684
logical_reads_per_call | 6
hit_percent            | 100
-[ RECORD 9 ]----------+-----------------------------------------------------------------------------------------------------------------------------
usename                | prusradm
queryid                | 3140360803
query_text             | SELECT                                                                                                                      +
                       | COMMSN_AUTO_UPD                        FROM PRUSR.AGREEMENT AG ,PRUSR.ACCOUNT A                                             +
                       | WHERE A.MSTR_AGREEMENT_NUM=AG.MSTR_AGREEMENT_NUM                                                                            +
                       | AND A.ACCOUNT_NUM= I_ACCOUNT_NUM
duration_s             | 10.017147
num_calls              | 38
num_rows               | 19
total_elapsed_time_ms  | 1
ms_per_call            | 0.0263157894736842
logical_reads_per_call | 5
hit_percent            | 100
-[ RECORD 10 ]---------+-----------------------------------------------------------------------------------------------------------------------------
usename                | prusradm
queryid                | 2723406496
query_text             | SELECT COUNT(*)              FROM PRUSR.COMP_AGREEMENT WHERE ACCOUNT_NUM=I_ACCT_NUM
duration_s             | 10.017147
num_calls              | 47
num_rows               | 47
total_elapsed_time_ms  | 1
ms_per_call            | 0.0212765957446809
logical_reads_per_call | 3
hit_percent            | 100

Time: 5204.974 ms (00:05.205)
Expanded display is off.


I have also incorporated this logic into pgcheck, a tool I developed. Using pgcheck makes it even easier to specify the sampling duration and the number of top queries to retrieve. The example below shows a 5-minute sample for the top 5 queries:



someip.vpc.myco.com:/misc/denis/pgcheck [] $  pgcheck.py ini/appa_prusr.ini  -psss --limit 5 --delta 300
Trying to obtain connection info from the configuation file  ini/appa_prusr.ini ...

****************************  SQL TEXT ********************************


                 select usename, queryid, query_text, duration_s, num_calls, num_rows, total_elapsed_time_ms,
                         case num_calls
                           when 0 then total_elapsed_time_ms
                           else  total_elapsed_time_ms/num_calls end  as  ms_per_call,
                           (num_blk_hits + num_blk_read)/nullif(num_calls,0)  as logical_reads_per_call,
                           100*num_blk_hits/nullif(num_blk_hits + num_blk_read,0) hit_percent
                           ,num_blk_hits
                           ,num_blk_read
                           ,begin_snap_time
                        from
                        (
                                select u.usename, b.queryid
                                       , substr(b.query, 1,500) query_text
                                       , extract ( epoch from (e.sample_time - b.sample_time) ) as duration_s
                                       , e.calls - b.calls as num_calls
                                       , e.rows - b.rows as num_rows
                                       , e.shared_blks_hit - b.shared_blks_hit as num_blk_hits
                                       , e.shared_blks_read - b.shared_blks_read as num_blk_read
                                       , round(e.total_time - b.total_time) as total_elapsed_time_ms
                                       , b.sample_time as begin_snap_time
                                from ( select * from tmp_pss_ where snap_id=1 ) b join
                                     ( select * from tmp_pss_ where snap_id=2 ) e on e.userid=b.userid and  e.queryid=b.queryid and e.dbid=b.dbid join
                                      pg_user u on u.usesysid=b.userid
                                order by total_elapsed_time_ms desc limit 5) t


***********************************************************************



=================  Sampling top 5 SQLs from pg_stat_statment view for duration 300s order by total elapsed time  =====================
usename                | prusradm
queryid                | 2553995311
begin_snap_time        | 2019-07-30 21:36:34.001250+00:00
duration secs          |     300.07
num_calls              |        571
num_row                |       1519
total elapsed_time_ms  |     1050.0
ms_per_call            | 1.83887915937
logical_reads_per_call |        170
num_blk_hits           |      97335
num_blk_reads          |          0
hit_percent            | 100
query_text             |
         (SELECT * FROM PRUSR.COMP_REVENUE_NONCONTRACT WHERE  ACCOUNT_NUM= I_ACCT_NUM AND   COMP_TYPE = ? ORDER BY CRTD_TIMESTAMP)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
usename                | prusradm
queryid                | 274367736
begin_snap_time        | 2019-07-30 21:36:34.001782+00:00
duration secs          |     300.07
num_calls              |        716
num_row                |        394
total elapsed_time_ms  |      787.0
ms_per_call            | 1.09916201117
logical_reads_per_call |        123
num_blk_hits           |      88139
num_blk_reads          |          0
hit_percent            | 100
query_text             |
         (Select
A.ACCOUNT_NUM ,
ACCOUNT_NAME,
ACCOUNT_TYPE ,
CONTACT_FIRST_NAME       ,
CONTACT_LAST_NAME       ,
CONTACT_NUM ,
EMAIL_ID        ,
PARENT_ID        ,
FIBER_READY_FLAG         ,
COMP_POINT ,
BILLING_POINT   ,
ADDR_TYPE_FLAG,
COMP_ACCOUNT_ID ,
BILLING_ACCOUNT_ID      ,
CAN     ,
MASTER_ORDER_NUM        ,
A.MSTR_AGREEMENT_NUM,
ORDER_ID        ,
ACCOUNT_STATUS  ,
VENDOR_ADDRESS_TYPE,
ACCOUNT_ACTIVE_DATE,
ACCOUNT_ACTVN_STATUS,
ACCOUNT_VALDN_STATUS,
AGREEMENT_SOURCE        ,
STATUS  ,
CONTACT_NUM_EXT ,
ACCOUNT_ADDRESS_ID,
ADDRESS_TYPE    ,
STREET_ADDRESS_1,
STR
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
usename                | prusradm
queryid                | 3783259900
begin_snap_time        | 2019-07-30 21:36:34.003112+00:00
duration secs          |     300.07
num_calls              |        716
num_row                |        716
total elapsed_time_ms  |      567.0
ms_per_call            | 0.791899441341
logical_reads_per_call |         18
num_blk_hits           |      12934
num_blk_reads          |          0
hit_percent            | 100
query_text             |
         select * from prusr.SEL_ACCT_DTL2($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) as result
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
usename                | prusradm
queryid                | 1011339641
begin_snap_time        | 2019-07-30 21:36:34.001504+00:00
duration secs          |     300.07
num_calls              |        643
num_row                |        643
total elapsed_time_ms  |      223.0
ms_per_call            | 0.346811819596
logical_reads_per_call |         11
num_blk_hits           |       7226
num_blk_reads          |          0
hit_percent            | 100
query_text             |
         select sv4.C41 ,sv4.O_SQLCODE ,sv4.O_SQLSTATE ,sv4.O_MESSAGE From  PRUSR.SEL_COMM_DTL_V4(I_ACCOUNT_NUM) sv4
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
usename                | prusradm
queryid                | 1590377131
begin_snap_time        | 2019-07-30 21:36:34.000081+00:00
duration secs          |     300.07
num_calls              |        358
num_row                |          0
total elapsed_time_ms  |      216.0
ms_per_call            | 0.603351955307
logical_reads_per_call |         68
num_blk_hits           |      24344
num_blk_reads          |          0
hit_percent            | 100
query_text             |
         SELECT TERM_DATE AS AGREEMENT_END_DATE ,
        (TERM_DATE - cast($1 as interval)) AS DUEL_MODE_PERIOD_BEGIN_DATE,
        (TERM_DATE+ cast($2 as interval)) AS SERVICE_TERMINATION_DATE
        FROM
        PRUSR.TEMP_TERM_AGREEMENT WHERE ACCOUNT_NUM=$3 UNION SELECT
        DATE_AGREEMENT_TERMINATED AS AGREEMENT_END_DATE ,
        (DATE_AGREEMENT_TERMINATED -cast($4 as interval) ) AS
        DUEL_MODE_PERIOD_BEGIN_DATE,
        (DATE_AGREEMENT_TERMINATED+cast($5 as interval)) AS SERVICE_TERMINATION_DATE FROM
        PRUSR.TEMP_TERM_AGREEMENT_HIST
        WHERE ACC
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I hope this post offers a useful approach for your PostgreSQL performance monitoring and troubleshooting needs! 

Summary 

PostgreSQL's pg_stat_statements view tracks cumulative statistics; therefore, by sampling this view twice over a specific interval, you can calculate delta metrics to identify the most resource-intensive SQL queries. This offers an effective troubleshooting approach similar to Oracle's ASH reports.