Friday, February 21, 2014

Cardinality feedback changed in 11.2.0.2

Recently noticed a change to Cardinality feedback with 11gR2 (Patch set 11.2.0.2).
In 11.2.0.1 Cardinality feedback kicks in immediately when there is major deviation in cardinality after first execution, but with 11.2.0.2 we need to tweak that with /*+ materialize */ hints.

rajesh@ORA11G>select * from v$version;

BANNER
-----------------------------------------------------------------------------
Personal Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production

rajesh@ORA11G>select * from table( parse_list(:x) );

COLUMN_VALUE
--------------------
a
b
c
d

rajesh@ORA11G>select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  cd280w5k60sbz, child number 0
-------------------------------------
select * from table( parse_list(:x) )

Plan hash value: 58440541

------------------------------------------------------------------------
| Id  | Operation                         | Name       | Rows  | Bytes |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |            |       |       |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |  8168 | 16336 |
------------------------------------------------------------------------


13 rows selected.

rajesh@ORA11G>select * from table( parse_list(:x) );

COLUMN_VALUE
--------------------
a
b
c
d

rajesh@ORA11G>select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  cd280w5k60sbz, child number 1
-------------------------------------
select * from table( parse_list(:x) )

Plan hash value: 58440541

------------------------------------------------------------------------
| Id  | Operation                         | Name       | Rows  | Bytes |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |            |       |       |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |     8 |    16 |
------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement


But starting with 11.2.0.2 this behaves differently, you need to force /*+ materialize */ to have Cardinality feedback kicks in.

rajesh@ORA11G2.2> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

5 rows selected.

rajesh@ORA11G2.2>
rajesh@ORA11G2.2> select * from table( parse_list(:x) );

COLUMN_VALUE
--------------------
a
b
c
d

4 rows selected.

rajesh@ORA11G2.2> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  cd280w5k60sbz, child number 0
-------------------------------------
select * from table( parse_list(:x) )

Plan hash value: 58440541

------------------------------------------------------------------------
| Id  | Operation                         | Name       | Rows  | Bytes |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |            |       |       |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |  8168 | 16336 |
------------------------------------------------------------------------

rajesh@ORA11G2.2> select * from table( parse_list(:x) );

COLUMN_VALUE
--------------------
a
b
c
d

4 rows selected.

rajesh@ORA11G2.2> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  cd280w5k60sbz, child number 0
-------------------------------------
select * from table( parse_list(:x) )

Plan hash value: 58440541

------------------------------------------------------------------------
| Id  | Operation                         | Name       | Rows  | Bytes |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |            |       |       |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |  8168 | 16336 |
------------------------------------------------------------------------


13 rows selected.

rajesh@ORA11G2.2>


But when added /*+ materialize */ hint, execution changes like this.


rajesh@ORA11G2.2> with t as
  2  ( select /*+ materialize */ * from table( parse_list(:x) ) )
  3  select * from t
  4  /

COLUMN_VALUE
--------------------
a
b
c
d

4 rows selected.

rajesh@ORA11G2.2>
rajesh@ORA11G2.2> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  949hj409nczd2, child number 0
-------------------------------------
with t as ( select /*+ materialize */ * from table( parse_list(:x) ) )
select * from t

Plan hash value: 3775689547

-------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Rows  | Bytes |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                             |       |       |
|   1 |  TEMP TABLE TRANSFORMATION          |                             |       |       |
|   2 |   LOAD AS SELECT                    |                             |       |       |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST                  |  8168 | 16336 |
|   4 |   VIEW                              |                             |  8168 | 98016 |
|   5 |    TABLE ACCESS FULL                | SYS_TEMP_0FD9D664B_4C1BF2F9 |  8168 | 16336 |
-------------------------------------------------------------------------------------------


18 rows selected.

rajesh@ORA11G2.2> with t as
  2  ( select /*+ materialize */ * from table( parse_list(:x) ) )
  3  select * from t
  4  /

COLUMN_VALUE
--------------------
a
b
c
d

4 rows selected.

rajesh@ORA11G2.2>
rajesh@ORA11G2.2> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  949hj409nczd2, child number 1
-------------------------------------
with t as ( select /*+ materialize */ * from table( parse_list(:x) ) )
select * from t

Plan hash value: 556187288

-------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Rows  | Bytes |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                             |       |       |
|   1 |  TEMP TABLE TRANSFORMATION          |                             |       |       |
|   2 |   LOAD AS SELECT                    |                             |       |       |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST                  |  8168 | 16336 |
|   4 |   VIEW                              |                             |     4 |    48 |
|   5 |    TABLE ACCESS FULL                | SYS_TEMP_0FD9D664C_4C1BF2F9 |     4 |     8 |
-------------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement


22 rows selected.

rajesh@ORA11G2.2>
Posted by at 1 comment:

Thursday, February 20, 2014

ORA-24247 error with SQL Developer on Oracle 12c

The PLSQL Debugger in SQL Developer works fine with Oracle version prior to 12c. These are the things we need in place to start debugging PL/SQL

              1.       A grant of DEBUG CONNECT SESSION privilege
              2.       A execute privilege on DBMS_DEBUG_JDWP.
              3.       Execute privilege on the procedure to debug.                                                         (make sure the procedure is Compiled for Debug )

But, even if you have these privileges in Oracle 12c, you will end up with this error message.

Connecting to the database RAJESH_12c.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP('10.191.59.133', '50504')
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database RAJESH_12c.

Starting with Oracle 12c, if you want to debug PL/SQL stored procedures in the database through a Java Debug Wire Protocol (JDWP)-based debugger, such as SQL Developer or JDeveloper, then you must be granted the jdwp ACL privilege to connect your database session to the debugger at a particular host.

The one way you can configure network access for JDWP operations:

sys@PDB1> begin
  2  dbms_network_acl_admin.append_host_ace
  3  (host=>'PC143855',
  4   ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
  5                         principal_name=>'rajesh',
  6                         principal_type=>sys.XS_ACL.PTYPE_DB) );
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.53
sys@PDB1>

Once done the error goes away

Connecting to the database RAJESH_12c.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( 'PC143855', '59255' )
Debugger accepted connection from database on port 59255.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Hello World
Process exited.
Disconnecting from the database RAJESH_12c.
Debugger disconnected from database.
Posted by at 6 comments:
Subscribe to: Posts (Atom)