Drop all SPM baselines for SQL handle
declaremyplan pls_integer;beginmyplan:=DBMS_SPM.drop_sql_plan_baseline (sql_handle => ‘&sql_handle’);end;/
Load SPM baseline from AWR
accept v_sqlid prompt ‘Enter sqlid ‘accept v_phv prompt ‘Enter PHV ‘set serveroutput on DECLAREv_first_snapid number;v_last_snapid number;v_sqlset varchar2(1000);loaded_plans number; BEGINselect max(ss.snap_id)-3, max(ss.snap_id)into v_first_snapid, v_last_snapidfrom DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SSwhere sql_id = ‘&v_sqlid’and plan_hash_value = ‘&v_phv’and ss.snap_id = S.snap_idand ss.instance_number = S.instance_numberand executions_delta > 0 ; loaded_plans := dbms_spm.load_plans_from_awr( begin_snap=>v_first_snapid,end_snap=>v_last_snapid,basic_filter=>q’# sql_id=’&v_sqlid’ and plan_hash_value=’&v_phv’ #’ ); dbms_output.put_line(‘loaded-plans= ‘ ||…
Drop specific SQL plan baseline – spm
set serveroutput onvar res numberexec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE (‘&original_sql_handle’,’&original_plan_name’);exec dbms_output.put_line(‘Number of plans dropped: ‘ || :res);
findinfo.sql (SQL for getting CPU and Active session info)
set lines 120 pages 200col dd format a20col inst_id format 99col metric_name format a30col value format 99.99 select x.dd, x.inst_id, x.metric_name, x.value “%CPU”, y.sessfrom(select to_char(sysdate, ‘DD-MON-RRRR:HH24:MI’) DD, inst_id, metric_name, valuefrom gv$sysmetricwhere metric_name like ‘Host CPU Utilization%’ and group_id=2 ) x,(select inst_id, count(1) sess from gv$sessionwhere status = ‘ACTIVE’ and osuser != ‘oracle’group by inst_id) ywhere…
Read More “findinfo.sql (SQL for getting CPU and Active session info)” »
SQL Tracker by SID sqltrackerbysid.sql
col SAMPLE_TIME format a25col program format a20col SQL_EXEC_START format a25col machine format a15set lines 140 pages 200alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’; select SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, USER_ID, sql_ID, Machine, program, SQL_EXEC_STARTfrom gv$active_session_historywhere session_id = &v_session_idand sample_time > sysdate – 1/24order by sample_time/
How to connect to Oracle Database with Wallet with Python.
To connect Python to Oracle Database using an Oracle Wallet, you can use the cx_Oracle or oracledb library with Oracle Client libraries (like Oracle Instant Client). Oracle Wallet simplifies secure connections by storing credentials and SSL certificates. ✅ Steps to Connect Python to Oracle using Oracle Wallet Step 1: Install Required Python Package Step 2:…
Read More “How to connect to Oracle Database with Wallet with Python.” »
JSON/XML Types in Oracle
Handling JSON and XML Data in Oracle Oracle provides built-in support for JSON and XML data storage, querying, and manipulation. Below are methods to handle both data types efficiently. 1. Handling JSON Data in Oracle A. Storing JSON Data Example: Creating a Table with JSON Column B. Inserting JSON Data C. Querying JSON Data 1….
CPU Core related projections
Rule of Thumb for Estimating Concurrent DB Connections on a 24-Core Machine (Single Thread Each) The number of concurrent database connections a 24-core machine can handle depends on various factors, including: Hypothetical Calculation Based on CPU A single-threaded, single-core system can typically handle 2-10 concurrent active queries (depending on execution time and parallelism). For a…
Exadata Basics
In an Oracle Exadata environment, cell node storage disks are presented to compute nodes as ASM disks, and this mapping occurs through the Oracle ASM (Automatic Storage Management) and Exadata Storage Server software (CellCLI). Here’s a detailed explanation of how the cell node storage disks are mapped to the compute nodes: 1. Cell Node Storage:…
Reading config file from other folder inside class
Directory Structure: root-folder/ config-folder/ json-file.config class-folder/ classfile.py tests/ pytest-file1.py json-file.config: { “setting1”: “value1”, “setting2”: “value2” } Now, Here I need to access json-file.config in my class file. I do not want to use dbutil, how can i access it and read it from json-file.config in my class file?? classfile.py import os import json class myclass:…
Read More “Reading config file from other folder inside class” »