Dear Readers, In this article, we will see the following Multiple Indexes On the Same Set Of Column in Oracle 12c. You can create multiple indexes on the same set of columns but only one of the indexes can be visible at a time and… Read More
Oracle :How to find Historical TOP SQL’s. In this article we will see how to find out historical TOP SQL’s. The SQL Query to find Historical Top SQL’s.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
set lines 235 set numwidth 20 set pages 3000 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 99999 set feedback off break on plan_hash_value on startup_time skip 1 break on plan_hash_value on TIME break on TIME col time for a30 select * from ( select ss.instance_number node,sql_id, to_Char(begin_interval_time,'MM-DD-YY HH24:MI') Time, sum(nvl(executions_delta,0)) execs, round(sum((elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 ),2) avg_etime, round(sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))),2) AVG_LIO, round(sum((DISK_READS_DELTA/decode(nvl(DISK_READS_DELTA,0),0,1,executions_delta))),2) AVG_PHR , round(sum((ROWS_PROCESSED_DELTA/decode(nvl(ROWS_PROCESSED_DELTA,0),0,1,executions_delta))),2) AVG_ROWS , round(sum((CPU_TIME_DELTA/decode(nvl(CPU_TIME_DELTA,0),0,1,executions_delta))/1000000),2) AVG_CPU, round(sum(CPU_TIME_DELTA)/1000000,2) TOTAL_CPU_TIME, RANK() OVER (PARTITION BY to_Char(begin_interval_time,'MM-DD-YY HH24:MI') ORDER BY sum(CPU_TIME_DELTA)*1000000 desc ) RANK from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where ss.snap_id = S.snap_id and ss.snap_id > (select max(snap_id)-1000 from DBA_HIST_SNAPSHOT ) and ss.instance_number = S.instance_number and executions_delta > 0 and begin_interval_time between to_date('27-JAN-2019 04:20','DD-MON-YYYY HH24:MI') and to_date('31-JAN-2019 06:30','DD-MON-YYYY HH24:MI') group by ss.instance_number,sql_id, to_Char(begin_interval_time,'MM-DD-YY HH24:MI') order by to_Char(begin_interval_time, 'MM-DD-YY HH24:MI'),sum(CPU_TIME_DELTA)*1000000,sum((CPU_TIME_DELTA/decode(nvl(CPU_TIME_DELTA,0),0,1,executions_delta))/1000000) ) where RANK<=10; |
Output : In screenshot Output in Text Format :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
NODE SQL_ID TIME EXECS AVG_ETIME AVG_LIO AVG_PHR AVG_ROWS AVG_CPU TOTAL_CPU_TIME RANK ------ ------------- ------------------------------ ------------ ------------ -------------- -------------------- -------------------- -------------------- -------------------- -------------------- 1 7kmbrw7q8hn4g 01-27-19 14:00 60 .010 78.0 0 1 .01 .32 10 1 3wrrjm9qtr2my 1 .390 482.0 0 2 .37 .37 9 1 4y1y43113gv8f 601 .000 50.6 0 15.18 0 .54 8 1 7hu2k3a31b6j7 614 .000 79.2 0 15.45 0 .57 7 1 6wrwqq7jkmv3w 1 .730 506.0 0 6 .72 .72 6 1 3xjw1ncw5vh27 21 .040 24,029.0 0 0 .04 .85 5 1 74cpnuu24wmx7 3 .470 18,446.3 0 2 .42 1.27 4 1 6mcpb06rctk0x 1 3.180 594,799.0 9 0 3.03 3.03 3 1 381t19fqhxdgp 1 5.180 28,698.0 2 75 5.07 5.07 2 1 b6usrg82hwsa3 1 11.360 314,544.0 22 0 11.21 11.21 1 1 39k4gf5t0831y 01-27-19 15:00 14 .000 10.4 0 0 0 .02 10 1 22356bkgsdcnh 12 .000 .0 0 1 0 .03 9 1 357cru8xpxh55 60 .000 3.0 0 0 0 .04 8 1 c9umxngkc3byq 60 .000 .0 0 0 0 .05 7 1 6ajkhukk78nsr 1 .060 1,156.0 0 1 .06 .06 6 1 d9vzav10pcpfh 60 .000 60.0 0 1 0 .06 5 1 fuws5bqghb2qh 60 .000 9.0 0 0 0 .07 4 1 772s25v1y0x8k 120 .000 .0 0 28 0 .1 3 1 79vddxwy7qz3b 3 .070 3,009.3 0 6.67 .07 .2 2 1 7kmbrw7q8hn4g 60 .010 78.0 0 1 .01 .32 1 1 2fh3rvu66aaqa 01-30-19 18:09 1 .210 369.0 11 1 .1 .1 10 1 7kmbrw7q8hn4g 11 .040 309.7 18.09 1 .01 .11 9 1 a6ygk0r9s5xuj 10 .010 71.5 1.2 0 .01 .12 8 1 02577v815yp77 1 .220 4,909.0 244 1 .13 .13 7 1 4phvdvx32a3mf 10 .020 162.4 3.3 1 .02 .18 6 1 bxywuzvtp6wjg 1 .400 4,753.0 166 0 .24 .24 5 1 3wrrjm9qtr2my 1 .840 1,232.0 56 2 .39 .39 4 1 644jqx2hk7qg0 1 .600 .0 0 1 .6 .6 3 1 12a2xbmwn5v6z 1 1.720 881,392.0 2643 3796 .71 .71 2 1 f6cz4n8y72xdc 1 2.510 914,468.0 2673 1 1.5 1.5 1 |
Tablespace Management in Oracle. A Tablespace is a logical storage unit within an Oracle database. It is logical because a table space is not visible in the file system of the machine on which the database resides. A table space, in turn, consists of at… Read More
How to Install VMware Workstation On Windows. VMware Workstation is a hosted hypervisor that runs on x64 versions of Windows and Linux operating systems (an x86 version of earlier releases was available);[3] it enables users to set up virtual machines (VMs) on a single physical machine, and use them simultaneously along with the actual machine. Each… Read More
DATABASE CREATION Oracle Database can create by using the following 3 methods Manual database creation OMF method (Oracle Managed database Files) DBCA method (Database Configuration Assistance) In this article we will discuss Manual database creation. Steps to create database in manual method. Declare… Read More
Startup and Shutdown modes in Oracle In this article we will discuss different STARTUP and SHUTDOWN MODES. Startup Modes : Startup nomount Startup mount Open Startup restrict Startup force Startup suspend Before reading article please check below image Startup Nomount : Oracle open… Read More
Dear Readers, In this article, we will see the following DDL Logging in Oracle 12c. What is DDL Logging ? Concerning the Data Definition Language log, or DDL log, in previous releases, there wasn’t any direct option available to log the DDL command/action. Like an… Read More
Dear Readers, In this article, we will see the following Temporary Undo in Oracle 12C. Temporary Undo in 12C Undo tablespaces facilitate the rollback of logical transactions. In addition, undo tablespaces support a number of other features, including read consistency, various database-recovery operation, and flashback… Read More
MYSQL TO ORACLE DATABASE LINK CREATION USING HETEROGENEOUS SERVICES A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The remote database can be same oracle or it can be non-oracle database. To access… Read More
DATABASE LINK IN ORACLE What is database link? A database link is a schema object in a database. The main purpose of the database link is to access database objects present in another database or remote database. A database link creates a connection between a… Read More
