Oracle Illustrated

Feb 21 2010   8:52AM GMT

Migrating from 9i to 11g – Miscellaneous features

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

1. DDL wait

Now all Data Definition languages can wait using DDL_LOCK_TIMEOUT() – default value is 0, the maximum value is 100000 (27.77 hours) !!
Lets look at a quick example -
create table test_lock (a number);
insert into test_lock values (1);

another session -
alter session set ddl_lock_timeout=4
create index test_lock_idx on test_lock (a);

SQL> create index test_lock_idx on test_lock (a);
create index test_lock_idx on test_lock (a)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:04.00
SQL>
It will wait for 4 seconds then error out.

**********************************************************************************

2. Non published statistics

Now, we can gather statistics without applying them into the DB actually.
SQL> Exec DBMS_STATS.SET_SCHEMA_PREFS(‘system’, ‘publish’, ‘false’);
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.12
If we want to set it as pending then,
ALTER SESSION SET OPTMIZER_PENDING_STATISTICS=TRUE
Later, we can use PUBLISH_PENDING_STATS procedure in DBMS_STATS package.

**********************************************************************************

3. Stale percentage

Prior to Oracle 10g statistics becomes stale if there is 10% variation in the data. From Oracle 11g (default is 10%) the stale percentage can be set using SET_SCHEMA_PREFS

EXEC DBMS_STATS.SET_SCHEMA_PREFS(‘SYSTEM’, ‘STALE_PERCENT’, ’20′);
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.89

**********************************************************************************

4. SIMPLE_INTEGER

SIMPLE_INTEGER

This new predefined subtype is newly introduced in Oracle 11g. It has a range of –2147483648 .. 2147483648 and does not include NULL values. Where ever PLS_INTEGER was allowd SIMPLE_INTEGER is allowed. The performance of SIMPLE_INTEGER will be more if the PLS_CODE_TYPE is set to NATIVE.

It is estimated to be faster than PLS_INTEGER and also, it eliminates the overhead of overflow checking.

A quick example to check which is better? – PLS_INTEGER or SIMPLE_INTEGER

PLS_INTEGER

DECLARE
n PLS_INTEGER;
BEGIN
FOR i in 1 .. 500000 LOOP
n := n + 1;
END LOOP;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.78

SIMPLE_INTEGER

DECLARE
n SIMPLE_INTEGER := 0;
BEGIN
FOR i in 1 .. 500000 LOOP
n := n + 1;
END LOOP;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.28

**********************************************************************************
5. Flush buffer_cache feature from Oracle 10g

Now, we can flush the buffer cache. Lets take a look at a quick example -
First time -
SQL> set autotrace traceonly statistics
SQL> select count(1) from dept;
Elapsed: 00:00:01.73
Statistics
———————————————————-
217 recursive calls
0 db block gets
37 consistent gets
5 physical reads
Second time -
Elapsed: 00:00:00.09

Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
ALTER SYSTEM FLUSH BUFFER_CACHE
SQL> select count(1) from dept;
Elapsed: 00:00:00.04
Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
2 physical reads

**********************************************************************************

6. Create and Rebuild index online

Prior to Oracle 11g it is not possible to create / rebuild index online – now, its possible to do so.
CREATE INDEX ON

ONLINE;
ALTER INDEX REBUILD ONLINE

**********************************************************************************

7. NOT Null with Default

Not Null columns with default value are maintained in the dictionary.
Not much of space is consumed
Instant add
Existing columns are not converted.

**********************************************************************************

8. Hierarchal query enhancements

New Features

The new hierarchical query features in Oracle Database 10g are:

New Operator
* CONNECT_BY_ROOT - when applied to a column, returns the value for that column for the root row
New Pseudo columns
* CONNECT_BY_ISCYCLE –
* CONNECT_BY_ISLEAF

CONNECT_BY_ROOT

SELECT EMP_NAME, CONNECT_BY_ISLEAF
FROM emp
START WITH EMPNO = 159
CONNECT BY PRIOR EMP_ID = MGR_ID;

CONNECT_BY_ISCYCLE

SELECT LEVEL, LPAD(‘ ‘,2*(LEVEL – 1)) || EMP_NAME “EMPLOYEE”, EMPNO, MGR_ID
FROM EMP
START WITH EMPNO = 159
CONNECT BY NOCYCLE PRIOR EMPNO = MGR_ID;

CONNECT_BY_ISLEAF

SELECT EMP_NAME, CONNECT_BY_ISLEAF
FROM EMP
START WITH EMPNO = 159
CONNECT BY PRIOR EMPNO = MGR_ID;

**********************************************************************************

9. Insert or Update – Value too large ORA-12899

Till Oracle 9i its not possible to determine if inserted value for a column is too long – from Oracle 10g its possible to determine. – boon !!

SQL> insert into dept values (600, ‘Front Office’, ‘SINGAPORE’);
insert into dept values (600, ‘Front Office’, ‘SINGAPORE’)
*
ERROR at line 1:
ORA-12899: value too large for column
“SYSTEM”.”DEPT”.”DNAME” (actual: 12, maximum: 10)

Cool !!

**********************************************************************************

10. Inlining

Inlining – introduced in Oracle 11g. It replaces the call to to subroutine with a copy of the code. Prior to Oracle 11g every time a subprogram is called – a call to the original procedure is made. But, from Oracle 11g a copy of procedure runs which is faster than the original. “inlined”.

Though this does not happen automatically !! We need to set some parameters for PLSQL to find out automatically which parts of the code should be inlined.

1.PLSQL_OPTIMIZE_LEVEL – set it to a value 2 or 3
2 -> No automatic inlining ; 3 -> automatic inlining
How to set this? => ALTER PROCEDURE COMPILE PLSQL_OPTIMIZE_LEVEL = REUSE SETTINGS;
2.PRAGMA INLINE
This can be done within the PLSQL program. This can be either set to YES or NO depends on whether you want the code to be inlined or not.

It is recommended by Oracle that we should inline small and frequently called programs. Only local subprograms can be inlined. Cursor functions should not be inlined. By inlining huge programs – it could increase the size of the program. Caution to be taken while inlining DETERMINISTIC functions.

Example -
CREATE OR REPLACE PROCEDURE inline_proc
IS
v_value NUMBER;
FUNCTION multiply_1(p1 number, p2 number) RETURN NUMBER as
begin
RETURN p1 * p2;
end multiply_1;
BEGIN
pragma INLINE (inline_proc, ‘YES’);
v_value := multiply_1(5,6) ;
END inline_proc;

Note: To check how the subprogram is inlined we can either use DBMS_HPROF or Debugger in toad.

**********************************************************************************

11. PLSQL Packages

1. DBMS_DATAPUMP

Feature in Oracle 10g.

This feature provides high speed, parallel, bulk data and metadata movement of Oracle database contents. DBMS_DATAPUMP – this new package enables faster data and metadata movement. Oracle recommends to use this data and metadata movements rather than using Export and Import.

2. DBMS_SCHEDULER

This is a new packaged procedure in Oracle 10g

This feature is introduced in Oracle 10g. DBMS_JOB is depricated and is available only for backup compatibility. Oracle recommends not to use DBMS_JOB anymore as it could not exist in the future versions of Oracle.

Advantage of DBMS_SCHEDULER –
1.Oracle procedures, packaged procedure, packaged functions and functions can be executed
2.Binary and shell scripts can be scheduled.

For administering the job scheduling one need to have SCHEUDLER_ADMIN role. With DBMS_JOB initialization parameter is required but, the same is not needed for DBMS_SCHEDULER.

To create job -

BEGIN
DBMS_SCHEDULER.create_job (
job_name => ‘CREATE_JOB_TEST’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN NULL; END;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=hourly; byminute=0′,
end_date => NULL,
enabled => TRUE,
comments => ‘Job’);
END;
/

To create schedule -

begin
dbms_scheduler.create_schedule
(schedule_name => ”,
start_date=> trunc(sysdate)+18/24,
repeat_interval=> ‘FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;’,
comments=>’Sample job’);

To create program -

begin
dbms_scheduler.create_program
(program_name=> ‘Pgm_create’,
program_type=> ‘STORED_PROCEDURE’,
program_action=> ”,
enabled=>true,
comments=>’Procedure to create program’ );
end;

To set attribute -

begin
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ”,
attribute => ‘start_date’,
value => to_date(’22.02.2010 18:15′,’dd.mm.yyyy hh24:mi’)
);

To run a job -

begin
dbms_scheduler.run_job(”,TRUE);
end;

We can get information about scheduled jobs in – user_scheduler_jobs
——————————————————————————————————————

3 DBMS_CRYPTO

create or replace Function encrypt( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC
IS
V_Key RAW(128) := UTL_RAW.cast_to_raw(‘testkey1′); — Key
l_data RAW(2048) := utl_raw.cast_to_raw(p_data);
l_encrypted RAW(2048);
BEGIN
NULL;
l_encrypted := dbms_crypto.encrypt — Algorithm
( src => l_data,
typ => DBMS_CRYPTO.DES_CBC_PKCS5,
key => V_KEY );

Return l_encrypted;
END encrypt;
/

create or replace Function decrypt( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC
IS
V_Key RAW(128) := UTL_RAW.cast_to_raw(‘testkey1′); — Key
l_decrypted RAW(2048);
BEGIN
l_decrypted := dbms_crypto.decrypt — Algorithm
( src => p_data,
typ => DBMS_CRYPTO.DES_CBC_PKCS5,
key => V_KEY );

Return utl_raw.cast_to_varchar2(l_decrypted);
END decrypt;
/

——————————————————————————————————————

4 DBMS_SQLTUNE

Step 1 – Drop the task (if already exist to avoid error while creating)

exec DBMS_SQLTUNE.DROP_TUNING_TASK( task_name => ‘emp_tune’);

ORA-13605: The specified task or object emp_tune does not exist for the current user

Step 2 – Create and execute the task

declare
tuning_Task varchar2(500);
begin

tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => ‘select * from emp e;’,
time_limit => 1,
scope => ‘COMPREHENSIVE’,
task_name => ‘emp_tune’,
description => ‘Demo’
);

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => ‘emp_tune’);
end;
/

Step 3 – Select the report
set pagesize 0
set long 100000000
set longchunksize 1000
set linesize 180
set timing on trim spool on

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘emp_tune’) FROM dual;

RECOMMENDATIONS
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : emp_tune
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/01/2010 18:29:13
Completed at :05/01/201018:29:15

——————————————————————————-
SQL ID : Qwdvfv2yvswx1
SQL Text: SELECT e.*, d.* FROM emp e, dept d WHERE e.deptno = d.deptno
AND NVL(empno, ’0′) = :empno

——————————————————————————-
FINDINGS SECTION (2 findings)
——————————————————————————-

1- Statistics Finding
———————
Table “EMP” and its indices were not analyzed.

Recommendation
————–
Consider collecting optimizer statistics for this table and its indices.

**********************************************************************************

5 DBMS_FGA

BEGIN
DBMS_FGA.add_policy(
object_schema => ‘LUX’,
object_name => ‘EMP’,
policy_name => ‘COMM_AUDIT’,
audit_condition => NULL,
audit_column => ‘COMM’,
statement_types => ‘SELECT,INSERT,UPDATE,DELETE’);
END;
/

Other PL/SQL Procedures included in 10g & 11g -

Other important PL/SQL packages included are -

dbms_file
dbms_epg
utl_recomp
utl_dbws utl_i18n utl_lms
dbms_streams_tablespace_adm
dbms_warning
dbms_xmlstore
dbms_stat_funcs
dbms_streams_auth
dbms_streams_messaging
dbms_server_alert
dbms_service
dbms_sqltune
dbms_frequent_itemset
dbms_monitor
dbms_scheduler
dbms_dimension
dbms_file_transfer
dbms_frequent_itemset
dbms_advanced_rewrite
dbms_advisor
dbms_datapump

**********************************************************************************

12. Performance features

This topic is out and out based on the other topics. Performance features in Oracle 11g could be improved by using the following techniques -

A. Inlining instead of calling the sub-program over and again
B. Native Compilation for not too many sql related processes as it is pretty simple to set native compilation
C. Faster Triggers
D. Fast Dual
E. Result Caching
F. Adaptive Cursor Sharing
G. DBMS_SQLTUNE help
H. Optimizer and stats collection enhancements
I. DML Error logging vs. normal error handling
J. Merge with Delete (in place of separate Merge and Delete)

No new feature comes without a cost – always better to test the new feature before applying the same !!

**********************************************************************************

13. PLW-06009 Warning

New PLW warning is available now. This warning means that the OTHERS handler would exit without executing some form of RAISE or a call to the standard RAISE_APPLICATION_ERROR procedure. Best pl/sql practices suggest that the OTHERS handler must always pass an exception upward.

CREATE OR REPLACE PROCEDURE samp_num
IS
BEGIN
NULL;
EXCEPTION
WHEN OTHERS THEN null;
END samp_num;
/

Lets try to enable warnings and see what happens -

ALTER PROCEDURE samp_num COMPILE PLSQL_warnings = ‘enable:all’ REUSE SETTINGS;

SP2-0805: Procedure altered with compilation warnings

If we make it as OTHERS NULL then, it is marked as warning.

SQL> SHOW ERRORS
Errors for PROCEDURE SAMP_NUMM:

LINE/COL ERROR
——– —————————————————————
6/10 PLW-06009: procedure “P” OTHERS handler does not end in RAISE
or RAISE_APPLICATION_ERROR

**********************************************************************************

14. RBO obsoleted

Rule Based Optimizer – RBO is obsolete from Oracle 10g. We can still set the optimizer mode to RULE but just that no new features are implemented / will be implemented from Oracle 10g and it is depricated. The default optimizer mode is set to ALL_ROWS (Cost based optimizer).

Oracle 9i

Oracle 9i
SQL> show parameter optim

NAME TYPE VALUE
———————————— ———– ——————————
object_cache_optimal_size integer 102400
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.1.0.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE

NAME TYPE VALUE
———————————— ———– ——————————
plsql_optimize_level integer 2
SQL>

SQL> select * from emp;

40541 rows selected.

Elapsed: 00:00:08.42

Execution Plan
———————————————————-
Plan hash value: 3956160932

———————————-
| Id | Operation | Name |
———————————-
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
———————————-

Oracle 11g
SQL> show parameter optim

NAME TYPE VALUE
———————————— ———– ——————————
object_cache_optimal_size integer 102400
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.1.0.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE

NAME TYPE VALUE
———————————— ———– ——————————
plsql_optimize_level integer 2
SQL>

SQL> select * from emp;

40541 rows selected.

Elapsed: 00:00:08.42

Execution Plan
———————————————————-
Plan hash value: 3956160932

———————————-
| Id | Operation | Name |
———————————-
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
———————————-

Note
—–
– rule based optimizer used (consider using cbo)

**********************************************************************************

15. Secure file LOBs

Secure File LOBs (known as “FastFiles” in the beta releases) is an Oracle 11g database feature that provides faster access to unstructured data stored in LOB columns.
Write access to Secure Files is faster than a standard Linux file system, while read access is about the same. In addition, it features data compression, encryption and data deduplication.

Benefits

LOB compression
LOB encryption
Deduplication (detect duplicate LOB data and only store one copy)
Faster access to LOB data

CREATE TABLE secu_files (a NUMBER, l_clob CLOB)
lob (l_clob) STORE AS SECUREFILE secfile_segment_name
(TABLESPACE users
RETENTION AUTO
CACHE LOGGING
STORAGE (MAXEXTENTS 5))

Table created.

Deduplication – Quick overview -

It enables the storage of physical image for duplicate data and reduces space consumption. There are no negative impact on the read operations. This is part of the advanced compression option in 11g.

**********************************************************************************

16. XML Enhancements

Storing XML in Oracle is there for quite sometime now. We can store the XML data in the database either in unstructured or CLOB format. With Oracle 11g we are allowed to store in binary format also.

Improvements in Oracle 10g -

DBMS_XMLGEN now supports hierarchal queries
Import or export of XML data becomes very much possible

Improvements in Oracle 11g –

Binary format – Binary XML stores it after parsing the designed specifically for XML.

Create or replace directory xml_dir as ‘F:\app\Luxananda’

Create table test_binary_xml OF XMLTYPE XMLTYPE STORE AS BINARY XML
/
INSERT into test_binary_xml VALUES (XMLTYPE(BFILENAME, ‘xml_dir’, ‘sample_doc.xml’)))

XML Index – New universal index can be created on Binary and LOB based XMLType. All the possible paths are indexed by default.

**********************************************************************************

17. Autotrace / Tkprof

1 Autotrace

Autotrace is a handy utility that shows the “actual” execution statistics and explain plan of a query.

SET AUTOTRACE ON – Enables autotrace and displays the EXPLAIN PLAN of the query and displays the STATISTICS.
Here it actually executes the query
SET AUTOTRACE ON EXPLAIN – Displays the EXPLAIN PLAN and the results.
SET AUTOTRACE ON STATISTICS – Displays the STATISTICS and the results.
SET AUTOTRACE TRACEONLY – Does not display the results. Displays the EXPLAIN PLAN and STATISTICS. Executes the query.
SET AUTOTRACE TRACEONLY EXPLAIN – Displays only the EXPLAIN PLAN; Does not execute the query.
SET AUTOTRACE TRACEONLY STATISTICS – Executes the query and displays the statistics only.
SET AUTOTRACE OFF – Switches off autotrace.

Explain plan vs autotrace:

Explain plan ON – Shows what the database will do if the query is executed – it is an estimate.
Autotrace (SET AUTOTRACE …) – Displays the actual – what the database has actually done by firing the query – actual time taken, number of rows hit, consistent gets etc.,

Autotrace should always be the first tool – Just after writing the query its good to run the query in autotrace on full volume environment to see how much time, resources, rows etc., are retrieved by executing the query. Note – while running the query we tend to supply literals, but the actual program might have bind variable. We cannot say that the performance of a query with literals and bind variables would be exactly the same.

So, if your query has bind variable it is always good to trace like this so that you can have the explain plan same as you will get when the process runs.

set autotrace on

variable :var_name
exec :var_name :=

select * from

WHERE condition = :var_name;

ORACLE 9i
SQL> set autotrace traceonly

SQL> SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.com
m,qry.deptno,qry.row_desc from (SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC) ROW_ASC, COUNT(1) OVER (ORDER BY ROWNUM DESC) ROW_DESC FROM scott.EMP A) QRY whe
re qry.row_Asc between 1 and 11;

11 rows selected.

Execution Plan
———————————————————-
Plan hash value: 2175649969

—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost |
—————————————————————————-
| 0 | SELECT STATEMENT | | 14 | 1582 | 8 |
|* 1 | VIEW | | 14 | 1582 | 8 |
| 2 | WINDOW SORT | | 14 | 518 | 8 |
| 3 | WINDOW SORT | | 14 | 518 | 8 |
| 4 | COUNT | | | | |
| 5 | TABLE ACCESS FULL | EMP | 14 | 518 | 2 |
—————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – filter(“QRY”.”ROW_ASC”>=1 AND “QRY”.”ROW_ASC”<=11)

Note
—–
– cpu costing is off (consider enabling it)

Statistics
———————————————————-
124 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
1360 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
11 rows processed

ORACLE 11g
SQL> set autotrace traceonly

SQL> SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.comm,qry.deptno,qry.row_desc from (SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC) ROW_ASC, COUNT(1) OVER (ORDER BY ROWNUM DESC) ROW_DESC FROM EMP A) QRY where qry.row_Asc between 1 and 11;

11 rows selected.

Execution Plan
———————————————————-
Plan hash value: 2175649969

——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 14 | 1582 | 5 (40)| 00:00:01 |
|* 1 | VIEW | | 14 | 1582 | 5 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 518 | 5 (40)| 00:00:01 |
| 3 | WINDOW SORT | | 14 | 518 | 5 (40)| 00:00:01 |
| 4 | COUNT | | | | | |
| 5 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
——————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – filter(“QRY”.”ROW_ASC”>=1 AND “QRY”.”ROW_ASC”<=11)

Statistics
———————————————————-
419 recursive calls
0 db block gets
79 consistent gets
8 physical reads
0 redo size
1360 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
11 rows processed

——————————————————————————————————————

DBMS_XPLAN

DBMS_XPLAN is used to query the execution plan. It is now more improved with Oracle 10g and 11g. Internally it queries the PLAN_TABLE. Best part about this package is – filter and warning.

Oracle 9i

DELETE FROM PLAN_TABLE

SQL> EXPLAIN PLAN for
2 SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.com
m,qry.deptno,qry.row_desc from (SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC)
ROW_ASC, COUNT(1) OVER (ORDER BY ROWNUM DESC) ROW_DESC FROM scott.EMP A) QRY whe
re qry.row_Asc between 1 and 11;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–

Plan hash value: 2175649969

————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
————————————————————–
| 0 | SELECT STATEMENT | | 14 | 1582 | 8 |
|* 1 | VIEW | | 14 | 1582 | 8 |
| 2 | WINDOW SORT | | 14 | 518 | 8 |
| 3 | WINDOW SORT | | 14 | 518 | 8 |
| 4 | COUNT | | | | |
| 5 | TABLE ACCESS FULL| EMP | 14 | 518 | 2 |

PLAN_TABLE_OUTPUT
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————

1 – filter(“QRY”.”ROW_ASC”>=1 AND “QRY”.”ROW_ASC”<=11)

21 rows selected.

Oracle 11g

DELETE FROM PLAN_TABLE

EXPLAIN PLAN for
SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.comm,qry.deptno,qry.row_desc from (SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC) ROW_ASC, COUNT(1) OVER (ORDER BY ROWNUM DESC) ROW_DESC FROM scott.EMP A) QRY where qry.row_Asc between 1 and 11;

SELECT * FROM TABLE(dbms_xplan.display)

SQL>
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–

Plan hash value: 2175649969

——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 14 | 1582 | 5 (40)| 00:00:01 |
|* 1 | VIEW | | 14 | 1582 | 5 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 518 | 5 (40)| 00:00:01 |
| 3 | WINDOW SORT | | 14 | 518 | 5 (40)| 00:00:01 |
| 4 | COUNT | | | | | |
| 5 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————

1 – filter(“QRY”.”ROW_ASC”>=1 AND “QRY”.”ROW_ASC”<=11)

17 rows selected.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: