PROACTIVE MONITORING IS BETTER THAN REACTIVE BOTTLENECK ELIMINATION OF PERFORMANCE AFTER AN UPGRADE

PROACTIVE MONITORING IS BETTER THAN REACTIVE BOTTLENECK ELIMINATION OF PERFORMANCE AFTER AN UPGRADE

To proactively achieve something, benchmark analysis is needed to prevent upcoming issues.

It is expected that after every upgrade, there arise a performance issue due to major changes in the optimizer behavior and other value added features. Hence, application team often complaint about the regression of sql after upgrade rather than the benefits that are achieved.

Upgrading database should improve overall performance of the database and some sql work faster and some may get slower.

Firstly, install OSwatcher report to monitor and capture the preupgrade behavior of the OS layer to have OS baseline for the oracle database.

[oracle@hydrupgrd ~]$ tar -xvf oswbb840.tar >/dev/null 2>&1

Take a note of initial connections and active session counts.

SQL> select count(*) from v$session;

  COUNT(*)
----------
       141

SQL> select count(*) from v$session where username='C##HYDRA';

  COUNT(*)
----------
       102

SQL> select count(*) from dba_hist_active_sess_history;

  COUNT(*)
----------
      1269

SQL> select count(*) from dba_hist_snapshot;

  COUNT(*)
----------
        17

Initialize the application workload in the database or if it is production, then leave the database as it is for business operations. 

[oracle@hydrupgrd bin]$ ./oewizard

After the workload is initiated or if running 24/7 in production system, then capture the OS reports using OSwatcher. 

[oracle@hydrupgrd oswbb]$ ./startOSWbb.sh
[oracle@hydrupgrd oswbb]$
Info...You did not enter a value for snapshotInterval.
Info...Using default value = 30
Info...You did not enter a value for archiveInterval.
Info...Using default value = 48
Setting the archive log directory to/home/oracle/oswbb/archive

Testing for discovery of OS Utilities...
VMSTAT found on your system.
IOSTAT found on your system.
[oracle@hydrupgrd oswbb]$ MPSTAT found on your system.
IP found on your system.
TOP found on your system.
Warning... /proc/slabinfo not found on your system. Check to see if this user has permission to access this file.
PIDSTAT found on your system.
NFSIOSTAT found on your system.
Warning... TRACEROUTE not found on your system. No TRACEROUTE data will be collected.

Discovery of CPU CORE COUNT
CPU CORE COUNT will be used by oswbba to automatically look for cpu problems

CPU CORE COUNT = 4
VCPUS/THREADS = 4

Discovery completed.

Starting OSWatcher v8.4.0  on Wed Oct 11 06:58:01 IST 2023
With SnapshotInterval = 30
With ArchiveInterval = 48

OSWatcher - Written by Carl Davis, Center of Expertise,
Oracle Corporation
For questions on install/usage please go to MOS (Note:301137.1)

Data is stored in directory: /home/oracle/oswbb/archive

Starting Data Collection...

oswbb heartbeat:Wed Oct 11 06:58:06 IST 2023

If auto baseline is enabled, then all the plans of sql query are captured during the execution in the database. Hence, enable the capture auto baseline parameter and these baselines are very important after upgrade to tune the sql with benchmark performance. Check the available baselines in the database.

SQL> show parameter sql_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL> alter system set optimizer_capture_sql_plan_baselines=TRUE scope=both;

System altered.


SQL> SELECT count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
       152

Collect SQL Performance Statistics using STS CAPTURE:

Pack all the baselines from the 12c database as a backup so that it will be useful in future.

SQL> EXEC dbms_sqltune.create_sqlset(sqlset_name => 'B4UPGDLOAD', description => 'SQL performance statistics backup before upgrade');

PL/SQL procedure successfully completed.

SQL> SELECT name,statement_count as total_sql, description from all_sqlset;

NAME                  TOTAL_SQL DESCRIPTION
-------------------- ---------- ------------------------------
B4UPGDLOAD                    0 SQL performance statistics bac
                                kup before upgrade
SQL> DECLARE
  c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR
   SELECT VALUE(p)
   FROM   TABLE(
            DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
            ' module = ''B4UPGDLOAD'' AND parsing_schema_name = ''SH'' ')
          ) p;
-- load the tuning set
  DBMS_SQLTUNE.LOAD_SQLSET (
    sqlset_name     => 'B4UPGDLOAD'
,   populate_cursor =>  c_sqlarea_cursor
  2    3    4    5    6    7    8    9   10   11   12   13   14  );
END;
/ 15   16

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( -
                                        sqlset_name     => 'B4UPGLOAD', -
                                        time_limit      =>  30, -
                                        repeat_interval =>  5);> > >

PL/SQL procedure successfully completed.

SQL> col owner for a20
SQL> col description for a20
SQL> col statement_count for 9999999
SQL> SELECT owner,description,statement_count "TOTALSQLCAPTURED" from ALL_SQLSET;

OWNER                DESCRIPTION          TOTALSQLCAPTURED
-------------------- -------------------- ----------------
SYS                  SQL performance stat                0
                     istics backup before
                      upgrade

C##HYDRA             Performance statisti              147
                     cs before upgrade


SQL> BEGIN
    DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'STS',
    schema_name => 'KISH',
    tablespace_name => 'HYDRA');
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'B4UPGLOAD',
sqlset_owner => 'C##HYDRA',
staging_table_name => 'STS',
staging_schema_owner => 'KISH');
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

Run the SQL performance analyzer for the database to capture the workload.

SPA can be used to capture the SQL workload

SQL> var v_spa char(50)
begin
:v_spa:=dbms_sqlpa.create_analysis_task(
sqlset_name => 'B4UPGLOAD',
task_name => 'spa_task');
end;
/
SQL> print v_spa

V_SPA
--------------------------------------------------------------------------------

SQL> COL TASK_NAME FOR A20
SQL> SET LINES 200 PAGES 1000
SQL> SELECT task_name,task_id from dba_advisor_tasks WHERE task_name like '%spa%';

TASK_NAME               TASK_ID
-------------------- ----------
spa_task                    786


SQL>

SQL> begin
        DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
        task_name => 'spa_task',
        execution_type => 'TEST EXECUTE',
        execution_name => 'spa_task_exe');
end;
/         2    3    4    5    6    7

PL/SQL procedure successfully completed.

variable rep CLOB;
begin
:rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
task_name=>'spa_task',
type=>'HTML',
level=>'ALL',
section=>'ALL');
end;
/
PL/SQL procedure successfully completed.
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep

Create a stat table to pack and save the database statistics to the backup table.

SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE('C##HYDRA','HYDRA','USERS');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.EXPORT_DATABASE_STATS('HYDRA','B4UPGRADE','C##HYDRA');

PL/SQL procedure successfully completed.

SQL> conn c##hydra/hydra
Connected.
SQL>
SQL> BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => ‘BASELINE’);
END;
/ 2 3 4 5

PL/SQL procedure successfully completed.

SQL> DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => ‘BASELINE’,
enabled => ‘yes’);
END;
/ 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

Collect the spa report for comparison and to identify the top resourceful sql before upgrade.

SQL> SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130 SQL> PRINT :rep REP ———————————————————————————————————————————- General Information

Task Information:Workload Information:
Task Name: spa_taskTask Owner: C##HYDRADescription:SQL Tuning Set Name: B4UPGLOADSQL Tuning Set Owner: C##HYDRATotal SQL Statement Count: 2484

Execution Information:

Execution Name: spa_task_exeExecution Type: TEST EXECUTEDescription:Scope: COMPREHENSIVEStatus: COMPLETEDNumber of Unsupported SQL: 23Started: 01/02/2023 21:21:13Last Updated: 01/02/2023 21:33:26Global Time Limit: UNLIMITEDPer-SQL Time Limit: UNUSEDNumber of Errors: 1065

Report Summary

Top 100 SQL Sorted by elapsed_time desc


object_id
sql_id
Executions		Parse
time(s)		Elapsed
time(s)		CPU
time(s)		Buffer
gets
659401yc6ds819kc1.00018998.30286116.301622598900
6343vpgn6npg6qc01.00007495.7614932.75148598900
1784bgu2pvaum84661.00020775.5384091.719255323315
1750b8zgdxhtb88jr1.000926.61881.609323808
4272nr2b6gp7d0rh1.001026.539213.5324113169
12497xqrwj883n4pj1.000444.377705.0459646823
1655an7zy20kanc4w1.00017.307784.2520613301
2351f3kd1crx53qk1.000134.254286.2213583295
1000juypq5ys6gnc1.000049.205632.203654168872
2000cv6ctbmh9u2411.000139.198326.114289315
2208f7qhsax7hkhmd1.000104.165782.1395941353
3762c8au1whd41z31.001002.155694.1533411939
1996ctjvga0vf5md1234.164331.065059.0629372153
2240fh85sau14d9yp1.000678.055803.0546261580
640bxa6g4t3a19x26.004101.053302.0527444109
2107dn4y2m7wgc7t01.000784.037015.0364861355
13158cppfttsw1xsv1.072965.030027.02951212
14759fgfas6bfzfp01.000177.028029.027396261
9946ax6d90rq13vq1.060067.027295.0268610
2305fvkavpa902cmv1.059187.027187.02660314
12097mp45jmrxj8351.052025.026628.02606666
2259fn269s4zzd6km1.058514.026628.026292160
13608prvy3kmknq4x1.058228.025809.02546510
15559zvgx4wwamxha1.06068.025372.02497766
126582q67m7d9uyhk1.092256.0252.02491166
2451hnyn6v69xks41.055554.025174.024839113
2805218zwf6vh231.000057.023443.023182703
1579a51991cqqyak61.00061.022836.022475389
126782tzq2gv0h4jt1.00014.022755.02256471
2170f07qu0h30qvu51.070401.020065.01938992
1755ba3m1hr5zy5q01.011365.018716.0183721005
113574cpnuu24wmx731.562778.018238.0180627574
2191f48m4mt7xh01r1.000536.016735.01613824
4432snp8upbjtf9h1.00125.016676.016414589
50535ju38anvqk7q1.00119.016534.016269361
6453xjw1ncw5vh2770.022218.016476.0164587045
1872c1p08yuayfcp21.000577.016287.01611924
128686crkuax70gm11.000204.015901.015638628
2014cx8adhucss1801.076542.015848.01536120743
5683jc3q52z4z80p1.000361.015221.015170
1883c44c7dsy44qab1.000397.015147.014866413
1752b9pm1w8gauh8a1.062539.014693.014422248
12407v0r54aa5j3hh1.000486.014615.014336374
1609ab1u1phxvmmuj1.067135.014203.01379776
13438jpxkhmbxsun11.02446.01338.0130871005
3201z8nzdcjuxyx91.000425.012508.0123120
8064ynvg2s77fxdf1.036005.012133.011968106
1903c88j7ssdk3y1a1.000496.011662.01144524
112572krsth7b7smr1.069245.01121.011002374
2398gg2cz79k0yjbh1.001778.01117.0108021865
1190pbkanxamxgvy1.000347.010559.01036624
2192f4a02jbd8hhw41.000363.010329.0101455
1290qa2n6kz2ay1w1.000618.01031.01014334
1968cpfcvxn2dytp91.000082.010078.00980755
5823nam07bx4dqq81.000352.009805.00978134
2501j86gd5p5y3921.000338.009747.0095065
12067mc90jfb8sn071.000304.009561.0094465
15469y2r9ctm8sq2g18.004213.009455.00913918
14889jtrdu77d66373.010211.009325.00899547
141790ctbpqpmy4tt3.009853.009279.00919847
2095dk479ty4sut9z3.009829.009206.00900210
2403ggrn6a5xs009f3.010294.009206.00895347
95961xnxx28469mr1.091224.008952.008809789
1675at0pvc5v0yg7m1.030275.008905.00881392
114677asq3hypx9661.000092.00851.008304244
1590z1kvzckt62cz18.004506.008489.00823318
2631msk5g3ktpj7t37.003969.008343.008239
70249jnj80n7wfw518.003729.008311.0082318
9936atd17x59rdus1.046059.008214.0078667419
1639ajj2ta3ygqq622.053946.007162.0070552420
8104zvang41gdxw31.000773.007157.00708314
15069q32z5h7knjrm1.000531.007097.0070363803
1569a1zm097zca8qg1.00031.00681.00678170
1401xv155rhts3j1.017455.006733.006467419
10826xcgfkf6u7sq01.000112.006588.006479359
2178f1hxmdtdj2p391.000535.006272.006124101
12267st5288cd82zm1.000374.006254.0060791580
12547zup5jg28jffr1.000457.006125.005961376
2183f35by4mtvw8811.000426.006111.0059731580
1673asyh9u1uh3rvd1.000304.005959.00576632
2394gf9psa2a0fd481.00009.005927.005757130
142090u1f2xf5j3gb1.000164.005514.005422155
3171yz61wkagq6r62.020025.00524.0052471590
3071xfz9wthyh45m2.019973.005063.0050531590
1771bf7tbka73w96a1.038153.00501.004817114
10076d31mkvp1vq3j1.255112.004999.003873803
1577a4mub1jc8waz91.065198.004977.004928101
2781q6swssfmnhj11.000312.004867.00483641
2329g0u4unb36u1006.000119.004795.0046791422
4792zgnwxrqaz8gc1.000353.00467.0046151580
1870c1juncpz96u731.000312.004654.004449129
2056d7z9aqg6ykngg1.000392.004303.00408540
14989n46v29kftuyg1.012484.004292.00425248
18814pxdq7txvfvs1.000189.004223.00414692
113674d2pct303ts51.036912.004171.00413319
2122dpzw9aq5s2pd71.000079.004048.003873312
84358b5u5brjbzpu1.000237.003856.00378913
1992csrmkat7qmy501.000687.003745.00371853
8905jtujanbxdysq1.000094.003554.0033799
1160nppdhg0y1hmc1.000318.003486.00342612


Report Details


SQL Details:

Object ID: 659
Schema Name: C##HYDRA
Container Name: CDB$ROOT
SQL ID: 401yc6ds819kc
Execution Frequency: 1
SQL Text: /* SQL Analyze(1) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_ex act use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */to_char(count(“PROD_ID”)),substrb( dump(min(“PROD_ID”),16,0,64),1,240),substrb(dump(max(“PROD_ID”),16,0,64),1,240),to_char(count(“CUST_ID”)),substrb(dump(min(“CUST_I D”),16,0,64),1,240),substrb(dump(max(“CUST_ID”),16,0,64),1,240),to_char(count(“TIME_ID”)),substrb(dump(min(“TIME_ID”),16,0,64),1,2 40),substrb(dump(max(“TIME_ID”),16,0,64),1,240),to_char(count(“CHANNEL_ID”)),substrb(dump(min(“CHANNEL_ID”),16,0,64),1,240),substr b(dump(max(“CHANNEL_ID”),16,0,64),1,240),to_char(count(“PROMO_ID”)),substrb(dump(min(“PROMO_ID”),16,0,64),1,240),substrb(dump(max( “PROMO_ID”),16,0,64),1,240),to_char(count(“QUANTITY_SOLD”)),substrb(dump(min(“QUANTITY_SOLD”),16,0,64),1,240),substrb(dump(max(“QU ANTITY_SOLD”),16,0,64),1,240),to_char(count(“SELLER”)),substrb(dump(min(“SELLER”),16,0,64),1,240),substrb(dump(max(“SELLER”),16,0, 64),1,240),to_char(count(“FULFILLMENT_CENTER”)),substrb(dump(min(“FULFILLMENT_CENTER”),16,0,64),1,240),substrb(dump(max(“FULFILLME NT_CENTER”),16,0,64),1,240),to_char(count(“COURIER_ORG”)),substrb(dump(min(“COURIER_ORG”),16,0,64),1,240),substrb(dump(max(“COURIE R_ORG”),16,0,64),1,240),to_char(count(“TAX_COUNTRY”)),substrb(dump(min(“TAX_COUNTRY”),16,0,64),1,240),substrb(dump(max(“TAX_COUNTR Y”),16,0,64),1,240),to_char(count(“TAX_REGION”)),substrb(dump(min(“TAX_REGION”),16,0,64),1,240),substrb(dump(max(“TAX_REGION”),16, 0,64),1,240),to_char(count(“AMOUNT_SOLD”)),substrb(dump(min(“AMOUNT_SOLD”),16,0,64),1,240),substrb(dump(max(“AMOUNT_SOLD”),16,0,64 ),1,240) from “C##HYDRA”.”SALES” t /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL ,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/

Execution Statistics:

parse_time: .000189
cost: 163016
elapsed_time: 98.302861
cpu_time: 16.301622
user_io_time: 82.602702
buffer_gets: 598900
reads: 598879
writes: 0
io_interconnect_bytes: 4906016768
rows: 1

Note: time statistics are displayed in seconds

Notes:

The statement was first executed to warm the buffer cache.Statistics shown were from the second execution.

Execution Plan:

Plan Id: 4357
Plan Hash Value: 1047182207
IdOperationNameRowsBytesCostTime
0SELECT STATEMENT14916301600:00:07
1. SORT AGGREGATE149
2.. TABLE ACCESS FULLSALES76249610373623089016301600:00:07


SQL Details:

Object ID: 634
Schema Name: SYS
Container Name: CDB$ROOT
SQL ID: 3vpgn6npg6qc0
Execution Frequency: 1
SQL Text: select /*+ DYNAMIC_SAMPLING(0) NO_INDEX(mytab) */ count(*) from “C##HYDRA”.”SALES” mytab

Execution Statistics:

parse_time: .000074
cost: 162593
elapsed_time: 95.761493
cpu_time: 2.75148
user_io_time: 94.009092
buffer_gets: 598900
reads: 598879
writes: 0
io_interconnect_bytes: 4906016768
rows: 1

Note: time statistics are displayed in seconds

Notes:

The statement was first executed to warm the buffer cache.Statistics shown were from the second execution.

Execution Plan:

Plan Id: 4332
Plan Hash Value: 1047182207
IdOperationNameRowsBytesCostTime
0SELECT STATEMENT116259300:00:07
1. SORT AGGREGATE1
2.. TABLE ACCESS FULLSALES7624961016259300:00:07


SQL Details:

Object ID: 1784
Schema Name: SYS
Container Name: CDB$ROOT
SQL ID: bgu2pvaum8466
Execution Frequency: 1
SQL Text: select /*+ DYNAMIC_SAMPLING(0) NO_INDEX(mytab) */ count(*) from “KISH”.”STS” mytab

Execution Statistics:

parse_time: .000207
cost: 87796
elapsed_time: 75.538409
cpu_time: 1.719255
user_io_time: 74.405416
buffer_gets: 323315
reads: 323113
writes: 0
io_interconnect_bytes: 2646941696
rows: 1

Note: time statistics are displayed in seconds

Notes:

The statement was first executed to warm the buffer cache.Statistics shown were from the second execution.

Execution Plan:

Plan Id: 5482
Plan Hash Value: 1256971950
IdOperationNameRowsBytesCostTime
0SELECT STATEMENT18779600:00:04
1. SORT AGGREGATE1
2.. TABLE ACCESS FULLSTS24058818779600:00:04


SQL Details:

Object ID: 1750
Schema Name: SYS
Container Name: CDB$ROOT
SQL ID: b8zgdxhtb88jr
Execution Frequency: 1
SQL Text: select /*+ no_parallel_index(t, “I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST”) dbms_stats cursor_sharing_exact use_weak _name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad no_expand index(t,”I_WRI$_OPTSTAT_H_OBJ#_ICOL#_S T”) */ count(*) as nrw,count(distinct sys_op_lbid(12938,’L’,t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend(“OBJ#”)||sys_o p_descend(“INTCOL#”)||sys_op_descend(“SYS_NC00017$”)||sys_op_descend(“COLNAME”))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from “SYS”.”WRI$_OPTSTAT_HISTGRM_HISTORY” t where “OBJ#” is not null or “INTCOL#” is not null or “SYS_NC00017$” is not null or “COLNAME” is not null

Execution Statistics:

parse_time: .000926
cost: 3508
elapsed_time: .61881
cpu_time: .60932
user_io_time: 0
buffer_gets: 3808
reads: 0
writes: 0
io_interconnect_bytes: 0
rows: 1

Note: time statistics are displayed in seconds

Notes:

The statement was first executed to warm the buffer cache.Statistics shown were from the second execution.

Execution Plan:

Plan Id: 5448
Plan Hash Value: 3428983716
IdOperationNameRowsBytesCostTime
0SELECT STATEMENT157350800:00:01
1. SORT GROUP BY157
2.. INDEX FULL SCANI_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST42719824350286350800:00:01


SQL Details:

Object ID: 427
Schema Name: SYS
Container Name: CDB$ROOT
SQL ID: 2nr2b6gp7d0rh
Execution Frequency: 1
SQL Text: /* SQL Analyze(1) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_ex act use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */to_char(count(“OBJ#”)),substrb(dum p(min(“OBJ#”),16,0,64),1,240),substrb(dump(max(“OBJ#”),16,0,64),1,240),to_char(count(“INTCOL#”)),substrb(dump(min(“INTCOL#”),16,0, 64),1,240),substrb(dump(max(“INTCOL#”),16,0,64),1,240),to_char(count(“SAVTIME”)),substrb(dump(min(“SAVTIME”),16,0,64),1,240),subst rb(dump(max(“SAVTIME”),16,0,64),1,240),to_char(count(“BUCKET”)),substrb(dump(min(“BUCKET”),16,0,64),1,240),substrb(dump(max(“BUCKE T”),16,0,64),1,240),to_char(count(“ENDPOINT”)),substrb(dump(min(“ENDPOINT”),16,0,64),1,240),substrb(dump(max(“ENDPOINT”),16,0,64), 1,240),to_char(count(“EPVALUE”)),substrb(dump(min(“EPVALUE”),16,0,64),1,240),substrb(dump(max(“EPVALUE”),16,0,64),1,240),to_char(c ount(“COLNAME”)),substrb(dump(min(“COLNAME”),16,0,64),1,240),substrb(dump(max(“COLNAME”),16,0,64),1,240),to_char(count(“EP_REPEAT_ COUNT”)),substrb(dump(min(“EP_REPEAT_COUNT”),16,0,64),1,240),substrb(dump(max(“EP_REPEAT_COUNT”),16,0,64),1,240),to_char(count(“EP VALUE_RAW”)),substrb(dump(min(“EPVALUE_RAW”),16,0,64),1,240),substrb(dump(max(“EPVALUE_RAW”),16,0,64),1,240),to_char(count(“SAVTIM E_DATE”)),substrb(dump(min(“SAVTIME_DATE”),16,0,64),1,240),substrb(dump(max(“SAVTIME_DATE”),16,0,64),1,240),to_char(count(“SPARE1” )),substrb(dump(min(“SPARE1”),16,0,64),1,240),substrb(dump(max(“SPARE1”),16,0,64),1,240),to_char(count(“SPARE2”)),substrb(dump(min (“SPARE2”),16,0,64),1,240),substrb(dump(max(“SPARE2”),16,0,64),1,240),to_char(count(“SPARE3”)),substrb(dump(min(“SPARE3”),16,0,64) ,1,240),substrb(dump(max(“SPARE3”),16,0,64),1,240),to_char(count(“SPARE4”)),substrb(dump(min(“SPARE4”),16,0,64),1,240),substrb(dum p(max(“SPARE4”),16,0,64),1,240),to_char(count(“SPARE5”)),substrb(dump(min(“SPARE5”),16,0,64),1,240),substrb(dump(max(“SPARE5”),16, 0,64),1,240),to_char(count(“SPARE6”)),substrb(dump(min(“SPARE6”),16,0,64),1,240),substrb(dump(max(“SPARE6”),16,0,64),1,240),to_cha r(count(“SYS_NC00017$”)),substrb(dump(min(“SYS_NC00017$”),16,0,64),1,240),substrb(dump(max(“SYS_NC00017$”),16,0,64),1,240),count(r owidtochar(rowid)) from “SYS”.”WRI$_OPTSTAT_HISTGRM_HISTORY” t /* TOPN,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,ND V,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL ,TOPN,NIL,NIL,RWID,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254,U254U*/

Execution Statistics:

parse_time: .001026
cost: 847
elapsed_time: .539213
cpu_time: .532411
user_io_time: 0
buffer_gets: 3169
reads: 0
writes: 0
io_interconnect_bytes: 0
rows: 1

Note: time statistics are displayed in seconds

Notes:

The statement was first executed to warm the buffer cache.Statistics shown were from the second execution.

Execution Plan:

Plan Id: 4125
Plan Hash Value: 2746973440
IdOperationNameRowsBytesCostTime
0SELECT STATEMENT15784700:00:01
1. SORT AGGREGATE157
2.. PARTITION RANGE ALL4271982435028684700:00:01
3… TABLE ACCESS FULLWRI$_OPTSTAT_HISTGRM_HISTORY4271982435028684700:00:01


SQL Details:

Object ID: 1249
Schema Name: SYS
Container Name: CDB$ROOT
SQL ID: 7xqrwj883n4pj
Execution Frequency: 1
SQL Text: select substrb(dump(val,16,0,64),1,240) ep, freq, cdn, ndv, (sum(pop) over()) popcnt, (sum(pop*freq) over( )) popfreq, substrb(dump(max(val) over(),16,0,64),1,240) maxval, substrb(dump(min(val) over(),16,0,64),1,240) minval from (sele ct val, freq, (sum(freq) over()) cdn, (count(*) over()) ndv, (case when freq > ((sum(freq) over())/254) then 1 else 0 end) pop from (select /*+ 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 no_substrb_pad */ “ORDER_ID” val, count(“ORDER_ID”) freq from “C##HYDRA”.”ORDER_ITEMS” sample ( .0128204435) t where “ORDER_ID” is not null group by “ORDER_ID”)) order by val

Execution Statistics:

parse_time: .000444
cost: 27009
elapsed_time: .377705
cpu_time: .045964
user_io_time: .338925
buffer_gets: 6823
reads: 6136
writes: 0
io_interconnect_bytes: 50266112
rows: 5496

Note: time statistics are displayed in seconds

Notes:

The statement was first executed to warm the buffer cache.Statistics shown were from the second execution.

Execution Plan:

Plan Id: 4947
Plan Hash Value: 1884174387
IdOperationNameRowsBytesCostTime
0SELECT STATEMENT54992639522700900:00:02
1. WINDOW SORT54992639522700900:00:02
2.. VIEW54992639522700900:00:02
3… WINDOW BUFFER5499989822700900:00:02
4…. HASH GROUP BY5499989822700900:00:02
5….. INDEX SAMPLE FAST FULL SCANITEM_ORDER_IX5500990002700800:00:02


SQL Details:

Object ID: 1655
Schema Name: SYS
Container Name: CDB$ROOT
SQL ID: an7zy20kanc4w
Execution Frequency: 1
SQL Text: select /*+ parallel_index(t, ” SQL> spool off

SQL> select count(*) from dba_sql_profiles;

  COUNT(*)
----------
         0

set lines 200 pages 1000
col SQL_TEXT for a50

SELECT * from (
select to_char(dhsn.BEGIN_INTERVAL_TIME,'dd-mm-yy hh24:mi:ss') time,
         dhss.elapsed_time_total,
         dhss.plan_hash_value,
         dhst.sql_id,
         dhst.sql_text
         from dba_hist_sqlstat dhss
inner join dba_hist_snapshot dhsn on ( dhss.snap_id = dhsn.snap_id )
inner join dba_hist_sqltext dhst on ( dhst.dbid = dhsn.dbid)
where plan_hash_value <> 0
order by dhss.elapsed_time_total asc
)
WHERE ROWNUM < 6;
  6    7    8    9   10   11   12   13

TIME              ELAPSED_TIME_TOTAL PLAN_HASH_VALUE SQL_ID        SQL_TEXT
----------------- ------------------ --------------- ------------- --------------------------------------------------
13-01-23 19:30:50                368      4024720576 5n1cu9123nabt create index r_id_name on rp(r_name)
13-01-23 19:30:50                368      4024720576 asvzxj61dc5vs select timestamp, flags from fixed_obj$ where obj#
                                                                   =:1

13-01-23 19:30:50                368      4024720576 7tchj0bmt6tn1 select xmlagg(
                                                                                           xmlelement("operation",


13-01-23 19:30:50                368      4024720576 0v3dvmc22qnam insert into sys.col_usage$ (obj#, intcol#, equalit
                                                                   y_preds, equijoin_preds, noneq

13-01-23 19:30:50                368      4024720576 b1wc53ddd6h3p select audit$,options from procedure$ where obj#=:
                                                                   1
SQL> SQL> @coe_xfr_sql_profile 7tchj0bmt6tn1 4024720576

Parameter 1:
SQL_ID (required)



PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     1660022729        .011

Parameter 2:
PLAN_HASH_VALUE (required)


Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "7tchj0bmt6tn1"
PLAN_HASH_VALUE: "4024720576"

Export AWR data from the database for capturing the baseline performance data of the instance level.

SQL> @?/rdbms/admin/awrextr
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the         ~
~  following information:                                          ~
~     (1) database id                                              ~
~     (2) snapshot range to extract                                ~
~     (3) name of directory object                                 ~
~     (4) name of dump file                                        ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     DB Name      Host
------------ ------------ ------------
* 1185489427 HYDRUPGD     hydrupgrd


[oracle@hydrupgrd ~]$ cd /u01/app/oracle/product/19.0.0/db_home1/
[oracle@hydrupgrd db_home1]$ ls
[oracle@hydrupgrd db_home1]$ ls
LINUX.X64_193000_db_home.zip
[oracle@hydrupgrd db_home1]$ unzip LINUX.X64_193000_db_home.zip
Comments 0

Leave a Reply

%d bloggers like this: