BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle 19c Analysis SQL in AWR report Tips

2020-02-12

Oracle 19c Analysis SQL in AWR report

There are some sql in SQL ordered by CPU Time AWR report.

These sql were using so many cpu time…

AnalysisSQL

/* SQL Analyze(0) */ select /*+ full(t) parallel(t, 6) parallel_index(t, 6) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */to_char...
 

What are these sqls? what are they doing?

Simulate test

LIN@pdb1> drop table lin.test_tab purge;

Table dropped.

LIN@pdb1> create table lin.test_tab(col1 number, col2 varchar(20));

Table created.

LIN@pdb1>
begin
 for i in 1..10000 loop
 insert into lin.test_tab values (i, i||'aaaabbbb');
 end loop;
 commit;
end;
  7  /

PL/SQL procedure successfully completed.

LIN@pdb1> select count(*) from lin.test_tab;

  COUNT(*)
----------
     10000

LIN@pdb1>
LIN@pdb1> conn sys/oracle@pdb1 as sysdba
Connected.
SYS@pdb1> alter session set max_dump_file_size = UNLIMITED;

Session altered.

SYS@pdb1> alter session set events '10046 trace name context forever, level 12';

Session altered.

SYS@pdb1> exec DBMS_STATS.GATHER_TABLE_STATS('LIN','TEST_TAB');

PL/SQL procedure successfully completed.

SYS@pdb1> alter session set events '10046 trace name context off';

Session altered.

SYS@pdb1>
SYS@pdb1> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17733.trc

SYS@pdb1> !ls -ltr /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17733.trc
-rw-r-----. 1 oracle oinstall 520812 Feb 19 09:12 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17733.trc

SYS@pdb1> 

the /* SQL Analyze(0) */ sql can be found in the trace file

PARSING IN CURSOR #139867558904224 len=513 dep=1 uid=126 oct=3 lid=0 tim=16068278669 hv=2609264150 ad='71e71608' sqlid='1391vrydscdhq'
/* SQL Analyze(0) */ select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */to_char(count("COL1")),substrb(dump(min("COL1"),16,0,64),1,240),substrb(dump(max("COL1"),16,0,64),1,240),to_char(count("COL2")),substrb(dump(min("COL2"),16,0,64),1,240),substrb(dump(max("COL2"),16,0,64),1,240) from "LIN"."TEST_TAB" t  /* NDV,NIL,NIL,NDV,NIL,NIL*/

Result

the sql - /* SQL Analyze(0) */ is similare with the internal sql which are from using DBMS_STATS package to gather statistics with objects

Maybe happened in mid-night, there are some batch job doing insert append sql and gather statistics with objects online.

Oracle 19c online statistics gathering Tips

Other Tips.

  • Use talbe/index parallel to limit the parallel.

Tht total parallel: parallel_threads_per_cpu * cpu_count * rac numbers

Some sql tips:

$ show parameter parallel_threads_per_cpu
$ show parameter cpu_count
$ show parameter parallel

$ select TABLE_NAME,DEGREE from dba_tables where table_name='<TABLE_NAME>';
$ alter table <SCHEMA_NAME>.<TABLE_NAME> parallel <number>;

Reference

Oracle 19c online statistics gathering Tips

Have a good work&life! 2020/02 via LinHong


Similar Posts

Comments