Abdul Hafeez Kalsekar -- Oracle database tuning case studies

  -- Copyright 2018 Tanel Poder. All rights reserved. More info at  http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. -- mon_topsql.sql ver2 -- added plan_hash_value support and ordered by descending date -- script by Tanel Poder (  http://blog.tanelpoder.com  ) SET LINES 999 PAGES 5000 TRIMSPOOL ON TRIMOUT ON TAB OFF  COL pct FOR A10 JUST RIGHT COL cpu_pct FOR 999.9 COL io_pct FOR 999.9 BREAK ON day SKIP 1 DEF days=7 DEF weekdays="mon,tue,wed,thu,fri, sat,sun" DEF separator="," WITH ash AS (     SELECT          day       , weekday       , owner       , object_name       , procedure_name       , sql_id       , sql_plan_hash_value       , distinct_days       , total_seconds       , io_seconds       , cpu_seconds ...

 -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. -------------------------------------------------------------------------------- -- File name:   mon_topsql.sql (Daily TOP SQL reporting for Exadata) -- -- Purpose:     This script can be used for getting an overview of your most --              time-consuming SQL statements along with important IO and execution --              count metrics needed for determining whether a query would --              benefit from Exadata Smart Scanning features. -- -- Usage:       Run @mon_topsql.sql -- --              There's a variable called "days" in the beggining of this script --             ...

  Connection pooling is very  less known  to dba community and hence it becomes tough to  troubleshoot connection  issues  Connection pooling is generally the practice of a middle tier (application server) getting N connections to a database (say 20 connections). These connections are stored in a pool in the middle tier, an "array" if you will. Each connection is set to "not in use" When a user submits a web page to the application server, it runs a piece of your code, your code says "i need to get to the database", instead of connecting right there and then (that takes time), it just goes to this pool and says "give me a connection please". the connect pool software marks the connection as "in use" and gives it to you. We have many connection  poling strategies  for oracle database .   1)  Database side Resident Connection Pooling ( database side )  2)  Jdbc connection pooling using Hikari pool and other 3rd party application...

  set lines 80 col c1 heading 'STAT'  format a25 col c2 heading 'Count' format 999,999,999,999 select distinct    stat_name   c1,    value       c2 from    dba_hist_osstat where    stat_name in (    'NUM_CPU_CORES',    'NUM_CPU_SOCKETS',    'PHYSICAL_MEMORY_BYTES' );   clear breaks clear computes clear columns   prompt prompt *********************************************** prompt Get cpu_count prompt *********************************************** col c1 heading 'CPU|Cores' format a20 col c2 heading 'Size'      format a20 select    name  c1,    value c2 from    v$parameter where    name like '%cpu_count%';   select *  from DBA_HIST_SYSMETRIC_SUMMARY where snap_id=<snap_id>   and metric_name in ('Host CPU Utilization (%)','I/O Megabytes per Second','I/O Requests per Second','Total PGA Alloc...

  -- reduce soft parsing  setting session_cache_cursors setting hold_cursor  setting cursor_space_for_time