Autotrace is quite easy to use and provides useful info about the query statistics. It should be the first tool to be used for perf. Testing an application.
Autotrace provides many of the TRACE and TKPROF statistics such as disk reads and total reads.
The main difference between the AUTOTRACE and EXPLAIN PLAN commands in Oracle is that AUTOTRACE actually executes the query (in the way TRACE does) and automatically queries the plan table, whereas EXPLAIN PLAN does neither.
It can be used from SQL*Plus and provides query stats on the screen.
Options for the Autotrace –
• autotrace on – Enables all options.
• autotrace on explain – Displays returned rows and the explain plan.
• autotrace on statistics – Displays returned rows and statistics.
• autotrace trace explain – Displays the execution plan for a select statement without actually executing it. "set autotrace trace explain"
• autotrace traceonly – Displays execution plan and statistics without displaying the returned rows. This option should be used when a large result set is expected.
Components of a trace –
Sample of a TKPROF’ed file
PARSE – the phase where Oracle finds the query in the shared pool(called soft parse) or creates a new plan(hard parse)
EXECUTE – this is the work done by Oracle in the OPEN or EXECUTE part of the statement. It will be empty for SELECT stmt.
FETCH – will have the most part of processing in case of a SELECT stmt, but would be mostly empty in case of an UPDATE stmt.
COUNT – Gives count on how many times this phase of the query was performed. Ideally, the parse count of a query should be 1 and execute count can be 1 or more. In a properly written application, the parse count will be 1.
CPU – amount of cpu time spent in thousands of seconds.
ELAPSED – The actual clock time spent on the query. If the elapsed time is quite large than the value of cpu time, then it means oracle spent time waiting for something.
DISK – how many physical I/Os were performed on the query. Like we have 34 physical I/O reads in the above query.
QUERY – Number of logical I/Os performed to retrieve consistent mode blocks. Generally all physical I/Os result into a logical I/O.
CURRENT – number of logical I/Os performed to retrieve blocks as of now. This is used in cases when DML is performed in the query, like an UPDATE stmt. In this case, the query data must be retrieved in the current mode.
ROWS – The number of rows processed/ affected by this phase. For update, this value would be populated in the Execute phase, and in case of a SELECT stmt, this value would be in Parse phase.
Implicit vs. Explicit
Both the above are purely conceptual things and for Oracle engine, both are same. But implicit cursors are more efficient and easy to deal with.
Implicit
Explicit
begin
for x in ( select * from t )
loop
process x;
end loop;
end;
declare
cursor c is select * from t;
l_rec c%rowtype;
begin
open c;
loop
fetch c into l_rec;
exit when (c%notfound);
process l_rec;
end loop;
close c;
end;
/
Both these have no performance benefit over the other. But for explicitly defined cursors, there is more code that needs to be written and hence chances of programmatic errors are more.
Composite Index
In a composite index, the selected columns should be chosen wisely as this impacts the index performance. The more the columns of a composite index are used in the WHERE clause , the faster would be the execution. The rule is most commonly used column should do first in the composite index.
---------
Cardinality
This is the measure of distinct values of a set. And in terms of database, cardinality is defined as the number of distinct rows returned by a query.
-----------
HINTS -
ALL_ROWS- It instructs the optimizer to fetch the last row of the result-set as fast as possible. non-interactive, batch mode type of application would benefit from this.
FIRST_ROWS - It instructs the optimizer to fetch the first row to the client as fast as possible. An interactive, typical end-user application would benefit from it.
It’s a list of some tables which optimizer uses to gather various statistics related to the database tables.
select * from user_tables
where table_name ='EMP'
select * from user_tab_statistics
where table_name ='EMP'
select * from user_tab_col_statistics
where table_name ='EMP'
select * from user_tab_histograms
where table_name ='EMP'
and column_name = 'EMPID'
A collection is an ordered group of elements which have the same data type and we use subscripts to actually access those elements.
SELECT empno, deptno, sal,
RANK() OVER (PARTITION BY deptno
ORDER BY sal DESC NULLS LAST) RANK,
DENSE_RANK() OVER (PARTITION BY
deptno ORDER BY sal DESC NULLS
LAST) DENSE_RANK
FROM emp
WHERE deptno IN (10, 20)
ORDER BY 2, RANK;
EMPNO DEPTNO SAL RANK DENSE_RANK
------ ------- ----- ----- ----------
7839 10 5000 1 1
7782 10 2450 2 2
7934 10 1300 3 3
7788 20 3000 1 1
7902 20 3000 1 1
7566 20 2975 3 2
7876 20 1100 4 3
7369 20 800 5 4
8 rows selected.
|
The list is analytical functions as found on Ora documentation.
AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *
The process of archiving the redo log files is called archiving.But server has to be run in the archivelog mode.
SELECT * FROM v$log;If you want to see the information on redo log files.
