Dear Readers,
In this article, we will see the following Daily Tips for Oracle DBA.
Tip-1 : Date: 25-Nov-20 Provided By (Linkedin): Rakesh Monga
If Database running slow then check below basic things:
👉 When this issue happens.
👉 When last it ran successfully.
👉 What exactly issue is.
👉 Any patching / upgrade happen in application side.
👉 Any data Volume change or Any change in job
Source : https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg06idx.htm
Tip-2 : Date: 26-Nov-20 Provided By (Linkedin): Venkat Vinod Kumar Siram
Index Creation (Part-1):
Use the following guidelines for determining when to create an index:
👉 Create an index if you frequently want to retrieve less than 15% of the rows in a large table.
👉 Index columns used for joins to improve performance on joins of multiple tables.
👉 Primary and unique keys automatically have indexes,but you might want to create an index on a foreign key.
Source : https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg06idx.htm
Tip-3 : Date: 27-Nov-20 Provided By (Linkedin): Venkat Vinod Kumar Siram
Index Creation (Part-2):
👉Small tables do not require indexes
👉If a qurey is taking too long, then the table might have grown from small to large.
Some columns are storng candidates for indexing.
Column with one or more of the following characteristics are candidates for indexing:
👉 Values are relatively unique in the column.
👉 There is a wide range of values (good for regular indexes)
👉 There is a small range of values (good for bitmap indexes)
Source : https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg06idx.htm
Tip-4 : Date: 28-Nov-20 Provided By (Linkedin): Venkat Vinod Kumar Siram
Advantages of Function-Based Indexes:
👉 Increase the number of situations where the optimizer can perform a range scan instead of a full table scan.
👉 For example,consider the expression in the WHERE clause below:
CREATE INDEX Idx ON Example_tab(Column_a + Column_b);
SELECT * FROM Example tab WHERE Column_a + Column p < 10;
Source : https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg06idx.htm
Tip-5 : Date: 29-Nov-20 Provided By (Linkedin): Venkat Vinod Kumar Siram
Advantages of Function-Based Indexes:
👉 Precompute the value of a computationally intesive function and store it in the index
👉 An index can store computationally intensive expression that you access often.
👉 When you need to access a value, it is already computed, greatly improving query execution performance.
Source : https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg06idx.htm
Tip-6 : Date: 30-Nov-20 Provided By (Linkedin): Venkat Vinod Kumar Siram
Limit the Number of Indexes for EachTable:
👉 The more indexes,the more overhead is incurred as the table is altered.
👉 When rows are iserted or deleted,all indexes on the table must be updated.
👉 When a column is updated,all indexes on the column must be updated.
👉 You must weigh the performance benefit of indexes for queries against performance overhead of updates.
👉 For example,if a table is primarily read-only,you might use more indexes.
👉 But,if a table is heavily updated,you might use fewer indexes.
Source : https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg06idx.htm
Tip-7 : Date: 01-Dec-20 Provided By (Linkedin):Venkat Vinod Kumar Siram
Choose the Order of Columns in Composite Indexes :
👉 Although you can specify columns in any order in the CREATE INDEX command, the order of columns in the CREATE INDEX statement can affect query performance.
👉 In general, you should put the column expected to be used most often first in the index.
👉 You can create a composite index (using several columns), and the same index can be used for queries that reference all of these columns, or just some of them.
Source : https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg06idx.htm
Tip-8 : Date: 02-Dec-20 Provided By (Linkedin): Venkat Vinod Kumar Siram
👉Gather Statistics to Make Index Usage More Accurate :
👉The database can use indexes more effectively when it has statistical information about the tables involved in the queries.
👉You can gather statistics when the indexes are created by including the keywords COMPUTE STATISTICS in the CREATE INDEX statement.
👉As data is updated and the distribution of values changes, you or the DBA can periodically refresh the statistics by calling procedures like DBMS_STATS.
👉GATHER_TABLE_STATISTICS and DBMS_STATS.GATHER_SCHEMA_STATISTICS.
Source : https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg06idx.htm
Tip-9 : Date: 03-Dec-20 Provided By (Linkedin): Venkat Vinod Kumar Siram
Advantages of Function-Based Indexes:
👉Increase the number of situations where the optimizer can perform a range scan instead of a full table scan.
👉For example, consider the expression in the WHERE clause below:
CREATE INDEX Idx ON Example_tab(Column_a + Column_b);
SELECT * FROM Example_tab WHERE Column_a + Column_b < 10;
The optimizer can use a range scan for this query because the index is built on (column_a + column_b).
Source : https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg06idx.htm
Tip-10 : Date: 04-Dec-20 Provided By (Linkedin): Sirisha
Invisible Index:
👉Index can be created invisible using Invisible keyword.
👉create index index_name on table_name(col_name) invisible;
👉Visibility Status of Index is indicated by VISIBILITY column of DBA_INDEXES view.
👉Invisible Index is Ignored by optimizer unless parameter OPTIMIZER_USE_INVISIBLE_INDEXES is set to TRUE.
(or) Hint “/*+ USE_INVISIBLE_INDEXES */” used in the code.
Uses :
👉Testing the impact of adding or dropping the index
👉Useful for processed with specific indexing needs, where presence of same index may adversely affect other processes.
Tip-11 : Date: 07-Dec-20 Provided By (Linkedin): Venkat Vinod Kumar Siram
Dataguard:
👉 If the gap between primary and physical gets very large, use the incremental backup to speed up the gap
resolution.
👉 Gap resolution – increase the number of arch processes from 2 to 4.
Tip-12 : Date: 09-Dec-20 Provided By (Linkedin): Ankush Chawla
Performance Tuning Tip when using functions:
👉 Try to place functions on top of constants(liberals, binds) rather than on columns
👉 Where func(A)= X (Any literal Value); should be rewritten as where A= In_func(X);
👉 Avoid using functions on index columns or partition keys as it prevents index use or paritition pruning.
Tip-13 : Date: 10-Dec-20 Provided By (Linkedin): Sirisha
Real Time Statistics:
👉 Oracle 19c, Introduces real-time statistics, which extends online stats gathering to also include conventional DML statements.
👉 In general, stats are gathered using dbms_stats during a maintenance window.
👉 However Stats can go stale between DBMS_STATS job runs.
👉 This feature helps optimizer to generate more optimal plans for situations where tables are truncated and reloaded (change in volumes).
Tip-14 : Date: 11-Dec-20 Provided By (Linkedin): Sirisha
TABLE Monitoring & FLUSH_DATABASE_MONITORING_INFO:
👉 Truncates, DMLs on Table are tracked using *_TAB_MODIFICATIONS view.
👉 This view holds info since the last time stats are gathered on table.
👉 A Table is considered to have stale stats if it is modified by more than STALE_PERCENT set (default 10%).
👉 In general, oracle database does not populate this view immediately after the table modification.
👉 Its refreshed periodically.
👉 FLUSH_DATABASE_MONITORING_INFO flushes in-memory Monitoring Info for all tables in dictionary and populates this view with latest info along with “_TAB_STATISTICS” & “_IND_STATISTICS”.
Tip-15 : Date: 11-Dec-20 Provided By (Linkedin): Ramesh
Oracle SQL performance improvement tip:
👉 Attempt to utilize “exists” clause instead of “in” clause where ever feasible for better query performance.
Tip-16 : Date: 14-Dec-20 Provided By (Linkedin): Ramesh
Oracle SQL performance improvement tip:
👉 Attempt to use “union all” clause instead of “union” clause where ever feasible for better query performance.
Tip-17 : Date: 15-Dec-20 Provided By (Linkedin): Yash
👉 Select allows a user to query a table but it also allows a user to do Select..for update thus allowing the read only user ability to lock rows.
Thus for a read only user:-
SQL> Grant READ on schema.object to user;
Tip-18 : Date: 16-Dec-20 Provided By (Linkedin): Ramesh
Oracle SQL performance improvement tip:
👉 Attempt to keep frequently used static data small tables in memory where ever feasible for better query performance.
Tip-19 : Date: 16-Dec-20 Provided By (Linkedin): Ramesh
Oracle SQL performance improvement tip:
👉Attempt to keep small size table as a driving table (I.e. as first table in from clause) in order to achieve better query performance when multiple tables involved in the query.
Tip-20 : Date: 11-Dec-20 Provided By (Linkedin): Sirisha
Disable archive logging :
👉 Oracle 12c has a new parameter disable_archive_logging introduced for datapump.
👉 It disables archive logging during large import. thereby speeding up imp operations.
USAGE : impdp scott/tiger parfile=mypar.par transform=disable_archive_logging:y
NOTE: It can be limited to specific type of object type.
Eg :
👉 transform=disable_archive_logging:y:Table
👉 transform=disable_archive_logging:y:Index
Tip-21 : Date: 22-Dec-20 Provided By (Linkedin): Sirisha
👉 Feature introduced with oracle 18c.Both table data and definition are temporary dropped at the end of session/transaction.
Tip-22 : Date: 29-Dec-20 Provided By (Linkedin): Shireen
👉If possible use bind variables instead of constant or literal values in the predicate filter conditions to reduce repeated parsing of the same statement.
Example : select * from emp where emp_no = :number
Tip-23 : Date: 30-Dec-20 Provided By (Linkedin): Shireen
👉In case of good throughput, optimizer mode should be set to ALL ROWS, to achieve it try to avoid sorts in your query like groups by, order by etc.
Tip-24 : Date: 02-Dec-20 Provided By (Linkedin): Shireen
👉Without knowing the gain in performance, avoid using HINTS.
Tip-25 : Date: 03-Dec-20 Provided By (Linkedin): Shireen
👉To improve SQL efficiency, use equi-joins whenever possible.
Tip-26 : Date: 04-Dec-20 Provided By (Linkedin): Shireen
👉If possible use partition key in “WHERE” clause while querying partitioned table to ensure partition pruning.
Tip-27 : Date: 05-Feb-21 Provided By (Linkedin): Shireen
👉To avoid network traffic and unnecessary I/O on the database, it is recommended to use only those COLUMNS which are required.
Tip-28 : Date: 11-Feb-21 Provided By (Linkedin): Shireen
👉While tunning the SQL Query, read the execution plan and use the largest restriction (filter) as the driving site followed by the next largest restriction for the query. Following it you can observe the less buffer_gets,I/O and execution in subsequent phases of the plan.
Tip-29 : Date: 26-Feb-21 Provided By (Linkedin): Shireen
👉 Use view in query only when all the tables from the view are accessible for the data to be returned or else define a new view as per your need.
Tip-30 : Date: 01-March-21 Provided By (Linkedin): Shireen
👉 HINTS should only be used in the query when the performance gains are clear, else it might degrade the performance.
Tip-31 : Date: 26-March-21 Provided By (Linkedin): Shireen
👉 To avoid confusion and guess work on oracle, always refer to a column by either using an alias or using the fully qualified name in case using multiple tables in a query.
Tip-32 : Date: 29-March-21 Provided By (Linkedin): Yash
Performance Tip
👉 Use bind variables instead of literal variables.
Tip-33 : Date: 31-March-21 Provided By (Linkedin): Yash
Performance Tip
👉 Use an index if less than 5% of data needs to be accessed.
Tip-34 : Date: 01-April-21 Provided By (Linkedin): Yash
Performance Tip
👉 Use equi-joins whenever possible.
Tip-35 : Date: 02-March-21 Provided By (Linkedin): Yash
Performance Tip
👉 Always use a WHERE clause.
Tip-36 : Date: 02-April-21 Provided By (Linkedin): Yash
Performance Tip
👉 Avoid using Cursor or row-by-row processing
Tip-37 : Date: 05-April -21 Provided By (Linkedin): Ankush Chawla
Lag on Active DataGuard can be checked by below query:
👉 SQL> SELECT name, value, datum_time, time_computed FROM v$dataguard_stats;
👉 The DATUM_TIME column contains a timestamp of when this data was last received by the standby database.
👉 The TIME_COMPUTED column contains a timestamp taken when the apply lag metric was calculated.
Tip-38 : Date: 06-April-21 Provided By (Linkedin): Yash
Identify High-Impact SQL Statements
👉 High-impact SQL could be determined by number of rows processed, buffer gets, disk reads, memory KB, CPU seconds, sorts, or executions.
👉 Any queries with high numbers in any of these areas will be high impact and therefore high cost.
Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS
Loading...
