oracle 删除列 大数据-ag凯发k8国际
1. oracle10g以前我们都是直接刷新共享池,但这样数据库中所有sql的执行计划都会被删掉。这种办法也不可取,下面简单介绍一下这种办法:
测试版本:
sql> select * from v$version;
banner con_id
-------------------------------------------------------------------------------- ----------
oracle database 12c enterprise edition release 12.1.0.1.0 - 64bit production 0
1
2
3
4
sql>select *fromv$version;
bannercon_id
------------------------------------------------------------------------------------------
oracledatabase12centerpriseeditionrelease12.1.0.1.0-64bitproduction0
pl/sql release 12.1.0.1.0 - production 0
core 12.1.0.1.0 production 0
tns for linux: version 12.1.0.1.0 - production 0
nlsrtl version 12.1.0.1.0 - production 0
1
2
3
4
pl/sqlrelease12.1.0.1.0-production0
core12.1.0.1.0production0
tnsforlinux:version12.1.0.1.0-production0
nlsrtlversion12.1.0.1.0-production0
1. 我们执行一条简单的sql:
sql> select 1 from dual;
1
----------
1
1
2
3
4
5
sql>select1fromdual;
1
----------
1
2. 此时sql的执行计划进入shared pool,但共享池中还有其他sql的执行计划
sql> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations from v$sqlarea where sql_text = 'select 1 from dual';
sql_id address hash_value executions loads parse_calls invalidations
------------- ---------------- ---------- ---------- ---------- ----------- -------------
520mkxqpf15q8 000000006eccf9c0 2866845384 1 1 1 0
sql> select count(*) from v$sqlarea;
count(*)
----------
196
1
2
3
4
5
6
7
8
sql>selectsql_id,address,hash_value,executions,loads,parse_calls,invalidationsfromv$sqlareawheresql_text='select 1 from dual';
sql_idaddresshash_valueexecutionsloadsparse_callsinvalidations
-----------------------------------------------------------------------------------
520mkxqpf15q8000000006eccf9c028668453841110
sql>selectcount(*)fromv$sqlarea;
count(*)
----------
196
3. 然后flush一下共享池
sql> alter system flush shared_pool;
system altered.
1
2
3
sql>altersystemflushshared_pool;
systemaltered.
4. 发现最开始那条sql的执行计划已经被flush,但同时共享池所有的sql也被flush,这个代价是非常大的。不可取
sql> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations from v$sqlarea where sql_text = 'select 1 from dual';
no rows selected
sql> select count(*) from v$sqlarea;
count(*)
----------
83
1
2
3
4
5
6
7
sql>selectsql_id,address,hash_value,executions,loads,parse_calls,invalidationsfromv$sqlareawheresql_text='select 1 from dual';
norowsselected
sql>selectcount(*)fromv$sqlarea;
count(*)
----------
83
2. 下面我们来看一下删除指定sql的执行计划,10.2.0.5版本以上可直接使用
1. 执行一条简单的测试sql
sql> select 2 from dual;
2
----------
2
1
2
3
4
5
sql>select2fromdual;
2
----------
2
2. 查看sql在共享池的执行计划
sql> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where sql_text = 'select 2 from dual';
sql_id address hash_value executions loads parse_calls invalidations
------------- ---------------- ---------- ---------- ---------- ----------- -------------
9fusd37prv595 000000006c5d2988 3950875941 1 1 1 0
1
2
3
4
5
6
7
sql>selectsql_id,address,hash_value,executions,loads,parse_calls,invalidations
2fromv$sqlarea
3wheresql_text='select 2 from dual';
sql_idaddresshash_valueexecutionsloadsparse_callsinvalidations
-----------------------------------------------------------------------------------
9fusd37prv595000000006c5d298839508759411110
3. 将指定sql的执行计划flush
sql> exec sys.dbms_shared_pool.purge('000000006c5d2988,3950875941', 'c');
pl/sql procedure successfully completed.
1
2
3
sql>execsys.dbms_shared_pool.purge('000000006c5d2988,3950875941','c');
pl/sqlproceduresuccessfullycompleted.
4.然后我们再来查看这条sql的执行计划的时候,已经被flush掉了
sql> select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
2 from v$sqlarea
3 where sql_text = 'select 2 from dual';
no rows selected
1
2
3
4
5
sql>selectsql_id,address,hash_value,executions,loads,parse_calls,invalidations
2fromv$sqlarea
3wheresql_text='select 2 from dual';
norowsselected
删除完毕。就这样就删掉了指定sql的执行计划了。
转载请注明: ag凯发k8国际的版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
最后编辑:2013-09-26作者:jerry
一个积极向上的小青年,热衷于分享--focus on db,bi,etl
总结
以上是ag凯发k8国际为你收集整理的oracle 删除列 大数据_oracle 删除指定sql的执行计划。的全部内容,希望文章能够帮你解决所遇到的问题。
- 上一篇: python里面两个大于号_【课堂笔记】
- 下一篇: 智能高柜机器人_丰田公司推出新型助力机器