PERFORMANCE TUNING ORACLE DATABASE
===============================
LONG RUNNING SQL Query SOLUTION:
===============================
* Is there any changes did in I/O Hardware/network/database parameters
* Check CPU, RAM usage and also rman backups and export import activities are running on cpu.
* Is there any stale statistics (missing stats) gather fresh stats
* Check execution plan and plan hash value changed
* Indexes are used or not verify
* Generate AWR report and verify any wait events, locks are holding in database
* Cretae ADDM/sqltuning advisory for sql_id and implement the recommendations
1)Identify the Query: Determine which query is running for a long time. You can use Oracle Enterprise Manager, SQL Developer,
or query the V$SQL or V$SESSION views to find the SQL statement causing the slowdown.
2)Analyze Execution Plan: Use the EXPLAIN PLAN command, SQL Developer's Explain Plan feature, or Oracle's SQL Tuning Advisor to analyze
the execution plan of the query. Look for potential bottlenecks such as full table scans, inefficient index usage, or suboptimal join methods.
3)Check Indexes: Ensure that appropriate indexes exist for the tables involved in the query. Missing or improperly designed indexes can
significantly impact query performance. Consider creating, dropping, or rebuilding indexes as necessary based on the query execution plan.
4)Optimize SQL: Review the SQL statement for optimization opportunities. Rewrite the query to use more efficient constructs, eliminate
unnecessary subqueries, or add hints to guide the optimizer. Consider using Oracle's SQL Tuning Advisor to automatically suggest improvements.
5)Statistics: Ensure that table and index statistics are up to date. Use the DBMS_STATS package or Oracle Enterprise Manager to gather
statistics on tables and indexes involved in the query. Accurate statistics help Oracle's optimizer make better decisions when generating
execution plans.
6)Memory Allocation: Check the memory allocation for the Oracle instance. Adjust parameters such as SGA_TARGET, PGA_AGGREGATE_TARGET,
and SORT_AREA_SIZE to ensure that sufficient memory is available for query processing, sorting, and hashing operations.
7)Concurrency and Locking: Investigate potential locking and concurrency issues. Use Oracle's Automatic Lock Management features to minimize
contention and improve concurrency. Check for long-running transactions or locks that may be blocking the query.
8)I/O Performance: Monitor disk I/O performance using Oracle's Automatic Workload Repository (AWR) or Operating System utilities. Ensure
that data files, redo logs, and control files are distributed across disks for optimal I/O throughput. Consider using Oracle's Automatic
Storage Management (ASM) for simplified storage management and improved performance.
9)Query Caching: Consider enabling query result caching for frequently executed queries. This can reduce the execution time of subsequent
executions by caching the results in memory.
10)Review System Configuration: Check the system configuration, including CPU, memory, and network resources. Ensure that the database server
has sufficient resources to handle the workload efficiently.
===================================
⏺top 10 wait events in awr reports:
===================================
The **Automatic Workload Repository (AWR)** report in Oracle Database provides insights into the system's performance, including the top wait events. Below are the **top 10 common wait events** often observed in AWR reports:
### 1. **DB File Sequential Read**
- **Description:** Indicates single-block reads from datafiles, usually for index lookups.
- **Cause:** Poor index design, excessive indexed reads, or slow I/O subsystem.
- **Resolution:** Optimize index usage, tune queries, or improve storage performance.
---
### 2. **DB File Scattered Read**
- **Description:** Represents multi-block reads, often for full table scans.
- **Cause:** Lack of proper indexing or inefficient queries causing full table scans.
- **Resolution:** Use proper indexing, optimize queries, and consider table partitioning.
---
### 3. **Log File Sync**
- **Description:** Time taken to synchronize the redo log buffer to disk when a transaction commits or rolls back.
- **Cause:** High commit frequency or slow disk I/O for redo logs.
- **Resolution:** Tune application commits, improve redo log performance, or use fast storage.
---
### 4. **Log File Parallel Write**
- **Description:** Time spent writing redo log entries from the log buffer to redo log files.
- **Cause:** Slow I/O for redo log files.
- **Resolution:** Place redo logs on faster storage, tune I/O, or use multiple redo log members.
---
### 5. **Enq: TX – Row Lock Contention**
- **Description:** Indicates contention for row-level locks.
- **Cause:** Multiple sessions attempting to modify the same rows.
- **Resolution:** Resolve application-level contention, tune transactions to avoid lock contention.
---
### 6. **Enq: TM – Contentions**
- **Description:** Table-level locks due to foreign key constraints or DML blocking operations.
- **Cause:** Unindexed foreign key columns or long-running DML operations.
- **Resolution:** Index foreign keys and optimize DML statements.
---
### 7. **DB File Parallel Write**
- **Description:** Time spent writing dirty buffers from the buffer cache to datafiles.
- **Cause:** High buffer cache activity or slow I/O.
- **Resolution:** Improve storage subsystem or optimize buffer cache.
---
### 8. **Control File Sequential Read**
- **Description:** Single-block read requests from the control file.
- **Cause:** High control file access or poor I/O performance.
- **Resolution:** Ensure control files are on fast storage or reduce access.
---
### 9. **Direct Path Read/Write**
- **Description:** Direct read or write operations bypassing the buffer cache.
- **Cause:** Queries involving large sorts, parallel operations, or temporary tablespaces.
- **Resolution:** Tune SQL queries, optimize parallel operations, or use sufficient temporary tablespace.
---
### 10. **Buffer Busy Waits**
- **Description:** Occurs when a session waits for access to a data block in the buffer cache.
- **Cause:** Contention on specific buffers due to hot blocks or poor schema design.
- **Resolution:** Reduce contention by partitioning, tuning queries, or spreading I/O load.
---
### General Tips to Address Wait Events:
- **Analyze AWR/ASH Reports:** Identify the most significant wait events and their impact.
- **Tune SQL:** Focus on high-resource-consuming queries.
- **Optimize I/O:** Place critical files (redo logs, control files) on fast storage.
- **Scale Resources:** Ensure adequate CPU, memory, and storage bandwidth.
- **Index Properly:** Use efficient indexing strategies to reduce unnecessary full table scans.
Let me know if you need further assistance!
===============
AWR Analysis :
===============
An AWR (Automatic Workload Repository) report is a detailed performance report generated by Oracle databases that provides insights into
the database's workload, performance characteristics, and system health over a specified time period. AWR reports are typically used for
performance tuning, troubleshooting, and capacity planning purposes. Here's what you need to check in an AWR report:
1)Database Configuration: Review the database configuration settings, including initialization parameters, memory allocation, and storage configuration.
Ensure that the configuration is appropriate for the workload and resources available on the server.
2)System Statistics: Check the system-level statistics, such as CPU utilization, memory usage, and I/O activity. Look for any bottlenecks or resource
contention issues that may be impacting database performance.
3)Wait Events: Analyze the wait events section to identify the top wait events affecting database performance. Common wait events include I/O waits,
latch waits, and CPU waits. Addressing the most significant wait events can help improve overall database performance.
4)SQL Statistics: Review SQL statistics to identify the top SQL statements consuming CPU, I/O, or memory resources. Look for poorly performing
SQL statements, long-running queries, or high resource-consuming operations that may need optimization.
5)SQL Tuning Recommendations: Check for SQL tuning recommendations provided in the AWR report. Oracle may suggest creating indexes, rewriting
SQL queries, or adjusting optimizer settings to improve SQL performance.
6)Buffer Cache Statistics: Analyze buffer cache statistics to assess buffer cache efficiency and identify potential cache-related issues.
Look for buffer busy waits, buffer hit ratios, and cache usage patterns.
7)PGA and Temporary Space Usage: Review PGA (Program Global Area) and temporary space usage to ensure efficient memory allocation and usage.
Monitor PGA memory usage, sorts, and temporary space allocation to avoid performance issues related to memory management.
8)Instance Efficiency Percentages: Evaluate instance efficiency percentages, such as database time ratio and parse time ratio, to gauge overall
database efficiency and resource utilization.
9)Concurrency and Contention: Check for contention-related metrics, such as enqueue waits, latch contention, and library cache contention.
Addressing contention issues can improve database concurrency and scalability.
10)Performance Baselines: Compare the current AWR report with previous reports to identify performance trends, anomalies, or deviations.
Establish performance baselines to track changes over time and identify areas for improvement.
=================
1. DB Time
Definition:
DB Time is the total time spent by Oracle database processes on user calls, including both CPU time and wait time.
It is the cumulative time consumed by database processes serving user requests.
Scope:
Specific to database activity.
It includes time spent on SQL execution, parsing, fetching rows, waiting for I/O, and other activities related to user queries.
Formula: DB Time = CPU Time + Wait Time
Key Points:
DB Time is reported in seconds but may represent cumulative time across multiple sessions.
If 10 sessions each spend 1 second executing SQL, the DB Time is 10 seconds.
A high DB Time relative to Elapsed Time indicates heavy database activity or inefficiencies.
Use Case:
Useful for identifying database workload and bottlenecks.
A high DB Time might suggest excessive waits, poorly tuned SQL, or CPU contention.
2. Elapsed Time
==============
Definition:
Elapsed Time is the wall-clock time taken to complete a task, query, or workload.
It measures the start-to-end time, regardless of how many sessions or threads are involved.
Scope:
Broader than DB Time.
Represents the total duration as perceived externally, including all time spent inside and outside the database.
Key Points:
Elapsed Time includes both database and non-database time.
For example, it covers time waiting for user input, network delays, or application processing outside the database.
Use Case:
Useful for end-to-end performance analysis.
Helps measure overall system responsiveness or throughput.
======================
1)top 5 Waits section:
Critical to look closely at this section
Use highest wait times to guide this section
DB File type waits - physical IO
BUFFER type waits - Logical IO
LOG type waits - Redo related
PX - Parallel Query
GC - Global Cache (RAC related)
2)CPU and Memory:
Watch for number of CPU's
Pay attention to changes in Memory size
An idle CPU can be a bad thing
Always look at IO Wait verses CPU Usage
If the system is IO bound CPU will be idle
RAC Specific Sections when you have Real Application Cluster RAC
Shows health of Global Cache (GC)
Shows health of Global Enqueue (GES)
Show health of interconnect (Latency, Send and Receive times)
1 gb interconnect = 100 MB/sec (approx)
3)Global Cache and Enqueue Workload:
Watch for Timings
If interconnect latency>IO subsystem latency, RAC is a bottleneck
Components of time can show where issue is located
4)Time Model Statistics:
In early version we have to do this manually and some statpack wont have this
Shows where system is spending its time
Generally we except SQL processing time high, parsing and other stuff low
IF SQL time>DB CPU time then probably have IO issues
5)Operating System Statistics:
What you get here will get depending on OS that we use
Some may not give IO timings
6)Foreground Wait Events:
Foreground = user processes
This is the most important stat
Usual source for top 5 wait events
2 sections - classes and events
Classes are the rolled up sums for waits
7)Background Wait Events:
SMON, PMON, DBWR, LMON, LMS etc process waits
This is not usually a big contributor
Types of waits should correspond to foreground waits.
=================
Execution plan :
===============
The EXECUTION PLAN and the EXPLAIN PLAN in Oracle are related but distinct concepts used for understanding and analyzing how a SQL query will be or is being executed by the Oracle database. Here's a breakdown of their differences:
1.Execution Plan
===============
Definition: The execution plan represents the actual steps taken by the Oracle database to execute a query. It is the real plan used by the optimizer at runtime.
Characteristics:
Shows the actual execution details of the query.
Can include runtime statistics such as the number of rows processed, elapsed time, and I/O costs.
To view it, you typically use the DBMS_XPLAN.DISPLAY_CURSOR function after running the query with SQL trace or enabling statistics gathering.
More accurate because it reflects the optimizer's decision at execution time, considering all dynamic runtime factors like bind variables or data skew.
Use Case:
Debugging performance issues.
Verifying how the query is actually being executed.
Analyzing the impact of runtime conditions (e.g., current statistics, parameter values).
Example :
=========
Execution Plan:
SQL> SELECT /*+ gather_plan_statistics */ * FROM emp;
EMP_ID EMP_NAME JOB_TITLE HIRE_DATE SALARY DEPT_ID
---------- -------------------------------------------------- ------------------------------ --------- ---------- ----------
102 Jane Smith Analyst 01-FEB-23 50000 20
103 Sam Wilson Developer 10-MAR-23 60000 10
104 Lisa Brown Tester 20-APR-23 45000 30
105 Michael Green Developer 25-MAY-23 65000 10
106 Michael Green Developer 25-MAY-23 65000 10
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
SQL_ID 2wy8w798r81rv, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM emp
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS FULL| EMP | 1 | 5 | 5 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------
13 rows selected.
2. EXPLAIN PLAN
================
Definition: The EXPLAIN PLAN command shows a predicted or estimated execution plan generated by the optimizer before the query is executed.
Characteristics:
Does not require the query to run.
Outputs a theoretical execution plan based on current object statistics and optimizer parameters.
To view it, you can use the DBMS_XPLAN.DISPLAY function after running the EXPLAIN PLAN FOR command.
May differ from the actual execution plan due to runtime factors like dynamic sampling, adaptive execution, or bind variable peeking.
Use Case:
Analyzing potential query performance before execution.
Educating on how the optimizer plans to execute the query.
ex :
EXPLAIN PLAN:
ESQL> EXPLAIN PLAN FOR SELECT * FROM emp;
Explained.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 200 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 5 | 200 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
By comparing the two, you can identify discrepancies and refine query performance.
set autotrace on
select * from emp;
==========================
Wait events vs wait classes - star method
==========================
Did you know that all the wait events inside Oracle database are categorized into wait classes? Every wait event always belongs to a wait class.
Even before you learn more about each and every wait event, you must be aware of wait classes.
Wait Classes in Oracle
All the wait events are grouped under wait classes and here are the most important wait classes you must know.
Administrative: Waits resulting from DBA commands that cause users to wait (for example, an index rebuild)
Application: Waits resulting from user application code (for example, lock waits caused by row level locking or explicit lock commands)
Cluster: Waits related to Real Application Cluster resources (for example, global cache resources such as gc cr block busy
Commit: This wait class only comprises one wait event – wait for redo log write confirmation after a commit (that is, ‘log file sync’)
Concurrency: Waits for internal database resources (for example, latches)
Configuration: Waits caused by inadequate configuration of database or instance resources (for example, undersized log file sizes, shared pool size)
Idle: Waits that signify the session is inactive, waiting for work (for example, ‘SQL*Net message from client’)
Network: Waits related to network messaging (for example, ‘SQL*Net more data to dblink’)
Other: Waits which should not typically occur on a system (for example, ‘wait for EMON to spawn’)
Scheduler: Resource Manager related waits (for example, ‘resmgr: become active’)
System I/O: Waits for background process IO (for example, DBWR wait for ‘db file parallel write’)
User I/O: Waits for user IO (for example ‘db file sequential read’)
Find Top Wait Classes
====================
User below query to get the top wait classes in Oracle database
Select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits)
Sum_Waits
From v$system_wait_class
Group by wait_class
Order by 3 desc;
WAIT_CLASS SUM(TIME_WAITED) SUM_WAITS
-------------------- ---------------- ----------
Idle 302785 61.8433415
Other 6685 7.9112426
Configuration 12 4
Commit 45 3.75
Concurrency 512 1.18793503
User I/O 5245 .748430365
System I/O 502 .362716763
Network 0 0
Administrative 0 0
9 rows selected.
Find Top Wait Events in a Wait Class
====================================
From the above query, supply each wait class into below query to get the top wait events in database with respect to particular wait class
Select a.event, a.total_waits, a.time_waited, a.average_wait
From v$system_event a, v$event_name b, v$system_wait_class c
Where a.event_id=b.event_id
And b.wait_class#=c.wait_class#
And c.wait_class = '&Enter_Wait_Class'
order by average_wait desc;
Enter value for enter_wait_class: Commit
old 5: And c.wait_class = '&Enter_Wait_Class'
new 5: And c.wait_class = 'Commit'
EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- ----------- ------------
log file sync 14 45 3.25
===================
LATCHES IN ORACLE:
==================
Latches in Oracle are a type of low-level locking mechanism used to protect data structures in the System Global Area (SGA).
Monitoring and diagnosing latch contention can be crucial for performance tuning.
=>VIEW =V$LATCH;
SELECT name, gets, misses, sleeps
FROM v$latch;
=>The V$LATCHHOLDER view shows which session holds which latch.
select name,pid from v$latchholder;
=>The V$LATCHNAME view provides the names of the latches.
SELECT latch#, name
FROM v$latchname;
=>The V$LATCH_MISSES view provides detailed statistics about latch misses and sleeps.
SELECT parent_name, where, misses, sleeps
FROM v$latch_misses;
=>The V$LATCH_CHILDREN view provides information about individual child latches if a latch has multiple children.
SELECT name, latch#, child#, gets, misses, sleeps
FROM v$latch_children;
LATCH MANAGING
==============
1. Identify Latch Contention
First, identify the latch causing contention using the methods mentioned earlier:
Query V$LATCH and V$LATCH_CHILDREN to identify latches with high misses and sleeps.
Generate AWR reports to get a detailed overview of latch activity.
Monitor wait events in V$SESSION or V$SYSTEM_EVENT views for latch-related waits.
2. Understand the Latch Type
Different latches protect different parts of the Oracle memory structures. Common latches include:
Shared Pool Latch: Protects the shared pool area.
Cache Buffers Chains Latch: Protects the buffer cache.
Redo Allocation Latch: Protects the redo log buffer allocation.
Understanding the type of latch can help determine the appropriate corrective actions.
3. Addressing Specific Latch Contention
Increase Shared Pool Size: Increasing the size of the shared pool can reduce contention
Pin Frequently Used Objects: Pinning frequently accessed objects in the shared pool can reduce latch contention.
Reduce Hot Blocks: Identify and reduce hot blocks by spreading out I/O operations or partitioning tables.
Tune Buffer Cache: Increase the buffer cache size if needed
Optimize Redo Log Buffer: Increase the redo log buffer size
Batch Commits: Reduce the frequency of commits in the application to lower the load on the redo log buffer.
======================
Automatic SQL Tuning in Oracle
========================
Database optimizer runs very fast and must select the best execution plan for a query within a fraction of seconds.Due to time constraint
(under normal query execution), sometimes optimizer will choose wrong execution plan for a query. We can force to run database optimizer
to run a tuning mode so that optimizer can perform more analysis on a given SQL.
Some of the analysis performed by the optimizer when it is running in tuning mode are
Statistical analysis
Access path analysis
SQL structure analysis
CHECKING SQLID OF THE TEXT -
SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '%select * from emp where value=12;%';
->
SQL_ID SQL_TEXT
------------- ----------------------------------------
9jkzmjfrgg5jd SELECT sql_id, sql_text FROM v$sql WHERE
sql_text LIKE '%select * from emp where
value=12;%'
Create tuning task
===================
First of all get the sql id of the statement that you would like to tune. You can get this information via AWR report or other database reports
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'ap9t6jhyrf4zp',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'fxdbrc4jhqn5r_tuning_task',
description => 'Tuning task for statement ap9t6jhyrf4zp.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
Execute tuning task
=================
Once the task is defined, you must execute it
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'fxdbrc4jhqn5r_tuning_task');
Status of tuning task
======================
Once you execute the tuning task, you should be able to find the status of the task
SELECT task_name, status
FROM dba_advisor_log
WHERE task_name like 'fxdbrc4jhqn5r_tuning_task';
TASK_NAME STATUS
------------------------------ -----------
fxdbrc4jhqn5r_tuning_task COMPLETED
Display recommendations
==================
Once the tuning task is executed successfully, the recommendations can be displayed using below
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('fxdbrc4jhqn5r_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24
RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : fxdbrc4jhqn5r_tuning_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 12/24/2024 00:23:17
Completed at : 12/24/2024 00:23:18
-------------------------------------------------------------------------------
Schema Name : SYS
Container Name: CDB$ROOT
SQL ID : ap9t6jhyrf4zp
SQL Text : SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '% 2 3 select *;%'
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
Drop tuning task
===============
Once you get the recommendations, you can drop the tuning task
BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => 'fxdbrc4jhqn5r_tuning_task');
END;
/
=================
AWR REPORT -
===============
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
awrrpt_1_125_126.html
=================
Oracle ADDM Report
=================
It analysis AWR report to identify potential performance bottle neck.
For each identified issue, it locates the root cause and provides recommendations.
It is created and stored every time AWR report is run provided STATISTICS_LEVEL parameter is set to TYPICAL or ALL.
To create ADDM Report
@$ORACLE_HOME/rdbms/admin/addmrpt.sql
Sample output of ADDM report:
FINDING: 59% impact (944 seconds)
The buffer cache was undersized causing significant additional read I/O.
RECOMMENDATION 1: DB Configuration, 59% benefit (944 seconds)
ACTION: Increase SGA target size by increasing the value of parameter “sga_target” by 28 M.
SYMPTOMS THAT LED TO THE FINDING:
Wait class User I/O was consuming significant database time. (83% impact [1336 seconds])
==========================
Remove Table Fragmentation in Oracle
=================================
Fragmentation causes database slowness and un-necessary storage space wastage. In this article I will show you how to find if a table is fragmented and then remove table fragmentation in Oracle.
Gather Table Stats
Check Table Size
Remove Fragmentation
Gather Table Stats
==================
If you have recently gathered table stats, then skip to next step, else gather table stats
EXEC dbms_stats.gather_table_stats(ownname => 'sbz', tabname => 'sbz.temp', cascade => true, estimate_percent => 10,method_opt=>'for all
indexed columns size 1', granularity => 'ALL', degree => 1);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SYS',
tabname => 'EMP',
cascade => TRUE,
estimate_percent => 10,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1',
granularity => 'ALL',
degree => 1
);
END;
/
PL/SQL procedure successfully completed.
Gather Statistics for All Tables in a Schema
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'SCHEMA_NAME',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);
END;
/
Gather Statistics for All Tables in the Database
BEGIN
DBMS_STATS.GATHER_DATABASE_STATS(
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);
END;
/
Verify stats gathered :
SELECT
table_name,
num_rows,
blocks,
sample_size,
last_analyzed
FROM
user_tab_statistics
WHERE
table_name = 'EMP';
TABLE_NAME NUM_ROWS BLOCKS SAMPLE_SIZE LAST_ANAL
---------- ---------- ----------- ---------
EMP 6 1 6 24-DEC-24
Check STATISTICS_LEVEL Parameter
Ensure the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL for automatic statistics gathering.
SHOW PARAMETER statistics_level;
ANALYZE TABLE SYS.EMP COMPUTE STATISTICS;
Check Table Size
================
Use below query to get table size from dba_segments
SELECT SUM(bytes) / 1024 / 1024 AS size_mb
FROM dba_segments
WHERE segment_name = 'EMP';
SIZE_MB
----------
573.5
Now find out actual table size, fragmentation size and fragmentation percentage using below query
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables WHERE table_name='SEG_TABLE';
TABLE_NAME AVG_ROW_LEN TOTAL_SIZE ACTUAL_SIZE FRAGMENTED_SPACE percentage
-------------------- ----------- -------------------- -------------------- -------------------- ----------
SEG_TABLE 4006 1140.84MB 278.94Mb 861.9MB 75.548193
Note: Proceed with de-fragmentation only if you see percentage above 20%
Remove Fragmentation
Use below query to remove fragmentation from the table
alter table SEG_TABLE move;
Rebuild Indexes on Table
new index creation
(SQL> create index new_seg on seg_table(ID,TEXT);
CREATE INDEX SEG_INDEX ON seg_table (SUBSTR(ID, 1, 100), text);
alter index new_seg rebuild online;
Gather Stats Again
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SYS',
tabname => 'SEG_TABLE',
cascade => TRUE,
estimate_percent => 10,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1',
granularity => 'ALL',
degree => 1
);
END;
/
Re-check fragmented space again
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables WHERE table_name='seg_table';
--------------------------------------
INDEXING SIZE CHANGES WHILE REBUILDING THE INDEX
---------------------------------------
SQL> analyze index t_idx validate structure;
Index analyzed.
SQL> select lf_rows, del_lf_rows, lf_blks, (lf_rows-del_lf_rows)/lf_blks avg_rows_per_block from index_stats;
LF_ROWS DEL_LF_ROWS LF_BLKS AVG_ROWS_PER_BLOCK
---------- ----------- ---------- ------------------
291028 41028 1184 211.148649
SQL> select sum(bytes)/1048576 from user_segments where segment_name='T_IDX';
SUM(BYTES)/1048576
------------------
10
SQL> insert into t
select (level+500000)+0.3 l
from dual
connect by level <= 250000; 2 3 4
250000 rows created.
SQL> commit;
Commit complete.
SQL> select l from t where rownum < 10;
L
----------
583204.3
130482.2
583205.3
130484.2
583206.3
130486.2
583207.3
130488.2
583208.3
9 rows selected.
SQL> analyze index t_idx validate structure;
Index analyzed.
SQL> select lf_rows, del_lf_rows, lf_blks, (lf_rows-del_lf_rows)/lf_blks avg_rows_per_block from index_stats;
LF_ROWS DEL_LF_ROWS LF_BLKS AVG_ROWS_PER_BLOCK
---------- ----------- ---------- ------------------
540934 40934 1716 291.375291
SQL> select sum(bytes)/1048576 from user_segments where segment_name='T_IDX';
SUM(BYTES)/1048576
------------------
14
SQL> alter index T_IDX rebuild online;
Index altered.
SQL> select sum(bytes)/1048576 from user_segments where segment_name='T_IDX';
SUM(BYTES)/1048576
------------------
10
=======================
Oracle Segment Advisor
=======================
When there are lot updates, deletes inside database, it creates lot of empty pockets of space that are not large enough to insert new data.
We call this type of empty space as fragmented free space. Database performance can be impacted by such fragmented space.
The process of combining fragmented space into one big free space is known as de-fragmentation.
Create test tablespace
We will create a separate tablespace to store new table for this activity
CREATE TABLESPACE seg_tbs DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/seg_tbs.dbf' SIZE 2G;
Create test table & index
Let us create a test table, insert some records and create an index into the above tablespace
Note: you can use this method to create new tables into a different tablespace even though the default tablespace of a user is different.
CREATE TABLE seg_table(id NUMBER, text VARCHAR2(4000)) tablespace seg_tbs;
Insert some dummy 100K records inside the table
SET TIMING ON
BEGIN
FOR I IN 1..100000
LOOP
INSERT INTO seg_table VALUES (I, DBMS_RANDOM.STRING('P','4000'));
COMMIT;
END LOOP;
END;
/
Create index on table
CREATE INDEX seg_index ON seg_table(TEXT) tablespace seg_tbs;
Analyze table & index
==========================
Gather table and index stats
ANALYZE TABLE seg_table COMPUTE STATISTICS;
ANALYZE INDEX seg_index COMPUTE STATISTICS;
Let us check the size of each segment (table and index)
set lines 999;
col segment_name for a20;
SELECT SEGMENT_TYPE, SEGMENT_NAME, ROUND(BYTES/1024/1024,1) MB, BLOCKS,
EXTENTS FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN ('SEG_TABLE','SEG_INDEX');
SEGMENT_TYPE SEGMENT_NAME MB BLOCKS EXTENTS
------------------ -------------------- ---------- ---------- ----------
TABLE SEG_TABLE 600 76800 146
INDEX SEG_INDEX 600 76800 146
Elapsed: 00:00:00.51
Delete some rows
================
Let us delete half of the rows from the table. Note, the below procedure is deleting alternate rows from the table which will create lot of fragmented space
SELECT COUNT(*) FROM SEG_TABLE;
SET TIMING ON
BEGIN
FOR I IN (SELECT id FROM seg_table)
LOOP
IF MOD(I.id,2) != 0
THEN
DELETE seg_table WHERE id=I.id;
COMMIT;
END IF;
END LOOP;
END;
Analyze table & index again
==========================
Now that we have delete half of the rows, let us gather stats for table and index again
ANALYZE TABLE seg_table COMPUTE STATISTICS;
ANALYZE INDEX seg_index COMPUTE STATISTICS;
Let us check the size of each segment (table and index)
Note: even after deleting half of the records, the segment size will still show same as it was before delete.
SELECT SEGMENT_TYPE, SEGMENT_NAME, ROUND(BYTES/1024/1024,1) MB, BLOCKS,
EXTENTS FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN ('SEG_TABLE','SEG_INDEX');
Run Oracle segment advisor
=============================
Even though we have delete half of the rows, the statistics show no change. We will run Oracle segment advisor and see what recommendations we will get
Create advisor task for table
SET SERVEROUTOUT ON
DECLARE
TSK_ID NUMBER;
TSK_NAME VARCHAR2(100);
TSK_DESCRIPTION VARCHAR2(500);
OBJ_ID NUMBER;
BEGIN
TSK_NAME := 'ANALYZE_TABLE_TASK';
TSK_DESCRIPTION := 'Segment advice for SEG_TABLE';
-- 1. Create a Task
DBMS_ADVISOR.CREATE_TASK(
ADVISOR_NAME => 'Segment Advisor',
TASK_ID => TSK_ID,
TASK_NAME => TSK_NAME,
TASK_DESC => TSK_DESCRIPTION);
-- 2. Assign the object to the task
DBMS_ADVISOR.CREATE_OBJECT(
TASK_NAME => TSK_NAME,
OBJECT_TYPE => 'TABLE',
ATTR1 => 'SYS',
ATTR2 => 'SEG_TABLE',
ATTR3 => NULL,
ATTR4 => NULL,
ATTR5 => NULL,
OBJECT_ID => OBJ_ID);
-- 3. Set the task parameters
DBMS_ADVISOR.SET_TASK_PARAMETER(
TASK_NAME => TSK_NAME,
PARAMETER => 'recommend_all',
VALUE => 'TRUE');
-- 4. Execute the task
DBMS_ADVISOR.EXECUTE_TASK(TSK_NAME);
END;
/
Create advisor task for index
=============================
SET SERVEROUTOUT ON
DECLARE
TSK_ID NUMBER;
TSK_NAME VARCHAR2(100);
TSK_DESCRIPTION VARCHAR2(500);
OBJ_ID NUMBER;
BEGIN
TSK_NAME := 'ANALYZE_INDEX_TASK';
TSK_DESCRIPTION := 'Segment advice for SEG_INDEX';
-- 1. Create a Task
DBMS_ADVISOR.CREATE_TASK(
ADVISOR_NAME => 'Segment Advisor',
TASK_ID => TSK_ID,
TASK_NAME => TSK_NAME,
TASK_DESC => TSK_DESCRIPTION);
-- 2. Assign the object to the task
DBMS_ADVISOR.CREATE_OBJECT(
TASK_NAME => TSK_NAME,
OBJECT_TYPE => 'INDEX',
ATTR1 => 'SYS',
ATTR2 => 'SEG_INDEX',
ATTR3 => NULL,
ATTR4 => NULL,
ATTR5 => NULL,
OBJECT_ID => OBJ_ID);
-- 3. Set the task parameters
DBMS_ADVISOR.SET_TASK_PARAMETER(
TASK_NAME => TSK_NAME,
PARAMETER => 'recommend_all',
VALUE => 'TRUE');
-- 4. Execute the task
DBMS_ADVISOR.EXECUTE_TASK(TSK_NAME);
END;
/
Verify tasks are completed
===========================
SELECT TASK_NAME,
STATUS
FROM DBA_ADVISOR_TASKS
WHERE ADVISOR_NAME='Segment Advisor' AND
TASK_NAME IN ('ANALYZE_TABLE_TASK','ANALYZE_INDEX_TASK');
TASK_NAME STATUS
------------------------------ -----------
ANALYZE_INDEX_TASK COMPLETED
ANALYZE_TABLE_TASK COMPLETED
Review segment advisor
=====================
Let us check what Oracle segment advisor has to say
SET LINES 300
SET PAGES 999
COL SEGNAME FOR A15
COL PARTITION FOR A10
COL TYPE FOR A10
COL MESSAGE FOR A60
SELECT DAO.ATTR2 SEGNAME,
DAO.ATTR3 PARTITION,
DAO.TYPE,
DAF.MESSAGE
FROM DBA_ADVISOR_FINDINGS DAF,
DBA_ADVISOR_OBJECTS DAO
WHERE DAO.TASK_ID = DAF.TASK_ID AND
DAO.OBJECT_ID = DAF.OBJECT_ID AND
DAF.TASK_NAME IN ('ANALYZE_TABLE_TASK','ANALYZE_INDEX_TASK');
==
SEGNAME PARTITION TYPE MESSAGE
--------------- ---------- ---------- ------------------------------------------------------------
SEG_TABLE TABLE Enable row movement of the table SYS.SEG_TABLE and perform
shrink, estimated savings is 48437808 bytes.
SEG_INDEX INDEX Perform shrink, estimated savings is 29491775 bytes.
Mostly the advisor will recommend you to run shrink space command on the table and give an estimate as to how much space can be saved.
Shrink table cascade
====================
Use below command to shrink table but before that you must enable row movement
ALTER TABLE SEG_TABLE ENABLE ROW MOVEMENT;
ALTER TABLE SEG_TABLE SHRINK SPACE CASCADE;
ALTER TABLE SEG_TABLE DISABLE ROW MOVEMENT;
Analyze table & index size
Post shrinking table and index, let us gather stats and check segment space savings
ANALYZE TABLE seg_table COMPUTE STATISTICS;
ANALYZE INDEX seg_index COMPUTE STATISTICS;
Let us check the size of each segment (table and index)
SELECT SEGMENT_TYPE, SEGMENT_NAME, ROUND(BYTES/1024/1024,1) MB, BLOCKS,
EXTENTS FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN ('SEG_TABLE','SEG_INDEX');
SEGMENT_TYPE SEGMENT_NAME MB BLOCKS EXTENTS
------------------ -------------------- ---------- ---------- ----------
TABLE SEG_TABLE 573.5 73408 143
INDEX SEG_INDEX 576.3 73760 144
27mb table space reclaimed
24mb index space reclaimed
Deallocate space
Now that we have shrink table and index, we can release the deallocated space to the tablespace so that new data can be inserted
ALTER TABLE seg_table DEALLOCATE UNUSED;
ALTER INDEX seg_index DEALLOCATE UNUSED;
At this stage, you can delete the advisor tasks that you created earlier
EXEC DBMS_ADVISOR.DELETE_TASK(TASK_NAME => 'ANALYZE_TABLE_TASK');
EXEC DBMS_ADVISOR.DELETE_TASK(TASK_NAME => 'ANALYZE_INDEX_TASK');
============================================
EXEC dbms_stats.gather_table_stats(ownname => 'sys', tabname => 'emp', cascade => true, estimate_percent =>
10,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);
====================================
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCg+3dZPT8GMOvFQVB7wlRbHWeGJvVgNPOSv4+hAaE6K1nadVxnxGiigSp6e5ZpvdSfRYbv+AYXuJqIO9xMTjMcAYrsH1
zsu0pciTRgfYpPVLQQqftPSNd8/yKceaB/Jj1spqFSxX68izGKMVUhny3SEHDwizjfbdhP+Rp2f4oXr2FQZSoFHylOiVfZHb2/dbIMT7zKdXEEKmCYQyR4f8vEEoo44tVGsxI
+dwc39YcLGovSQgODTSmN1JFRt+7vdpjrnvP3yXmjHTvOK3j9FBqA+4oIaX/hn/TevTFjjimmBRl5OzL5oes1qXIciHn/AIXHZHcb0BEpU94+jd1glaiR oracle_cloude_key
================================================================
Stale stats checking :
select stale_stats from user_tab_statistics where table_name='EMP';
STALE_S
-------
NO
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL', '60');
SELECT OPID, ORIGIN, STATUS, TO_CHAR(START_TIME, 'DD/MM HH24:MI:SS') AS BEGIN_TIME,
TO_CHAR(END_TIME, 'DD/MM HH24:MI:SS') AS END_TIME, COMPLETED, FAILED,
TIMED_OUT AS TIMEOUT, IN_PROGRESS AS INPROG
FROM DBA_AUTO_STAT_EXECUTIONS
ORDER BY OPID;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='EMP';
EXEC DBMS_STATS.SET_GLOBALS_PREFS('AUTO_TASK_STATUS','OFF');
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'sbz', tabname => 'MYOBJECT');
---------------------------------
Steps to gather table statistics:
========================================
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'C##SBZ', -- Owner of the table
tabname => 'EMP' -- Name of the table
);
END;
/
Collect Statistics for All Tables in a Schema:
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'C##SBZ'
);
END;
/
Collect Statistics for the Entire Database:
EXEC DBMS_STATS.GATHER_DATABASE_STATS;
View Existing Table Statistics:
SELECT * FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'EMP';
--------------------------------------
Check Last Analyzed Date for a Table:
SELECT OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE TABLE_NAME = 'EMP';
SQL> col OWNER for a20
SQL> /
OWNER TABLE_NAME NUM_ROWS LAST_ANAL
-------------------- -------------------- ---------- ---------
SYS EMP 8 16-OCT-24
-------------------
Check Column-Level Statistics:
SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, LAST_ANALYZED
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP';
COLUMN_NAME NUM_DISTINCT NUM_NULLS LAST_ANAL
-------------------- ------------ ---------- ---------
EMP_ID 5 0 25-DEC-24
EMP_NAME 4 0 25-DEC-24
JOB_TITLE 3 0 25-DEC-24
HIRE_DATE 4 0 25-DEC-24
SALARY 4 0 25-DEC-24
DEPT_ID 3 0 25-DEC-24
EMP_ID 2 0 25-DEC-24
EMP_NAME 2 0 25-DEC-24
JOB_TITLE 2 0 25-DEC-24
HIRE_DATE 2 0 25-DEC-24
SALARY 2 0 25-DEC-24
DEPT_ID 2 0 25-DEC-24
12 rows selected.
Check Index Statistics:
SELECT INDEX_NAME, NUM_ROWS, LAST_ANALYZED
FROM DBA_INDEXES
WHERE TABLE_NAME = 'EMP';
INDEX_NAME NUM_ROWS LAST_ANAL
-------------------- ---------- ---------
SYS_C007657 5 25-DEC-24
BIN$KfGRahJTDrrgY2UB 2 25-DEC-24
qMD1gg==$0
Check if Statistics Are Stale:
SELECT OWNER, TABLE_NAME, STALE_STATS
FROM DBA_TAB_STATISTICS
WHERE TABLE_NAME = 'EMP';
OWNER TABLE_NAME STALE_S
-------------------- -------------------- -------
SYS EMP YES
Steps to Enable Automatic Statistics Gathering:
SELECT JOB_NAME, ENABLED
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'GATHER_STATS_JOB';
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
END;
/
verify -
SELECT client_name, status
FROM DBA_AUTOTASK_CLIENT
WHERE client_name = 'auto optimizer stats collection';
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
Check Maintenance Windows:
SELECT WINDOW_NAME, ENABLED
FROM DBA_SCHEDULER_WINDOWS;
WINDOW_NAME ENABL
-------------------- -----
MONDAY_WINDOW TRUE
TUESDAY_WINDOW TRUE
WEDNESDAY_WINDOW TRUE
THURSDAY_WINDOW TRUE
FRIDAY_WINDOW TRUE
SATURDAY_WINDOW TRUE
SUNDAY_WINDOW TRUE
WEEKNIGHT_WINDOW FALSE
WEEKEND_WINDOW FALSE
FInding sql id -
Using DBMS_SQLTUNE Package
--------------------------
SELECT sql_id
FROM v$sql
WHERE sql_text LIKE '%select * from emp%';
SQL> SELECT sql_id
FROM v$sql
WHERE sql_text LIKE '%select * from emp%'; 2 3
SQL_ID
-------------
2qh71q2ypc27n
d5j6aaw8khtqk
d5j6aaw8khtqk
a2dk8bdn0ujx7
2g7fn8mrpmz0a
2g7fn8mrpmz0a
select SID,SQL_ID from v$session where process =' ';
select sql_text from v$sql where sql_id ='a2dk8bdn0ujx7';
select * from table(dbms_xplan.display_cursor('2wy8w798r81rv'));
select * from table(dbms_xplan.display_cursor('a2dk8bdn0ujx7',NULL,'ALLSTATS LAST'));
select * from table(dbms_xplan.display_awr('2wy8w798r81rv'));
SET AUTOTRACE ON ( for execution plan )
select * from emp;
set autotrace off;
Using the V$SESSION View :
------------------------
SELECT s.sql_id, s.username, s.sid, s.serial#, q.sql_text
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.sid = SYS_CONTEXT('USERENV', 'SID');
Using V$SQL View
----------------
SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '%<your_query_part>%'
ORDER BY last_active_time DESC;
Checking table hostogram -
A histogram in Oracle is a data structure that provides a distribution of data values for a column. It helps the Oracle optimizer make better decisions when generating execution plans for SQL queries by providing insight into how the data is distributed within the column. Here's how histograms are useful:
SELECT
TABLE_NAME,
COLUMN_NAME,
HISTOGRAM,
NUM_BUCKETS
FROM
DBA_TAB_COL_STATISTICS
WHERE
TABLE_NAME = 'EMP';
TABLE_NAME COLUMN_NAME HISTOGRAM NUM_BUCKETS
-------------------- -------------------- --------------- -----------
EMP EMP_ID NONE 1
EMP EMP_NAME NONE 1
EMP JOB_TITLE NONE 1
EMP HIRE_DATE NONE 1
EMP SALARY NONE 1
EMP DEPT_ID NONE 1
EMP EMP_ID NONE 1
EMP EMP_NAME NONE 1
EMP JOB_TITLE NONE 1
EMP HIRE_DATE NONE 1
EMP SALARY NONE 1
EMP DEPT_ID NONE 1
select * from table (dbms_xplan.display_cursor());
------------------------------------------------
HINTS IN ORACLE -
SELECT /*+ FULL(emp) */ * FROM emp;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
SQL_ID d0572bksw8rkb, child number 0
-------------------------------------
SELECT /*+ FULL(emp) */ * FROM emp
Plan hash value: 3956160932
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS FULL| EMP | 9 |
-------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Index use hints
===============
SELECT /*+ INDEX(emp SYS_C008227) */ * FROM emp;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
SQL_ID 4rjn8k1v5q6uu, child number 0
-------------------------------------
SELECT /*+ INDEX(emp SYS_C008227) */ * FROM emp
Plan hash value: 2824562243
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 10 |
| 2 | INDEX FULL SCAN | SYS_C008227 | 10 |
--------------------------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
20 rows selected.
SQL PROFILES :
============
checking sql profiles :
SELECT name, status
FROM dba_sql_profiles;
Drop profile :
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE('MYORDERS_PROFILE');
END;
/
Example :
CREATE TABLE myorders (ord_no, shipped)
AS SELECT level, CAST (sysdate-level/24 AS DATE)
FROM dual
CONNECT BY level <= 500000
/
CREATE INDEX myorders_idx ON myorders (shipped);
SELECT * FROM myorders WHERE shipped >= trunc(sysdate) -1;
set linesize 200 pages 300
select * from table(dbms_xplan.display_cursor);
SELECT /*+ full (O) */ * FROM myorders O WHERE shipped >= trunc(sysdate) -1;
select * from table(dbms_xplan.display_cursor);
SQL>
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 7vacrpyqxd4yt, child number 0
-------------------------------------
SELECT /*+ full (O) */ * FROM myorders O WHERE shipped >=
trunc(sysdate) -1
Plan hash value: 3790298851
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 369 (100)| |
|* 1 | TABLE ACCESS FULL| MYORDERS | 30 | 660 | 369 (9)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SHIPPED">=TRUNC(SYSDATE@!)-1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
---------------------------------------------
begin
dbms_sqltune.import_sql_profile(
name => 'MYORDERS_PROFILE',
category => 'DEFAULT',
sql_text => ' SELECT /*+ full (O) */ * FROM myorders O WHERE shipped >= trunc(sysdate) -1',
profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS')
);
end;
/
SELECT /*+ full (O) */ * FROM myorders O WHERE shipped >= trunc(sysdate) -1;
select * from table(dbms_xplan.display_cursor);
------
Plan hash value: 3925307399
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| MYORDERS | 30 | 660 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MYORDERS_IDX | 30 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SHIPPED">=TRUNC(SYSDATE@!)-1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / "O"@"SEL$1"
U - full (O) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL profile MYORDERS_PROFILE used for this statement
Checking sql id
----------------
set feedback on sql_id;
select * from emp;
========================================
Oracle Partitioning :
=======================================
Oracle, partitioning is a method of dividing a large table or index into smaller, more manageable pieces called partitions, while still being treated as a single entity. Each partition can be managed and accessed independently, improving performance and manageability, especially in large databases. Partitioning is commonly used in data warehousing and large OLTP systems to improve query performance, maintenance, and scalability.
Benefits of Partitioning:
Performance: Queries can be directed to specific partitions, reducing the amount of data scanned.
Manageability: You can manage each partition separately, such as backing up or restoring only a specific partition.
Storage: You can store partitions on different tablespaces or physical disks, optimizing space usage.
Data Lifecycle Management: Different partitions can hold data for different time ranges, making it easier to archive old data.
Types of Partitioning in Oracle:
Range Partitioning: Divides data based on a range of values (e.g., date ranges).
List Partitioning: Divides data based on a list of discrete values.
Hash Partitioning: Distributes data evenly across partitions based on a hash function.
Composite Partitioning: Combines two or more partitioning methods, such as range-hash or range-list.
Syntax to Create Partitioned Table
Here’s how to create a partitioned table using some of the common partitioning methods:
1. Range Partitioning Example
This example creates a table partitioned by a date column using range partitioning:
CREATE TABLE sales
(
sale_id NUMBER,
sale_date DATE,
customer_id NUMBER,
amount NUMBER
)
PARTITION BY RANGE (sale_date)
(
PARTITION p_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')),
PARTITION p_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY')),
PARTITION p_2022 VALUES LESS THAN (TO_DATE('01-JAN-2023','DD-MON-YYYY')),
PARTITION p_max VALUES LESS THAN (MAXVALUE)
);
In this example:
The sales table is partitioned by sale_date.
Each partition holds data for a specific year.
2. List Partitioning Example
This example creates a table partitioned by a list of values:
CREATE TABLE customers
(
customer_id NUMBER,
customer_name VARCHAR2(100),
region VARCHAR2(50)
)
PARTITION BY LIST (region)
(
PARTITION p_north VALUES ('NORTH'),
PARTITION p_south VALUES ('SOUTH'),
PARTITION p_east VALUES ('EAST'),
PARTITION p_west VALUES ('WEST')
);
In this example:
The customers table is partitioned by the region column.
Data is divided based on region names.
3. Hash Partitioning Example
This example demonstrates hash partitioning:
CREATE TABLE orders
(
order_id NUMBER,
order_date DATE,
customer_id NUMBER,
order_total NUMBER
)
PARTITION BY HASH (order_id)
PARTITIONS 4; -- Divides the table into 4 hash partitions
In this example:
The orders table is partitioned using a hash of the order_id.
Data is distributed evenly across 4 partitions.
4. Composite Partitioning Example
This example shows a combination of range and list partitioning:
CREATE TABLE employee
(
emp_id NUMBER,
emp_name VARCHAR2(100),
hire_date DATE,
department VARCHAR2(50)
)
PARTITION BY RANGE (hire_date)
SUBPARTITION BY LIST (department)
(
PARTITION p_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY'))
(
SUBPARTITION p_2020_hr VALUES ('HR'),
SUBPARTITION p_2020_it VALUES ('IT')
),
PARTITION p_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY'))
(
SUBPARTITION p_2021_hr VALUES ('HR'),
SUBPARTITION p_2021_it VALUES ('IT')
)
);
In this example:
The employee table is first partitioned by hire_date (range).
Each partition is further divided into subpartitions by department (list).
How to Check Partitions
You can check the partitions of a table using the USER_TAB_PARTITIONS and USER_TAB_SUBPARTITIONS views:
SELECT partition_name, high_value, partition_position
FROM user_tab_partitions
WHERE table_name = 'SALES';
This will list the partitions of the SALES table.
Partition Maintenance
Oracle also provides several maintenance commands for partitions:
Adding a partition:
ALTER TABLE sales ADD PARTITION p_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY'));
Dropping a partition:
ALTER TABLE sales DROP PARTITION p_2022;
Merging partitions :
ALTER TABLE sales MERGE PARTITIONS p_2020, p_2021 INTO PARTITION p_2020_2021;
Splitting partitions:
ALTER TABLE sales SPLIT PARTITION p_2022 AT (TO_DATE('01-JUL-2022', 'DD-MON-YYYY')) INTO
(PARTITION p_2022a, PARTITION p_2022b);
===================
SQL Plan Baseline :
====================
A SQL Plan Baseline in Oracle is a feature used to manage and stabilize the performance of SQL queries by controlling which execution plans are used. It helps in ensuring that the database consistently uses the optimal execution plan for a query, even when new statistics, database changes, or upgrades might cause Oracle to generate a different (and potentially worse) plan.
Here’s how SQL Plan Baselines work:
Capture Execution Plans: When a SQL query is executed, Oracle can capture the generated execution plan and store it in a SQL Plan Baseline repository. This can be done automatically or manually.
Plan Evaluation: When Oracle needs to execute a SQL query, it will first check if there is a baseline available for that query. If a baseline exists, Oracle will try to use one of the accepted plans from the baseline instead of generating a new plan.
Plan Evolution: If Oracle generates a new plan that is better than the plans in the baseline, the new plan can be added to the baseline after verification. Oracle will then evolve the baseline by comparing performance metrics of the new plan against the existing baseline plans.
Execution Plan Stability: By using SQL Plan Baselines, Oracle ensures that queries don’t regress in performance when the database undergoes changes, such as the introduction of new optimizer statistics, index creation, or database version upgrades.
Benefits:
Stability: Prevents performance regressions caused by sudden plan changes.
Performance: Ensures optimal execution plans are used consistently.
Control: Database administrators have more control over which execution plans are used.
Example Workflow:
Capture a Baseline: Oracle can automatically capture a baseline by setting the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE, or baselines can be manually created.
Query Execution with Baseline: When a SQL query is executed, Oracle checks the SQL Plan Baseline for accepted plans. If found, it uses one of these plans, ensuring consistent performance.
Evolving Baselines: New execution plans generated for the same SQL statement can be evaluated and, if better, added to the baseline, allowing the query to "evolve" with better performance over time.
Example of Manual Baseline Management:
You can manually load and evolve a SQL Plan Baseline with commands like:
BEGIN
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => 'YOUR_SQL_ID');
END;
/
To evolve a plan:
BEGIN
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'YOUR_SQL_HANDLE');
END;
========================
EXECUTION PLAN READ
Example Plan Breakdown:
| Id | Operation | Name | Rows | Bytes | Cost | Time | Starts | E-Rows |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10430 | 00:00:00.08 | 800 | 10 |
|* 1 | HASH JOIN | | 1 | 12762 | 10430 | 00:00:00.08 | 800 | 10 |
| 2 | NESTED LOOPS | | 1 | 12762 | 10430 | 00:00:00.08 | 800 | 10 |
| 3 | NESTED LOOPS | | 1 | 12762 | 10430 | 00:00:00.02 | 51 | 1 |
| 4 | STATISTICS COLLECTOR | | 1 | | 1 | 00:00:00.01 | 3 | 0 |
|* 5 | TABLE ACCESS FULL | PRODUCTS | 1 | 1 | 1 | 00:00:00.01 | 3 | 0 |
| 6 | PARTITION RANGE ALL | | 1 | | 10430 | 00:00:00.01 | 48 | 1 |
| 7 | BITMAP CONVERSION TO ROWIDS | | 28 | | 10430 | 00:00:00.01 | 48 | 1 |
|* 8 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | 28 | | 16 | 00:00:00.01 | 48 | 1 |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 10430 | 12762 | 10430 | 00:00:00.04 | 749 | 9 |
| 10 | PARTITION RANGE ALL | | 0 | 12762 | 0 | 00:00:00.01 | 0 | 0 |
| 11 | TABLE ACCESS FULL | SALES | 0 | 12762 | 0 | 00:00:00.01 | 0 | 0 |
---------------------------------------------------------------------------------------------
How to Read It:
Start at the bottom (ID 11):
ID 11: TABLE ACCESS FULL (SALES): Oracle performs a full scan of the SALES table. However, no rows are expected to be returned here (indicated by 0 rows).
Move upwards:
ID 10: PARTITION RANGE ALL: Oracle scans all partitions of the SALES table. But again, no rows are expected from this step.
Next is ID 9:
ID 9: TABLE ACCESS BY LOCAL INDEX ROWID (SALES): Oracle uses an index to fetch 10430 rows from the SALES table. This is done using ROWID, which is a direct access method.
The SALES_PROD_BIX bitmap index (ID 8) is used to locate rows.
Look at the join and nested loop operations (ID 1, 2, 3):
ID 2: NESTED LOOPS: This indicates Oracle is performing a nested loop join between the output from the SALES table and the rows from the PRODUCTS table (ID 5).
ID 1: HASH JOIN: The hash join combines the result of the nested loops with another set of data (not shown in the plan) based on hash tables for optimal performance.
Final Result:
At the top of the plan (ID 0: SELECT STATEMENT), Oracle combines all operations and returns 1 row as the final result.
Key Points:
Start from the bottom and work upwards to see how Oracle gathers data step by step.
Focus on the base table operations (e.g., TABLE ACCESS, INDEX) and how they feed into higher-level operations (e.g., JOIN, AGGREGATE).
Understand the cost and estimated rows for each step to identify bottlenecks or performance issues.
=====================
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1904 (100)| | | |
| 1 | NESTED LOOPS | | 12762 | 461K| 1904 (1)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 12762 | 461K| 1904 (1)| 00:00:01 | | |
|* 3 | TABLE ACCESS FULL | PRODUCTS | 1 | 30 | 3 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | | | | | 1 | 28 |
| 5 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 12762 | 89334 | 1904 (1)| 00:00:01 | 1 | 1 |
1. Start at the bottom and work upwards:
Since the operations are performed in reverse order, let's start from the bottom (ID 7) and move upwards.
2. Understand each operation:
ID 7: TABLE ACCESS BY LOCAL INDEX ROWID (SALES)
Operation: Oracle accesses rows from the SALES table using ROWIDs, which were retrieved from an index.
Rows: It expects to return 12,762 rows.
Bytes: The result set size will be 89,334 bytes.
Cost: This operation has a cost of 1904, indicating the resource cost (CPU, I/O) to perform the operation.
Pstart/Pstop: This refers to the partition range. Oracle is accessing partition 1 in the SALES table, as specified by Pstart = 1 and Pstop = 1.
ID 6: BITMAP INDEX SINGLE VALUE (SALES_PROD_BIX)
Operation: Oracle uses a bitmap index (SALES_PROD_BIX) to quickly locate the rows matching a specific condition.
Cost: This operation’s cost is not listed but is typically low since it only retrieves row locations (ROWIDs).
Pstart/Pstop: Oracle is scanning the bitmap index across partition 1 to partition 28.
ID 5: BITMAP CONVERSION TO ROWIDS
Operation: Converts the bitmap index results from ID 6 into ROWIDs that are used to fetch the actual rows from the SALES table.
ID 4: PARTITION RANGE ALL
Operation: Oracle scans all partitions (from partition 1 to 28) of the SALES table to find the relevant rows.
ID 3: TABLE ACCESS FULL (PRODUCTS)
Operation: A full table scan is performed on the PRODUCTS table, meaning Oracle reads all rows in this table.
Rows: Oracle expects 1 row from the PRODUCTS table.
Bytes: The total data size for this row is 30 bytes.
Cost: The cost is very low, only 3, which is typical for small tables or efficient operations.
ID 2: NESTED LOOPS
Operation: A nested loops join is used to match the output of PRODUCTS (ID 3) with rows from SALES (IDs 4–7). For each row in the PRODUCTS table, Oracle fetches the matching rows from SALES using the index and row IDs.
Rows: Oracle expects 12,762 rows as output from this join.
Cost: The cost remains 1904, shared by the upper nested loop (ID 1).
ID 1: NESTED LOOPS
Operation: This is the main nested loops join that combines the two sources: PRODUCTS (ID 3) and SALES (ID 7).
Rows: Oracle expects the final result to have 12,762 rows.
Bytes: The total size of the result set is 461K bytes.
Cost: The overall cost for the operation is 1904.
ID 0: SELECT STATEMENT
This is the final operation, combining the results of all previous steps and producing the output of the query.
3. Flow Summary:
Oracle starts with a full table scan on the PRODUCTS table (ID 3).
Then, Oracle accesses all partitions of the SALES table using a bitmap index on the SALES_PROD_BIX index (ID 6), converting the index values to ROWIDs (ID 5).
The nested loops join (ID 2 and ID 1) combines the rows from PRODUCTS and SALES, with 12,762 rows expected as the final output.
--------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 6637 (100)| | | |
| 1 | MERGE JOIN | | 12762 | 461K| | 6637 (1)| 00:00:01 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 1 | 30 | | 3 (0)| 00:00:01 | | |
| 3 | INDEX FULL SCAN | PRODUCTS_PK | 72 | | | 1 (0)| 00:00:01 | | |
|* 4 | SORT JOIN | | 918K| 6281K| 28M| 6634 (1)| 00:00:01 | | |
| 5 | PARTITION RANGE ALL | | 918K| 6281K| | 3481 (1)| 00:00:01 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 6281K| | 3481 (1)| 00:00:01 | 1 | 28 |
Summary of the Plan:
* Oracle scans the SALES table (ID 6) across all partitions, retrieves 918K rows, and sorts them for the merge join (ID 4).
* It performs an index scan on the PRODUCTS table using the primary key (ID 3), retrieves 1 row via ROWID (ID 2).
* A merge join (ID 1) is performed between the sorted SALES rows and the PRODUCTS row.
* The final result set contains 12,762 rows with a total size of 461K bytes.
=================
Fragmentation -
================
Table Fragmentation occurs when we update/delete data in table. The space which gets freed up during non-insert DML operations is not
immediately re-used (or sometimes, may not get reused ever). This leaves behind holes in the table, which results in table fragmentation.
1. Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-
(Depends upon the free space available in the tablespace)
Remove Fragmentation
Gather Table Stats-
EXEC dbms_stats.gather_table_stats(ownname => 'TABLE_OWNER', tabname => 'TABLE_NAME', cascade => true, estimate_percent =>
10,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);
Check Table Size
select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='&TABLE_NAME';
Now find out actual table size, fragmentation size and fragmentation percentage using below query
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables WHERE table_name='&TABLE_NAME';
Use below query to remove fragmentation from the table
alter table <table_name> move;
Rebuild Indexes on Table
alter index index_name rebuild online;
2. Export and import the table:- (difficult to implement in production environment)
3. Shrink command (fron Oracle 10g)
(Shrink command is only applicable for tables which are tablespace with auto segment space management)
Fragmentation in an Oracle database can be mitigated through various maintenance activities such as:
Regularly reorganizing tables and indexes using tools like Oracle's Segment Advisor or Online Reorganization.
Monitoring and optimizing storage allocation to minimize extent fragmentation.
Implementing efficient data loading and deletion strategies to minimize table and index fragmentation.
Using Automatic Segment Space Management (ASSM) to manage free space within segments more efficiently.
Monitoring and tuning database performance to identify and address fragmentation-related performance issues.
In Oracle databases, fragmentation refers to the phenomenon where data is stored inefficiently, leading to wasted space and degraded performance.
There are several types of fragmentation that can occur in an Oracle database:
Table Fragmentation: Table fragmentation occurs when data within a table becomes dispersed or scattered across data blocks.
This can happen due to frequent inserts, updates, and deletes, which can lead to gaps and unused space within data blocks.
Table fragmentation can result in slower query performance and increased I/O overhead.
Index Fragmentation: Index fragmentation occurs when the logical order of index entries does not match the physical order of data blocks on disk.
This can happen due to frequent inserts, updates, and deletes, which can cause index entries to become scattered across data blocks.
Index fragmentation can degrade query performance and increase index maintenance overhead.
Tablespace Fragmentation: Tablespace fragmentation occurs when free space within a tablespace becomes fragmented or scattered across data files.
This can happen due to uneven allocation and deallocation of extents, leading to wasted space and inefficient storage utilization.
Tablespace fragmentation can impact database performance and increase storage management complexity.
Extent Fragmentation: Extent fragmentation occurs when free extents within a data file become fragmented or scattered across disk blocks.
This can happen due to frequent allocation and deallocation of extents, leading to inefficient space utilization and increased I/O overhead.
Extent fragmentation can impact database performance and increase storage management overhead.
Segment Fragmentation: Segment fragmentation occurs when the extents of a database object (such as a table or index) become fragmented or
scattered across data files. This can happen due to uneven growth or shrinkage of database objects, leading to wasted space and degraded performance.
Segment fragmentation can impact query performance and increase storage management complexity.
=====================================================
** What will you check if there is slowness in database -
To diagnose a slow database, start by checking server resources like CPU, memory, and disk usage. Analyze slow queries to identify bottlenecks
and optimize them for better performance. Monitor database metrics continuously to detect patterns and spikes, enabling proactive troubleshooting.
1)SQL Query Performance: Identify slow-performing SQL queries using Oracle's performance monitoring tools such as
Enterprise Manager or SQL Developer. Analyze execution plans and optimize queries by adding appropriate indexes, rewriting SQL statements,
or using hints.
2)Database Statistics: Ensure that database statistics are up to date. Use the Oracle Analyze or DBMS_STATS package to gather statistics
on tables, indexes, and columns. Accurate statistics help Oracle's optimizer generate efficient execution plans.
3)Indexes and Constraints: Review the indexes and constraints on tables. Ensure that indexes are properly designed and used to speed up
data retrieval. Avoid unnecessary constraints that may impact performance during data manipulation operations.
4)Memory Allocation: Check the memory allocation for Oracle's System Global Area (SGA) and Program Global Area (PGA).
Adjust memory parameters such as SGA_TARGET, PGA_AGGREGATE_TARGET, and DB_CACHE_SIZE based on workload and available system
resources to optimize performance.
5)I/O Performance: Monitor disk I/O performance using Oracle's Automatic Workload Repository (AWR) or Operating System utilities.
Ensure that data files, redo logs, and control files are distributed across disks for optimal I/O throughput. Consider using Oracle's
Automatic Storage Management (ASM) for simplified storage management and improved performance.
6)Database Configuration: Review initialization parameters and database configuration settings. Adjust parameters such as optimizer_mode,
cursor_sharing, and parallelism settings to optimize query performance and resource utilization.
7)Concurrency and Locking: Identify and resolve contention issues related to concurrency and locking. Use Oracle's Automatic Lock Management features
such as row-level locking and transaction isolation levels to minimize contention and improve scalability.
8)Temp Space Management: Monitor temporary tablespace usage and ensure that temporary space is properly sized and configured.
Avoid excessive sorting and hashing operations that may spill to temporary tablespaces, causing performance degradation.
9)Backup and Recovery: Ensure that backup and recovery processes are properly configured and tested. Regularly back up the database using
Oracle Recovery Manager (RMAN) to prevent data loss and minimize downtime in case of failures.
10)Network Latency: Check for network latency issues between the client application and the database server. Optimize network configuration,
use network compression, or consider colocating application servers with the database server to reduce latency.
**If a query is running for longtime what will be your approach
There are several common reasons for long-running queries and updates:
Slow network communication
Insufficient memory in the SQL Server host or not enough memory available for the SQL Server
Lack of useful statistics, data striping, or useful partitioning
Lack of useful indexes
Lack of useful indexed view
Possible causes of long-running queries:
1- Queries are waiting for data that depends on locked resources Priority: High
When multiple transactions are contending for resources, blocking situations can occur, causing delays in query execution.
Blocking happens when another transaction has locked the table source and prevented this transaction from being committed
since the table is already in use.
2- Overloaded or wrongly set hardware resources Priority: High
Overloaded server resources such as CPU, memory, disk I/O, or network can slow down query execution. It might happen due to a specific
application or many processes running at the same time. These processes might be external operations such as anti-virus scans, backups,
and restores from networked data stores.
Other cases might be that the resource settings do not fit the requirements of the instance operation—for example, not enough cores or
improper settings of disks.
Killing the sql---
The syntax of CANCEL SQL is rather easy and similar to the KILL SESSION syntax, as you may expect:
ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID';
Killing the processes
alter system kill session 'sid,serial#';
Finding sid and serial#
select s.sid, s.serial#, p.spid, s.username, s.schemaname
, s.program, s.terminal, s.osuser
from v$session s
join v$process p
on s.paddr = p.addr
where s.type != 'BACKGROUND'
===============================
Wait events:
Waits are conditions where a session is waiting for something to happen. Waits can be caused by a number of things from slow disks,
to locking situations (like the one we saw above) to various kinds of internal Oracle contention.
Information about wait events is displayed in three dynamic performance views:
V$SESSION_WAIT displays the events for which sessions have just completed waiting or are currently waiting.
V$SYSTEM_EVENT displays the total number of times all the sessions have waited for the events in that view.
V$SESSION_EVENT is similar to V$SYSTEM_EVENT, but displays all waits for each session.
ACTIVE SESSION HISTORY SIZE :
SELECT POOL,NAME,BYTES FROM V$SGASTAT WHERE NAME='ASH buffers';
TO CHECK ACTIVE SESSION TIMING:
SELECT MIN(SAMPLE_TIME),MAX(SAMPLE_TIME) FROM V$ACTIVE_SESSION_HISTORY;
----------------------
./oratop
sys as sysdba
Oracle 21c - 07:02:26 Primary r/w orcl up: 2.1h, 0 sn, 1 ins, 1 pdb, 0 er, 4.6G sz, 960M sga, 0%fra, archivelog 0.1%db
ID CPU %CPU LOAD AAS ASC ASI ASW ISW REDO TEMP IORT MBPS IOPS R/S W/S LIO GCPS %FRE PGA NETV UTPS UCPS SQRT %DBC %DBW
1 4 5 0.2 0 0 0 0 0 259 0 14u 0 26.0 2.3 1.2 1.3 0 12.3 503M 122 0 0 200u 0.1 0
EVENT (RT) SESSIONS WAIT TIME AVG %DBT WAIT_CLASS
control file parallel write 28 40m 1.8m 47.2 System I/O
log file parallel write 10 10m 1.9m 17.5 System I/O
latch free 30 10m 358u 10.1 Other
DB CPU 0 5.4
PGA memory operation 189 0 24u 4.4 Other
ID SID SPID USERNAME PROGRAM SRV SERVICE OPN SQLID/BLOCKER E/T %CPU %LIO PGA STS STE WAIT_CLASS EVENT/OBJECT NAME W/T
Interactive Keys: [default]
f : toggle between [standard] & detailed format
r : toggle between [Cumulative (C)] & Real-Time (RT) (section 3)
s : switch to SQL mode (section 4)
p : switch to [session/process] mode (section 4)
m : Toggle sessions [USERNAME/PROGRAM] & MODULE/ACTION (section 4)
t : tablespace information (N/A on standby)
x : SQL plan table (requires a value)
i : refresh interval, requires value in seconds [5]
q : quit/ exit program
Esc : pause.
Symbols:
=================================
TKPROF :-
SQL> conn system/Oracle_4U@pdb1
Connected.
SQL> /
SID SERIAL#
---------- ----------
26 41196
SQL> BEGIN
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id => 26
, serial_num => 41196
, waits => true
, binds => false);
END;
/ 2
###########IN SH SESSION ###############
SELECT * FROM EMP1;
##################IN SYSTEM SESSION #####################
SQL> BEGIN
DBMS_MONITOR.SESSION_TRACE_DISABLE(
session_id => 21
, serial_num => 11935);
END;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
###########IN SH SESSION ###############
SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle1/diag/rdbms/orcl/orcl/trace/orcl_ora_71541.trc
[oracle@db2 trace]$ tkprof orcl_ora_71541.trc /tmp/mytrace.out SYS=NO explain=sys as sysdba/sys
TKPROF: Release 21.0.0.0.0 - Development on Sat Nov 2 07:58:08 2024
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
[oracle@db2 trace]$ cat /tmp/mytrace.out
[oracle@db2 trace]$ tkprof orcl_ora_71541.trc /tmp/mytrace.out SYS=NO SORT=PRSDSK, EXEDSK, FCHDSK PRINT = 1
TKPROF: Release 21.0.0.0.0 - Development on Sat Nov 2 08:00:00 2024
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
[oracle@db2 trace]$ cat /tmp/mytrace.out
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
===========================
Oracle Table and Index Partitions
===========================
Optimize performance with Oracle table and index partitioning.
Partitioning allows tables and indexes to be divided into small parts for easy management inside database. You can create small portions of very large tables inside database which makes data access more simple and manageable.
Crate Table Partitions
You can use below query to create sample table with time range partition. Please note that each partition is placed under different tablespace inside the database
CREATE TABLE time_range_sales
( prod_id NUMBER(6) primary key
, cust_id NUMBER
, time_id DATE
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')) tablespace USERS,
PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')) tablespace USERS,
PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')) tablespace USERS,
PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE) tablespace USERS
);
You can query user_tab_partitions to get details about the table, partition name, number of rows in each partition and more
COLUMN high_value FORMAT A20
SELECT table_name,
partition_name,
high_value,
num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
You can query individual table partition to get records only from the specific partition
select * from TIME_RANGE_SALES partition (SALES_1998);
select * from TIME_RANGE_SALES partition (SALES_1999);
select * from TIME_RANGE_SALES partition (SALES_2000);
select * from TIME_RANGE_SALES partition (SALES_2001);
You can always query a partitioned table like a normal table too. In this case, the output will be from all the partitions inside the table
select * from TIME_RANGE_SALES;
======================
Create Partitioned Index
You can create two types of indexes on a partition table:
Local Index and
Global Index
You will be using LOCAL keyword in CREATE INDEX statement to create a local index
CREATE INDEX time_range_sales_indx ON TIME_RANGE_SALES(time_id) LOCAL
(PARTITION year_1 TABLESPACE users,
PARTITION year_2 TABLESPACE users,
PARTITION year_3 TABLESPACE users,
PARTITION year_4 TABLESPACE users);
To create a global index, you will be using GLOBAL keyword with CREATE INDEX
CREATE INDEX glob_time_range_sales ON TIME_RANGE_SALES(time_id)
GLOBAL PARTITION BY RANGE (time_id)
(PARTITION glob_y1 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION glob_y2 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION glob_y3 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
PARTITION glob_y4 VALUES LESS THAN (MAXVALUE));
Check Partition Indexes
select INDEX_NAME, PARTITION_NAME from user_ind_partitions;
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
TIME_RANGE_SALES_INDX YEAR_1
TIME_RANGE_SALES_INDX YEAR_2
TIME_RANGE_SALES_INDX YEAR_3
TIME_RANGE_SALES_INDX YEAR_4
==================
Export the table using Oracle exp utility
exp file=emp.exp tables=emp
[oracle@ahmed ~]$ ls -lrt
total 20
drwxr-xr-x 3 oracle oinstall 4096 Mar 10 13:21 oradiag_oracle
-rw-r--r-- 1 oracle oinstall 16384 Mar 11 03:20 sales_new.exp
[oracle@db1 ~]$ exp sysdba/sys file=emp.exp tables=emp
Export: Release 19.0.0.0.0 - Production on Wed Dec 18 07:10:36 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
EXP-00056: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon denied
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table EMP 6 rows exported
Export terminated successfully without warnings.
[oracle@db1 ~]$
Rename The Original table
SQL> rename sales_new to sales_new_bkp;
Create New Partition Table
CREATE TABLE sales_test
( prod_id NUMBER(6) primary key
, cust_id NUMBER
, time_id DATE
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE)
);
Import Data Into New Partition Table
imp file=emp.exp tables=emp ignore=y
[oracle@db1 dp_dir]$ cd
[oracle@db1 ~]$ ll
total 40
-rw-r--r-- 1 oracle oinstall 352 Sep 21 04:33 db_env
-rw-r--r-- 1 oracle oinstall 16384 Dec 18 07:10 emp.exp
-rw-r--r-- 1 oracle oinstall 259 Sep 11 11:11 grid_env
-rw-r--r-- 1 oracle oinstall 187 Jun 10 2024 logdump.hst
drwxr-xr-x 29 oracle oinstall 4096 Jun 7 2024 ogghome
-rwxr-xr-x 1 oracle oinstall 2464 Sep 12 08:45 top_script
[oracle@db1 ~]$ imp file=emp.exp tables=emp ignore=y
Import: Release 19.0.0.0.0 - Production on Wed Dec 18 07:16:48 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export file created by EXPORT:V19.00.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
IMP-00403:
Warning: This import generated a separate SQL file "import_sys" which contains DDL that failed due to a privilege issue.
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. . importing table "EMP" 6 rows imported
Import terminated successfully with warnings.
[oracle@db1 ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 18 07:16:57 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from emp;
EMP_ID EMP_NAME JOB_TITLE HIRE_DATE SALARY DEPT_ID
---------- -------------------------------------------------- ------------------------------ --------- ---------- ----------
101 John Doe Manager 15-JAN-23 75000 10
102 Jane Smith Analyst 01-FEB-23 50000 20
103 Sam Wilson Developer 10-MAR-23 60000 10
104 Lisa Brown Tester 20-APR-23 45000 30
105 Michael Green Developer 25-MAY-23 65000 10
106 Michael Green Developer 25-MAY-23 65000 10
6 rows selected.
Split & Exchange Partition
===============================
Create a test table for this activity and make sure its not a partition table
CREATE TABLE my_table (
id NUMBER,
description VARCHAR2(50)
);
INSERT INTO my_table (id, description) VALUES (1, 'One');
INSERT INTO my_table (id, description) VALUES (2, 'Two');
INSERT INTO my_table (id, description) VALUES (3, 'Three');
INSERT INTO my_table (id, description) VALUES (4, 'Four');
COMMIT;
Now create another single full partition table with only one partition to contain whole table
CREATE TABLE my_table_2 (
id NUMBER,
description VARCHAR2(50)
)
PARTITION BY RANGE (id)
(PARTITION my_table_part VALUES LESS THAN (MAXVALUE));
Switch original table segment with partition table segment
ALTER TABLE my_table_2
EXCHANGE PARTITION my_table_part
WITH TABLE my_table
WITHOUT VALIDATION;
Drop original table and rename partition table
DROP TABLE my_table;
RENAME my_table_2 TO my_table;
Finally, we can split the new partition table into Multiple partitions
ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3)
INTO (PARTITION my_table_part_1,
PARTITION my_table_part_2);
Check partition details via below query
SELECT table_name,
partition_name,
high_value,
num_rows
FROM dba_tab_partitions
ORDER BY table_name, partition_name;
Add Partition to Existing Partition Table
To add a new partition to an existing partitioned table, use below command
ALTER TABLE SALES_NEW
ADD PARTITION sales_2002
values less than (TO_DATE('01-JAN-2003','DD-MON-YYYY'))
tablespace TBS_01;
Run below command to check high value of each partition
SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
========================
Oracle Database Auditing
Secure and monitor your database with Oracle auditing.
Oracle database auditing allows you to monitor certain database actions happening inside the database. Auditing also helps in looking at the actions performed against a particular table, schema or certain specific rows.
You can check database auditing is enabled via SHOW PARAMETER command
Show parameter audit;
SQL> Show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/prod/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_common_systemlog string
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
he AUDIT_TRAIL parameter defines database auditing status. It can take any of the following values
none - Database auditing is disabled
os - Enabled, audit logs are stored at OS level, not inside the database
db - Enabled, audit records are stored inside database (SYS.AUD$ table)
db,extended - Same as db but populates SQL_BIND & SQL_TEXT too
xml - Enabled, audit records are stored at OS level in XML format
xml,extended - Same as xml but populates SQL_BIND & SQL_TEXT too
Default is DB if you create database via DBCA, else its NONE
The AUDIT_FILE_DEST parameter defines the OS level location of the audit trail files. By default it is set to adump location.
The AUDIT_SYS_OPERATIONS parameter defines auditing is enabled or disabled for any user connecting to database as SYSDBA. This is enabled by default. All the SYS operations audit records goes to OS level into AUDIT_FILE_DEST location.
=================
Generate Table DDL Command
=================
SQL> set long 100000
set pagesize 0 ;
set linesize 1000;
set feedback off ;
set verify off ;
set trimspool on;SQL> SQL> SQL> SQL> SQL>
SQL> SELECT DBMS_METADATA.get_ddl ('TABLE', 'EMP') from dual;
CREATE TABLE "SYS"."EMP"
( "EMP_ID" NUMBER(5,0),
"EMP_NAME" VARCHAR2(50) NOT NULL ENABLE,
"JOB_TITLE" VARCHAR2(30),
"HIRE_DATE" DATE DEFAULT SYSDATE,
"SALARY" NUMBER(10,2),
"DEPT_ID" NUMBER(5,0),
PRIMARY KEY ("EMP_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
The above command will give you complete CREATE TABLE statement along with segment attributes. In case you just want the CREATE TABLE statement along with TABLESPACE details then
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
SQL> DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);
2 3 4 5 END;
/
6 SELECT DBMS_METADATA.get_ddl ('TABLE', 'EMP') from dual; 7 SQL> SQL>
CREATE TABLE "SYS"."EMP"
( "EMP_ID" NUMBER(5,0),
"EMP_NAME" VARCHAR2(50) NOT NULL ENABLE,
"JOB_TITLE" VARCHAR2(30),
"HIRE_DATE" DATE DEFAULT SYSDATE,
"SALARY" NUMBER(10,2),
"DEPT_ID" NUMBER(5,0),
PRIMARY KEY ("EMP_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "SYSTEM" ;
-----------------
Generate View DDL Command
The below statement will give CREATE VIEW command to respective view
set long 100000
set pagesize 0 ;
set linesize 1000;
set feedback off ;
set verify off ;
set trimspool on;
SELECT DBMS_METADATA.get_ddl ('VIEW','TEST_VIEW','emp') from dual;
Generate Create User DDL Command
Below query will generate CREATE USER ddl command for an existing user
set long 100000
set pagesize 0 ;
set linesize 1000;
set feedback off ;
set verify off ;
set trimspool on;
SELECT DBMS_METADATA.GET_DDL('USER','HR') FROM dual;
==================
Open the glogin.sql file
vi $ORACLE_HOME/sqlplus/admin/glogin.sql
Put below query at the end of the file
select 'Welcome, you are connected to '||prod||' database' "Hello" from v$database;
Save and close. That's it, now whenever you login to SQL*PLUS, you will be greeted with a nice welcome message!
===================================
Comments
Post a Comment