execute immediate 'alter session set db_file_multiblock_read_count=128';
This technique commonly used terms in the stored procedure , each read block size ( 128 ) also according to the actual needs and the configuration of the machine into account
2. When the query function exists in the column operation, at this point in the column index is invalid ( to create a functional index ) , for example, to_char (abc, 'xxxx'), abc field index lapse
3.like 'abc%' use of the index , like '% abc%' do not use indexes ( google themselves to reason )
4.
insert /*+ append */ --使用这个hint可以将数据使用直接路径插入到表的高水线之后，由于是连续的没有使用的空间，所以插入速度快。
into tab_abc select * from ......
5. guide can be used when the data
insert into tab_abc nologging select * from tab_cde; ( tenets of his investigation, you will learn more )
6.alter table hu move partition "REN130602" compress parallel 8 nologging;
compression table can improve query performance , but will greatly reduce the insertion and removal efficiency ( when the table is larger than 100G personal recommendations when considering it )
7. minimize the use Truncate, do not fearless , just get rid of a table , this habit will kill you .
8. even kill do not be afraid , Oracle 9i began to support the Flashback Query to recover accidentally deleted data . (truncate also can reply , but a prerequisite )
9. Dynamic cursors opened must remember to turn off .
---------------------------------------------- -------------------------------------------------- -
handle lock table
first step , check out the table is locked session_id, and the serial #.
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial# ;
The second step , kill session
alter system kill session 'AAA,BBB' -- 其中AAA是上面查询出的sid,BBB是serial#的值
here note , promising is not your own lock table , spotted in the kill
The third part , generally two steps to get the lock table , if not kill , they kill ...... into the OS level
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24 （24是上面的sid）
linux under the kill -9 12345 (12345 for the top check out the spid)
here should pay attention to , kill wrong, anything could happen.
---------------------------------------------- -------------------------------------------------- -
many are head to think , usually unwritten , the statement will certainly be the wrong place , we hope the attitude of learning to see.
knowledge basis for comparison , hope cattle were downstairs complement and pointing error .... thanks .
amount , write so much , playing a xx bar, where their base, very good, group number: 53596919
------ Solution ------------------------------------ --------
good welcome to share, please scattered points.
------ Solution ---------------------------------------- ----
learn ~ ~
------ Solution ----------------------------- ---------------
Thank you to share
------ Solution -------------------- ------------------------
Thanksgiving , learning
------ Solution --------- -----------------------------------
Muzi , guess who even ~ !
------ Solution ---------------------------------------- ----
0.0,
------ Solution -------------------------- ------------------
Thank you to share
------ Solution ----------------- ---------------------------
well , long time no CSDN on the ~ stuff was good , wow ha ha ha ha ~ < br> ------ Solution ----------------------------------------- ---
- ---- Solution -------------------------------------------- < br> decisively Favorites learning
------ Solution ----------------------------------- ---------
LZ share more good ah !
------ Solution ---------------------------------------- ----
collection about
------ Solution ------------------------------- -------------
Well learned , was about to begin with the oracle database under study ,
Q: What additive group needs verification code , verification code is the number ! ! !
------ Solution ---------------------------------------- ----
study under
------ Solution ------------------------------- -------------
good!
------ Solution ---------------------------------------- ----
strong UP ------ Solution ------------------------------------------ -
Some time ago, a man said to me: it more than 200 million data , just mess things like,
------ Solution - -------------------------------------------
active learning ah. . .
------ Solution ---------------------------------------- ----
support . . . . . .
------ Solution ---------------------------------------- ----
learning
Thank landlord
------ Solution ---------------------------------- ----------
very useful, thank you
------ Solution ----------------------- ---------------------
Thank LZ. learn
------ Solution ------------------------------------ --------
truncate after a commit, flashback query can restore it ? Doubts ......
------ Solution ------------------------------------- -------
add a few personal -related
1: The importance of bind variables
using bind variables to see whether the case may be , in most cases , bind variables will reduce the number of hard analysis , reducing the size of the shared pool , often NA bind variables are system -based energy flawed, but in the 11g before ( in 11g has extended cursor sharing ) , if used in a where clause bind variables , CBO will look into the depths bind variable values , which may lead to choose an inefficient execution plan. Therefore, if a sql processing large amounts of data as much as possible not to use bind variables where .
there are even using bind variables , there are several cases , oracle does not use the shared pool shared cursor
1) Father vernier different , even if the sql statement is the same, but more spaces , but also will have a new parent cursor
2) changes in the execution environment , the parent cursor under the same circumstances if the execution environment is different , it will generate a different child cursor , so it will not use the shared cursor
2: a large number of open cursors commit, a large number of commit can improve performance ?
This error is common in the batch update , we can try, if you undo segment is not big enough , batch update , when a large number of commit, will inevitably result in ora-01555
If you are in a large number of open cursors commit, will encounter this error, and this error is not reversible .
Many people will feel that commit will release a lot of resources that can improve performance, but prior to actually commit , oracle refresh has completed most of the work output , commit the time just to do some finishing work , into the block cleanup , release the lock and the like, instead of repeatedly commit more expensive , so the correct things become a habit is a submit only once
------ Solution ------------ --------------------------------
------ Solution ---------------- ----------------------------
truncate need to commit after
truncate after the resumption
The following article has more detailed steps.
http://space.itpub.net/12778571/viewspace-341815
- ----- Solution --------------------------------------------
truncate not commit ...
Flashback Database can flash back to the state before
can scn or time flashback
------ Solution ---------------------------- ----------------
studied .
------ Solution ---------------------------------------- ----
1: pagination does not have to calculate rownum, you can use the Analysis function row_number () over (partition by order by), ( do not know the difference between efficiency )
2: large volumes of data may not necessarily be a full table scan , according to the data you want to check the percentage of accounts for the entire table , CBO will choose to take the index or full table scan
------ Solution ------------------------------------ --------
8. even kill do not be afraid , Oracle 9i began to support the Flashback Query to recover accidentally deleted data . (truncate also can reply , but a prerequisite )
In addition to flashback database, what method can recover truncate data , please enlighten.
------ Solution ---------------------------------------- ----
Thank landlord !
------ Solution ---------------------------------------- ----
... afternoon reason so many people turned out to be a sudden someone advertising a wonder
------ Solution -------------- ------------------------------
+ +
------ Solution ---------------------------------- ----------
1: Restoring from backup
2: offline immediately , with some analytical tools to extract data data files
3: logmnr parse the log file, locate the redo information before the insert , Oh , more nonsense
------ Solution ------------------ --------------------------
collection , thank you share
------ Solution ------ --------------------------------------
ran , learning ed.
------ Solution ---------------------------------------- ----
learn.
------ Solution ---------------------------------------- ----
good, learning a ~ ~ ~ ~
------ For reference only ----------------------- ----------------
Muzi students in the group do not know what is inside
------ For reference only ---------- -----------------------------
called Muzi
------ For reference only ---------------------------------------
not guess what is thy head
------ For reference only ---------------------------------------
landlord large amount of data What inquiries have experience more than 300W
------ For reference only ----------------------------- ----------
sister , data mining and processing of data in one hundred million level . G is the unit commonly used .
------ For reference only ---------------------------------- -----
some of the top , just to see one or a sql query , I can think of , did not keep such optimizations script that I made up the next post will be finishing .
------ For reference only -------------------------------------- -
millions of data , good table design , sub- districts, plus an index basically get on ,
------ For reference only ---------------------------------------
it is very good
one hundred million the amount of data paging query is how to operate
------ For reference only ---------------------------------- -----
ah ? ? Pagination ? you funny right, BROTHER go to Google under BI. In replies to this bar.
------ For reference only ---------------------------------- -----
Zaiding down immediately Results posted ~ ~
------ For reference only ----------------- ----------------------
what data mining sub- pages ah , but not to the web to provide data showing
------ For reference only -------------------- -------------------
Who is data mining the amount of data in the paging query oracle full table after more than 300W scan calculate the efficiency will decline rownum
------ For reference only ---------------------------------- -----
good good study, day day up
------ For reference only ------------------ ---------------------
Home invariably "We love to share ---" ......
------ For reference only ---------------------------------------
learn, dynamic Superman ! ! !
------ For reference only -------------------------------------- -
collection Thank
------ For reference only ------------------------------ ---------
Thank you sharing ~ ~ learn !
------ For reference only -------------------------------------- -
learn to get points scattered people
------ For reference only -------------------------- -------------
Q: What additive group needs verification code , verification code is the number ! ! !
------ For reference only -------------------------------------- -
table is locked in what should not kill to kill the process , you should find out the reasons to avoid the use of kill nothing
PS: formal environment database environment is generally not allowed for security reasons developer contact .
------ For reference only -------------------------------------- -
group numbers can give me no, " !
------ For reference only -------------------------------------- -
learn, thank you ha
------ For reference only ----------------------------- ----------
learning birds. .
------ For reference only -------------------------------------- -
can not be restored, because it is not written to the log .
If fear of accidentally deleted important data , use the delete, but the efficiency is not high.
------ For reference only ---------------------------------- -----
Thank you to share , to learn
------ For reference only ------------------------ ---------------
sharing , sharing
------ For reference only ----------------- ----------------------
ddddddddddddddddddddddd
------ For reference only ------------ ---------------------------
taught it. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
------ For reference only -------------------------------------- -
study, taught the
------ For reference only ----------------------------- ----------
accounted floor learning
------ For reference only ---------------------- -----------------
ODU with AUL or to read data files, there is the difficulty of this thing , you need to spend money . Haha
------ For reference only ------------------------------------- -
first collection .
------ For reference only -------------------------------------- -
learn
------ For reference only -------------------------------- -------
good, learning
------ For reference only ------------------------- --------------
Thank landlord learn it ~
------ For reference only -------------- -------------------------
Thank landlord learn it ~
------ For reference only --- ------------------------------------
good welcome to share, please scattered points.
------ For reference only -------------------------------------- -
learn !
------ For reference only -------------------------------------- -
learned
------ For reference only -------------------------------- -------
learn ...
------ For reference only ----------------------- ----------------
Thank you to share !
------ For reference only -------------------------------------- -
leave footprints after watching ------ For reference only --------------------------------------- < br> I see do not speak
------ For reference only ---------------------------------- -----
------ For reference only ---------------------------------------
really is good , LZ shocked What
------ For reference only ------------------------------------- -
top one first .
------ For reference only -------------------------------------- -
first collection
------ For reference only ------------------------------- --------
good, thank you to share
------ For reference only ----------------------- ----------------
make
------ For reference only ------------------ ---------------------
mark!
------ For reference only ------------ ---------------------------
a source, has to explain , I like
------ For reference only ---------------------------------- -----
Thanksgiving , learning
------ For reference only -------------------------- -------------
Thank you to share , learn
------ For reference only ----------------- ----------------------
learn
------ For reference only ----------- ----------------------------
collection · · ·
------ For reference only ---------------------------------------
learn
- ---- For reference only ---------------------------------------
plus groups are afraid to speak up
too many cattle XX individuals the
------ For reference only ---------------------------- -----------
expressed concern !
------ For reference only ------------------- --------------------
,
chef
------ For reference only -------------------------------- -------
learned ... top ...
------ For reference only ------------------- Thank you for sharing --------------------
------ For reference only ---------------------------------- -----
top . . . . . . . . .
没有评论:
发表评论