You can utilize Oracle Database dynamic performance views to query important tuning related parameters like memory utilization, Disk I/O, Disk structure etc. These dynamic performance views are special views that are continuously updated while a database is open and in use.
Note that the actual dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. You should access only the V$ objects, not the V_$ objects.
Leave a CommentIn our EBS system, we found that SYSADMIN login was slow. It was taking around 4-5 minutes to login and when inside the EBS application it was taking 2-3 minutes every time we were navigating in the self-service pages. This issue was not occurring for normal EBS users. We thought of applying the trace to SYSADMIN user to find the root cause of it.
2 CommentsBelow are some of the frequently used commands related to Transparent Data Encryption (TDE)
— TO FIND WHICH ALL TABLESPACES ARE ENCRYPTED
SQL> select * from dba_tablespaces where encrypted='YES';
OR
SQL> select * from V$ENCRYPTED_TABLESPACES;Leave a Comment
Keeping data secure is one of the primary job of DBA. Oracle’s Transparent Data Encryption Tablespace Encryption option of Oracle Database 11g Enterprise Edition provides an excellent method to encrypt data at rest. This option works perfectly with Oracle EBS R12.
The TDE Tablespace Encryption option can be used to encrypt the tablespaces that store the content of your E-Business Suite application so you can encrypt the database files and any backups of these files. Good thing about TDE is you do not have to change any of your existing code as TDE is transparent to application.
3 CommentsA well-planned methodology is the key to success in performance tuning. Without a proper diagnosis of the issue any troubleshooting step taken will further delay the resolution. Example, a DBA can not just blindly start tracing/tuning a SQL/Sessions when all symptoms on careful analysis points towards issues with DIsk/Memory/Network etc.
2 CommentsHistogram helps Oracle Optimizer in generating a better execution plan specially when the data is highly skewed in the table. You can say a histogram is an optional special type of column statistic data that provides more detailed information about the actual data distribution in a table column.
Before going into Oracle Histograms details, let us understand what it Histogram in simple language.
2 CommentsOracle’s Cost based optimizer OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach. Choosing the right mode setting can improve many times the average performance of your queries. Optimizer is the mind of the Oracle engine which takes into account many factors like indexes, statistics etc and decide the optimum path to fetch the data and return back to the user session. Oracle provides some parameters which if tweaked can make Optimizer mode to go alternate ways which can be beneficial to your SQL queries. optimizer_mode database parameter is one such useful parameters.
2 CommentsJust like TKPROF, Trace Analyzer (TRCA) tool can help you analyze one or several SQL trace(s) generated by Event 10046 to produce an output diagnostics report which can be used to diagnose a poorly performing SQ statement. TRCA can produce output in two formats (html and text).
TRCA identifies expensive SQL, generates their explain plan and collects some other useful information like Cost-based Optimizer CBO statistics, metadata, configuration parameters, and similar elements that influence the query performance.
This post is part 4 of the below posts on trace and tkprof. Please refer other posts to read related information.
Part 1: Trace and the methods to generate trace
Part 2: Generating TKPROF from trace file
Part 3: Analyzing TKPROF files
Part 4: Using Trace Analyzer (TRCANLZR) Tool
Analyzing a tkprof file is the key part for a DBA to get to the root of performance issue. This post will describe many useful…
2 CommentsTKPROF output can be generated from a raw SQL Trace. It formats and summarizes the diagnostic information from the raw SQL Trace.TKPROF allows you to analyse a trace file in easy way to determine where time is being spent and what query plans are being used on SQL statements.
This post is part 2 of the below posts on trace and tkprof. Please refer other posts to read related information.
Part 1: Trace and the methods to generate trace
Part 2: Generating TKPROF from trace file
Part 3: Analyzing TKPROF files
Part 4: Using Trace Analyzer (TRCANLZR) Tool
This post will cover Part 2: Generating a tkprof file from trace file.
WHAT DOES A TKPROF FILE CONTAINS:
It also contains information like Library cache misses (hard parse) and total SQL and wait statistics for Recursive and Non-recursive SQL Statements
2 Comments
