The view v$sql contains almost of queries which are executed in your Oracle Database. I attached few sql queries which will be useful to check history of SQL.
1. Get latest query
select sql_txt from v$sql where first_load_time=(select max(1st_load_time) from v$sql);
2. Sort executed queries by load time
select sql_text, first_load_time from v$sql order by first_load_time desc;
3. Get executed queries in a schema which have special text and sort by load time
select * from v$sql where parsing_schema_name like 'YOUR_SCHEMA_DETAILS' and
sql_text like '%YOUR_SQLTEXT%' order by first_load_time desc;
4. Get 50 last executed queries
select sql_fulltext from (select * from v$sql where parsing_schema_name
like 'ORAAGENT' order by first_load_time desc) where rownum < 51;
5. Get 50 executed UPDATE or DELETE queries in a specific time period and sort by load time
select sql_text,sql_fulltext, first_load_time, parsing_schema_name from( select * from v$sql where parsing_schema_name like 'YOUR_SCHEMA' and (sql_text like '%UPDATE %' or sql_text like '%INSERT %')
and to_timestamp(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') >
to_timestamp('2021-01-24/09:06:00', 'YYYY-MM-DD/HH24:MI:SS') order by first_load_time desc)where rownum < 51
You can create your own queries to find out what queries you need to check. Remember this view v$sql doesn’t store prepared statements.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 20 14:53:07 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2483027968 bytes
Fixed Size 8623736 bytes
Variable Size 671091080 bytes
Database Buffers 1795162112 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
EXECUTE DATAPATCH FOR UPDATE THE PATCH DETAILS IN DATABASE SIDE.
[oracle@trichyOPatch]$ cd $ORACLE_HOME/OPatch
[oracle@trichyOPatch]$ ./datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Wed Jan 20 14:54:12 2021
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_16800_2021_01_20_14_54_12/sqlpatch_invocation.log
Connecting to database…OK
Bootstrapping registry and package to current versions…done
Determining current state…done
Current state of SQL patches:
Bundle series DBRU:
ID 210119 in the binary registry and ID 200414 in the SQL registry
Adding patches to installation queue and performing prereq checks…
Installation queue:
Nothing to roll back
The following patches will be applied:
32228578 (DATABASE JAN 2021 RELEASE UPDATE 12.2.0.1.210119)
Installing patches…
Patch installation complete. Total patches installed: 1
Validating logfiles…
Patch 32228578 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32228578/24008225/32228578_apply_BIJU1_2021Jan20_14_54_32.log (no errors)
SQL Patching tool complete on Wed Jan 20 14:55:10 2021
After installing the SQL portion of the patch, some packages could become INVALID. This will get recompiled upon access or you can run utlrp.sqlto get them back into a VALID state.
[oracle@trichyOPatch]$ cd $ORACLE_HOME/rdbms/admin
[oracle@trichyadmin]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 20 15:00:48 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> @utlrp.sql
TIMESTAMP
COMP_TIMESTAMP UTLRP_BGN 2021-01-20 15:00:56
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC> Use the following queries to track recompilation progress:
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
SQL> SELECT owner, object_name, object_type FROM all_objects WHERE status =’INVALID’;
no rows selected
CHECK PATCH INFORMATION IN DATABASE LEVEL
SQL> select DESCRIPTION,ACTION,ACTION_TIME,VERSION from dba_registry_sqlpatch;
DESCRIPTION ACTION ACTION_TIME VERSION ————————- ——————-———— ———————–————— ——————- DATABASE JAN 2021 APPLY20-JAN-21 02.55.10.831438 PM 12.2.0.1 RELEASEUPDATE
12.2.0.1.210119
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
In Putty session while we execute ./dbca command we will get this screen.
STEP 1:
select create database option then click next button
STEP 2:
We need to supply Global database name and password
STEP 3:
click finish button to complete database creation
Once progress completed we will get below screen
STEP 5:
Now the database has been created. We can check by logging in.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
In Putty session while we execute ./dbca command we will get this screen.
STEP 1:
select create database option then click next button
STEP 2:
We need to supply Global database name and password
STEP 3:
click finish button to complete database creation
Once progress completed we will get below screen
STEP 5:
Now the database has been created. We can check by logging in.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
In Putty session while we execute ./dbca command we will get this screen. In that screen we need to select manage Pluggable databases option to create new PDB database.
Select Create a Pluggable database option.
Click finish button to complete database creation.
Click on new Pluggable database
Enter Pluggable database name and passwords
We need to chose storage locations. click on next button
Check on the summary of inputs in Pluggable databases
Once all details confirmed click on finish button to progress on installation.
Once progress completed PDB100 plugged in.
We can check pluggable databases in putty session whether its installed properly.
Now we can see that pluggable databases installed successfully.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
In Putty session while we execute ./dbca command we will get this screen. In that screen we need to select manage Pluggable databases option to create new PDB database.
Select Create a Pluggable database option.
Click finish button to complete database creation.
Click on new Pluggable database
Enter Pluggable database name and passwords
We need to chose storage locations. click on next button
Check on the summary of inputs in Pluggable databases
Once all details confirmed click on finish button to progress on installation.
Once progress completed PDB100 plugged in.
We can check pluggable databases in putty session whether its installed properly.
Now we can see that pluggable databases installed successfully.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
Welcome! to my Oracle DBA blog. This blog helps you troubleshoot various day-to-day Activities/Oracle issues. It will give some useful queries/SQL and Tips to troubleshoot the problem and their remediation. It covers a few useful information below topics, please go through the menu option OR search is based on a keyword in the search option.
Oracle DBA
PL/SQL
Linux
Oracle Tuning
Golden Gate
RAC
Oracle EBS R12
Standby Database/Active data guard.
For those who are interested to find out more about me: This is Ramkumar, an enthusiastic and knowledgeable Senior Consultant with 10-plus years of Oracle experience (as of 2022).
I’ve worked extensively in the following technologies: Oracle 9i, 10g, 11g, 12c ,19c Database Oracle Application EBS R12.1,12.2 Oracle Maximum Availability Architecture Oracle RAC 10g, 11g, 12c, and 19c Oracle Data Guard 10g, 11g, 12c, and 19c Oracle Enterprise Manager 10g, 11g,12c, and 13c Oracle RMAN 10g, 11g, 12c, and 19c Performance and SQL Tuning
Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter: https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
[oracle@oracleagent 30886680]$ opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.19 Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc OPatch version : 12.2.0.1.19 OUI version : 12.2.0.1.4 Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-18_10-27-13AM_1.log
Here we are going to apply patch with opatch apply.
User need to give Y to proceed Patch.
[oracle@oracleagent 30886680]$ opatch apply Oracle Interim Patch Installer version 12.2.0.1.19 Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc OPatch version : 12.2.0.1.19 OUI version : 12.2.0.1.4 Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-18_10-03-10AM_1.log
Verifying environment and performing prerequisite checks… OPatch continues with these patches: 30886680
Do you want to proceed? [y|n] y User Responded with: Y All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/u01/app/oracle/product/12.2.0.1/db_1')
Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files… Applying interim patch '30886680' to OH '/u01/app/oracle/product/12.2.0.1/db_1' ApplySession: Optional component(s) [ oracle.swd.oui, 12.2.0.1.0 ] , [ oracle.oid.client, 12.2.0.1.0 ] , [ oracle.has.crs, 12.2.0.1.0 ] , [ oracle.rdbms.drdaas, 12.2.0.1.0 ] , [ oracle.ons.daemon, 12.2.0.1.0 ] , [ oracle.network.cman, 12.2.0.1.0 ] not present in the Oracle Home or a higher version is found.
Bringing up the instance and execute the datapatch verbose.
SQL> startup ORACLE instance started. Total System Global Area 1883107968 bytes Fixed Size 8623736 bytes Variable Size 671011080 bytes Database Buffers 1795162112 bytes Redo Buffers 8151040 bytes Database mounted. Database opened. SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oracleagent 30886680]$ cd $ORACLE_HOME/OPatch [oracle@oracleagent OPatch]$ ./datapatch -verbose SQL Patching tool version 12.2.0.1.0 Production on Mon Jan 18 10:33:28 2021 Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_30825_2021_01_18_10_33_28/sqlpatch_invocation.log Connecting to database…OK Bootstrapping registry and package to current versions…done Determining current state…done Current state of SQL patches: Bundle series DBRU: ID 200414 in the binary registry and not installed in the SQL registry
Adding patches to installation queue and performing prereq checks… Installation queue: Nothing to roll back The following patches will be applied: 30886680 (DATABASE APR 2021 RELEASE UPDATE 12.2.0.1.200414)
Installing patches… Patch installation complete. Total patches installed: 1
Validating logfiles… Patch 30886680 apply: SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30886680/23474251/30886680_apply_BIJU1_2021Jan18_10_34_01.log (no errors) SQL Patching tool complete on Mon Jan 18 10:37:30 2021
[oracle@oracleagent OPatch]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 18 10:38:48 2021 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> SELECT owner, object_name, object_type FROM all_objects WHERE status ='INVALID'; no rows selected
SQL> set line 1000 col action form a12 col version form a40 col description form a85 col action_date form a20 select description, action, to_char(action_time,'DD/MM/RR HH18:MI:SS') action_date, ' ' version from dba_registry_sqlpatch;
Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
[oracle@oracleagent 30886680]$ opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.19 Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc OPatch version : 12.2.0.1.19 OUI version : 12.2.0.1.4 Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-18_10-27-13AM_1.log
Here we are going to apply patch with opatch apply.
User need to give Y to proceed Patch.
[oracle@oracleagent 30886680]$ opatch apply Oracle Interim Patch Installer version 12.2.0.1.19 Copyright (c) 2021, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.2.0.1/db_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc OPatch version : 12.2.0.1.19 OUI version : 12.2.0.1.4 Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2021-01-18_10-03-10AM_1.log
Verifying environment and performing prerequisite checks… OPatch continues with these patches: 30886680
Do you want to proceed? [y|n] y User Responded with: Y All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/u01/app/oracle/product/12.2.0.1/db_1')
Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files… Applying interim patch '30886680' to OH '/u01/app/oracle/product/12.2.0.1/db_1' ApplySession: Optional component(s) [ oracle.swd.oui, 12.2.0.1.0 ] , [ oracle.oid.client, 12.2.0.1.0 ] , [ oracle.has.crs, 12.2.0.1.0 ] , [ oracle.rdbms.drdaas, 12.2.0.1.0 ] , [ oracle.ons.daemon, 12.2.0.1.0 ] , [ oracle.network.cman, 12.2.0.1.0 ] not present in the Oracle Home or a higher version is found.
Bringing up the instance and execute the datapatch verbose.
SQL> startup ORACLE instance started. Total System Global Area 1883107968 bytes Fixed Size 8623736 bytes Variable Size 671011080 bytes Database Buffers 1795162112 bytes Redo Buffers 8151040 bytes Database mounted. Database opened. SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oracleagent 30886680]$ cd $ORACLE_HOME/OPatch [oracle@oracleagent OPatch]$ ./datapatch -verbose SQL Patching tool version 12.2.0.1.0 Production on Mon Jan 18 10:33:28 2021 Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_30825_2021_01_18_10_33_28/sqlpatch_invocation.log Connecting to database…OK Bootstrapping registry and package to current versions…done Determining current state…done Current state of SQL patches: Bundle series DBRU: ID 200414 in the binary registry and not installed in the SQL registry
Adding patches to installation queue and performing prereq checks… Installation queue: Nothing to roll back The following patches will be applied: 30886680 (DATABASE APR 2021 RELEASE UPDATE 12.2.0.1.200414)
Installing patches… Patch installation complete. Total patches installed: 1
Validating logfiles… Patch 30886680 apply: SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30886680/23474251/30886680_apply_BIJU1_2021Jan18_10_34_01.log (no errors) SQL Patching tool complete on Mon Jan 18 10:37:30 2021
[oracle@oracleagent OPatch]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 18 10:38:48 2021 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> SELECT owner, object_name, object_type FROM all_objects WHERE status ='INVALID'; no rows selected
SQL> set line 1000 col action form a12 col version form a40 col description form a85 col action_date form a20 select description, action, to_char(action_time,'DD/MM/RR HH18:MI:SS') action_date, ' ' version from dba_registry_sqlpatch;
Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
set lines 1000 pages 9999
COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';
SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,
TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions_total,
TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM dba_hist_sqlstat h, dba_hist_snapshot s WHERE h.sql_id = '&sql_id'
AND h.executions_total > 0 AND s.snap_id = h.snap_id
AND s.dbid = h.dbid AND s.instance_number = h.instance_number
UNION ALL
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id, h.plan_hash_value, h.executions,
TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,
TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,
TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,
TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,
TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,
TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,
TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,
TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM gv$sqlarea_plan_hash h
WHERE h.sql_id = '&&sql_id'
AND h.executions > 0
order by source ;
AWR DATA:
set lines 1000 pages 9999
COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';
SELECT h.instance_number,
TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id, h.plan_hash_value, h.executions_total,
TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE h.sql_id = '&sql_id'
AND h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
ORDER BY
s.begin_interval_time,
s.end_interval_time;
AWR-LIO:
col execs for 999,999,999
col avg_etime for 999,999
col avg_lio for 999,999,999
col avg_pio for 999,999,999
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
ROUND(disk_reads_delta/DECODE(executions_delta,0,1, executions_delta),1) avg_pio,
ROUND(rows_processed_delta/DECODE(executions_delta,0, 1, executions_delta), 1) avg_rows,
round(px_servers_execs_delta/decode(executions_delta,0,1, executions_delta), 1) avg_px
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
To find which is using Current memory:
set linesize 999
col avg_et_secs justify right format 9999999.99
col cost justify right format 9999999999
col timestamp justify center format a25
col parsing_schema_name justify center format a30
col inst_id format 999999999
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
select 'gv$sqlarea_plan_hash' source, INST_ID, SQL_ID, PLAN_HASH_VALUE,
round(elapsed_time/decode(nvl(executions,0),0,1,executions)/1e6/
decode(px_servers_executions,0,1,px_servers_executions)/decode(nvl(executions,0),0,1,executions),2) avg_et_secs,
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
optimizer_cost cost, LAST_LOAD_TIME timestamp, parsing_schema_name --FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SQL_PROFILE
from gv$sqlarea_plan_hash
where sql_id = nvl(trim('&sql_id'),sql_id)
UNION
SELECT 'dba_hist_sql_plan' source, null INST_ID, t1.sql_id sql_id, t1.plan_hash_value plan_hash_value, t2.avg_et_secs avg_et_secs, t2.avg_px, t1.cost cost, t1.timestamp timestamp, NULL parsing_schema_name
FROM dba_hist_sql_plan t1,
(
SELECT sql_id, plan_hash_value, --round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6,2) avg_et_secs
round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6/
decode(SUM(px_servers_execs_total),0,1,SUM(px_servers_execs_total))/decode(SUM(executions_total),0,1,SUM(executions_total)),2) avg_et_secs,
SUM(px_servers_execs_total)/decode(SUM(executions_total),0,1,SUM(executions_total)) avg_px
FROM dba_hist_sqlstat
WHERE
executions_total > 0
GROUP BY sql_id, plan_hash_value
) t2
WHERE
t1.sql_id = nvl(TRIM('&sql_id'), t1.sql_id)
AND t1.depth = 0
AND t1.sql_id = t2.sql_id(+)
AND t1.plan_hash_value = t2.plan_hash_value(+)
order by avg_et_secs, cost
/
Identify the Culprit: SQL , SQL TEXT , BIND VARIABLE
col exact_matching_signature for 99999999999999999999999999
col sql_text for a50
select sql_id, exact_matching_signature, SQL_TEXT from v$sqlarea where UPPER(sql_text) like '%DUMMY%' order by UPPER(sql_text);
set long 20000
set lines 750 pages 9999
select sql_text from dba_hist_sqltext where sql_id = '&SQL_ID';
set long 20000
set lines 750 pages 9999
select sql_text from gv$sqlarea where sql_id = '&SQL_ID';
col VALUE_STRING for a50
SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='&sql_id';
statistics of objects of a specific sql id:
set lines 300 set pages 300
col table_name for a40
col owner for a30
select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked
from dba_tab_statistics
where (owner, table_name) in
(select distinct owner, table_name
from dba_tables
where ( table_name)
in ( select object_name
from gv$sql_plan
where upper(sql_id) = upper('&sql_id') and object_name is not null))
--and STALE_STATS='YES'
/
col index_name for a50
SELECT owner, index_name, table_name,last_analyzed, sample_size, num_rows, partitioned, global_stats
FROM dba_indexes
WHERE index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
ORDER BY owner, table_name, index_name
/
Sql id current rows, I/O , read/wrire details:
select SQL_ID,PLAN_HASH_VALUE,ELAPSED_TIME,PHYSICAL_READ_BYTES,PHYSICAL_WRITE_BYTES,DISK_READS,DIRECT_WRITES,BUFFER_GETS,ROWS_PROCESSED from v$sqlstats where sql_id='&sql_id';
select sql_text,rows_processed from v$sql
where USERS_EXECUTING>0;
select s.sid, s.serial#, p.spid, s.username, s.program,
t.xidusn, t.used_ublk, t.used_urec, sa.sql_text from
v$process p,v$session s, v$sqlarea sa, v$transaction t
where s.paddr=p.addr
and s.taddr=t.addr
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
order by s.sid
/
exec dbms_stats.flush_database_monitoring_info;
select inserts,updates,deletes from user_tab_modifications where table_name = '&table_name';
Check the progress of DML statements:
col sql_text for a60
SELECT rows_processed "Total Rows Processed",
ROUND((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 1) "Total Time (Min)",
TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) "Rows/Min",
TRUNC(rows_processed /((SYSDATE - TO_DATE(first_load_time, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60)) "Rows/Sec" , SUBSTR(sql_text, 1, 60) sql_text
FROM gv$sqlarea
WHERE SQL_ID='&SQL_ID'
AND open_versions > 0
AND rows_processed > 0;
Detailed history of SQL_ID:
select instance_number inst_id,SESSION_ID,USER_ID,PROGRAM,sql_id,SQL_CHILD_NUMBER,sql_plan_hash_value,to_char (sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start from
dba_hist_active_sess_history where
sql_id='&sql_id';
select sql_id,
starting_time,
end_time,
(EXTRACT(HOUR FROM run_time) * 3600
+ EXTRACT(MINUTE FROM run_time) * 60
+ EXTRACT(SECOND FROM run_time))/60 run_time_MIN,
READ_IO_BYTES,
PGA_ALLOCATED PGA_ALLOCATED_BYTES,
TEMP_ALLOCATED TEMP_ALLOCATED_BYTES
from (
select
sql_id,
max(sample_time - sql_exec_start) run_time,
max(sample_time) end_time,
sql_exec_start starting_time,
sum(DELTA_READ_IO_BYTES) READ_IO_BYTES,
sum(DELTA_PGA) PGA_ALLOCATED,
sum(DELTA_TEMP) TEMP_ALLOCATED
from
(
select sql_id,
sample_time,
sql_exec_start,
DELTA_READ_IO_BYTES,
sql_exec_id,
greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_PGA,
greatest(TEMP_SPACE_ALLOCATED - first_value(TEMP_SPACE_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_TEMP
from
dba_hist_active_sess_history
where
sample_time >= trunc(sysdate)
--and sample_time < to_date ('2013/04/16 03:10:00','YYYY/MM/DD HH24:MI:SS')
and sql_exec_start is not null
and IS_SQLID_CURRENT='Y'
)
group by sql_id,SQL_EXEC_ID,sql_exec_start
order by sql_id
)
where sql_id = '&sql_id'
order by sql_id, run_time_MIN desc;
To find Sql Hanging or not:
select sess_io.inst_id,
sess_io.sid,
sesion.sql_id,
sess_io.block_gets,
sess_io.consistent_gets,
sess_io.physical_reads,
sess_io.block_changes,
sess_io.consistent_changes
from gv$sess_io sess_io, gv$session sesion
where sesion.sid = sess_io.sid and
sess_io.inst_id = sesion.inst_id and
sesion.sql_id='&sql_id'
and sesion.username is not null ;
select sess_io.inst_id,
sess_io.sid,
sess_io.block_gets,
sess_io.consistent_gets,
sess_io.physical_reads,
sess_io.block_changes,
sess_io.consistent_changes
from gv$sess_io sess_io, gv$session sesion
where sesion.sid = sess_io.sid and
sess_io.inst_id = sesion.inst_id
and sesion.username is not null ;
SQL to show the full SQL executing for active sessions:
select sesion.sid,sql_text from v$sqltext sqltext, v$session sesion where sesion.sql_hash_value = sqltext.hash_value and sesion.sql_address = sqltext.address and sesion.username is not null
order by sqltext.piece;
select a.sid,b.sql_fulltext from V$Session a, V$SQLAREA b where a.sql_id=b.sql_id and a.status='ACTIVE';
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
