Skip to Main Content

Japanese

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

USB接続の外部メディアHDDドライブへのバックアップについて

user12177367Apr 18 2011 — edited Jun 28 2011
WINDOWSServer2003、orecle10G Version: 10.2.0.3.0.環境で
一日一回AM01:00にバッチファイルで下記パラメータファイルを起動して
USB接続の外部メディアHDDドライブへのバックアップを実施しています。

パラメータファイルの内容
--------------------------------
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup incremental level 0 database plus archivelog delete all input;
delete noprompt obsolete;
crosscheck backup;
delete noprompt expired backup;
--------------------------------

3ヶ月に1回程度、サーバのフリーズや上記外付けUSB接続HDDドライブを
サーバが認識できなくなることがあります。

外付けUSB接続のHDDはバッファロー製です。
(USB2.0でシリアルATAインターフェース3.5inchHDD 500GB)

サーバのハード不良やアプリケーションのエラーは無い為、
USB接続のメディアにバックアップをとる場合、
内蔵ドライブよりも処理速度が遅い為に上記障害が
発生しているのではないかと推測していますが、
原因特定できていません。

USB接続の外部メディアへのバックアップで
上記障害発生した場合の対策をご存知な方が
いらっしゃいましたらアドバイスをお願いします。

Comments

Frank Kulash

Hi, @user-r4vjd
How can we write this code efficiently in oracle 11g
Don't use cursors unless you really need to. Depending on what you're trying to do, you can do it in a single query. If you really do need to use a cursor, use only one; combine the two cursors into one. Exactly how depends on your data and your requirements. If you'd care to post some sample data (CREATE TABLE and INSERT statements) for all tables involved, the results you want from that sample data, and explanation (in general terms) of how you get those results from that data, and the code behind the other cursor, then I could try to show you exactly how to do it.

User_R4VJD

Please avoid that cursor details.
Just help in writing code efficiently

GregV

Hi,
As Frank said, don't use cursors if you don't need to, especially here as you seem to be interested only in getting a max date.
Combine both queries and retrieve the max dates directly.
By the way, are you sure about this kind of condition:
AND T1_cln =NVL (p_vent, '%')
Did you mean a LIKE operator since you're using '%', or is '%' really stored as is?

Jonathan Lewis

There is no purely logical reason why that code should be inefficient, given the correct constraints and indexing there would be an extremely efficient execution path to produce the required result.
It's possible, however, that in your specific case the data model means the relevant constraints are not valid.
Note that the "via = (select from T2)" means the subquery MUST logically be able to return exactly one row. (If it returns more than one then Oracle will raise an error, if it returns no rows you have effectively got a predicate "via = null" which does not evaluate to true (this is not an error, but it might not be what you were expecting).
As the previous two responses have implied - is this query inefficient, or is it simply that the surrounding pl/sql cursor approach means it executes a very large number of times, accumulating a much greater volume of work that seems reasonable.
Regards
Jonathan Lewis

EdStevens

Please avoid that cursor details.
Just help in writing code efficiently
But it is most likely that the cursor IS the source of any inefficiency.
I'm always amazed when people ask for help solving a problem, but try to take the most likely solutions off the table.

BluShadow

We don't have your data, nor have you provided details of the cur1 and cur2 cursors, nor shown the full PL code nor described the logic you are trying to achieve, so that kinda makes it hard for us to help fully.
From what I can gather, you've got cur1 as a main cursor, then cur2 is likely a cursor that opens up inside that cur1 cursor based on some related data from cur1, and then you're querying the MAX value from T1 using some related data in T2 based on both of the cursors.
As others have already said, having cursor looping is more likely to slow things down as you'll be issuing your inner query for each loop of the inner cursor. This is kind of "row by row" or otherwise known as "slow by slow" processing.
If you can, you should consider combining your cursors into a single SQL query, something along the lines of...

SELECT T1.c_cd, T1.d_cd, T1.l_cd, T1.l_sub_1, max(T1.generation_date)
FROM   (<query of cur1>) cur1
       JOIN (<query of cur2) cur2 on (<whatever join conditions related cursor 2 to cursor 1)
       JOIN T1 on (    T1.c_cd = cur1.c_cd
                   AND T1.d_cd = cur1.d_cd
                   AND T1.l_cd = cur1.l_cd
                   AND T1.T1_sub_l = cur2.sub_lat
                  )
       JOIN T2 on (    T2.via   = T1.via               -- equivalent via comparison
                   and T2.l_cd  = T1.l_cd              -- T1.l_cd = cur1.l_cd
                   and T2.sub_l = T1.T1_sub_l          -- T1.T1_sub_l = cur2.sub_lat
                   and T2.cln   = T1.T1_cln            -- T1.T1_cln = NVL(p_vent, '%')
                  )
WHERE  T1.T1_cln = NVL(p_vent, '%')
AND    T2.p_cd   = NVL(p_pri_code, '%')
GROUP BY T1.c_cd, T1.d_cd, T1.l_cd, T1.l_sub_1

Your "via = (select via from T2 where ...) was essentially using many of the same conditions from your cursors as you were using with T1, so it makes more logical sense to just treat it as a join to T2 instead using the values you've already compared with T1.
Then bring in your cursors as the main drivers of the query, starting with cur1, joining that to cur2 on whatever conditions are needed to relate them, and then join T1 to the results of those.
You'll then get results of your MAX for all the T1 records in one go, so you'll need to include the columns from T1 in the select and group by them, so you've got the MAX for each one.
Whether you stick all that in one cursor and process that one by one is up to you... depends what you're doing with those results.

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 26 2011
Added on Apr 18 2011
6 comments
2,941 views