GATHERING STATISTICS FASTER FOR LARGE DATABASE ORACLE

There are scenarios where statistics need to be gathered after an import of database after ignoring statistics with datapump. It is suggested to use DBMS_STATS package rather than datapump to collect statistics faster for the newly imported objects. What if, the database is too large? Let us see some methods and case studies to observe the faster ways to collect statistics for large database.

Solution is “high resource” and nothing magic here

Check the size of the target schema for gathering statistics.

SQL> SELECT distinct ds.segment_name,ds.bytes/1048576 MB,dt.degree
         FROM dba_Segments ds
  INNER JOIN dba_tables dt on ( dt.owner = ds.owner )
  WHERE
 dt.owner='C##HYDRA'
  ORDER BY MB desc;

Check the size of the database and top 5 big tables under the schema which contain big tables.

SQL> SELECT sum(bytes/1073741824) DB_GB from dba_segments;

     DB_GB
----------
33.2966919

SQL> col SEGMENT_NAME for a20
SQL> SELECT * from (SELECT distinct ds.segment_name,ds.bytes/1048576 MB,dt.degree FROM dba_Segments ds INNER JOIN dba_tables dt on ( dt.owner = ds.owner ) ORDER BY MB desc) WHERE rownum < 6;

SEGMENT_NAME                 MB DEGREE
-------------------- ---------- ----------------------------------------
ADDRESSES                  8422          1
ORDER_ITEMS                7350          1
CNS                   4315.4375          1
CNS                   4315.4375          4
UN                         4287          1

To monitor the stats job, use the below query.

SQL> set lines 200
set pages 1000
col OPNAME format a20
col MESSAGE format a20
col username format a10
col opname format a10
col sid format 999999
col serial# format 999999
col TIME_REMAINING format 999999
col pct format 9999999
select sid,
       serial#,
       USERNAME,
       opname,
       100*(sofar/totalwork) as pct,
       to_char(START_TIME,'dd-mm-yy hh24:mi:ss') as starttime,
       to_char(LAST_UPDATE_TIME,'dd-mm-yy hh24:mi:ss') as change,
       TIME_REMAINING,
       ELAPSED_SECONDS,
       MESSAGE
from v$session_longops
where  sofar < totalwork
and message like '%Stat%';SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13

    SID SERIAL# USERNAME   OPNAME          PCT STARTTIME         CHANGE            TIME_REMAINING ELAPSED_SECONDS MESSAGE
------- ------- ---------- ---------- -------- ----------------- ----------------- -------------- --------------- --------------------
     73   25839 SYS        Gather Dat       47 01-07-23 14:15:53 01-07-23 14:36:35           1375            1242 Gather Database Stat
                           abase Stat                                                                             istics: Database : 1
                           istics                                                                                 095 out of 2307 Obje
                                                                                                                  cts done

     73   25839 SYS        Gather Tab       20 01-07-23 14:44:38 01-07-23 14:46:17            396              99 Gather Table's Index
                           le's Index                                                                              Statistics: Table S
                            Statistic                                                                             ALES : 1 out of 5 In
                           s                                                                                      dexes done

     73   25839 SYS        Gather Dat       47 01-07-23 14:47:47 01-07-23 15:09:44           1458            1317 Gather Database Stat
                           abase Stat                                                                             istics: Database : 1
                           istics                                                                                 095 out of 2307 Obje
                                                                                                                  cts done

Flush the shared pool and buffer cache and start the statistics job as a benchmark. Consider that this is the first time, the stats job is executed in the database and this time should be the initial baseline performance for stats job.

SQL>  alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> SET TIMING ON
SQL> EXEC dbms_stats.gather_database_stats();

PL/SQL procedure successfully completed.

Elapsed: 00:46:11.69

It took 46 minutes for stats for whole database to complete.

Things to review are to check the configuration of parameters for stats preference.

  • Notice degree and concurrent parameters mainly which are the influencers to improve performance.
  • Also check for estimate_percent parameter which is related to the sample size of the stats gathering process. If this value is too low(5), then stats may be collected faster but with a trade off of less accuracy which means optimizer may generate suboptimal execution plans down the line. If the value is high(100), then the time taken may increase compared to low value as accurate samples are collected at granular level.
  • If the table is partitioned, then check for INCREMENTAL and INCREMENTAL_LEVEL if they are disabled. Enable the parameter immediately as this may benefit 10x by avoiding the full table scan during partition statistics collection.
SQL> select 'AUTOSTATS_TARGET =====> '||DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET')||'' as "STATS_PARAM" from dual
 union all
select 'CASCADE =====> '||DBMS_STATS.GET_PREFS('CASCADE')||'' from dual
 union all
select 'CONCURRENT =====> '||DBMS_STATS.GET_PREFS('CONCURRENT')||'' as "STATS_PARAM" from dual
 union all
select 'DEGREE =====> '||DBMS_STATS.GET_PREFS('DEGREE')||'' from dual
 union all
select 'ESTIMATE_PERCENT =====> '||DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')||'' from dual
 union all
select 'GRANULARITY =====> '||DBMS_STATS.GET_PREFS('GRANULARITY')||'' from dual
 union all
select 'INCREMENTAL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL')||'' from dual
 union all
select 'INCREMENTAL_LEVEL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_LEVEL')||'' from dual
 union all
select 'INCREMENTAL_STALENESS =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS')||'' from dual
 union all
select 'METHOD_OPT =====> '||DBMS_STATS.GET_PREFS('METHOD_OPT')||'' from dual
 union all
select 'OPTIONS =====> '||DBMS_STATS.GET_PREFS('OPTIONS')||'' from dual
 union all
select 'STALE_PERCENT =====> '||DBMS_STATS.GET_PREFS('STALE_PERCENT')||'' from dual; 10   11   12   13   14   15   16   17   18   19   20   21   22   23

STATS_PARAM
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AUTOSTATS_TARGET =====> AUTO
CASCADE =====> DBMS_STATS.AUTO_CASCADE
CONCURRENT =====> AUTO
DEGREE =====> NULL
ESTIMATE_PERCENT =====> DBMS_STATS.AUTO_SAMPLE_SIZE
GRANULARITY =====> AUTO
INCREMENTAL =====> FALSE
INCREMENTAL_LEVEL =====> PARTITION
INCREMENTAL_STALENESS =====>
METHOD_OPT =====> FOR ALL COLUMNS SIZE AUTO
OPTIONS =====> GATHER
STALE_PERCENT =====> 10

12 rows selected.

Delete statistics and regather stats with parallel servers. Do not allocate parallel for tables which are less than 1 GB in size which does not get benefitted from parallel.

SQL> EXEC dbms_stats.delete_database_stats();

PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> ALTER table c##hydra.ADDRESSES parallel 4;

Table altered.

SQL> ALTER table c##hydra.ORDER_ITEMS parallel 4;

Table altered.

SQL> ALTER table CNS parallel 4;

Table altered.

SQL> ALTER table c##hydra.ORDERS parallel 4;

Table altered.

SQL> ALTER table kish.sts parallel 2;

Table altered.

1.Check the settings of concurrent jobs which if set to manual, then concurrent jobs may use additional resources like CPU and MEMORY to further aggressively improve the performance but provided with more resource.
2. Make sure that the settigns for parallel max servers is correctly set as per the formula. ==> parallel_max_servers = cpu_count * parallel_threads_per_cpu * 5 * 2(if pga_aggregate_target parameter is set else 0)
3. Parallel degree policy plays a major role on how the parallel servers are controlled either manually or automatically. If set to manual then degree parameter is taken from table definition settings.(SELECT degree from dba_tables where table_name=<table>;) If set to auto, then parallel_degree_limit should be set to CPU so that oracle automatically control the parallel usage upto less than or equal to CPU threshold.

SQL> select dbms_stats.get_prefs('CONCURRENT') from dual;

DBMS_STATS.GET_PREFS('CONCURRENT')
--------------------------------------------------------------------------------
MANUAL


SQL> EXEC dbms_stats.set_global_prefs('DEGREE','DBMS_STATS.AUTO_DEGREE');

PL/SQL procedure successfully completed.

SQL> show parameter parallel_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     100

SQL> ALTER system set parallel_max_servers=200;

System altered.

SQL> ALTER system set parallel_degree_policy=MANUAL;

System altered.

Other option is to configure resource manager and allocate majority of the resource to statistics jobs.

SQL> EXEC dbms_stats.gather_database_stats();
BEGIN dbms_stats.gather_database_stats(); END;

*
ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: Resource Manager is not
enabled.
ORA-06512: at "SYS.DBMS_STATS", line 36309
ORA-06512: at line 1

SQL> BEGIN
 DBMS_RESOURCE_MANAGER.clear_pending_area;
 DBMS_RESOURCE_MANAGER.create_pending_area;

 -- Create plan
 DBMS_RESOURCE_MANAGER.create_plan(
   plan    => 'hybrid_plan',
   comment => 'Plan for a combination of high and low priority tasks.');

 -- Create consumer groups
  2    3    4    5    6    7    8    9   10   11   DBMS_RESOURCE_MANAGER.create_consumer_group(
   consumer_group => 'Gather_stats',
   comment        => 'Gather_stats - high priority');
 12   13   14
 DBMS_RESOURCE_MANAGER.create_consumer_group(
   consumer_group => 'Other',
   comment        => 'Other - low priority');

 -- Assign consumer groups to plan and define priorities
 DBMS_RESOURCE_MANAGER.create_plan_directive (
   plan             => 'hybrid_plan',
   group_or_subplan => 'Gather_stats',
   comment          => 'High Priority - level 1',
   cpu_p1           => 100,
   cpu_p2           => 100,
 15   16   17   18   19   20   21   22   23   24   25     cpu_p3           => 80);

 DBMS_RESOURCE_MANAGER.create 26  _plan_directive (
   plan             => 'hybrid_plan',
   group_or_subplan => 'Other',
   comment          => 'Low Priority - level 2',
   cpu_p1           => 0,
   cpu_p2           => 0,
   cpu_p3           => 20);

 27   28   29   30   31   32   33   34   35   36   DBMS_RESOURCE_MANAGER.validate_pending_area;
 DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/ 37   38   39
BEGIN
*
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 1003
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 114
ORA-06512: at line 6

SQL> DECLARE
  x_plan VARCHAR2(30) := 'Gather_stats';
BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  DBMS_RESOURCE_MANAGER.create_cdb_plan(
    plan    => 'x_plan',
    comment => 'Plan for statistics job');
  DBMS_RESOURCE_MANAGER.create_cdb_plan_directive(
    plan                  => 'x_plan',
    pluggable_database    => 'updb',
    shares                => 3,
    utilization_limit     => 100,
    parallel_server_limit => 100);
  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/  5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

PL/SQL procedure successfully completed.

Configure the resource plan and activate it so that the future jobs will use the plan for resource allocation.

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     TRUE
resource_manager_cpu_allocation      integer     4
resource_manager_plan                string
SQL> ALTER system set resource_manager_plan=x_plan;

System altered.

Once the resource manager plan directive is configured, then monitor the system for a while on how the CPU process is distributed across PDB’s

top - 14:31:18 up 21 min,  2 users,  load average: 12.31, 12.07, 8.83
Tasks: 308 total,   2 running, 306 sleeping,   0 stopped,   0 zombie
%Cpu(s): 14.6 us,  1.2 sy,  0.0 ni,  0.0 id, 83.1 wa,  0.9 hi,  0.1 si,  0.0 st
MiB Mem :   9670.4 total,     61.8 free,   1148.2 used,   8460.4 buff/cache
MiB Swap:   4096.0 total,   4082.9 free,     13.1 used.   2419.7 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   2405 oracle    20   0 6507416 510944 505968 D  16.9   5.2   0:29.23 ora_p005_hydrup
   2318 oracle    20   0 6507072 574400 569196 D  15.6   5.8   0:28.31 ora_p001_hydrup
   2401 oracle    20   0 6506920 503568 498504 D   6.3   5.1   0:30.86 ora_p003_hydrup
   2320 oracle    20   0 6508108 555344 550468 D   5.3   5.6   0:31.49 ora_p002_hydrup
   2409 oracle    20   0 6507176 462420 457508 D   5.3   4.7   0:28.64 ora_p007_hydrup
   2407 oracle    20   0 6507448 528824 523288 D   4.3   5.3   0:31.62 ora_p006_hydrup
   2403 oracle    20   0 6507180 503548 498812 D   3.0   5.1   0:29.91 ora_p004_hydrup

Check the long running stats job from v$session_longops to get an idea on the progress.

SQL> SELECT TARGET,STATUS,JOB_NAME,NOTES from dba_optstat_operations WHERE START_TIME > SYSDATE - (1/24);

TARGET               STATUS               JOB_NAME             NOTES
-------------------- -------------------- -------------------- --------------------
                     COMPLETED                                 <params><param name=
                                                               "force" val="FALSE"/
                                                               ><param name="no_inv
                                                               alidate" val="NULL"/
                                                               ><param name="statid
                                                               " val=""/><param nam
                                                               e="statown" val=""/>
                                                               <param name="stattab
                                                               " val=""/><param nam
                                                               e="stattype" val="AL
                                                               L"/></params>

                     IN PROGRESS                               <params><param name=
                                                               "block_sample" val="
                                                               FALSE"/><param name=
                                                               "cascade" val="NULL"
                                                               /><param name="concu
                                                               rrent" val="TRUE"/><
                                                               param name="degree"
                                                               val="DEFAULT_DEGREE_
                                                               VALUE"/><param name=
                                                               "estimate_percent" v
                                                               al="DEFAULT_ESTIMATE
                                                               _PERCENT"/><param na
                                                               me="gather_fixed" va
                                                               l="FALSE"/><param na
                                                               me="gather_sys" val=
                                                               "TRUE"/><param name=
                                                               "gather_temp" val="F
                                                               ALSE"/><param name="
                                                               granularity" val="DE
                                                               FAULT_GRANULARITY"/>
                                                               <param name="method_
                                                               opt" val="DEFAULT_ME
                                                               THOD_OPT"/><param na
                                                               me="no_invalidate" v
                                                               al="NULL"/><param na
                                                               me="options" val="GA
                                                               THER"/><param name="
                                                               reporting_mode" val=
                                                               "FALSE"/><param name
                                                               ="statid" val=""/><p
                                                               aram name="statown"
                                                               val=""/><param name=
                                                               "stattab" val=""/><p
                                                               aram name="stattype"
                                                                val="DATA"/></param
                                                               s>

Now execute the stats for the second time with parallel settings updated. It took 54 minutes to complete with parallel settings for specific big tables. However, this has not improved the overall performance of the database statistics.

SQL> EXEC dbms_stats.gather_database_stats();

PL/SQL procedure successfully completed.

Elapsed: 00:54:28.14

Check the existing plan directives for the resource plan X_PLAN which was configured before.

SQL> SELECT PLAN,PLUGGABLE_DATABASE "PDB",SHARES,UTILIZATION_LIMIT,PARALLEL_SERVER_LIMIT,MEMORY_LIMIT,COMMENTS from dba_cdb_rsrc_plan_directives WHERE PLAN='X_PLAN';

PLAN                           PDB                      SHARES UTILIZATION_LIMIT PARALLEL_SERVER_LIMIT MEMORY_LIMIT COMMENTS
------------------------------ -------------------- ---------- ----------------- --------------------- ------------ --------------------
X_PLAN                         ORA$AUTOTASK                                   90                   100              Autotask Directive
X_PLAN                         UPDB                          3               100                   100
X_PLAN                         ORA$DEFAULT_PDB_DIRE          1               100                   100              Default Directive
                               CTIVE

Allocate all the parallel resource upto 100% to the PDB which contain the big tables.

SQL> DECLARE
  l_plan VARCHAR2(30) := 'Gather_stats';
BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  DBMS_RESOURCE_MANAGER.create_cdb_plan(
    plan    => 'x3_plan',
    comment => 'Plan for statistics job');

  DBMS_RESOURCE_MANAGER.create_cdb_plan_directive(
    plan                  => 'x3_plan',
    pluggable_database    => 'updb',
    shares                => 100,
    utilization_limit     => 100,
    parallel_server_limit => 100);
  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

PL/SQL procedure successfully completed.

Validate if all the parallel processes are utilized after allocating 100% parallel to updb

SQL> SELECT count(*) FROM  v$px_process WHERE status <> 'AVAILABLE';

  COUNT(*)
----------
        56

It took 43 minutes after allocating 100% resource to updb which contain huge tables

SQL> set timing on
SQL> EXEC dbms_stats.gather_database_stats();

PL/SQL procedure successfully completed.

Elapsed: 00:43:42.06

When we enable restricted session to suppress other activities in the database,

Never ending process due to restricted session. Because parallel slaves are not used for stats gathering.

SQL> SET TIMING ON
SQL> ALTER system enable restricted session;

System altered.

SQL> EXEC dbms_stats.gather_database_stats();

^Z^CBEGIN dbms_stats.gather_database_stats(); END;

*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_AQ", line 366
ORA-06512: at "SYS.DBMS_STATS", line 5710
ORA-06512: at "SYS.DBMS_STATS", line 6594
ORA-06512: at "SYS.DBMS_STATS", line 34846
ORA-06512: at "SYS.DBMS_STATS", line 35711
ORA-06512: at "SYS.DBMS_STATS", line 36230
ORA-06512: at "SYS.DBMS_STATS", line 36301
ORA-06512: at line 1


Elapsed: 03:14:49.46

For aggressive speed of the stats gathering, set the following if more CPU is available for oracle database.

Warning: Always test the parameters and never blindly set CPU and parallel thread parameters in a production system.

[oracle@hydrupgrd ~]$ lscpu|grep 'CPU(s):'|grep -v 'NUMA'
CPU(s):              4

SQL> show parameter cpu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     1
parallel_threads_per_cpu             integer     1
resource_manager_cpu_allocation      integer     4

SQL> EXEC dbms_stats.set_global_prefs('CONCURRENT','TRUE');

PL/SQL procedure successfully completed.
SQL> ALTER system set cpu_count=4;

System altered.
SQL> ALTER system set parallel_threads_per_cpu=2;

System altered.

SQL> ALTER system set parallel_max_servers=80;

System altered.
SQL> ALTER system set parallel_servers_target=60;

System altered.

SQL> ALTER system set job_queue_processes=200;

System altered.

SQL> ALTER system set resource_manager_plan=x_plan;

System altered.
SQL> ALTER system set parallel_execution_message_size=32768 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area  637534208 bytes
Fixed Size                  2927624 bytes
Variable Size             494928888 bytes
Database Buffers          134217728 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.

Execute the stats job after changes and check the load on the server.


top – 14:22:46 up 3:29, 2 users, load average: 7.39, 5.87, 6.49
Tasks: 481 total, 1 running, 479 sleeping, 1 stopped, 0 zombie
%Cpu(s): 4.9 us, 0.4 sy, 0.0 ni, 28.7 id, 65.4 wa, 0.6 hi, 0.0 si, 0.0 st
MiB Mem : 9670.4 total, 72.0 free, 1895.5 used, 7702.9 buff/cache
MiB Swap: 4096.0 total, 4087.0 free, 9.0 used. 7333.9 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
16455 oracle 20 0 1002628 247140 231664 S 11.9 2.5 0:06.72 ora_j027_hydrup
16052 oracle 20 0 1058584 295500 251372 S 2.6 3.0 0:04.98 oracle_16052_hy
16213 oracle 20 0 1002536 240012 230148 S 2.3 2.4 0:04.90 ora_j164_hydrup
15121 oracle 20 0 992500 249032 243396 S 1.0 2.5 0:07.52 ora_j004_hydrup
14809 oracle -2 0 985180 50068 48056 S 0.7 0.5 0:10.80 ora_vktm_hydrup
14917 oracle 20 0 987120 188932 185096 D 0.7 1.9 0:06.50 ora_p003_hydrup
16983 oracle 20 0 65872 5384 4212 R 0.7 0.1 0:00.32 top
113 root 0 -20 0 0 0 I 0.3 0.0 0:01.30 kworker/3:1H-kblockd
14815 oracle 20 0 985180 174148 172108 S 0.3 1.8 0:00.32 ora_mman_hydrup
14823 oracle 20 0 985180 57620 55580 S 0.3 0.6 0:01.95 ora_vkrm_hydrup
14861 oracle 20 0 989660 180228 176572 S 0.3 1.8 0:03.39 ora_p001_hydrup
14863 oracle 20 0 987780 195612 191312 D 0.3 2.0 0:05.33 ora_p002_hydrup
15470 oracle 20 0 988032 134944 131152 S 0.3 1.4 0:00.05 ora_j068_hydrup
15778 oracle 20 0 988152 179724 175272 S 0.3 1.8 0:00.37 ora_j221_hydrup
15886 oracle 20 0 987104 201764 197664 S 0.3 2.0 0:00.85 ora_j066_hydrup
16647 oracle 20 0 987008 162324 158608 S 0.3 1.6 0:00.34 ora_j242_hydrup
16952 oracle 20 0 1002712 263716 246444 D 0.3 2.7 0:07.07 ora_j002_hydrup

Check CPU usage now.

SQL> set lines 250
set pages 2000
col username format a15 justify center
col program format a20 justify center
col event format a20 justify center
col sid format 999999 justify center
col CPU_USAGE format 999999999 justify center
col CPUPCT format 9999999999 justify center
col value format 999999
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> col PCT format 999999.99
with "CPUPCT" as (select round(sum(value),3) as total_cpu from v$sesstat)
select * from (select z.sid,z.status,nvl(z.username,'oracle-bg') as username,nvl(z.SQL_ID,'non-SQL') as SQL_ID,z.EVENT,z.program,
SQL>   2    3  round(sum(y.value)/100,6) as "CPU_USAGE",round(trunc(100*round(sum(y.value/100)),3)/(select * from CPUPCT),3) as PCT
from v$statname x
  4    5  inner join v$sesstat y on x.STATISTIC# = y.STATISTIC#
inner join v$session z on y.SID = z.SID
  6    7  where x.name in ('CPU used by this session')
group by z.sid,z.username,z.SQL_ID,z.EVENT,z.program,z.status order by CPU_USAGE desc)
where rownum < 6;  8    9

  SID   STATUS      USERNAME     SQL_ID               EVENT               PROGRAM        CPU_USAGE         PCT
------- -------- --------------- ------------- -------------------- -------------------- ---------- ----------
    161 ACTIVE   SYS             6hwah6nnt0dhd db file sequential r oracle@hydrupgrd (J0         11        .00
                                               ead                  27)

     11 ACTIVE   oracle-bg       non-SQL       rdbms ipc message    oracle@hydrupgrd (CJ          8        .00
                                                                    Q0)

    554 ACTIVE   SYS             4xg0vm951zzpx db file sequential r sqlplus@hydrupgrd (T          6        .00
                                               ead                  NS V1-V3)

    146 ACTIVE   oracle-bg       non-SQL       rdbms ipc message    oracle@hydrupgrd (MM          3        .00
                                                                    ON)

    308 ACTIVE   oracle-bg       non-SQL       EMON slave idle wait oracle@hydrupgrd (Q0          3        .00
                                                                    0E)


SQL> /

  SID   STATUS      USERNAME     SQL_ID               EVENT               PROGRAM        CPU_USAGE         PCT
------- -------- --------------- ------------- -------------------- -------------------- ---------- ----------
    161 ACTIVE   SYS             5jw2nd7s3kda0 PX Deq: Execute Repl oracle@hydrupgrd (J0         13        .00
                                               y                    27)

     11 ACTIVE   oracle-bg       non-SQL       rdbms ipc message    oracle@hydrupgrd (CJ          8        .00
                                                                    Q0)

    554 ACTIVE   SYS             4xg0vm951zzpx db file sequential r sqlplus@hydrupgrd (T          7        .00
                                               ead                  NS V1-V3)

    146 ACTIVE   oracle-bg       non-SQL       rdbms ipc message    oracle@hydrupgrd (MM          3        .00
                                                                    ON)

    308 ACTIVE   oracle-bg       non-SQL       EMON slave idle wait oracle@hydrupgrd (Q0          3        .00
                                                                    0E)

After enabling concurrent, increasing max parallel, job queues, pems, cpu count etc…., it took 19 minutes for the statistics to complete which is almost 60% improvement.

SQL> set timing on
SQL> set time on
16:11:34 SQL> EXEC dbms_stats.gather_database_stats();

PL/SQL procedure successfully completed.

Elapsed: 00:19:33.79

Another cause effective way to focus on the exact problem is to trace the stats job and find out the problematic table where the time is spent all the time using stats trace.

SQL> select dbms_stats.get_param('trace') from dual;


DBMS_STATS.GET_PARAM('TRACE')
--------------------------------------------------------------------------------
0

SQL> exec DBMS_STATS.SET_GLOBAL_PREFS('trace', 2+4+8+16+64+512+1024+2048 /* 3678 */);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param('trace') from dual;

DBMS_STATS.GET_PARAM('TRACE')
--------------------------------------------------------------------------------
3676

*** 2023-11-20 10:43:25.700
DBMS_STATS: gather_database_stats: <params><param name="block_sample" val="FALSE"/><param name="cascade" val="NULL"/><param name="concurrent" val="TRUE"/><param name="degree" val="DEFAULT_DEGREE_VALUE"/><param name="estimate_percent" val="DEFAULT_ESTIMATE_PERCENT"/><param name="gather_fixed" val="FALSE"/><param name="gather_sys" val="TRUE"/><param name="gather_temp" val="FALSE"/><param name="granularity" val="DEFAULT_GRANULARITY"/><param name="method_opt" val="DEFAULT_METHOD_OPT"/><param name="no_invalidate" val="NULL"/><param name="options" val="GATHER"/><param name="reporting_mode" val="FALSE"/><param name="statid" val=""/><param name="statown" val=""/><param name="stattab" val=""/><param name="stattype" val="DATA"/></params>

With a combination of stats trace and 10046 trace, more data can be collected with additional hints.

The below information shows that the stats job mostly waited on Streams AQ: waiting for messages in the queue wait event which is advanced queueing issue. This may not be the same case for every one who encounter stats slowness as every system is different. This may be a bug related to concurrent settings with DBMS_STATS. Disabling concurrent may reduce the events but may probably increase the time further if there is another potential problem because of non concurrent jobs.

SQL ID: 44pgaj5vdubxy Plan Hash: 0

BEGIN dbms_stats.gather_database_stats(); END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      3.51   11156.78        734       1484        934           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      3.51   11156.78        734       1484        934           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        4        0.00          0.00
  db file sequential read                       721        0.34          6.75
  control file sequential read                  600        0.00          0.00
  latch: shared pool                              1        0.00          0.00
  Streams AQ: waiting for messages in the queue
                                               1106       12.53      11146.59
  SQL*Net break/reset to client                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      3.51   11156.78        734       1484        934           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      3.51   11156.78        734       1484        934           0

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     1       39.74         39.74
  Disk file operations I/O                        4        0.00          0.00
  db file sequential read                       750        0.34          6.78
  control file sequential read                  600        0.00          0.00
  latch: shared pool                              1        0.00          0.00
  Streams AQ: waiting for messages in the queue
                                               1106       12.53      11146.59
  SQL*Net break/reset to client                   1        0.00          0.00

CREATE A CELL IN EXADATA STORAGE SERVER

A cell is a software which contains the exadata software in the storage server with CELLSRV, MS and RS processes to operate the exadata system.

During installation of exadata software in a virtual lab, the cell need to be created with the help of cellinit.ora file which contain the ipaddress of the host.

[celladmin@exdbaxcel00 ~]$ cellcli
CellCLI: Release 11.2.3.2.1 - Production on Wed Feb 16 05:45:07 GMT+05:30 2022

Copyright (c) 2007, 2012, Oracle.  All rights reserved.
Cell Efficiency Ratio: 1

CellCLI> alter cell shutdown services all

Stopping the RS, CELLSRV, and MS services...
The SHUTDOWN of services was successful.

CellCLI> create cell xstcell interconnect1=eth0

CELL-01514: Connect Error. Verify that Management Server is listening at the specified HTTP port: 8888.

CellCLI> create cell xstcell interconnect1=eth0

CELL-01514: Connect Error. Verify that Management Server is listening at the specified HTTP port: 8888.

CellCLI> alter cell startup services all

Starting the RS, CELLSRV, and MS services...
Getting the state of RS services...  running
Starting CELLSRV services...
The STARTUP of CELLSRV services was successful.
Starting MS services...
The STARTUP of MS services was successful.

CellCLI> create cell xstcell interconnect1=eth0
CELL-01518: Stop CELLSRV. Create Cell cannot continue with CELLSRV running.

CellCLI> alter cell shutdown services cellsrv

Stopping CELLSRV services...
The SHUTDOWN of CELLSRV services was successful.

CellCLI> create cell xstcell interconnect1=eth0
Cell xstcell successfully created
Starting CELLSRV services...
The STARTUP of CELLSRV services was successful.
Flash cell disks, FlashCache, and FlashLog will be created...
CellDisk FD_00_xstcell successfully created
CellDisk FD_01_xstcell successfully created
CellDisk FD_02_xstcell successfully created
CellDisk FD_03_xstcell successfully created
CellDisk FD_04_xstcell successfully created
Flash log xstcell_FLASHLOG successfully created
Flash cache xstcell_FLASHCACHE successfully created

After the cell software is created, then create the celldisks to store the database data. Post that, create griddisk on top of the celldisk for ASM mapping between database and storage server

CellCLI> create celldisk all
CellDisk CD_cell01_xstcell successfully created
CellDisk CD_cell02_xstcell successfully created
CellDisk CD_cell03_xstcell successfully created
CellDisk CD_cell04_xstcell successfully created
CellDisk CD_cell05_xstcell successfully created
CellDisk CD_cell06_xstcell successfully created
CellDisk CD_cell07_xstcell successfully created

CellCLI> create griddisk DATA_CD_cell01_xstcell celldisk=CD_cell01_xstcell;
GridDisk DATA_CD_cell01_xstcell successfully created

CellCLI> create griddisk DATA_CD_cell02_xstcell celldisk=CD_cell02_xstcell;
GridDisk DATA_CD_cell02_xstcell successfully created

CellCLI> create griddisk DATA_CD_cell03_xstcell celldisk=CD_cell03_xstcell;
GridDisk DATA_CD_cell03_xstcell successfully created

CellCLI> create griddisk RECO_CD_cell04_xstcell celldisk=CD_cell04_xstcell;
GridDisk RECO_CD_cell04_xstcell successfully created

CellCLI> create griddisk RECO_CD_cell05_xstcell celldisk=CD_cell05_xstcell;
GridDisk RECO_CD_cell05_xstcell successfully created

CellCLI> create griddisk REDO_CD_cell06_xstcell celldisk=CD_cell06_xstcell;
GridDisk REDO_CD_cell06_xstcell successfully created

CellCLI> create griddisk MGMT_CD_cell07_xstcell celldisk=CD_cell07_xstcell;
GridDisk MGMT_CD_cell07_xstcell successfully created
RESULT CACHE HINT IS NOT WORKING FOR A QUERY TO CACHE

When using the following database objects or functions in your SQL query, you cannot cache the results.

  • Temporary tables and dictionaries
  • CURRVAL and NEXTVAL pseudo columns for sequences
  • SQL functions sys_guid, sys_date, sys_timestamp, userenv/sys_context (with non-constant variables), and local_timestamp
  • Unpredictable PL/SQL functions

Check if result cache is enabled for the database

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

Test if the result cache is used for a query or not with result cache hints. For this query, a fixed baseline plan is used and so even though we specify the hint, the result set is not cached in result cache.

Also make sure that you notice this important “Note” section during troubleshooting of why result cache is not used for the query.

SQL> SELECT /*+ result_cache */ * from dspm;

291053 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3837617297

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   219K|   452M|  1570   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DSPM |   219K|   452M|  1570   (1)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - SQL plan baseline "SQL_PLAN_41x3ck184jygqb495c64e" used for this statement

SQL> SELECT * from table(dbms_xplan.display_cursor(null,null,FORMAT => 'ALLSTATS ADVANCED LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  32cu3gtu98t6c, child number 1
-------------------------------------
SELECT /*+result_cache*/ * from dspm where type='PROCEDURE'

Plan hash value: 2382681726

--------------------------------------------------------------------------------
--------------

| Id  | Operation                           | Name  | E-Rows |E-Bytes| Cost (%CP
U)| E-Time   |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--------------

|   0 | SELECT STATEMENT                    |       |        |       |   405 (10
0)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DSPM  |  15536 |  1729K|   405   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | DTYPE |  15536 |       |    44   (

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DSPM@SEL$1
   2 - SEL$1 / DSPM@SEL$1

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      INDEX_RS_ASC(@"SEL$1" "DSPM"@"SEL$1" ("DSPM"."TYPE"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "DSPM"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - access("TYPE"='PROCEDURE')

Column Projection Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------

   1 - "DSPM"."OWNER"[VARCHAR2,128], "DSPM"."NAME"[VARCHAR2,128],
       "TYPE"[VARCHAR2,12], "DSPM"."LINE"[NUMBER,22], "DSPM"."TEXT"[VARCHAR2,400
0],

       "DSPM"."ORIGIN_CON_ID"[NUMBER,22]
   2 - "DSPM".ROWID[ROWID,10], "TYPE"[VARCHAR2,12]

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - SQL plan baseline SQL_PLAN_f91shd5a09z5087226bf8 used for this statement
   - Warning: basic plan statistics not available. These are only collected when
:

       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system leve

SQL> show parameter baseline

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL> ALTER system set optimizer_capture_sql_plan_baselines=FALSE;

System altered.

SQL> show parameter result

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
multishard_query_partial_results     string      not allowed
result_cache_max_result              integer     5
result_cache_max_size                big integer 400M
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0

SQL> ALTER system set result_cache_max_size=10;

System altered.

SQL> set autot traceonly
SQL> SELECT /*+ result_cache */ * from dspm;

291053 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3837617297

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   219K|   452M|  1570   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DSPM |   219K|   452M|  1570   (1)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - SQL plan baseline "SQL_PLAN_41x3ck184jygqb495c64e" used for this statement

SQL> set autot traceonly
SQL> SELECT /*+ result_cache */ * from dspm;

291053 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3837617297

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   219K|   452M|  1570   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DSPM |   219K|   452M|  1570   (1)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL> ALTER session set tracefile_identifier='RC';

Session altered.

SQL> ALTER SESSION SET EVENTS '43905 trace name context forever, level 12';

Session altered.

SQL> SELECT /*+result_cache */ count(*) from dspm;

  COUNT(*)
----------
    291053

SQL> ALTER SESSION SET EVENTS '43905 trace name context off';

Session altered.
Password not provided. Neither locked accounts nor accounts without passwords are supported.

Specify the OS username password to move forward

ROLL_INVALID_MISMATCH UNSHARED CURSORS ISSUE ORACLE

I recently came into an issue related to high version counts for a particular query where the reason is ROLL_INVALID_MISMATCH. Customer was frequently gathering statistics for the objects in the database making the old statistics to invalidate with a new child cursor.

Oracle invalidate the statistics for the objects in the database periodically from 10g to avoid unexpected surprises of CPU spikes due to hard parse of the new statistics which need to be used for the sql statements as part of optimizer execution plan generation.

Oracle has introduced automatic control over invalidation of the cursors for freshly created statistics with no_invalidate => DBMS_STATS.AUTO_INVALIDATE of DBMS_STATS package from 10g onwards. Until the threshold value for _optimizer_invalidation_period parameter is met, the old cursors are reused for the object statistics.

kish@x3z6zx9<^>col INVALIDATION_MODE for a30
kish@x3z6zx9<^>SELECT DBMS_STATS.get_prefs(pname=>'NO_INVALIDATE') Invalidation_mode FROM dual;

INVALIDATION_MODE
------------------------------
DBMS_STATS.AUTO_INVALIDATE

A sample shell script to simulate the ROLL_INVALID_MISMATCH issue. This script will generate a pool of child cursors over time and populate the shared pool.

[oracle@x3dbzx36 ~]$ cat test.sql
#!/bin/bash -x

export ORACLE_SID=x3x6zx6
export ORACLE_HOME=/apps01/base/product/11.2.0/dbhome

c=0
while [ $c -lt 1000 ];
do
$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' <<EOF
spool ri.txt
set lines 200 pages 1000
set timing on
set echo on
ALTER system set "_optimizer_invalidation_period"=10;
!sleep 15
select to_char(last_analyzed,'DD-MM-YY HH24:MI:SS') last_analyzed from dba_tables where table_name='YTBL';
select count(*) from ytbl;
col child_number for 99999
col parse_calls for 99999
col executions for 9999999
col first_load_time for a20
col last_load_time for a20
SELECT      child_number,parse_calls,executions,first_load_time,last_load_time,last_active_time
FROM v\$sql WHERE sql_id='8bsh7ckvjnx9x';
!sleep 15
SELECT ROLL_INVALID_MISMATCH from v\$sql_shared_cursor WHERE sql_id='8bsh7ckvjnx9x';
insert /*+append parallel(8)*/ into ytbl
select dbms_random.string('a','10'),
           yid.nextval,
           decode(round(dbms_random.value(0,10)),0,'Chennai',1,'Canberra',2,'Berlin',3,'London',4,'Paris',5,'Washington',6,'Lisbon',7,'Madrid',8,'Mumbai',9,'Moscow',10,'Rome'),
       floor(dbms_random.value(1,1000000))
from (select level from dual connect by level <= 1),
(select level from dual connect by level <= 1),
(select level from dual connect by level <= 1);
commit;
EXEC dbms_stats.gather_table_stats('SYS','YTBL');
spool off
exit;
EOF
c=`expr $c + 1`
done

There are almost 1602 cursors which are created as child for parent cursor

kish@x3z6zx9<^>col RIM for a30
kish@x3z6zx9<^>col COUNT for 999999999
kish@x3z6zx9<^>SELECT rpad('ROLL_INVALID_MISMATCH : ',33)|| ROLL_INVALID_MISMATCH as RIM ,count(*) count FROM v$sql_shared_cursor GROUP BY ROLL_INVALID_MISMATCH;

RIM                                 COUNT
------------------------------ ----------
ROLL_INVALID_MISMATCH :              1602
   Y

ROLL_INVALID_MISMATCH :              3834
   N

The output shows two parse calls for every 1 minute as the value for _optimizer_invalidation_period is 10 but the first cursor load time is same from parent cursor 0.

CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME      LAST_LOAD_TIME       LAST_ACTI
------------ ----------- ---------- -------------------- -------------------- ---------
           0          37         37 2023-10-31/10:56:41  2023-10-31/10:56:41  31-OCT-23
           1           2          2 2023-10-31/10:56:41  2023-10-31/16:19:28  31-OCT-23
           2           2          2 2023-10-31/10:56:41  2023-10-31/16:20:30  31-OCT-23
           3           2          2 2023-10-31/10:56:41  2023-10-31/16:21:32  31-OCT-23
           4           2          2 2023-10-31/10:56:41  2023-10-31/16:22:34  31-OCT-23
           5           2          2 2023-10-31/10:56:41  2023-10-31/16:23:37  31-OCT-23
           6           2          2 2023-10-31/10:56:41  2023-10-31/16:24:39  31-OCT-23
           7           2          2 2023-10-31/10:56:41  2023-10-31/16:25:41  31-OCT-23
           8           2          2 2023-10-31/10:56:41  2023-10-31/16:26:44  31-OCT-23
           9           2          2 2023-10-31/10:56:41  2023-10-31/16:27:48  31-OCT-23
          10           2          2 2023-10-31/10:56:41  2023-10-31/16:28:51  31-OCT-23
          11           2          2 2023-10-31/10:56:41  2023-10-31/16:29:54  31-OCT-23

Solution:

  • Make sure that the statistics are gathered in equal interval and not too frequently for the objects.
  • Set _optimizer_invalidation_period parameter to a value which suits the data load period of the database.
  • Another workaround can be setting _cursor_obsolete_threshold to lower value to forcefully obsolete the child cursors in the database after every new child spawn.
kish@x3z6zx9<^>set lines 200 pages 1000
col "Hidden_parameter" for a40
col "It's Value" for a20
col Desc for a40
select x.ksppinm as Hidden_parameter,
         y.ksppstvl "It's Value",
                   x.ksppdesc "Desc"
     from
  x$ksppi x
  inner join x$ksppcv y on (x.indx = y.indx)
and
  x.ksppinm like '%_cursor_obsolete_threshold%'
order by x.ksppinm;kish@x3z6zx9<^>kish@x3z6zx9<^>kish@x3z6zx9<^>kish@x3z6zx9<^>  2    3    4    5    6    7    8    9

HIDDEN_PARAMETER                         It's Value           Desc
---------------------------------------- -------------------- ----------------------------------------
_cursor_obsolete_threshold               1024                 Number of cursors per parent before obso
                                                              letion.
--Default value of _cursor_obsolete_threshold per versions
11.2.0.3 :=100
11.2.0.4 :=1024
12.1.0.1 :=1024
12.1.0.2 :=1024
12.2.0.1 :=8192
18c :=8192
19c :=8192
21c :=8192

Setting this parameter to lower value may be obsoleted from shared pool which is the remaining 1024 – 200 = 824 cursors which were child versions of the parent stats version for ytbl.

kish@x3z6zx9<^>ALTER system set "_cursor_obsolete_threshold"=200 scope=spfile;

System altered.

What Is This PARSE ERROR: ospid=31513, error=936 for statement:?

For every 100 parse errors in the database, an entry is recorded in the alertlog with “PARSE ERROR: ospid=<nnnnn>, error=<nnn> for statement: ” format.

Simulate the error by forcing syntax error with 10000 loops. For every 100 errors consolidated, 10000 / 100 = 100 entries are recorded in alertlog.

[oracle@xhydra trace]$ cat ~/select.sh
#!/bin/bash -x

export ORACLE_SID=sqldb
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

c=0
while [ $c -lt 10000 ];
do
$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' <<EOF
whenever sqlerror continue;
select count(*)* from dspm;
exit;
EOF
c=`expr $c + 1`
done

The entry contains the error message which can point to cause of the issue including the sql text with sql id.

It also provide the application information with the username and action etc..

[oracle@xhydra trace]$ tail -100f alert_sqldb.log |grep -A5 'PARSE ERROR'
PARSE ERROR: ospid=30863, error=936 for statement:
2023-10-30T21:28:31.006154+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=31513, error=936 for statement:
2023-10-30T21:30:40.623706+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=32079, error=936 for statement:
2023-10-30T21:32:28.160547+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=32663, error=936 for statement:
2023-10-30T21:34:18.773368+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=33248, error=936 for statement:
2023-10-30T21:36:08.751102+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=33754, error=936 for statement:
2023-10-30T21:37:59.365650+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=34305, error=936 for statement:
2023-10-30T21:39:44.152553+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=34877, error=936 for statement:
2023-10-30T21:41:28.786950+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:
--
PARSE ERROR: ospid=35437, error=936 for statement:
2023-10-30T21:43:12.664262+05:30
select count(*)* from dspm
Additional information: hd=0x79bfa4f8 phd=0x79bfa920 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0 sqlid=bg9pxaf3r8akn
...Current username=SYS
...Application: sqlplus@xhydra (TNS V1-V3) Action:

In AWR report, we notice high “connection management call elapsed time” and also “failed parse elapsed time” with less significant value. Whenever, parse errors happen in the database, then these symptoms always show in AWR report.

There are also high parse count failures out of total parse count which is not good.

Also, if the parse failures are significant, then instance efficiency ratio drop for Execute to parse and Parse CPU

Solution:

  1. There is no action that can be taken from DBA rather to inform application team to fix the code with correct syntax or permissions on the object depending on the type of error.
[oracle@xhydra trace]$ oerr ora 936
00936, 00000, "missing expression"
// *Cause:
// *Action:

2. Error stack should help

<error barrier> at 0x7ffee3f36a30 placed dbkda.c@296
ORA-00936: missing expression
<error barrier> at 0x7ffee3f39af0 placed prsde.c@1119
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.

----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+95         call     kgdsdst()            7FFEE3F35E90 000000002
                                                   7FFEE3F301C0 ? 7FFEE3F302D8 ?
                                                   000000000 000000082 ?
ksedst()+58          call     ksedst1()            000000000 000000001
                                                   7FFEE3F301C0 ? 7FFEE3F302D8 ?
                                                   000000000 ? 000000082 ?
dbkedDefDump()+2308  call     ksedst()             000000000 000000001 ?
0                                                  7FFEE3F301C0 ? 7FFEE3F302D8 ?
                                                   000000000 ? 000000082 ?
ksedmp()+577         call     dbkedDefDump()       00000000C 000000000
                                                   7FFEE3F301C0 ? 7FFEE3F302D8 ?
                                                   000000000 ? 000000082 ?
dbkdaKsdActDriver()  call     ksedmp()             00000000C 000000000 ?
+2484                                              7FFEE3F301C0 ? 7FFEE3F302D8 ?
                                                   000000000 ? 000000082 ?
dbgdaExecuteAction(  call     dbkdaKsdActDriver()  7F67548AF6D0 7FFEE3F38320
)+354                                              7FFEE3F301C0 ? 7FFEE3F302D8 ?
                                                   000000000 ? 000000082 ?
dbgdaRunAction()+76  call     dbgdaExecuteAction(  7F67548AF6D0 0140B5E00
2                             )                    04EC1CBB0 7FFEE3F38320
                                                   000000001 000000082 ?
dbgdRunActions()+83  call     dbgdaRunAction()     7F67548AF6D0 7FFEE3F385A0
                                                   7F67548AF6D0 000000000
                                                   7FFEE3F385A8 000000082 ?
dbgdProcessEventAct  call     dbgdRunActions()     7F67548AF6D0 ? 7FFEE3F385A0 ?

========== FRAME [17] (qcpiapr()+1615 -> kgesecl0()) ==========
defined by frame pointers 0x7ffee3f39410  and 0x7ffee3f39230
CALL TYPE: call   ERROR SIGNALED: yes   COMPONENT: (null)

There is a hidden parameter “_kks_parse_error_warning” which has a default value of 100 threshold for which the number of times that the warning message should print in alertlog. If this value is increased, then oracle does not print the message too frequently. But, this may also hide parse failures which is very important for the DBA to troubleshoot any performance problems of parsing issues.

SQL> set lines 200 pages 1000
col “Hidden_parameter” for a40
col “It’s Value” for a20
col Desc for a40
select x.ksppinm as Hidden_parameter,
y.ksppstvl “It’s Value”,
x.ksppdesc “Desc”
from
x$ksppi x
inner join x$ksppcv y on (x.indx = y.indx)
and
x.ksppinm like ‘%kks%’
order by x.ksppinm;SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9

HIDDEN_PARAMETER It’s Value Desc
—————————————- ——————– —————————————-
_kks_cached_parse_errors 0 KKS cached parse errors
_kks_obsolete_dump_threshold 1 Number of parent cursor obsoletions befo
re dumping cursor

_kks_parse_error_warning 100 Parse error warning

The failed parse time can be queried from time model statistics tables.

SQL> SELECT stat_name,value from v$sys_time_model where stat_name like '%fail%';

STAT_NAME                                                             VALUE
---------------------------------------------------------------- ----------
failed parse elapsed time                                          89025886
failed parse (out of shared memory) elapsed time                          0

SQL> SELECT stat_name,sum(value) from dba_hist_sys_time_model where stat_name like '%fail%' group by stat_name;

STAT_NAME                                                        SUM(VALUE)
---------------------------------------------------------------- ----------
failed parse (out of shared memory) elapsed time                          0
failed parse elapsed time                                            646813

Alternatively, 10035 trace can also help to identify the parse failures.

SQL> ALTER system set events '10035 trace name context forever,level 12';

System altered.

Summary:

  • Although these parse errors looks harmless, these errors can be ignored if it is less in numbers
  • If the parse errors are significant, then this indicates a potential performance problem of unnecessary parsing which lead to shared pool processing
  • These sessions can be killed after confirmation from application team because of useless parses
SQL QUERY TO FIND USERS AND OBJECTS WHICH GENERATE MORE ARCHIVES ORACLE

set lines 200 pages 1000
col OBJECT_NAME for a30
col OWNER for a10
def arccol="do.OWNER,dhse.OBJ#,do.OBJECT_NAME,to_char(al.COMPLETION_TIME,'DD-MM-YY HH24:MI') ARCTIME,(sum(BLOCKS*BLOCK_SIZE)/1073741824) ARCSIZE_GB"
def arccol2="count(*) ARCOUNT,((sum(BLOCKS*BLOCK_SIZE)/1073741824)/(select value/1073741824 from v$parameter where name='db_recovery_file_dest_size')) ARCPCT"
def arcview="v$archived_log al"
def arcgrp="group by do.OWNER,dhse.OBJ#,do.OBJECT_NAME,to_char(al.COMPLETION_TIME,'DD-MM-YY HH24:MI')"
def arcord="order by ARCOUNT desc"
def snap="inner join dba_hist_snapshot dhss on to_char(al.COMPLETION_TIME,'DD-MM-YY HH24') = to_char(dhss.BEGIN_INTERVAL_TIME,'DD-MM-YY HH24')"
def seg="inner join dba_hist_seg_stat dhse on dhse.SNAP_ID = dhss.SNAP_ID"
def obj="inner join dba_objects do on do.OBJECT_ID = dhse.OBJ#"
SELECT &arccol,&arccol2 from
           &arcview
           &snap
           &seg
           &obj
           &arcgrp
           &arcord
/

OWNER            OBJ# OBJECT_NAME                    ARCTIME        ARCSIZE_GB    ARCOUNT     ARCPCT
---------- ---------- ------------------------------ -------------- ---------- ---------- ----------
SYS             11358 WRH$_CON_SYSMETRIC_SUMMARY     20-10-23 12:20  .06503582          2 .005230653
SYS             73576 WRH$_SYSSTAT                   20-10-23 12:20  .06503582          2 .005230653
SYS                37 I_OBJ2                         24-10-23 11:25 .197018623          2  .01584567
SYS                65 I_OBJ#_INTCOL#                 24-10-23 11:25 .197018623          2  .01584567
SYS                68 HIST_HEAD$                     24-10-23 11:25 .197018623          2  .01584567
SYS                18 OBJ$                           27-10-23 18:14 .161505699          2 .012989462
SLOW SQL QUERY DUE TO FIXED BASELINE ORACLE

There was a customer who had a performance issue for a batch query which was executing as per expected timelines earlier and now it take more than half of previous time.

After investigating the problem, the query picked up the fixed baseline plan which was not suitable for all data sets as the query had bind variables in it which fetched variable results.

Enable auto capture baselines for all the sql. Warning! this may capture millions of baseline if multiple queries are executed in database which may exhaust sysaux space.

This is just a sample test case query for understanding purpose.

Execute the query for the first time with out creating any index on the where clause.

The query execute with a full table scan and also the execution plan is captured for the query due to auto capture baseline.

SQL> ALTER system set optimizer_capture_sql_plan_baselines=TRUE;

System altered.

SQL> set autot traceonly
SQL> SELECT name,type from dspm where type='PROCEDURE' and owner='MDSYS';

45 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3837617297

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2515 | 75450 |  1570   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DSPM |  2515 | 75450 |  1570   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("TYPE"='PROCEDURE' AND "OWNER"='MDSYS')

Note
-----
   - SQL plan baseline "SQL_PLAN_74gw55r480awsb495c64e" used for this statement

Check if the plan is accepted as a benchmark plan. The plan is accepted but not fixed.

SQL> col SQL_HANDLE for a20
SQL> col PLAN_NAME for a20
SQL> SELECT sql_handle, sql_text, plan_name, enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE plan_name='SQL_PLAN_74gw55r480awsb495c64e';

SQL_HANDLE           SQL_TEXT             PLAN_NAME            ENA ACC FIX
-------------------- -------------------- -------------------- --- --- ---
SQL_723f852dc8802b98 SELECT name,type fro SQL_PLAN_74gw55r480a YES YES NO
                     m dspm where type='P wsb495c64e
                     ROCEDURE' and owner=
                     'MDSYS'

Accidentally, the full table scan plan is fixed considering that the plan performs well at the present.

SQL> var v_5cygq95qjwmzr_ft number
SQL> EXEC :v_5cygq95qjwmzr_ft := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( sql_handle => 'SQL_723f852dc8802b98', plan_name => 'SQL_PLAN_74gw55r480awsb495c64e', attribute_name => 'FIXED', attribute_value => 'YES' );

PL/SQL procedure successfully completed.

Now, application team expects a better performance for the query and creates index on one specific column ‘type’. Again the same fixed plan is used, even though a better index plan is available.

SQL> CREATE index dtype on dspm(type);

Index created.

SQL> set autot traceonly
SQL> SELECT name,type from dspm where type='PROCEDURE' and owner='MDSYS';

45 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3837617297

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2515 | 75450 |  1570   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DSPM |  2515 | 75450 |  1570   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("TYPE"='PROCEDURE' AND "OWNER"='MDSYS')

Note
-----
   - SQL plan baseline "SQL_PLAN_74gw55r480awsb495c64e" used for this statement

Again, application team creates another index on ‘owner’ column. This time again the fixed full table scan is used due to baseline.

SQL> CREATE index downer on dspm(owner);

Index created.

SQL> set autot traceonly
SQL> SELECT name,type from dspm where type='PROCEDURE' and owner='MDSYS';

45 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3837617297

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2515 | 75450 |  1570   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DSPM |  2515 | 75450 |  1570   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("TYPE"='PROCEDURE' AND "OWNER"='MDSYS')

Note
-----
   - SQL plan baseline "SQL_PLAN_74gw55r480awsb495c64e" used for this statement

So, the above test case is just for learning purpose.

In reality, the same case may be suitable, where sql tuning advisor would have recommended to accept full table scan baseline but not to fix it. DBA might have faced performance issue during the time with a worse plan than full table scan plan and he might have fixed the fts plan considering it fast.

Consider that the DBA left the job and another new DBA comes in. After some days, there may be change on the size of data to be retrieved like more rows for the sql. At that time, optimizer still use the fts plan because of fixed baseline and another performance issue arise as a consequence of previous fix.

Summary:

  • Always make sure of the existing sql profiles and baselines for the sql
  • Make sure of the status of those baselines if they are enabled or accepted or fixed or all of them together
  • If so, drop the unnecessary bad baselines created on older plans which may not be suitable currently

First identify the number of baselines available for the sql id.

SQL> set lines 200 pages 1000
SQL> col PLAN_NAME for a20
SQL> col SQL_TEXT for a20
SQL> SELECT SQL_TEXT,SIGNATURE,SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,ELAPSED_TIME from dba_sql_plan_baselines WHERE SQL_TEXT like '%SELECT name,type from dspm where type%';

SQL_TEXT              SIGNATURE SQL_HANDLE                     PLAN_NAME            ENA ACC FIX ELAPSED_TIME
-------------------- ---------- ------------------------------ -------------------- --- --- --- ------------
SELECT name,type fro 8.2324E+18 SQL_723f852dc8802b98           SQL_PLAN_74gw55r480a YES YES YES            0
m dspm where type='P                                           wsb495c64e
ROCEDURE' and owner=
'MDSYS'

One baseline is available for the SQL and it is fixed for the query. This is the reason why a fixed plan is used irrespective of other good plans.

Check the contents of the baseline for the plan.

SQL> SELECT * from table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(PLAN_NAME => 'SQL_PLAN_74gw55r480awsb495c64e'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_723f852dc8802b98
SQL text: SELECT name,type from dspm where type='PROCEDURE' and owner='MDSYS'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_74gw55r480awsb495c64e         Plan id: 3029714510
Enabled: YES     Fixed: YES     Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 3837617297

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2515 | 75450 |  1570   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DSPM |  2515 | 75450 |  1570   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("TYPE"='PROCEDURE' AND "OWNER"='MDSYS')

25 rows selected.

Drop the baseline containing the worst plan and purge the sql_id from the shared pool.

SQL> DECLARE
varint INTEGER;
BEGIN
varint := DBMS_SPM.drop_sql_plan_baseline(plan_name => 'SQL_PLAN_74gw55r480awsb495c64e');
END;
/    2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> SELECT address,hash_value from v$sql where sql_id='3bjw18gb6jthj';

ADDRESS          HASH_VALUE
---------------- ----------
0000000076701840 3597198865

SQL> EXEC dbms_shared_pool.purge('0000000076701840,3597198865','C');

PL/SQL procedure successfully completed.

SQL> SELECT address,hash_value from v$sql where sql_id='3bjw18gb6jthj';

no rows selected

After the baseline with worst plan is dropped, good plan is used.

SQL> set autot traceonly
SQL> SELECT name,type from dspm where type='PROCEDURE' and owner='MDSYS';

45 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2382681726

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |  2515 | 75450 |   405   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DSPM  |  2515 | 75450 |   405   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DTYPE | 15536 |       |    44   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   1 - filter("OWNER"='MDSYS')
   2 - access("TYPE"='PROCEDURE')

Note
-----
   - SQL plan baseline "SQL_PLAN_74gw55r480aws87226bf8" used for this statement

Summary:

  • It is always good to verify that if there are any fixed baseline plans for a query during performance issue and also examine the history
  • Do not fix the baseline plan unless until the data for the objects are used only for archival purpose which means that the table data is stable for long time and there is no data load or growth.
  • Fix the baseline only if the data is constant.
  • If the data is dynamic, then consider only accepting the good plan.
SQL ID IS NOT CAPTURED IN V$SQL OR V$SQLAREA

I was trying to identify SQL ID for a microsecond query from v$sql, v$session, v$sqlarea etc.. But none of them retained the information of the sql because of ultra fast execution of the sql.

There may be other parsing reasons of the sql when specifying the sql text in WHERE clause. If the sql text is provided in WHERE clause with either upper or lower case or with additional spaces, then the sql may not be properly retrieved due to syntax variations. But here, this is not the case.

v$SQL displays the sql query details at the end of query execution generally as per oracle doc.

But if the query executes in fraction of milliseconds, then the details are not visible in v$sql or v$sqlarea.

So, alternatively v$open_cursor captures the query details immediately after query execution and it disappears too in a while. None of the AWR tables shows the details about this fast query.

SQL> SELECT sql_id,sql_text FROM v$open_cursor WHERE sql_id='5cygq95qjwmzr';

SQL_ID        SQL_TEXT
------------- --------------------
5cygq95qjwmzr SELECT name,type fro
              m dspm where type='P
              ROCEDURE' and owner=

SQL> SELECT sql_id from v$session where sql_id IS NOT NULL;

SQL_ID
-------------
30fux3fx84xuu

SQL> SELECT sql_text from v$sql where sql_id='30fux3fx84xuu';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT sql_id from v$session where sql_id IS NOT NULL

SQL> SELECT sql_id FROM v$active_session_history WHERE sql_id='5cygq95qjwmzr';

no rows selected
SQL>  SELECT sql_id from dba_hist_sqlstat WHERE PLAN_HASH_VALUE=3837617297;

no rows selected

SQL> SELECT sql_id from dba_hist_active_sess_history where SQL_PLAN_HASH_VALUE=3837617297;

no rows selected

The sql details were kept in v$sqlstats even though the query completed faster.

SQL> col SQL_TEXT for a20
SQL> SELECT sql_id,sql_text FROM v$sqlstats WHERE sql_text like '%SELECT name,type from dspm where type=%';

SQL_ID        SQL_TEXT
------------- --------------------
5cygq95qjwmzr SELECT name,type fro
              m dspm where type='P
              ROCEDURE' and owner=
              'MDSYS'

Also, another way to identify the sql id is to use 10046 trace if nothing works.

PARSING IN CURSOR #139655058600344 len=67 dep=0 uid=0 oct=3 lid=0 tim=3227964751 hv=1830703095 ad='7f481648' sqlid='5cygq95qjwmzr'
SELECT name,type from dspm where type='PROCEDURE' and owner='MDSYS'
END OF STMT
HOW TO PRINT Nth LINE FROM A FILE IN SHELL SCRIPT

I was asked this question in an interview for testing pattern matching skills in shell script. Though, the question looked easy but still it was really difficult to think of logic immediately and it took some time. Also the difficult question was “How to filter the 10 row without using head or tail”

Create a sample file, populate with numbers and cat the contents

[oracle@hydrupgrd ~]$ touch abc.txt
[oracle@hydrupgrd ~]$ echo -ne "1\n2\n3\n4\n5\n6\n" > abc.txt
[oracle@hydrupgrd ~]$ cat abc.txt
1
2
3
4
5
6

head and tail are the easiest combination to filter the Nth row in the file
[oracle@hydrupgrd ~]$ cat abc.txt|head -3|tail -1
3

Another method is to use AWK to filter the exact line

[oracle@hydrupgrd ~]$ cat abc.txt |awk 'NR==3'
3

SED can also be used to filter complex logics.

[oracle@hydrupgrd ~]$ cat abc.txt |sed -n '3p'
3