SYS_OP_C2C internal function, implicit data type converstion

Our database is heavily loaded. In addition, developers are writing codes that make even Exadata to hang 🙂  They are the best hackers ever :):)

Investigating database performance using 13c Cloud Control found one very interesting SQL.
At glance everything is fine, but there is one thing that is important and makes SQL  heavy.

Top SQL:

SELECT nvl(max(, 0) 
FROM schemaname.eb_restriction_balance bl 
WHERE bl.restrictcode = :b1


SQL> DESC schemaname.eb_restriction_balance

Name Type Nullable Default Comments 
-------------- ------------ -------- ------- -------- 

RESTRICTCODE column is indexed.

So in ideal way the above select should use the index on RESTRICTCODE.

Cloud Control shows that CBO chooses TABLE ACCESS STORAGE FULL.

You can run SQL Tuning Advisor from Cloud Control easily. Advisor generated the following recommendation:

The execution plan of this statement can be improved by creating one or more indices. Consider running the Access Advisor to improve the physical schema design or creating the recommended index.schemaname.EB_RESTRICTION_BALANCE(SYS_OP_C2C(“RESTRICTCODE”))

SYS_OP_C2C means that there happened implicit data type conversion.
So we must find the exact bind value that was used at the time sql was run.

SQL> SELECT name, datatype_string, value_string
 2 FROM v$sql_bind_capture
 3 WHERE sql_id='dnb1771sbm98x';

------- ------------------ -------------
:B1 NVARCHAR2(128) BR16215493
:B1 NVARCHAR2(128) BR16213680

As you see bind value type was NVARCHAR, that is why oracle converted varchar2 to nvarchar2 and did not use index on RESTRICTCODE.

Solution is to make developer change code and pass the parameter with the type varchar2.


How to determine if PGA is set properly

PGA is a memory region, that contains data and control information for a server process. This is not shared region. For each server process there is one PGA allocated.

In order to determine the proper size of PGA, we need to examine measuring criteria from performance views.

1. v$PGASTAT –displays PGA memory usage statistics.

select name,value
from v$pgastat
where name in ('aggregate PGA target parameter'
               ,'aggregate PGA auto target'
               ,'total PGA inuse'
               ,'total PGA allocated'
               ,'over allocation count'
               ,'extra bytes read/written'
               ,'cache hit percentage')

—My output

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
aggregate PGA target parameter                                   8589934592
aggregate PGA auto target                                         536870912
total PGA inuse                                                 18699225088
total PGA allocated                                             23640664064
over allocation count                                                187532
extra bytes read/written                                       305283407872
cache hit percentage                                                  99.2

7 rows selected
As you can see “total PGA inuse” is greater than “aggregate PGA target parameter” this happens when PGA_AGGREGATE_TARGET is too small and it in turn causes lots of system I/O as indicated “extra bytes read/written”.
2. v$SQL_WORKAREA – displays information about work areas used by SQL cursors. SQL statements, that are stored in the shared pool have one or more child cursor that are shown in V$SQL view. And v$SQL_WORKAREA shows all work areas needed by these child cursors.
The important columns of this view are the followings:

OPTIMAL_EXECUTIONS– number of times, this work area ran optimally without using temporary tablespace.

ONEPASS_EXECUTIONS– number of times, this work area used temporary tablespace only once to get it finished.

MULTIPASSES_EXECUTIONS-number of times, this work area used temporary tablespace in multiple times to get it finished.

Let’s see the total picture, how are they distributed:

select sum(optimal_executions) optimal,
       sum(onepass_executions) onepass,
       sum(multipasses_executions) multipass
from v$sql_workarea

—My output

------- ---------- ----------
0487582         51         16
We have 51 onepass and 16 multipass executions… They are not big numbers but it would be better if all of them would be optimal.
So we should think about properly adjusting PGA size.
3. If we join the following views v$SESSTAT, v$SYSSTAT and v$STATNAME we can see if PGA is properly set or not:

select,sum(a.value) value from v$sesstat a, v$statname b where a.STATISTIC#=b.STATISTIC# and like '%workarea executions – %' group by;

—My output

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
workarea executions - onepass                                            19
workarea executions - multipass                                           0
workarea executions - optimal                                       2633589


select, sum(a.value) value
from v$sysstat a, v$statname b
and like '%workarea executions - %'
group by;

—My output

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
workarea executions - onepass                                          4712
workarea executions - multipass                                         800
workarea executions - optimal                                    9760345515


The result above shows that PGA size is not properly set because number of onepass and multipass are considerable.

4. v$PGA_TARGET_ADVICE– view shows the predicted cache hit-ratio improvement.

select round(pga_target_for_estimate/1024/1024) pga_size_mb
from v$pga_target_advice;

—My output

----------- ----------------------------- --------------------
       1024                            96                38530
       2048                            97                28413
       4096                            97                19187
       6144                            97                17741
       8192                           100                16326
       9830                           100                15458
      11469                           100                15049
      13107                           100                14336
      14746                           100                13136
      16384                           100                10570
      24576                           100                    0
      32768                           100                    0
      49152                           100                    0
      65536                           100                    0

Setting PGA to 24576MB can eliminate over allocation count and gain maximum hit ratio.

To change PGA size:

alter system set pga_aggregate_target=24576m;

SQL Access Advisor(Tuning a single sql)

SQL Access Advisor Overview

The SQL Access Advisor gives you the recommendations about performance tuning. For example it may recommend you to create materialized views, partitions, and/or indexes..You should just give it sql/sqls which are somehow problematic.For example sql which needs big amount of time to execute.

Now  I will discuss Quick Tune, because it is a simple way to tune sqls and also it is widely used . But its “disadvantage” may be counted that it tunes just one sql than multiple sqls(which can be performed by workloads).

Privileges Needed to Use the SQL Access Advisor

You need to have the ADVISOR privilege to manage or use the SQL Access Advisor.

If that user does not have SELECT privileges to a particular table, the SQL Access Advisor neglects  the statement referencing the table. To avoid missing recommendations, the current database user must have SELECT privileges on the tables.

Performing a Quick Tune

QUICK_TUNE procedure accepts as its input a task_name and a SQL statement text or id. It will then create a task and workload and execute that task.

                        'SELECT COUNT(*)
                         FROM CLIENTS');

To see the result:

FROM dual;

Interested Transaction Lists (ITL) Waits

When I’ve generated AWR report, there I’ve noticed “Segments by ITL Waits” at this moment I‘ve not had any idea what was that. But after searching it on the internet I found very good information…Now, I want to share it with you.

First of all let’s discuss what ITLs is:

When a row is locked by a transaction, this information is placed in the block(header), where this row exists. The portion of the block header, which saves this information(transaction address and rowid) is called ITLs(Interested Transaction Lists).

ITLs contains several slots, initial number of slots are defined by the parameter INITRANS, which is indicated during the creation of the object (table, index) and can be increased till MAXTRANS.

  COL2 VARCHAR2(50))

When one transaction tries to modify row, one slot will be filled by the info about this transaction(Assume that transaction is not finished, means commit or rollback is not performed). If another transaction will try to change the row within this block, this process will wait till the first transaction will not end.

Question is why?

Because for the second transaction there(in the block header) must be allocated another free slot, but free slot doesn’t exist and also slots number can’t increase because of  MAXTRANS is 1 which equals to the number of already allocated slot. At this time increasing MAXTRANS will solve the problem, to change MAXTRANS do as follows:

–Maximum 10 slots can be allocated(10 transaction can be placed)

Alter table test_tab MAXTRANS 10;

What is ITL wait?

When slot can’t be increased and free slots still not available… Following transaction will wait until any slot will not be available, as we have had previously… This wait is called ITL waits. We solved this waits by increasing MAXTRANS but there may happen another problem. That ,for example we have initially 1 slot(INITRANS is 1) and can be allocated maximum 10 slots (MAXTRANS is 10), info  about one transaction is placed in one slot, then  comes another transaction and its info can’t be placed in the next slot(causes waiting) but as we know we can have 9 other available slots, still can’t be placed, why?

Let me draw some pictures so that you can easily guess what happens.

–Newly created block with initially one slot(during creation table we indicated INITRANS 1) looks like this:

Empty Block with one ITL slot

–Three rows are inserted in the block.

Full block with one ITL slot

–One transaction modified one row and did not commit. The Info is written to the available slot.

Full block with one alocated ITL slot(locking,filling ITL slot)

–Another transaction comes and wants to modify row, consequently info about this transaction should be placed in another free slot(do not forget we have initially one slot and number of this slots can be increased till 10)

Full block with one alocated ITL slot and not enought space for another slot

Note:In this case there is not empty space for new slot, that is why it can’t be allocated… If we have had indicated initially more slots another transaction would be able to be placed, as you can guess to solve this problem you should indicate bigger INITRANS during creation of object, or alter object and change this parameter…For the blocks which are already full as I can guess it will not affect, but for future it will be usefull. However this also means that there is less space in the block for actual data.

Global Hints

Oracle hints just refer to the table in the query. But there exist GLOBAL HINTS which allows you specify hints for a table within a view.

For example hint index:

SELECT /*+ index( indx_name) */col1,
col3 - 15,
FROM v_standard_struct
WHERE col1= 910
AND col2 = 2
ORDER BY col4;
Note: I have written “.ss”-> alias of the table within a view, not the actual name. When table has alias, you must use it. If you forget it and write the actual name of the table, but not alias, it will not work.(If table doesn’t have an alias,you must use the actual name.)