Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use oracle 9i database to do spa, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Get the script for the trace file

Mkdir / oracle/qwedir

Create table sys.tab_ospid (SID NUMBER,SERIAL# NUMBER,PADDR RAW (8), SPID VARCHAR2 (12))

Start the capture trace file script

#! / bin/bash

. / oracle/.profile

Sqlplus-s "/ as sysdba"'9i advance advance wkld')

END

/

-View sqlset data content

Select name,statement_count from dba_sqlset

Variable sts_task VARCHAR2 (64)

EXEC: sts_task: = DBMS_SQLPA.CREATE_ANALYSIS_TASK (-

Task_name = >'9i, 12c, spawn,-

Description = > 'experiment for 9i to 12c upgrade',-

Sqlset_name = >'9i advance Wkld')

Exec dbms_sqlpa.execute_analysis_task (-

Task_name = >'9i, 12c, spawn,-

Execution_name = > '9iota trialogy,-

Execution_type = > 'CONVERT SQLSET',-

Execution_desc = >'9i sql trial generated from STS')

Exec dbms_sqlpa.execute_analysis_task (-

Task_name = >'9i, 12c, spa, etc.-

Execution_name = > '12cThe trialogy-

Execution_type = > 'TEST EXECUTE',-

Execution_desc = > '12c sql trial generated from STS')

Select * from v$session_longops where target_desc='sts=9i_prod_wkld'

Select sofar,totalwork from v$advisor_progress where task_id=''

Begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (

Task_name = >'9i, 12c, spa'

Execution_type = > 'COMPARE PERFORMANCE'

Execution_name = > 'Compare_buffer_gets'

Execution_params = > dbms_advisor.arglist ('execution_name1','9i_trial','execution_name2','12c_trial','comparison_metric','buffer_gets'))

End

/

Begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (

Task_name = >'9i, 12c, spa'

Execution_type = > 'COMPARE PERFORMANCE'

Execution_name = > 'Compare_cpu_time'

Execution_params = > dbms_advisor.arglist ('execution_name1','9i_trial','execution_name2','12c_trial','comparison_metric','cpu_time'))

End

/

Begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (

Task_name = >'9i, 12c, spa'

Execution_type = > 'COMPARE PERFORMANCE'

Execution_name = > 'Compare_elapsed_time'

Execution_params = > dbms_advisor.arglist ('execution_name1','9i_trial','execution_name2','12c_trial','comparison_metric','elapsed_time'))

End

/

Begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (

Task_name = >'9i, 12c, spa'

Execution_type = > 'COMPARE PERFORMANCE'

Execution_name = > 'Compare_disk_reads'

Execution_params = > dbms_advisor.arglist ('execution_name1','9i_trial','execution_name2','12c_trial','comparison_metric','disk_reads'))

End

/

SELECT dbms_sqlpa.report_analysis_task ('9i, 12c) spawn, 'HTML',' ALL','ALL', execution_name= > 'Compare_disk_reads') FROM dual

SELECT dbms_sqlpa.report_analysis_task ('9i, 12c) spawn, 'HTML',' ALL','ALL', execution_name= > 'Compare_buffer_gets') FROM dual

SELECT dbms_sqlpa.report_analysis_task ('9i, 12c) spawn, 'HTML',' ALL','ALL', execution_name= > 'Compare_cpu_time') FROM dual

SELECT dbms_sqlpa.report_analysis_task ('9i, 12c) spawn, 'HTML',' ALL','ALL', execution_name= > 'Compare_elapsed_time') FROM dual

-- get the performance changes of all SQL

Set lines 188 pages 9999 long 999999 trim on trims on

Spool spa_elapsed_20180504.html

Select xmltype (dbms_sqlpa.report_analysis_task (-

'91cm spawn, 'html',' all', 'all', null, 100,-

'Compare_elapsed_time')) .getclobval (0d0) from dual

Spool off

-- get the list of unsupported SQL

Spool spa_elapsed_unsupported_20180504.html

Select xmltype (dbms_sqlpa.report_analysis_task (-

'91cm spawn, 'html',' unsupported', 'all', null, 100,-

'Compare_elapsed_time')) .getclobval (0d0) from dual

Spool off

-- get the SQL list of all execution errors

Spool spa_elapsed_errors_20180504.html

Select xmltype (dbms_sqlpa.report_analysis_task (-

'91cm spawn, 'html',' errors', 'all', null, 100,-

'Compare_elapsed_time')) .getclobval (0d0)-

From dual

Spool off

On how to use oracle 9i database to do spa to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.