some queries
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: some queries

  1. #1
    Join Date
    May 2001
    Location
    London
    Posts
    149
    Hi DBA's

    I have 3 queries.

    1. How will you find at the end of the day which are the expensive queries??
    How will I find that??

    2. Suppose Instance is crashed due to Shutdown Abort .. How Oracle will come to know at next startup
    that it need Instance Recovery??

    3. Suppose I have installed Unix on server and I am planning to Install Oracle on server??
    what will be my major responsibilities/decisions as a DBA before installation of Oracle??


    Regards,

    Paresh

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    1) look in V$SQLAREA for queries having a lot of buffer_gets, executions, and disk_reads. it will give you the most consuming requests in memory, those which were the most executed, and those which made the most disk accesses.

    2) it knows, don't worry, and you won't even notice that a crash recovery occured if you do not look in alert.log file

    3) - get a filesystem with some space (about 1Gb for Oracle itself, and the size you wish for data)
    - change kernel memory settings, according to your OS and version of Oracle
    - create group dba and user oracle
    - get a X station
    - click, click, click

  3. #3
    Join Date
    May 2001
    Location
    London
    Posts
    149
    Thanks Pipo .

    1. Thanks

    2. I wont worry about instance recovery which Oracle is doing.
    But One of my interview, that buddy asked me that question about Instance Rec. .Also if suppose Power failure occured,then also it will do Inst. Rec.How it is come to know that Inst. Rec. is requied?????

    3. I know the Installation of Oracle on Unix. but how will I determine size of database , size of db block , no of datafiles, size of all files etc....

    4.In our DB , there should be diff. tablespace for data, index , rollback segs, temp, tools...this is to follow OFA, and to reduce I/O contention on files. But what r adv. of this OFA other than to reduce I/O contention??

    Thanks

    Paresh

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    2) because of many things, the most evident one being the presence of a lkSID file in $ORACLE_HOME/dbs (or %OARCLE_HOME\database for NT users)

    3) it depends on the usage you wish to make of this instance ... if you have no precise idea but testing, choose a db_block_size of 8k, a SYSTEM tablespace of 100M, a TEMP tablespace of 100M, a RBS tablespace of 60M, take 2 tablespace like DATA and INDEX, 500M and 200M, and you should have something allowing you to do what you want to test ... take 1 datafile per tablespace if possible (unless you need tablespaces bigger than 2Gb).

    4) well OFA is more a generic proposition to reduce I/O than something else. It just explains you that it is advisable to spread your datafiles on several disks, trying not to have data and indexes on the same, and separating RBS and TEMP tablespaces from the others because there are lots of I/O, but it's only common sense : do not put all high activity files on the same disk (unless you only have one)

  5. #5
    Join Date
    May 2001
    Location
    London
    Posts
    149
    2. It only contains
    "DO NOT DELETE THIS FILE!" foll message.
    what do u mean from that??

    4. In OFA , u are telling u must have all the files and tablespaces on diff disks.So If I have one tablespace and all datafiles of that tablespace in diff disks?? then what is the problem??

    Paresh

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Originally posted by pareshg
    Thanks Pipo .

    2. I wont worry about instance recovery which Oracle is doing.
    But One of my interview, that buddy asked me that question about Instance Rec. .Also if suppose Power failure occured,then also it will do Inst. Rec.How it is come to know that Inst. Rec. is requied?????
    Oracle uses something called SCN (System Change Number) and would record it on its control file. So when you perform a crash recovery, oracle would basically use the SCN to apply all the redo logs inorder to recover from the crash. more


    3. I know the Installation of Oracle on Unix. but how will I determine size of database , size of db block , no of datafiles, size of all files etc....
    Normally it depends on the type of operation you would be doing on the database. For OLTP it would be from 2-8K for DSS it would be 16K and etc...


    4.In our DB , there should be diff. tablespace for data, index , rollback segs, temp, tools...this is to follow OFA, and to reduce I/O contention on files. But what r adv. of this OFA other than to reduce I/O contention??
    Paresh
    I/O contention would drastically reduce your database performance. Appart from having multiple tablespaces, you should also remember to spread them across multiple disks. Otherwise you would get into hot spots...
    Thanx
    Sam



    Life is a journey, not a destination!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width