Category: Tuning

What is Oracle Partitioning – ( Why ?,How ? &Benefits)

Partitioning ==>Increased performance and Ease of data management

Partitioning allows a database object(Table/View/IOT-index-organized table) to be subdivided into smaller pieces,called a partition.

Partition

It has its own name, and may optionally have its own storage characteristics
can be managed either collectively or individually.
From application point of View , no modifications are required.

Moreover, partitioning can greatly reduce the total cost of data ownership, using a “tiered archiving” approach of keeping older info still online on low cost storage devices.IT administrators can implement Information Lifecycle Management (ILM) protocols by partitioning data and moving historical data to low-cost storage.Partitioning can be used to obtain better concurrency as well as to decrease the number of rows to be processed through partition pruning and partition-wise joins

Partitioning for Performance
Partitioning pruning==> leveraging the partitioning metadata to only touch the data of relevance for a SQL operation
Partitioning for Manageability
Partitioning for Availability
Information
Lifecycle Management(ILM) with Partitioning

db_part_usagesWhich Partitioning Method Should Be Used? 

Step 1 – Is Partitioning Necessary?
Step 2 - Should This Object Be Partitioned?
Step 3 - Which Partitioning Method Should Be Used?
Step 4 - Identify the Partition Key
Step 5 – Performance Check & Access Path Analysis
Step 6 – Partitioned Table Creation and Data Migration

Method: 1 – Straight Insert
Oracle Data Pump
Data Pump Access Methods
Method 2 – Import/Export using Data Pump

Step 7 – Maintenance Step

PARTITION MAINTENANCE OPERATIONS
Adding partitions or sub partitions
Dropping a partition
Moving a partition
Splitting and merging partitions
Exchanging a partition with a table
Renaming a partition

Practical Partitioning Case Study 
Oracle General Ledger -->340 GB in size with General Ledger representing 90% of this data.
Background – Current Table Volumes , (35 % of database) , (6% of database) ,(5% of database)
Strategy ?
Partition Maintenance ?

The Benefits of This Partitioning Strategy 

Maintenance
Purging historic data
Performance- Performance improvements of SQL were achieved through partition pruning.
Read-only partitions-in read only table spaces as this reduced the cost of storage
Types of Partitioning Methods ( Oracle Versions)
Oracle Database 11g provides the most comprehensive partitioning offering in the market, with a broad variety of partitioning techniques and advanced mechanisms.
With Oracle8, Range partitioning (on single column) was introduced.
With Oracle8i, Hash and Composite(Range-Hash) partitioning was introduced.
With Oracle9i, List partitioning and Composite(Range-List) partitioning was introduced.
With Oracle 11g, Interval partitioning, Reference partitioning, Virtual column based partitioning, System partitioning and Composite partitionings [Range-Range, List-List, List-Range, List-Hash, Interval-Range, Interval-List, Interval-Interval] was introduced.
With Oracle 12c, Automatic List-Partitioning introduced.
USEFUL DATABASE VIEWS 
DBA_PART_TABLES |DBA_PART_INDEXES
DBA_TAB_PARTITIONS | DBA_TAB_SUBPARTITIONS
DBA_PART_KEY_COLUMNS | DBA_SUBPART_KEY_COLUMNS
DBA_PART_COL_STATISTICS | DBA_SUBPART_COL_STATISTICS
DBA_PART_HISTOGRAMS | DBA_SUBPART_HISTOGRAMS
DBA_IND_PARTITIONS |DBA_IND_SUBPARTITIONS 
DBA_SUBPARTITION_TEMPLATES

References
Using_Database_Partitioning_with_Oracle_E-Business_Suite.pdf
White-paper:Optimizing Storage for Oracle E-Business Suite Applications
Meta link:Using Database Partitioning with Oracle E-Business Suite
#tuning
11g Partitioning Features
Partitioning in Oracle

1

sqlrpt.sql

sqlrpt.sql (Enhanced for Output Display & Skip Top Session 

SET NUMWIDTH 10

SET TAB OFF

set long 1000000;

set lines 300 pages 300

set longchunksize 1000;

set feedback off;

set veri off;

prompt Specify the Sql id

prompt ~~~~~~~~~~~~~~~~~~

column sqlid new_value sqlid;

set heading off;

select 'Sql Id specified: &&sqlid' from dual;

set heading on;

prompt

prompt Tune the sql

prompt ~~~~~~~~~~~~

variable task_name varchar2(64);

variable err       number;

-- By default, no error

execute :err := 0;

set serveroutput on;

DECLARE

  cnt      NUMBER;

  bid      NUMBER;

  eid      NUMBER;

BEGIN

  -- If it's not in V$SQL we will have to query the workload repository

  select count(*) into cnt from V$SQLSTATS where sql_id = '&&sqlid';

  IF (cnt > 0) THEN

    :task_name := dbms_sqltune.create_tuning_task(sql_id => '&&sqlid',

	                                             scope=> DBMS_SQLTUNE.scope_comprehensive

												 ,time_limit  => 7200);

  ELSE

    select min(snap_id) into bid

    from   dba_hist_sqlstat

    where  sql_id = '&&sqlid';

    select max(snap_id) into eid

    from   dba_hist_sqlstat

    where  sql_id = '&&sqlid';

    :task_name := dbms_sqltune.create_tuning_task(begin_snap => bid,

                                                  end_snap => eid,

                                                  sql_id => '&&sqlid',

												  time_limit  => 7200);

  END IF;

    DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || :task_name);

  dbms_sqltune.execute_tuning_task(:task_name);

EXCEPTION

  WHEN OTHERS THEN

    :err := 1;

    IF (SQLCODE = -13780) THEN

      dbms_output.put_line ('ERROR: statement is not in the cursor cache ' ||

                            'or the workload repository.');

      dbms_output.put_line('Execute the statement and try again');

    ELSE

      RAISE;

    END IF;

END;

/

set heading off;

set lines 300 pages 3000

select dbms_sqltune.report_tuning_task(:task_name) from dual where :err <> 1;

select '   ' from dual where :err = 1;

set heading on;

undefine sqlid;

set feedback on;

set veri on;
0

Let’s look at below in detail

What is SGA/PGA ? 
SGA/PGA Suggestions (Best Practices)
Configuring SHMMAX and SHMALL for Oracle in Linux
Linux Huge Pages for Oracle (For Large SGA On Linux)
Disabling Transparent HugePages 
SGA/PGA Suggestions ( From AWR/ADDM)
SGA/PGA Paremeters ( What is? )
SGA/PGA Details ( From v$views)

sga

SGA is shared memory, so it is allocated when the database is started.
PGA (Process Global Area) is private memory allocated to individual processes,it can’t be pre-allocated,One need basis it will be allocated in RAM

SGA/PGA Suggestions (Best Practices)

OS Reserved RAM                  --> 10% of RAM for Linux & 20% or RAM for Windows
Oracle Database Connections RAM -->pga_aggregate_target(RAM regions for sorting and hash joins)
Oracle SGA Sizing for RAM        --> sga_max_size/sga_target
SGA Allocation (Capacity Planning)
45% of ram size RAM SIZE = 484 MB,
So 45 % should be = 216 MB
-->Split the 216 MM for SGA,PGA,BACKGROUNG PROCESSES
-->Fixed background process requires = 40 MB SGA =160 MB PGA =16 MB

Configuring SHMMAX and SHMALL for Oracle in Linux

Shared memory is important for the Oracle Database System Global Area (SGA).
Shared memory is nothing but part of Unix IPC System (Inter Process Communication) maintained by kernel
where multiple processes share a single chunk of memory to communicate with each other.
If there is insufficient shared memory, the Oracle database instance will not start. Set the SHMMAX and SHMALL to maximum values,for SGA Requirements

shm

SHMALL ==>(40% RAM), total size of Shared Memory Segments System wide set in “pages”.
SHMMAX ==> the maximum size (in bytes) of a single shared memory segment set
If the SHMMAX is set incorrectly, for example too low, you may receive the following error:
ORA-27123: unable to attach to shared memory segment.
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device.
is not relevant when you use /dev/shm POSIX shared memory (Oracle AMM).

Note :

SHMALL is the division of SHMMAX/PAGE_SIZE, e.g:. 1073741824/4096=262144.
Make SHMALL smaller than free RAM to avoid paging.
32-bit servers -3GB
64-bit servers - Half the RAM
Oracle recommends that the SHMALL should be the sum of the SGA regions divided by 4096 (The Linux page size).
-->Oracle 11g uses AMM by default,which relies on POSIX shared memory (/dev/shm-maps shared memory to files using a virtual shared memory filesystem) ,uses 4 KB pages(default PAGE_SIZE in Linux) and set to 50 % of RAM (allocated on demand and can be swapped)
For performance reasons,any Oracle database that uses more than 4 GB of SGA should use kernel Huge Pages.
Note:Linux kernel Huge Pages are setup manually, use 2M (2048 KB) pages, cannot be swapped and are reserved at system start up.
AMM uses either Sys V(virtual) shared memory(ipcs) including kernel hugepages or (POSIX) /dev/shm for SGA, but not both at the same time.
oracle@srinalla-db-1:~ $ grep -i shm /etc/sysctl.conf|grep -v '^#';ls -lrth /proc/sys/kernel/shm*;cd /proc/sys/kernel/;cat shmall;cat shmmni;cat shmmax
kernel.shmall = 154618822656
kernel.shmmax = 154618822656
kernel.shmmni = 4096
-rw-r--r-- 1 root root 0 Dec 21 03:11 /proc/sys/kernel/shmall
-rw-r--r-- 1 root root 0 Dec 21 03:11 /proc/sys/kernel/shmmni
-rw-r--r-- 1 root root 0 Dec 21 03:11 /proc/sys/kernel/shmmax
154618822656
4096
154618822656
oracle@srinalla-db-1:~ $ cat /etc/*-release ;ipcs -l
Red Hat Enterprise Linux Server release 5.11 (Tikanga)
------ Shared Memory Limits --------
max number of segments = 4096  /* SHMMNI  */
max seg size (kbytes) = 150994944 /* SHMMAX  */
max total shared memory (kbytes) = 618475290624 /* SHMALL  */
min seg size (bytes) = 1
------ Semaphore Limits --------
max number of arrays = 4096
max semaphores per array = 256
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767
------ Messages: Limits --------
max queues system wide = 2878
max size of message (bytes) = 8192
default max size of queue (bytes) = 65535

Linux Huge Pages for Oracle (For Large SGA On Linux)

With HugePages, the Linux memory page size is set at 2MB (instead of the default 4K).
This will improve OS performance when running Oracle databases with large SGA sizes.
For 11g,Enable ASMM feature additionally ,Refer more Linux HugePages

The AMM and HugePages are not compatible. One needs to disable AMM on 11g to be able to use HugePages. See Document 749851.1 for further information.
ebsdba@ebsdb-prd-01:~ $ grep Hugepagesize /proc/meminfo
Hugepagesize: 2048 kB
oracle@of3200:~ 
Oracle Linux: Script to find Recommended Linux HugePages (Doc ID 401749.1)
HugePages on Linux: What It Is... and What It Is Not... (Doc ID 361323.1)


Disabling Transparent HugePages 
Starting from RHEL6/OL6, Transparent HugePages are implemented and enabled by default. 
This is causing node reboots in RAC installations and performance problems on both single instance and RAC installations.
Oracle recommends disabling Transparent HugePages on all servers running Oracle databases, as described in this MOS note (Doc ID 1557478.1)
Check setting below
# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
#
SGA/PGA Suggestions ( From AWR/ADDM)
1)Undersized SGA Findings in ADDM Report:
2)SGA/PGA Advice Views select * from v$SGA_TARGET_ADVICE ;
    (Remember, bigger does not necessarily mean better/faster.) 
select * from v$PGA_TARGET_ADVICE;

SGA/PGA Paremeters ( What is? )

SGA

Some parameters to consider in SGA sizing include

SGA_MAX_SIZE --> For Dynamic Allocation set this higher than sga_target SGA_TARGET --> new(in 10g) -->Automatically sizes SGA components dynamic,Can be increased till SGA_MAX_SIZE,Change in value of 
affects only automatically sized components  
the (redo) log buffers, the shared pool, Java pool, streams pool, buffer cache, keep/recycle caches, 
and if they are  specified, the non-standard block size caches.
-------------->Usually, sga_max_size and sga_target will be the same value
DB_CACHE_SIZE - ->RAM buffer for the data buffers DB_XK_CACHE_SIZE SHARED_POOL_SIZE ->RAM buffer for Oracle and the library cache. LARGE_POOL_SIZE -> For RMAN parallel queries & Parallel execution allocates  buffers out of the large pool only when parallel_automatic_tuning=true. 
LOG_BUFFER ->RAM buffer for redo logs

PGA

PGA_AGGREGATE_LIMIT -> (12c),limit on Total PGA per Instance can't be below the PGA_AGGREGATE_TARGET.
** To revert to the pre-12c functionality,set parameter value to "0"

PGA_AGGREGATE_TARGET-->(9i), soft limit on the PGA used by the instance _pga_max_size ->

SGA/PGA Details ( From v$views)

set lines 200 pages 300 
Select Round(sum(bytes)/1024/1024,0) "Total Free memory(MB)" From V$sgastat Where Name Like '%free memory%'; 
Select POOL, Round(bytes/1024/1024,0) "Free memory(MB)" From V$sgastat Where Name Like '%free memory%';
show parameter sga ;
show parameter pga;
select NAME,round(BYTES/(1024*1024*1024),0) as "Size(GB)",round(BYTES/(1024*1024),0) as "Size(MB)" ,RESIZEABLE from v$sgainfo order by 2 desc;
Total Free memory(MB)
---------------------
5117
SQL>
POOL Free memory(MB)
------------ ---------------
shared pool 2143
large pool 706
java pool 1756
streams pool 512
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 100G
sga_target big integer 85G
unified_audit_sga_queue_size integer 1048576
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 50G
pga_aggregate_target big integer 30G
SQL>
NAME Size(GB) Size(MB) RES
-------------------------------- ---------- ---------- ---
Maximum SGA Size 100 102400 No
Buffer Cache Size 70 71936 Yes
Free SGA Memory Available 15 15360
Shared Pool Size 12 11776 Yes
Startup overhead in Shared Pool 5 5078 No
Java Pool Size 2 1792 Yes
Streams Pool Size 1 512 Yes
Large Pool Size 1 768 Yes
In-Memory Area Size 0 0 No
Shared IO Pool Size 0 256 Yes
Data Transfer Cache Size 0 0 Yes
Granule Size 0 256 No
Redo Buffers 0 249 No
Fixed SGA Size 0 7 No
14 rows selected.

References:

Oracle PGA behavior
PGA and SGA sizing on linux
Monitoring Oracle SGA & PGA Memory Changes
Oracle SGA Sizing
Optimal SHMMAX for Oracle
Monitoring SGA (Free Memory) Using v$sgastat
Get confused about how to calculate SHMMAX and SHMALL on Linux.
Configuring SHMMAX and SHMALL for Oracle in Linux
https://docs.oracle.com/database/121/LADBI/app_manual.htm#LADBI7864
Configuring HugePages for Oracle on Linux (x86-64)

#tuning

3

Oracle SQL Plan Changed?

Let’s look at below in detail

Why Execution Plan gets changes? 
How to fix Bad SQL Plan? 
Image flow of SQL Profile & SQL Plan BaseLine
Create SQL Profile ( Considering SQL Tuning Adviser Suggestions)
How to Create SQL Profile ? ( Which script is used?)

How to fix Bad SQL Plan? .

You can fix the SQL Plan by either SQL Profile / SQL Plan Baseline
Let’s take look at what they are and differences?

SQL profiles and SQL plan baselines help improve the performance of SQL statements by ensuring that the optimizer uses only optimal plans.
==>Baselines define the set of execution plans that can be used by each query, 
    SQL Profiles only provide additional information to “push” the optimizer to favor one or another plan,
==>SQL plan baselines are proactive, whereas SQL profiles are reactive.
==>SQL plan baselines reproduce a specific plan, whereas SQL profiles correct optimizer cost estimates.
SQL plan baselines prevent the optimizer from using suboptimal plans in the future.

Image flow of SQL Profile & SQL Plan BaseLine.

sqltsqlbase

Create SQL Profile ( Considering SQL Tuning Adviser Suggestions)

1) Run SQL Tuning Adviser(sqltrpt.sql/OEM) and get the suggestions for available best plan
2) Create SQL Profile using SQLT Script (coe_xfr_sql_profile.sql)
3) Flush the SQL ID from Memory ( Cancel any Jobs w.r to SQL ID,then flush)
4) Now you could see the new plan is reflected ( From gv$sql)

This is best plan available for this SQL ID , we have to force the good plan by SQL Profile creation

PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
2538395789 .437————> not reproducibe
2141716862 100.932———–last seen 2018-12-03/17:00:03 original plan
420475214 4898.131 ========== current Plan

How to Create SQL Profile ? ( Which script is used?)

Using coe_xfr_sql_profile.sql ,we can create it ( No need of Installation ,Just Download SQLTXPLAIN (SQLT),Refer Download SQLT section

SQL >  @coe_xfr_sql_profile.sql
SQL> @/dba/srinalla/scripts/sqlt/sqlt/utl/coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)

Enter value for 1: 8ap1zyhp2q7xd

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2538395789        .437
     2819292650        .599
     3897290700       4.068
     3111005336        4.21
     3446519203         6.4

Parameter 2:

PLAN_HASH_VALUE (required)
Enter value for 2: 2141716862

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "8ap1zyhp2q7xd"
PLAN_HASH_VALUE: "2141716862"

Execute coe_xfr_sql_profile_8ap1zyhp2q7xd_2141716862.sql
on TARGET system in order to create a custom SQL Profile
with plan 2141716862 linked to adjusted sql_text.

Now, You can create the SQL Profile with above Script

SQL> @coe_xfr_sql_profile_8ap1zyhp2q7xd_2141716862.sql
PL/SQL procedure successfully completed.
SQL> WHENEVER SQLERROR CONTINUE
SQL> SET ECHO OFF;
            SIGNATURE
---------------------
   582750245629242078
           SIGNATUREF
---------------------
   582750245629242078
  ... manual custom SQL Profile has been created

<strong><span style="color:#0000ff;">FROM SQL Tuning Advisor, this Can be run from DB Node as below(or OEM Cloud Control)</span></strong>
SQL @?/rdbms/admin/sqltrpt.sql 

Specify the Sql id ~~~~~~~~~~~~~~~~~~ 
Enter value for sqlid: 8ap1zyhp2q7xd 
Sql Id specified: 8ap1zyhp2q7xd 
Tune the sql ~~~~~~~~~~~~
GENERAL INFORMATION SECTION
Tuning Task Name : TASK_313228
 Tuning Task Owner : SYS 
Workload Type : Single SQL Statement 
Scope : COMPREHENSIVE 
Time Limit(seconds): 1800 
Completion Status : COMPLETED 
Started at : 12/10/2018 02:09:31 
Completed at : 12/10/2018 02:16:37

Schema Name: APPS SQL ID : 8ap1zyhp2q7xd 
SQL Text : SELECT /*+ leading (gjl gjh) */ GJL.EFFECTIVE_DATE ACC_DATE,

/
FINDINGS SECTION (3 findings)
1- SQL Profile Finding (see explain plans section below)
 -------------------------------------------------------- 
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 82.16%)
 ------------------------------------------ -
 Consider accepting the recommended SQL profile. 
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_313228',task_owner => 'SYS', replace => TRUE); 
id plan hash last seen elapsed (s) origin note 
-- ---------- -------------------- ------------ --------------- -------------- 
1 3013459780 2018-06-03/23:58:03 2.674 STS not reproducible 
2 3897290700 2018-12-06/14:00:54 7.268 AWR not reproducible 
3 2141716862 2018-12-03/17:00:03 22.321 AWR original plan
 4 1462347114 2018-12-06/17:00:38 50.572 AWR not reproducible 
5 1471114134 2018-12-07/16:00:04 60.236 AWR not reproducibe

3- Alternative Plan Finding
----------------------------------------------
Some alternative execution plans for this statement were found by searching the system's real-time and historical performance data. The following table lists these plans ranked by their average elapsed time. 
See section "ALTERNATIVE PLANS SECTION" for detailed information on each plan.

References

Database SQL Tuning Guide
Using Oracle baselines you can fix the sql plan for a SQLID
How To Improve SQL Statements Performance: Using SQL Plan Baselines
SQL Profiles Vs SQL Plan Baselines? by Girish Kumar
What is the difference between SQL Profiles and SQL Plan Baselines?
One Easy Step To Get Started With SQL Plan Baselines
2 Useful Things To Know About SQL Plan Baselines
Parsing SQL Statements in Oracle

#tuning

2

Performace Tuning (DB/EBS)

perf

How to Start ? 

———————————————————>Course<———
Learn SQL Performance Tuning in this Free Online Course
The Ultimate SQL Tuning Formula by Diana Robete
———————————————————>Course<——–

How do I learn to tune SQL?
Oracle Database 2 Day + Performance Tuning Guide provides an introduction to the principal SQL tuning tools.

http://facedba.blogspot.com/2014/01/performance-tuning-for-beginners.html
https://blogs.oracle.com/sql/category/sql-query-optimization

My Posts    Tuning (WIP)-Private

Oracle SQL Plan Changed?
Configuring Oracle SGA/PGA ?

( Still Writing Notes for below..)
What is Table Fragmentation in Oracle ? ( Finding & Removing)
What is Oracle Partitioning – ( Why ?,How ? &Benefits)
OS Watcher-OSWbb/RDA-Remote Diagnostics Agent/ORAchk – Health Checks
Locks vs Latch/Enqueues & Its Types 

Database Performance ( Article Collection)

Oracle Statistics (All About)–>An Excellent Article about end to end to Statistics for DB/SCHEAMA/object level
DBMS_ADVISOR–>Details of How to execute SQL access advisor (DBMS_ADVISOR)?
DB Performance Toolkit –> Overview of Performance tools (AWR/ADDM/ASH,SQLT & SQLHC) & What data you need to collect for Performance Issues for Oracle SR?
Performace Tuning DB level->Collection of articles related to DB Time,Hints ,Execution plans,Shared pool,WAIT EVENTS,Latch Issues & Sql Trace & Autotrace

================ Newly Added on 01-JAN-2019=================== (Gavin Soorma(Oracle ACE) Articles)

Oracle Wait Events Causes And Resolutions->Just going through
Why Do My Execution Plans Not Change After Gathering Statistics?->Just going through
Performance Tuning Tips And Techniques->Just going through
ASH And AWR Performance Tuning Scripts->Just going through
Using The Awr History Tables To Compare Performance – Part 1->Just going through
Using The Awr History Tables To Compare Performance – Part 2>->Just going through
LOB’S – SOME PERFORMANCE TUNING CONSIDERATIONS>->Just going through
DBMS_ADVANCED_REWRITE – TUNING WITHOUT TOUCHING THE CODE>->Just going through

EBS Performance ( Article Collection)

Gathering EBS Database Statistics
Recommended Database Parameters for EBS
Approach to Performance Tuning Oracle Apps
Webcast:EBS performance tuning
Troubleshooting Performance Issues??
Debugging ??
Database Partitioning-EBS
Tuning the EBS Database Layer
Tuning All Layers of EBS
Queries for Troubleshooting Adv Typologies
Pinning Objects to Improve Apps Performance
SR Data for Perf Issue

#tuning

3

Perf Tuning/ Trace/Gather Stats

How to Use AWR Reports to Diagnose Database Performance Issues (Doc ID 1359094.1)
https://carlos-sierra.net/tag/execution-plan/
http://kerryosborne.oracle-guy.com/category/oracle/plan-stability/
https://mauro-pagano.com/2015/05/04/historical-sql-monitor-reports-in-12c/
https://community.oracle.com/thread/3269017 SQL review from an DBA perspective

Enable Trace and Generate TKPROF
SELECT p.PID, p.SPID, s.SID,s.serial# FROM v$process p, v$session s WHERE s.paddr = p.addr AND s.SID = 2414 ;
PID SPID SID

552 4999 2414

ORADEBUG SETOSPID 5382
ORADEBUG UNLIMIT
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER,LEVEL 12
ORADEBUG TRACEFILE_NAME

SQL> ORADEBUG SETOSPID 5382
Oracle pid: 366, Unix process pid: 2244, image: oracle@123.nus.sri.com
SQL> ORADEBUG UNLIMIT
Statement processed.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER,LEVEL 12
Statement processed.
SQL> ORADEBUG TRACEFILE_NAME
/u01/app/db_1/diag/rdbms/SR12/SR/trace/SR_ora_2244.trc
SQL>
  1. Turn off the SQL trace for the session. 
    SQL> ORADEBUG SETOSPID 5382
SQL>oradebug event 10046 trace name context off

Others:
—————-
alter system set events ‘sql_trace[sql: cjrha4bzuupzf] level=12’;
alter system set events ‘sql_trace[sql: cjrha4bzuupzf] off’;

tkprof aa.trc aa.tkprof explain=apps/ZW48Acbrtkmts sort=prsela,exeela,fchela sys=no

SQLID TEXT AND Plan Details

SELECT inst_id,sql_id,LAST_LOAD_TIME, LAST_ACTIVE_TIME ,PLAN_HASH_VALUE,SQL_FULLTEXT,ROWS_PROCESSED,EXECUTIONS,sql_plan_baseline,sql_profile
FROM   gV$SQl a
WHERE  a.sql_id ='7ttgy18puazt6'
ORDER BY LAST_ACTIVE_TIME desc;

–> awr_high_cost_sql.sql

select p.sql_id,p.PLAN_HASH_VALUE,p.TIMESTAMP,p.cost
from dba_hist_sql_plan p,dba_hist_sqltext s
where p.id = 0 and p.sql_id = s.sql_id
and p.sql_id='8ap1zyhp2q7xd'
order by p.TIMESTAMP desc;

Select * from table(dbms_xplan.display_cursor(‘9w6zqap3f5xbw’));
Select * from table(dbms_xplan.display_awr(‘9w6zqap3f5xbw’));
Select * from table(dbms_xplan.display_awr(‘9w6zqap3f5xbw’,’2095498791′));
Select * from table(dbms_xplan.display_awr(‘9w6zqap3f5xbw’,’2716887413′));

@?/rdbms/admin/sqltrpt.sql–> SQL Tuning Advisor
coe_xfr_sql_profile.sql – -> To Create SQL Profile
@?/rdbms/admin/awrrpti.sql – -> To Create AWR by Node
@?/rdbms/admin/awrrpt.sql – -> To Create AWR by DB
@?/rdbms/admin/awrsqrpt.sql – -> To Create AWR by SQL
@?/rdbms/admin/ashrpt.sql – -> To Create ASH
@?/rdbms/admin/utlrp.sql – -> Recompile Invalids

select object_name,owner,created,status,object_type,’/*’ || created || ‘*/’ || dbms_metadata.get_ddl(‘INDEX’,object_name,owner)
from dba_objects
where object_type = ‘INDEX’ and object_name in
(select INDEX_NAME from dba_indexes where table_name in (‘GL_PERIOD_STATUSES’))
order by created desc, object_name;

select * from DBA_IND_COLUMNS where table_name=’GL_PERIOD_STATUSES’;

–> ADDMM Suggestin

Set pages 1000
Set lines 75
Select a.execution_end,a.OWNER,a.TASK_NAME,a.DESCRIPTION,a.ADVISOR_NAME, b.type, b.impact, d.rank, d.type,
'Message           : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message    : '||c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,
Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
--And a.task_name like 'ADDM%'
and a.status='COMPLETED'
and trunc(a.execution_end)>sysdate -1
Order by a.execution_end,b.impact, d.rank;

Gather Stats /Stale :

select OWNER,table_name,stale_stats,last_analyzed,NUM_ROWS,blocks,OBJECT_TYPE from
dba_tab_statistics where table_name=’WF_ITEMS’;

SELECT ob.owner, ob.object_name, ob.object_type, savtime,analyzetime,
DECODE(rowcnt,0,0,round((samplesize/rowcnt)*100,0)) "Sample(%)",
flags, rowcnt, blkcnt, avgrln ,samplesize
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE 1=1
and object_name='WF_ITEMS'
and object_type in ('TABLE')
and object_id=obj#
and  savtime > SYSDATE - 30
order by savtime desc;

select ‘exec fnd_stats.gather_table_stats(”’||owner||”’,”’||table_name||”’,percent=>25,degree=>8,cascade=>true);’ ” GSS”
from dba_tables where table_name in (‘WF_ITEMS’);
set lines 300 pages 300
select ‘exec DBMS_STATS.GATHER_TABLE_STATS(ownname =>”’||owner||”’,tabname =>”’||table_name||”’,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>8,cascade=>true);’ ” GSS”
from dba_tables where table_name in (‘WF_ITEMS’);

SELECT owner,
segment_name,
segment_type,
tablespace_name,
round(bytes/1048576/1024,2) GB,
initial_extent,
next_extent,
extents,
pct_increase
FROM
DBA_SEGMENTS
WHERE
SEGMENT_TYPE = ‘TABLE’
AND SEGMENT_NAME in (‘HR_LOCATIONS_ALL_TL’,’HR_LOCATIONS_ALL’);

Select * from DBA_TAB_MODIFICATIONS  where table_name='WF_ITEMS';
SELECT m.table_owner
, m.table_name
, t.last_analyzed,m.timestamp
, m.inserts
, m.updates
, m.deletes
, t.num_rows
, round(( m.inserts + m.updates + m.deletes ) / CASE WHEN t.num_rows IS NULL OR t.num_rows = 0 THEN 1 ELSE t.num_rows END,0) "Change Factor"
FROM dba_tab_modifications m
, dba_tables t
WHERE t.owner = m.table_owner
AND t.table_name = m.table_name
AND m.inserts + m.updates + m.deletes > 1
AND m.table_name='WF_ITEMS'
and round(( m.inserts + m.updates + m.deletes ) / CASE WHEN t.num_rows IS NULL OR t.num_rows = 0 THEN 1 ELSE t.num_rows END,0) >1
ORDER BY "Change Factor" DESC;
select trunc(last_analyzed),count(*)
from dba_tables
where owner  not in ('SYS','SYSTEM')
group by trunc(last_analyzed)
order by 1 desc;
0

Blog at WordPress.com.

Up ↑

%d bloggers like this: