9i 十个技巧 ( 38 ).ppt

BMC Software Confidential Copyright 2002 Dave’s 10 Random Yet Useful Oracle Tips Dave Moore Architect BMC Software / Austin dmoore@ About Dave Software Architect, Java, PL/SQL Developer Frequent Contributor Oracle Professional Oracle Internals DBAZ Wrox Press Burleson Press Dave’s Top Ten #10 - The Evil Statement SQL insert into dual values (‘gotcha); 1 row created. In PL/SQL: date_var date := sysdate; Is the same as: select sysdate into date_var from dual; Guarding DUAL Place insert and delete trigger on DUAL to ensure rows cannot be added or deleted Catch exceptions in PL/SQL Good programming practice: select max(sysdate) from dual; -- guarantees 1 row Dave’s Top Ten # 9 - If Tracing a Session, Don’t Leave DBMS_SESSION.set_sql_trace_in_session Oracle will trace until you stop it, or the session goes away Trace files in the OS are the only indicator something is being traced. Rename trc files Tracing penalizes well performing sessions 9.2 V$SQL_PLAN Question What is a limitation of the DBA_FREE_SPACE view? Dave’s Top Ten # 8 - What Trace File is Mine? Dave’s Top Ten Instead of Worrying About … Hit Ratios # 7 - Profiles, Why Didn’t I Think of That ? Resource Limits can be set for: SESSIONS_PER_USER CPU_PER_SESSION CPU_PER_CALL CONNECT_TIME LOGICAL_READS_PER_SESSION LOGICAL_READS_PER_CALL PRIVATE_SGA COMPOSITE_LIMIT SQL Requirements SQL must perform under certain thresholds Developers should be informed of these – no surprises DBA should create default profiles: cartesian_product_authors people_that_like_my_speed_racer_tshirt the_sky_is_the_limit Dave’s Top Ten # 6 - SQL is My Language Use 9i external table feature as a file browser: create directory BDUMP AS d:\oracle9i\admin\PROD\bdump; create table alert_log (text varchar2(200)) organization EXTERNAL ( type oracle_loader default directory BDUMP access parameters ( records delimited by newline badfile dave.bad logfile da