Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.
Hi all, please see this major site announcement: https://www.boards.ie/discussion/2058427594/boards-ie-2026

Oracle - Getting key information for DB analysis

  • 25-11-2011 03:41PM
    #1
    Jagera
    Closed Accounts Posts: 910 ✭✭✭


    I've long been developing database systems, but predominantly on Microsoft, and less so on Oracle. I'm currently working on an Oracle data warehouse and even without solid dba skills, its evident that the architecture of the database needs to be looked at. specifically: keys, indexes, partitions, tablespaces and statistics.

    I'm trawling through the various DBA_* views, but I'd like to know is there a way to pull back a good snapshot of a database or schema which could hint at ways to improve performance, from both a loading/ETL angle and user querying angle?

    I know this is a very open ended question, and if its not easily answerable, even a few key pointers would be appreciated.


Comments

  • #2
    mark renton
    Closed Accounts Posts: 2,696 ✭✭✭mark renton


    prob best to post this is oracle forums


  • #3
    Tom Dunne
    Registered Users, Registered Users 2 Posts: 23,202 ✭✭✭✭Tom Dunne


    Oracle Enterprise Manager has a number of tools for looking at performance, though there is no substitute for a good look "under the hood".

    A good start is always the explain plan of commonly used queries. See what tables and indexes queries are hitting.

    Statistics are always something worth looking at. If the statistics on a table are old, you would certainly get some performance improvement when they are updated.

    What version of Oracle is it?

    I'm a bit out of touch on the DBA front, but there is a load of information on Metalink (or My Oracle Support as they are calling it now) on performance.


  • #4
    Jagera
    Closed Accounts Posts: 910 ✭✭✭Jagera


    As said, I've already started looking under the hood at the DBA_ views to check all info on the database. I'm piecing together some queries, and am progressing slowly, but getting what I need.

    What I was hoping for (and you're both right. I'll go & look on Metalink) was if there was already some standard queries which return key information on tables/databases.

    I don't have access to any of the Oracle software (it's 10G btw) so I am looking for free stuff, or standard queries/procedures.

    The situation is, that we are given a database for data warehouse and BI purposes. The DBA's are not active in the operation of the database other than backups and space issues. Which is surprising, as it's a good 1-2TB database.


  • #5
    mark renton
    Closed Accounts Posts: 2,696 ✭✭✭mark renton


    you should also sign up on linkedin - and join a few of the oracle groups, there are some very useful topics and tips to be had


Advertisement
Advertisement