Uncategorized – Page 3

Uncategorized

OPTIMIZER USES SQL PROFILE WITH BAD PLAN EVEN THOUGH GOOD PLAN IS AVAILABLE

Optimizer even though had goof statistics, still chosen bad plan because of the profile.

SQL> EXEC dbms_stats.gather_database_stats();

PL/SQL procedure successfully completed.

SQL> SELECT name,line 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 | 85510 | 1570 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DSPM | 2515 | 85510 | 1570 (1)| 00:00:01 |
————————————————————————–

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

1 – filter(“TYPE”=’PROCEDURE’ AND “OWNER”=’MDSYS’)

Note
—–
– SQL profile “coe_3bjw18gb6jthj_3837617297” used for this statement

The only way to influence the plan is to use index hints to go for index scans

SQL> set lines 200 pages 1000
SQL> SELECT /*+INDEX(dspm dtype)*/ name,line 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 | | 45 | 1530 | 405 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DSPM | 45 | 1530 | 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
—–
– dynamic statistics used: dynamic sampling (level=AUTO)

SQL> SELECT sql_id,elapsed_time_total,plan_hash_value,sql_profile FROM dba_hist_sqlstat WHERE plan_hash_value in (‘3837617297′,’2382681726’) order by 2 desc;

SQL_ID ELAPSED_TIME_TOTAL PLAN_HASH_VALUE SQL_PROFILE
————- —————— ————— —————————————————————-
8x2s277rk1b0a 651207 3837617297
8x2s277rk1b0a 332166 3837617297
6bx1tjnfah91k 270681 3837617297
6bx1tjnfah91k 243248 3837617297
3bjw18gb6jthj 154033 3837617297 coe_3bjw18gb6jthj_3837617297
6bx1tjnfah91k 92445 2382681726
3bjw18gb6jthj 82910 3837617297
3t9upwfb4s9pp 45258 3837617297 coe_d46sq1upy11vr_3837617297
3t9upwfb4s9pp 41735 3837617297
3t9upwfb4s9pp 36449 3837617297
57f1y1z9zyt9k 29769 3837617297
d46sq1upy11vr 28266 3837617297 coe_d46sq1upy11vr_3837617297
d46sq1upy11vr 21207 3837617297
b6kqbg298h1j1 17603 3837617297
3t9upwfb4s9pp 17378 3837617297 coe_d46sq1upy11vr_3837617297
3gjtxtu6r5q93 8791 3837617297
b6kqbg298h1j1 6257 2382681726

17 rows selected.

Finally, dynamic sampling with level 11 also helped to use the good plan.

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: DSPM  Alias: DSPM
  #Rows: 291053  SSZ: 0  LGR: 0  #Blks:  5778  AvgRowLen:  114.00  NEB: 0  ChainCnt:  0.00  ScanRate:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0  CHR: 0  KQDFLG: 1
  #IMCUs: 0  IMCRowCnt: 0  IMCJournalRowCnt: 0  #IMCBlocks: 0  IMCQuotient: 0.000000
Index Stats::
  Index: DTYPE  Col#: 3
  LVLS: 2  #LB: 786  #DK: 9  LB/K: 87.00  DB/K: 750.00  CLUF: 6754.00  NRW: 291053.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1
  KKEISFLG: 1
try to generate single-table filter predicates from ORs for query block SEL$1 (#0)
finally: "DSPM"."TYPE"='PROCEDURE' AND "DSPM"."OWNER"='MDSYS'

SQL> set lines 200 pages 1000
SQL> SELECT /*+ dynamic_sampling(dspm 11) */ name,line from dspm WHERE type='PROCEDURE' and owner ='MDSYS';

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

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

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

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

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   1 -  SEL$1 / DSPM@SEL$1
         U -  dynamic_sampling(dspm 11)

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

The contents or outline hints of the sql profile can be further scrapped from dba_sqltune_plans view.

SQL> set long 1000000000
SQL> SELECT other_xml FROM dba_sqltune_plans WHERE plan_hash_value=3837617297;

OTHER_XML
--------------------------------------------------------------------------------

<other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0<
/info><info type="parse_schema"><![CDATA["SYS"]]></info><info type="dynamic_samp
ling" note="y">2</info><info type="plan_hash_full">3029714510</info><info type="
plan_hash">3837617297</info><info type="plan_hash_2">3029714510</info><stats typ
e="compilation"><stat name="bg">2032</stat></stats><qb_registry><q o="2" f="y"><
n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DSPM]]></t><s><![CDATA[SEL$1]]></s></h>
</f></q></qb_registry><outline_data><hint><![CDATA[FULL(@"SEL$1" "DSPM"@"SEL$1")
]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[ALL_ROWS
]]></hint><hint><![CDATA[OPT_PARAM('result_cache_mode' 'FORCE')]]></hint><hint><
![CDATA[DB_VERSION('19.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('
19.1.0')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_
data></other_xml>

<other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0<
/info><info type="parse_schema"><![CDATA["SYS"]]></info><info type="dynamic_samp
ling" note="y">2</info><info type="plan_hash_full">3029714510</info><info type="
plan_hash">3837617297</info><info type="plan_hash_2">3029714510</info><info type
="baseline_repro_fail" note="y">yes</info><stats type="compilation"><stat name="
bg">11509</stat></stats><qb_registry><q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><
h><t><![CDATA[DSPM]]></t><s><![CDATA[SEL$1]]></s></h></f></q></qb_registry><outl
ine_data><hint><![CDATA[FULL(@"SEL$1" "DSPM"@"SEL$1")]]></hint><hint><![CDATA[OU
TLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[DB
_VERSION('19.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('19.1.0')]]
></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></oth
er_xml>

<other_xml><info type="has_user_tab">yes</info><info type="db_version">19.0.0.0<
/info><info type="parse_schema"><![CDATA["SYS"]]></info><info type="dynamic_samp
ling" note="y">2</info><info type="plan_hash_full">3029714510</info><info type="
plan_hash">3837617297</info><info type="plan_hash_2">3029714510</info><stats typ
e="compilation"><stat name="bg">68</stat></stats><qb_registry><q o="2" f="y"><n>
<![CDATA[SEL$1]]></n><f><h><t><![CDATA[DSPM]]></t><s><![CDATA[SEL$1]]></s></h></
f></q></qb_registry><outline_data><hint><![CDATA[FULL(@"SEL$1" "DSPM"@"SEL$1")]]
></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[ALL_ROWS]]
></hint><hint><![CDATA[DB_VERSION('19.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FE
ATURES_ENABLE('19.1.0')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></
hint></outline_data><hint_usage><q><n><![CDATA[SEL$1]]></n><t><f><![CDATA["DSPM"
@"SEL$1"]]></f><h o="OU"><x><![CDATA[FULL(@"SEL$1" "DSPM"@"SEL$1")]]></x></h></t
></q><s><h o="OU"><x><![CDATA[ALL_ROWS]]></x></h><h o="OU"><x><![CDATA[DB_VERSIO
N('19.1.0')]]></x></h><h o="OU"><x><![CDATA[OPTIMIZER_FEATURES_ENABLE('19.1.0')]
]></x></h><h o="OU"><x><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></x></h></s></hint
_usage></other_xml>

6 rows selected.

kishan

Jan, Fri, 2024

Uncategorized

STATSPACK HANDY GUIDE

Statspack is a tool which contain set of packages that can be installed to report and diagnose performance issues. These performance data are recorded and stored in either sysaux (default) or perfstat or any other tablespace in the form of snapshots between interval and duration based on retention.

Supported edition:

Statspack is supported in all the editions(EE, XE, SE, SE2, PE). So there is no specific restrictions for license.

Baseline mark:

To mark a snapshot as a benchmark for good performance, a baseline can be used. This baseline snapshot gets retained for longer period, until they are explicitly specified.

STATSPACK Install:

To install statspack report, by default perfstat user will be used.

Invoke the script spcreate to install statspack in the database from $ORACLE_HOME/rdbms/admin/spcreate.sql. This main script contain three phases with three sub scripts

spcusr.sql –create user and grant privileges for perfstat
spctab.sql — create all the statspack tables stats$ related to statspack under perfstat
spcpkg.sql –create statistics packages for statspack under perfstat

Install statspack using the below sql. It is recommended to install statspack in dedicated tablespace for statspack or create a new tablespace like ‘PERFSTAT‘. Do not use SYSTEM or SYSAUX. SYSAUX can be used in most cases, but it is tedious to manage because it is an auxiliary inbuilt tablespace.

def perfstat_password='password' --provide the password
def default_tablespace='USERS' --provide default tablespace
def temporary_tablespace='TEMP' --provide temporary tablespace
@?/rdbms/admin/spcreate

Privileges of perfstat user: ( should not be granted manually, just information purpose)

[oracle@xhydra ~]$ grep 'grant' /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/spcusr.sql
grant create session              to PERFSTAT;
grant alter  session              to PERFSTAT;
grant create table                to PERFSTAT;
grant create view                 to PERFSTAT;
grant create procedure            to PERFSTAT;
grant create sequence             to PERFSTAT;
grant create public synonym       to PERFSTAT;
grant drop   public synonym       to PERFSTAT;
grant select on STATS$X_$KCBFWAIT       to PERFSTAT;
grant select on STATS$X_$KSPPSV         to PERFSTAT;
grant select on STATS$X_$KSPPI          to PERFSTAT;
grant select on STATS$X_$KSXPPING       to PERFSTAT;
grant select on STATS$V_$FILESTATXS     to PERFSTAT;
grant select on STATS$V_$TEMPSTATXS     to PERFSTAT;
grant select on STATS$V_$SQLXS          to PERFSTAT;
grant select on STATS$V_$SQLSTATS_SUMMARY to PERFSTAT;
grant SELECT_CATALOG_ROLE         to PERFSTAT;
grant select on V_$PARAMETER      to PERFSTAT;
grant select on V_$SYSTEM_PARAMETER to PERFSTAT;
grant select on V_$DATABASE       to PERFSTAT;
grant select on V_$INSTANCE       to PERFSTAT;
grant select on GV_$INSTANCE      to PERFSTAT;
grant select on V_$LIBRARYCACHE   to PERFSTAT;
grant select on V_$LATCH          to PERFSTAT;
grant select on V_$LATCH_MISSES   to PERFSTAT;
grant select on V_$LATCH_CHILDREN to PERFSTAT;
grant select on V_$LATCH_PARENT   to PERFSTAT;
grant select on V_$ROLLSTAT       to PERFSTAT;
grant select on V_$ROWCACHE       to PERFSTAT;
grant select on V_$SGA            to PERFSTAT;
grant select on V_$BUFFER_POOL    to PERFSTAT;
grant select on V_$SGASTAT        to PERFSTAT;
grant select on V_$SYSTEM_EVENT   to PERFSTAT;
grant select on V_$SESSION        to PERFSTAT;
grant select on V_$SESSION_EVENT  to PERFSTAT;
grant select on V_$SYSSTAT        to PERFSTAT;
grant select on V_$WAITSTAT       to PERFSTAT;
grant select on V_$ENQUEUE_STATISTICS to PERFSTAT;
grant select on V_$SQLAREA        to PERFSTAT;
grant select on V_$SQL            to PERFSTAT;
grant select on V_$SQLTEXT        to PERFSTAT;
grant select on V_$SESSTAT        to PERFSTAT;
grant select on V_$BUFFER_POOL_STATISTICS to PERFSTAT;
grant select on V_$RESOURCE_LIMIT to PERFSTAT;
grant select on V_$DLM_MISC       to PERFSTAT;
grant select on V_$UNDOSTAT       to PERFSTAT;
grant select on V_$SQL_PLAN       to PERFSTAT;
grant select on V_$DB_CACHE_ADVICE to PERFSTAT;
grant select on V_$PGASTAT        to PERFSTAT;
grant select on V_$INSTANCE_RECOVERY to PERFSTAT;
grant select on V_$SHARED_POOL_ADVICE     to PERFSTAT;
grant select on V_$SQL_WORKAREA_HISTOGRAM to PERFSTAT;
grant select on V_$PGA_TARGET_ADVICE      to PERFSTAT;
grant select on V_$SEGSTAT                  to PERFSTAT;
grant select on V_$SEGMENT_STATISTICS       to PERFSTAT;
grant select on V_$SEGSTAT_NAME             to PERFSTAT;
grant select on V_$JAVA_POOL_ADVICE         to PERFSTAT;
grant select on V_$THREAD                   to PERFSTAT;
grant select on V_$CR_BLOCK_SERVER          to PERFSTAT;
grant select on V_$CURRENT_BLOCK_SERVER     to PERFSTAT;
grant select on V_$INSTANCE_CACHE_TRANSFER  to PERFSTAT;
grant select on V_$FILE_HISTOGRAM           to PERFSTAT;
grant select on V_$EVENT_HISTOGRAM          to PERFSTAT;
grant select on V_$EVENT_NAME               to PERFSTAT;
grant select on V_$SYS_TIME_MODEL           to PERFSTAT;
grant select on V_$SESS_TIME_MODEL          to PERFSTAT;
grant select on V_$STREAMS_CAPTURE           to PERFSTAT;
grant select on V_$STREAMS_APPLY_COORDINATOR to PERFSTAT;
grant select on V_$STREAMS_APPLY_READER      to PERFSTAT;
grant select on V_$STREAMS_APPLY_SERVER      to PERFSTAT;
grant select on V_$PROPAGATION_SENDER        to PERFSTAT;
grant select on V_$PROPAGATION_RECEIVER      to PERFSTAT;
grant select on V_$BUFFERED_QUEUES           to PERFSTAT;
grant select on V_$BUFFERED_SUBSCRIBERS      to PERFSTAT;
grant select on V_$RULE_SET                  to PERFSTAT;
grant select on V_$OSSTAT                    to PERFSTAT;
grant select on V_$PROCESS                   to PERFSTAT;
grant select on V_$PROCESS_MEMORY            to PERFSTAT;
grant select on V_$STREAMS_POOL_ADVICE       to PERFSTAT;
grant select on V_$SGA_TARGET_ADVICE         to PERFSTAT;
grant select on V_$SQLSTATS                  to PERFSTAT;
grant select on V_$MUTEX_SLEEP               to PERFSTAT;
grant select on V_$DYNAMIC_REMASTER_STATS    to PERFSTAT;
grant select on V_$IOSTAT_FUNCTION           to PERFSTAT;
grant select on V_$IOSTAT_FUNCTION_DETAIL    to PERFSTAT;
grant select on V_$IOSTAT_FILE               to PERFSTAT;
grant select on V_$MEMORY_TARGET_ADVICE      to PERFSTAT;
grant select on V_$MEMORY_RESIZE_OPS         to PERFSTAT;
grant select on V_$MEMORY_DYNAMIC_COMPONENTS to PERFSTAT;
grant select on V_$MEMORY_CURRENT_RESIZE_OPS to PERFSTAT;
grant execute on DBMS_SHARED_POOL to PERFSTAT;
grant execute on DBMS_JOB         to PERFSTAT;
grant select on stats$database_instance to PERFSTAT;
grant select on stats$snapshot to PERFSTAT;

Note that, statspack need to be installed with SYS user and PERFSTAT user should not be created manually. PERFSTAT user will be created automatically by spcreate.sql. All the objects like tables, views and public synonyms are created with referential constraints under SYS schema not PERFSTAT schema.

SCHEDULE AUTOMATIC SNAPSHOTS JOB EVERY HOUR:

To generate snapshots for statspack automatically, use spauto.sql. This will create a one hour interval of snapshot in the database using dbms_job

kIsH@Xhydra<>variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  commit;
end;
/
kIsH@Xhydra<>kIsH@Xhydra<>  2    3    4    5    6
PL/SQL procedure successfully completed.

spauto.sql script can be invoked otherwise to schedule the job automatically

Note: Donot execute the script multiple times. Multiple jobs will be triggered which will create duplicate snapshots every hour unnecessarily

kIsH@Xhydra<>@?/rdbms/admin/spauto.sql

PL/SQL procedure successfully completed.


Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

     JOBNO
----------
         2


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     40


Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB
----------
NEXT_DATE
---------------------------------------------------------------------------
NEXT_SEC
--------------------------------
         2
13-SEP-22 05.00.00.000000 PM +05:30
17:00:00

This error occur when the internal statspack table overlap each other with same job name.

Errors in file /u01/app/oracle/diag/rdbms/db9zx/db9zx/trace/db9zx_j000_21175.trc:
ORA-12012: error on auto execute of job "SYS"."DBMS_JOB$_41"
ORA-00001: unique constraint (PERFSTAT.STATS$PARAMETER_PK) violated
ORA-06512: at "SYS.STATSPACK", line 5283
ORA-06512: at "SYS.STATSPACK", line 105
ORA-06512: at line 1

kishan

Jan, Thu, 2024

Uncategorized

ORA-00980: synonym translation is no longer valid

SQL> !oerr ora 00980
00980, 00000, "synonym translation is no longer valid"
// *Cause: A synonym did not translate to a legal target object. This
//         could happen for one of the following reasons:
//         1. The target schema does not exist.
//         2. The target object does not exist.
//         3. The synonym specifies an incorrect database link.
//         4. The synonym is not versioned but specifies a versioned
//            target object.
// *Action: Change the synonym definition so that the synonym points at
//          a legal target object.

SQL> CREATE table t(name varchar(2));

Table created.

SQL> CREATE public synonym tsyn for t;

Synonym created.

SQL> SELECT * from tsyn;

no rows selected

SQL> DROP table t;

Table dropped.

SQL> SELECT * from tsyn;
SELECT * from tsyn
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

Solution:

Recreate the table.

SQL> CREATE table t(name varchar(2));

Table created.

kishan

Jan, Wed, 2024

Uncategorized

ORA-01775: looping chain of synonyms


[oracle@xhydra ~]$ oerr ora 01775
01775, 00000, "looping chain of synonyms"
// *Cause:
// *Action:

Cause
Translation of a synonym resolved to the same synonym. This is caused by a chain of object definitions including multiple private or public synonyms.

Action
Review the object definitions to determine which synonym resolves back to the original and change one or more of the definitions to break the cycle.

SQL> CREATE table t(name varchar(2));

Table created.

SQL> CREATE synonym tsyn1 for t;

Synonym created.


SQL> CREATE public synonym t for tsyn1;

Synonym created.

SQL> DROP table t;

Table dropped.

SQL> SELECT * FROM t;
SELECT * FROM t
              *
ERROR at line 1:
ORA-01775: looping chain of synonyms

kishan

Jan, Wed, 2024

Uncategorized

ORACLE RESULT CACHE EFFICIENCY

Result cache can cache the results of a query and give 10x performance compared to normal sql query.

kIsH@Xhydra<>set timing on
kIsH@Xhydra<>set autot traceonly
kIsH@Xhydra<>select /*+result_cache */ * from kish.xtbl where xid=34393541;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1321960990

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

| Id  | Operation                    | Name                       | Rows  | Byte
s | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |                            |     1 |    5
3 |     3   (0)| 00:00:01 |

|   1 |  RESULT CACHE                | 23vuas094zhu04m4t5hczu1ug0 |     1 |    5
3 |     3   (0)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| XTBL                       |     1 |    5
3 |     3   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | SYS_C007542                |     1 |
  |     2   (0)| 00:00:01 |

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


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

   3 - access("XID"=34393541)

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=8; dependencies=(KISH.XTBL); attributes=(single-row); name="
select /*+result_cache */ * from kish.xtbl where xid=34393541"



Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          1  physical reads
        132  redo size
       1132  bytes sent via SQL*Net to client
        423  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

kIsH@Xhydra<>alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01
kIsH@Xhydra<>alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.15
kIsH@Xhydra<>select * from kish.xtbl where xid=34393541;

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 1321960990

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

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|
 Time     |

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

|   0 | SELECT STATEMENT            |             |     1 |    53 |     3   (0)|
 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| XTBL        |     1 |    53 |     3   (0)|
 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | SYS_C007542 |     1 |       |     2   (0)|
 00:00:01 |

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


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

   2 - access("XID"=34393541)


Statistics
----------------------------------------------------------
        239  recursive calls
          0  db block gets
        359  consistent gets
          0  physical reads
          0  redo size
        992  bytes sent via SQL*Net to client
        393  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         38  sorts (memory)
          0  sorts (disk)
          1  rows processeD

kIsH@xHydra<>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 11808K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0

kIsH@xHydra<>show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 2304M
kIsH@xHydra<>show parameter result_cache_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size                big integer 11808K

0.50 % of the sga_target is allocated to result_cache_max_size

kIsH@xHydra<>SELECT ((2304 * 0.50) / 100) * 1024 RC_SIZE_kb from dual;

RC_SIZE_KB
----------
  11796.48

Oracle database allocate upto 75% of shared pool memory to result cache but not more than that.

kishan

Jan, Fri, 2024

Uncategorized

INSTALL GDB IN LINUX FOR DEBUGGING

[root@hydrupgrd oracle]# yum install -y gdb valgrind
Last metadata expiration check: 1:32:35 ago on Mon 09 Jan 2023 02:55:09 PM IST.
Dependencies resolved.
==================================================================================================================================================
 Package                            Architecture                 Version                                Repository                           Size
==================================================================================================================================================
Installing:
 gdb                                x86_64                       8.2-19.0.1.el8                         ol8_appstream                       300 k
 valgrind                           x86_64                       1:3.19.0-1.el8                         ol8_appstream                        11 M
Installing dependencies:
 gdb-headless                       x86_64                       8.2-19.0.1.el8                         ol8_appstream                       3.7 M

Transaction Summary
==================================================================================================================================================
Install  3 Packages

Total download size: 15 M
Installed size: 42 M
Downloading Packages:
(1/3): gdb-8.2-19.0.1.el8.x86_64.rpm                                                                              1.0 MB/s | 300 kB     00:00
(2/3): gdb-headless-8.2-19.0.1.el8.x86_64.rpm                                                                     2.9 MB/s | 3.7 MB     00:01
(3/3): valgrind-3.19.0-1.el8.x86_64.rpm                                                                           4.3 MB/s |  11 MB     00:02
--------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                             5.9 MB/s |  15 MB     00:02
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                          1/1
  Installing       : gdb-headless-8.2-19.0.1.el8.x86_64                                                                                       1/3
  Installing       : gdb-8.2-19.0.1.el8.x86_64                                                                                                2/3
  Installing       : valgrind-1:3.19.0-1.el8.x86_64                                                                                           3/3
  Running scriptlet: valgrind-1:3.19.0-1.el8.x86_64                                                                                           3/3
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2011.1.2.el8uek.x86_64.conf:6: hwcap directive ignored

  Verifying        : gdb-8.2-19.0.1.el8.x86_64                                                                                                1/3
  Verifying        : gdb-headless-8.2-19.0.1.el8.x86_64                                                                                       2/3
  Verifying        : valgrind-1:3.19.0-1.el8.x86_64                                                                                           3/3

Installed:
  gdb-8.2-19.0.1.el8.x86_64                  gdb-headless-8.2-19.0.1.el8.x86_64                  valgrind-1:3.19.0-1.el8.x86_64

Complete!
[root@hydrupgrd oracle]# gdb
GNU gdb (GDB) Red Hat Enterprise Linux 8.2-19.0.1.el8
Copyright (C) 2018 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Type "show copying" and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
Type "show configuration" for configuration details.
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Find the GDB manual and other documentation resources online at:
    <http://www.gnu.org/software/gdb/documentation/>.

For help, type "help".
Type "apropos word" to search for commands related to "word".
(gdb)

kishan

Jan, Thu, 2024

Uncategorized

Can Constraints be added or enabled using parallel?

No constraints cannot be added using parallel.

top - 15:29:32 up  3:14,  3 users,  load average: 0.77, 0.78, 0.61
Tasks: 267 total,   1 running, 263 sleeping,   3 stopped,   0 zombie
%Cpu(s):  6.4 us,  1.1 sy,  0.0 ni, 67.0 id, 24.8 wa,  0.5 hi,  0.1 si,  0.0 st
MiB Mem :   9670.6 total,     70.5 free,    917.4 used,   8682.8 buff/cache
MiB Swap:   4096.0 total,   3984.2 free,    111.8 used.   3664.3 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   4372 oracle    20   0 6625016   1.8g   1.7g D  26.2  19.1   0:46.60 oracle_4372_hyd

SQL> alter session set tracefile_identifier='10046';

Session altered.

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> alter session set "_px_trace"=HIGH,ALL;

Session altered.

SQL> alter table cns add constraint un unique (sd,wc) nologging parallel 4;

Table altered.

SQL> alter session set "_px_trace"=none;

Session altered.

SQL> alter session set events '10046 trace name context off';

Session altered.

WAIT #140131729716152: nam='db file scattered read' ela= 91 file#=1 block#=120818 blocks=114 obj#=97122 tim=11663649506
WAIT #140131729716152: nam='db file scattered read' ela= 86 file#=1 block#=120932 blocks=114 obj#=97122 tim=11663651612
WAIT #140131729716152: nam='db file scattered read' ela= 87 file#=1 block#=121046 blocks=114 obj#=97122 tim=11663653664

*** 2023-03-20 15:29:20.707
WAIT #140131729716152: nam='direct path write temp' ela= 35415 file number=206 first dba=423424 block cnt=31 obj#=97122 tim=11663854265
WAIT #140131729716152: nam='direct path write temp' ela= 2024 file number=206 first dba=423455 block cnt=31 obj#=97122 tim=11663857617
WAIT #140131729716152: nam='direct path write temp' ela= 1876 file number=206 first dba=423486 block cnt=31 obj#=97122 tim=11663860603

SQL> SELECT file_name FROM dba_temp_files WHERE file_id=1;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/hydrupgd/temp01.dbf

SQL> SELECT object_name FROM dba_objects WHERE object_id=97122;

OBJECT_NAME
--------------------------------------------------------------------------------
CNS

kishan

Jan, Tue, 2024

Uncategorized

Shell Script To Set Environment Variables For Multiple Databases In A Server

script=$1
cat /etc/oratab |grep -Ev '#|^$' > oratab_new

while read x
do
        IFS=':' read -r -a array <<< $x
        ORACLE_SID="${array[0]}"
        ORACLE_HOME="${array[1]}"
        export ORACLE_SID
        export ORACLE_HOME
        echo $ORACLE_SID
        echo $ORACLE_HOME
        exit|$ORACLE_HOME/bin/sqlplus -S "/ as sysdba" @$script 2>/dev/null
done < oratab_new

kishan

Jan, Mon, 2024

Uncategorized

Bash Script To Get Total Size Of Redo Log Files

du -csh /u01/app/oracle/oradata/SQLDB/redo01.log /u01/app/oracle/oradata/SQLDB/redo02.log /u01/app/oracle/oradata/SQLDB/redo03.log |grep total|awk '{print $1}'


601M

kishan

Jan, Thu, 2024

Uncategorized

Purge And Flush SQL From Shared Pool

Check the sql with more than on executions in the database currently.

kIsH@xHydra<>SELECT address,hash_value,count(*) from V$SQLAREA group by address,hash_value order by count(*) desc;

ADDRESS          HASH_VALUE   COUNT(*)
---------------- ---------- ----------
0000000071C58E18  183191396          2
0000000072D07B40 3932776936          2
0000000074586738 2317730631          2
0000000072B829A8  103545976          2
0000000072FC7B50  673844243          2
00000000743922D8 2609265457          2
000000007456FB40 2159830729          2
0000000074348120 3499000126          2
0000000074423E30 3834947339          2
0000000071D20648 3895769138          2
0000000074389840 2109556032          2

Get the sql text of the sql id to check if it is internal or external query.

kIsH@xHydra<>select sql_id,sql_text from v$sql where sql_text like '%ztbl%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
8fnr91j8pc4x7
select sql_id from v$sql where sql_text like '%ztbl%'

09348g5avp2qz <=========================
select * from ztbl

If the sql is executing with a bad execution plan, then that sql should be pinned with good plan but before that the sql plan hash value should be flushed from shared pool.

kIsH@xHydra<>col SQL_TEXT for a20
kIsH@xHydra<>set lines 200 pages 1000
kIsH@xHydra<>SELECT sql_id,sql_text,address,hash_value,count(*) from V$SQLAREA where sql_id='09348g5avp2qz' group by sql_id,sql_text,address,hash_value order by count(*) desc;

SQL_ID        SQL_TEXT             ADDRESS          HASH_VALUE   COUNT(*)
------------- -------------------- ---------------- ---------- ----------
09348g5avp2qz select * from ztbl   0000000071393F68 1438288607          1

--QUERY IS STILL EXECUTING .....

kIsH@xHydra<>SELECT users_opening, users_executing FROM v$sql WHERE sql_id='09348g5avp2qz';

USERS_OPENING USERS_EXECUTING
------------- ---------------
            9               9

Use DBMS_SHARED_POOL package to purge the sql with address and plan hash value.

kIsH@xHydra<>EXEC DBMS_SHARED_POOL.PURGE('0000000071393F68,1438288607','C');

PL/SQL procedure successfully completed.

kIsH@xHydra<>SELECT sql_id,sql_text,address,hash_value,count(*) from V$SQLAREA where sql_id='09348g5avp2qz' group by sql_id,sql_text,address,hash_value order by count(*) desc;

SQL_ID        SQL_TEXT             ADDRESS          HASH_VALUE   COUNT(*)
------------- -------------------- ---------------- ---------- ----------
09348g5avp2qz select * from ztbl   0000000071393F68 1438288607          1

Verify if the sql is still staying in the shared pool.

kIsH@xHydra<>EXEC DBMS_SHARED_POOL.PURGE('0000000071393F68,1438288607','C');

PL/SQL procedure successfully completed.

kIsH@xHydra<>SELECT sql_id,address,hash_value,count(*) from V$SQLAREA where sql_id='09348g5avp2qz' group by sql_id,address,hash_value order by count(*) desc;

no rows selected

kishan

Dec, Sun, 2023

Category Uncategorized