没有合适的资源？快使用搜索试试~ 我知道了~

文库首页数据库Oracleoracle相关操作oracle相关操作

oracle相关操作oracle相关操作

oracle

需积分: 5 0 下载量 112 浏览量 2024-03-12 09:02:54 上传评论收藏 86KB DOCX 举报

温馨提示

试读

26页

oracle相关操作oracle相关操作

资源推荐

资源详情

资源评论

Oracle spm

2023 年 6 月 17 日

10:05

var temp number

exec :temp :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'60txg87j30pvw',plan_has

h_value=>'0bgyrpnhshvh7',sql_handle=>'SQL_e0c270bd59762c23');

DECLARE

my_plans pls_integer;

BEGIN

my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

(sql_id=>'0bgyrpnhshvh7',plan_hash_value=>2927943852,sql_handle=>'SQL_e0c270bd5976

2c23');

END;

DECLARE

my_plans pls_integer;

BEGIN

my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name =>

'SQLSET_SFIT',sqlset_owner => 'SPA',basic_filter => 'sql_id=''2up1q0k19nxtm''');

END;

var temp number

exec :temp :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'6z6861m9r3hdq',plan_has

h_value=>1394031684,sql_handle=>'SQL_45c404e9b4180c2c');

declare

report clob;

begin

report:=dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SQL_e0c270bd59762c23');

dbms_output.put_line(report);

end;

declare

plans_altered PLS_INTEGER;

begin

plans_altered:=dbms_spm.alter_sql_plan_baseline(

sql_handle => 'SQL_e0c270bd59762c23',

plan_name=>'SQL_PLAN_f1hmhrpcrcb130ea82bab',

attribute_name=>'enabled',

attribute_value=>'NO');

end;

SET SERVEROUTPUT ON

DECLARE

l_plans_dropped PLS_INTEGER;

BEGIN

l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (

sql_handle => 'SQL_7b76323ad90440b9',

plan_name => NULL);

DBMS_OUTPUT.put_line(l_plans_dropped);

END;

declare

v_plan_num PLS_INTEGER;

begin

for cur in (SELECT * FROM dba_sql_plan_baselines) loop

begin

v_plan_num := dbms_spm.drop_sql_plan_baseline(sql_handle => cur.sql_handle);

exception

when others then

null;

end;

end loop;

end;

Oracle spa

2023 年 6 月 17 日

10:05

begin

dbms_stats.gather_database_stats(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZ

E, method_opt => 'for all indexed columns size auto', cascade=>true, degree=>128);

end;

32 8:30-9:00

128 9:08-9:00

DBMS_SQLTUNE.EXTRACT_BINDS

CREATE USER SPA IDENTIFIED BY SPA DEFAULT TABLESPACE SYSAUX;

GRANT DBA TO SPA;

GRANT ADVISOR TO SPA;

GRANT SELECT ANY DICTIONARY TO SPA;

GRANT ADMINISTER SQL TUNING SET TO SPA;

EXEC DBMS_SQLTUNE.DROP_SQLSET (SQLSET_NAME => 'SQLSET_SFIT',SQLSET_OWNER =>

'SPA');

EXEC DBMS_SQLTUNE.CREATE_SQLSET (SQLSET_NAME => 'SQLSET_SFIT',-

DESCRIPTION => 'SQL Set Create at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), -

SQLSET_OWNER => 'SPA')

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ('SQLSET_SFIT', 'SPA', 'SYSAUX');

DECLARE

SQLSET_CUR DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN

OPEN SQLSET_CUR FOR

SELECT VALUE(P) FROM TABLE(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( 18916, 19115,

'PARSING_SCHEMA_NAME NOT IN (''SYS'', ''SYSTEM'')',

NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;

DBMS_SQLTUNE.LOAD_SQLSET(

SQLSET_NAME => 'SQLSET_SFIT',

SQLSET_OWNER => 'SPA',

POPULATE_CURSOR => SQLSET_CUR,

LOAD_OPTION => 'MERGE',

UPDATE_OPTION => 'ACCUMULATE');

CLOSE SQLSET_CUR;

END;

EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET ( sqlset_name => 'SQLSET_SFIT',-

time_limit => 600,-

repeat_interval=>10,-

capture_option=>'MERGE',-

capture_mode => DBMS_SQLTUNE.MODE_REPLACE_OLD_STATS,-

basic_filter=> 'parsing_schema_name NOT IN (''SYS'', ''SYSTEM'', ''SYSMAN'')',-

sqlset_owner => NULL,-

recursive_sql=> DBMS_SQLTUNE.HAS_RECURSIVE_SQL);

SELECT * FROM dba_sqlset_statements

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET (SQLSET_NAME=>

'SQLSET_SFIT',SQLSET_OWNER=> 'SPA',STAGING_TABLE_NAME=>

'SQLSET_SFIT',STAGING_SCHEMA_OWNER => 'SPA');

expdp \'/ as sysdba\' dumpfile=mes12db_stage_table.dmp

logfile=mes12db_stage_table.log directory=data_pump_dir tables=SPA.SQLSET_SFIT

impdp \'/ as sysdba\' dumpfile=mes12db_stage_table_2.dmp

logfile=mes12db_stage_table_2.log directory=dumpdir tables=spa.SQLSET_SFIT cluster=n

EXEC

DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(SQLSET_NAME=>'SQLSET_SFIT',SQLSET_OWNER

=>'SPA',REPLACE=> TRUE,STAGING_TABLE_NAME=>

'SQLSET_SFIT',STAGING_SCHEMA_OWNER => 'SPA');

VARIABLE SPA_TASK VARCHAR2(64);

EXEC :SPA_TASK :=

DBMS_SQLPA.CREATE_ANALYSIS_TASK(TASK_NAME=>'SPA_TASK_SFIT',DESCRIPTION =>'SPA

Analysis task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'),SQLSET_NAME =>

'SQLSET_SFIT',SQLSET_OWNER =>'SPA');

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME=>

'SPA_TASK_SFIT',EXECUTION_NAME => 'EXEC_11G_SFIT',EXECUTION_TYPE => 'CONVERT

SQLSET', EXECUTION_DESC => 'Convert 11g SQLSET for SPA Task at : '||TO_CHAR(SYSDATE,

'YYYY-MM-DD HH24:MI:SS'));

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME=>

'SPA_TASK_SFIT',EXECUTION_NAME => 'EXEC_11G_TARGET_SFIT',EXECUTION_TYPE => 'TEST

EXECUTE',-

execution_params =>

dbms_advisor.argList('EXECUTE_COUNT','3'),EXECUTION_DESC => 'Execute SQL in

11G TARGET for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME=>

'SPA_TASK_SFIT',EXECUTION_NAME =>'COMPARE_ET_SFIT',-

EXECUTION_TYPE=>'COMPARE PERFORMANCE',-

EXECUTION_PARAMS=>DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',

'ELAPSED_TIME','EXECUTE_FULLDML','TRUE','EXECUTION_NAME1','EXEC_11G_SFIT','EXECUT

ION_NAME2','EXEC_11G_TARGET_SFIT'),-

EXECUTION_DESC => 'Compare SQLs between 11g and 19g Traget at :'||TO_CHAR(SYSDATE,

'YYYY-MM-DD HH24:MI:SS'));

exec dbms_sqlpa.DROP_ANALYSIS_TASK('SPA_TASK_SFIT');

SPOOL execute_comparison_report_0225_oltp_sts.html

SELECT dbms_sqlpa.report_analysis_task('SPA_TASK_SFIT', 'HTML', 'ALL', 'ALL') FROM dual;

SPOOL OFF

ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';

SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON

SIZE UNLIMITED

SPOOL elapsed_all.html

SELECT

XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_SFIT','HTML','ALL','ALL',null,10

00,'COMPARE_ET_SFIT')).GETCLOBVAL(0,0) FROM DUAL;

ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';

SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON

SIZE UNLIMITED

SPOOL unsupported.html

SELECT

XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_SFIT','HTML','UNSUPPORTED','

ALL',NULL,NULL,'COMPARE_ET_SFIT')).GETCLOBVAL(0,0) FROM DUAL;

set trimspool on

set trim on

set pages 0

set linesize 10000

set long 999999999

set longchunksize 10000000

spool spa_REGRESSED_report_elapsed_time.html

SELECT dbms_sqlpa.report_analysis_task('SPA_TASK_SFIT', 'HTML', 'REGRESSED','ALL',

top_sql=>18,execution_name=>'COMPARE_ET_SFIT') FROM dual;

spool off;

SPOOL execute_comparison_report_0509_oltp_sts.htm

SELECT dbms_sqlpa.report_analysis_task('SPA_TASK_SFIT', 'HTML', 'ALL', 'ALL') FROM dual;

SPOOL OFF

set trimspool on

剩余25页未读，继续阅读

评论收藏

内容反馈

资源评论

资源反馈

评论星级较低，若资源使用遇到问题可联系上传者，3个工作日内问题未解决可申请退款~

bestinter2010

粉丝: 45
资源: 7

上传资源快速赚钱

我的内容管理展开

我的资源快来上传第一个资源

我的收益

登录查看自己的收益

我的积分登录查看自己的积分

我的C币登录后查看C币余额

我的收藏

我的下载

下载帮助

前往需求广场，查看用户热搜

oracle相关操作oracle相关操作

Oracle EBS 操作手册

oracle数据库操作手册

oracle常用操作大全

arcsde 操作oracle的操作原理

Linux下Oracle常用操作

C#版Oracle数据库通用操作类

Oracle基础操作手册

在Oracle运行操作系统命令

Oracle数据库操作命令集合

C# Oracle操作类

oracle操作手册.zip

oracle数据库操作软件golden

skyline与oracle数据库坐标关联之oracle数据库操作说明

Oracle操作详细文档

Oracle11g日常操作与维护手册

MFC对Oracle数据库操作

DatabaseHelper_oracle_c＃Oracle_C#_ManagedDataAccess_oracle操作_

Oracle操作语句大全

PHP Oracle 数据库操作类

oci.dll 12版本全部

数据库系统概念（原书第七版）课后作业题

fineReport课后10题答案.zip

win64-11gR2-client.zip

Oracle19c-Windows客户端

oracle 12c windows 安装包下载

Kingbase KCA题库

Oracle11安装依赖包，11.2.0.4.0版本

Oracle19c(19.3)数据库驱动ojdbc.jar

最新资源