OraFAQ Forum: Suggestions & Feedback => OraFAQ Forum Guide

OraFAQ Forum Guide

Introduction

Welcome to OraFAQ Forum! Please, read this document before you start to actively participate. Here are guidelines of how to behave on the Forum in order to make it a cozy place to stay.

Some of the most important instructions are:

Be polite!
Never belittle anyone for asking beginner-level questions or for their English skills.
Use English language in the main forum (non-English forums are also provided - see bottom of list) and DO NOT use IM-speak!
Provide all relevant information about your problem, including Oracle software version (4 decimal places) and operating system version.
Format your code and make sure that lines do not exceed 80 characters. Use the "Preview Message" button to check it.

Click on a section title to expand its text.

How to get a quick answer to your question?

Useful sites

Posting guidelines

Posting guidelines

Theory

Have the courtesy to write in plain English; it is not the first language of most of the participants to this forum, but that is no excuse for poor or non-existent punctuation.
- If everyone used standard punctuation, like spaces between words and sentences, capitals for the first word of a sentence, a full-stop (period) to mark the end of a statement or a question mark to terminate a question then your requests will be more easily understood and your questions answered more clearly and quickly. Got it? Try it, it isn't that hard.
- Also, stop putting every phrase in a new paragraph, it makes entries deep and hard to read.
People don't feel encouraged when they have to decipher some gobbledygook.

Abbreviations like "U", "Ur", "U r", "Plz", "prb", "qry" especially tend to get on people's nerves.
IM speak
Instant Messager language, like the (non-exhaustive) list below
- u = you
- your = you are
- any one = anyone
- grt = great
- pls/plz = please (especially avoid this in topic titles)
- ugt = urgent (especially avoid this in topic titles)
- wrt = write
- i = I
- im = I am
is not appreciated:
- It is hard to read.
- It is unprofessional.
- It doesn't show much respect towards your fellow forum members.
If you don't take time to write in proper English, how can you expect that others would answer properly?
Always spell check and proof read your messages before posting them.
As a rule of thumb: be polite. No one is paid for answering your question. The least you can do is show some respect to those who are willing to dedicate some of their spare time to look at your problem.
If you feel that a message is not admissible for some reason (offending language/opinions, personal attacks, ...), you can report it to the moderator staff. But if you want extra attention for your topic, reporting will act counter-productive.
Don't use uppercase only. It's harder to read and gives the impression you're shouting.
Choose a topic title that makes sense. A good title might be "ORA-1555 during pl/sql loop"
A less fortunate choice might be "URGNT PLZ!!!!!!!!!!!!"

Avoid the following keywords: urgent/please/help/now/immediately (and derivatives)
Choose the appropriate forum. If in doubt, don't worry - moderators can always move it
Don't cross-post. Duplicates will be removed.
Commercial posts are admitted only in the Marketplace Forum.
- As a corollary, do not post a link to your site or blog unless it answers the question.
Post exact Database (4 decimal places, please) and OS version. By doing so, you'll avoid reactions using features you can't use.

Practice

Show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session), including errors and/or why the result is not what you want.
Do not describe, explain or report - show us!
Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
Provide your expected result set and explain the rules/reasons that lead to it.

If you want to post error codes or SQL*Plus output, just copy everything that is on your screen when the error occurred, for instance:

SQL> insert into t2 values (t2_s.nextval, x1) returning id into :x2;
insert into t2 values (t2_s.nextval, x1) returning id into :x2
*
ERROR at line 1:
ORA-00984: column not allowed here

Do not describe your problem and/or solution in MS Office Word, Excel or similar files and attach them to your message. Some people can not, some do not want to download them. Plain TXT files are acceptable.
Be patient. A lot of people here are just browsing the board once a day.
Don't think you have more chance if you PM individual members. Just post your question in public: more people can see/answer them that way. Most PM'ed questions are posted to the forum anyway.
Again, forum members are not here to do your work for you nor to do your homework assignment for you. If you are stuck, we will gladly help you out.
Don't "wild post", i.e. post a reply that has nothing to do with the original thread.
If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.

How to format your post?

How to format your post?

If you are providing code snippets such as SQL*Plus copy/paste, please be sure to use formatting tags:

[code] Enter your code here.[/code]

It makes a huge difference to the forum readers. Unreadable code makes a question harder to understand and will delay answers. A lot of people don't spend that much time on the forum and unclear posts will be skipped more often because they take a lot more time to decipher.

The difference between the code blocks below is clear:

First a block without proper tags, you'll notice that it is not that readable:

DECLARE
CURSOR yourcursor
IS
SELECT yourcolumn
, another_column
FROM yourtable
WHERE some_column BETWEEN A AND B
AND some_other > SYSDATE;
BEGIN
FOR a_record IN yourcursor
LOOP
do_something_here;
IF a_record.another_column = 1
THEN
do_extra_stuff;
ELSE
do_other_stuff;
END IF;
END LOOP;
END;

Now, the same PL/SQL block this time with use of the code tags. It clearly delineates the code from the other text and preserves text indenting.

 DECLARE
  CURSOR yourcursor
      IS
  SELECT yourcolumn
       , another_column
    FROM yourtable
   WHERE some_column BETWEEN A AND B
     AND some_other  > SYSDATE;
BEGIN
  FOR a_record IN yourcursor
  LOOP
    do_something_here;
    IF a_record.another_column  = 1
    THEN
      do_extra_stuff;
    ELSE
      do_other_stuff;
    END IF;
  END LOOP;
END;

You can also paste the code in your message, select it and hit this button

http://www.orafaq.com/forum/theme/orafaq/images/b_code.gif

.

This is how the post form screen looks like and the way [code] tags are to be used:

./fa/5759/0/

What if the code is not formatted itself? Code tags will do little about that. So you first have to format the code

by hand
via an external tool (like TOAD)
via the SQL Formatter page of OraFAQ.

Make sure that lines of code do not exceed 80 characters. Use the "Preview Message" button to check it.

Scott Mackey has created a nice little document explaining in detail and with screencaps how to format. The document can be downloaded/viewed here.

Responding to Posts

Performance Tuning

Performance Tuning

Please consider the following when tuning your query:

The first step is to read the Oracle Performance Tuning Guide in its entirety. It describes the different access paths and teaches you how to use explain plan and tkprof to tune your query. (In Oracle 10g, it also covers the new 10g automated tuning features that may be at your disposal.) While you are reading, you can proceed with the remaining steps below.
The second step to tuning your query is to gather comprehensive statistics, and re-gather them every time your data undergoes significant change. This means using dbms_stats with cascade => true and a method_opt of at least 'for all indexed columns size 250'.
The third step is to make sure that all of the columns that should not / will not contain null values are set to not null, that all columns that are used as primary and foreign keys have a primary or foreign key constraint on them, and seriously consider putting indexes on all foreign key columns.
The fourth step is to re-evaluate your physical database design. Ideally, this step was conducted in development, before the system went live. But regardless, consider all of the available oracle data structures (heap tables, index organized tables, hash clusters, btree clusters, btree indexes, bitmap indexes, partitions, materialized views, and compression).
The fifth step is to maintain your system at a reasonably up to date patch and version level. Each new version eliminates many older bugs, provides added functionality that you can use for performance, and typically has inherent optimizations at the internal level that cause your existing code to run faster. At the time of this writing, you should be at, or currently upgrading to, at least version 10gR2.

During the tuning process, keep in mind the following:

Full Table Scans are not always bad, and Index Scans are not always good.
Routinely rebuilding btree indexes often does more harm than good. Only take action for a reason, and measure/evaluate whether your goal was achieved by the action you took.
Unless you have a particularly strange circumstance with a single query, and have exhausted all other options, it is generally best to only provide informational hints (such as cardinality, first_rows, and all_rows), rather than to provide specific access plan hints (like full and index).

All about braindumps

Marketplace

You have an SQL Question but you don't use Oracle?

Acknowledgments

OraFAQ Forum Guide History