| What info should be gathered while doing sql tuning? [message #538857] |
Tue, 10 January 2012 00:41  |
snowball
Messages: 229
Registered: April 2006
Location: China
|
Senior Member |
|
|
Hi, guys
What will you collect the info if you have several sql to tunning?
Here is a list I collect the info of the sql:
1. execution plan;
2. the table records related to this sql;
3. bind variable values via v$sql_bind_capture;
I want to make sure the infomation I collected are mostly sactified that latter tuning. So at least, dont give client bad impression.
Any suggestion is welcome. Thanks very much.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: What info should be gathered while doing sql tuning? [message #541223 is a reply to message #541215] |
Mon, 30 January 2012 01:11  |
snowball
Messages: 229
Registered: April 2006
Location: China
|
Senior Member |
|
|
Hi, Michel
After trying to do with this way:
1. create a table for store statistics
SQL> EXEC dbms_stats.create_stat_table('SCOTT','SCOTT_STATS','USERS');
PL/SQL procedure successfully completed.
2. export the statistics to the created table
SQL> select count(*) from scott.t;
COUNT(*)
----------
3225728
11:12:01 SQL> exec dbms_stats.export_table_stats('SCOTT','T',NULL,'SCOTT_STATS','SCOTT_STATS1',TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
3. export the table
[oracle@rac1 ~]$ exp scott/tiger tables=scott_stats file=scott_stats.dmp log=scott_stats.log
Export: Release 10.2.0.4.0 - Production on Thu Jan 19 11:14:46 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table SCOTT_STATS 15 rows exported
Export terminated successfully without warnings.
4. get the related ddl
SQL> select dbms_metadata.get_ddl('TABLE','T','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','T','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
DBMS_METADATA.GET_DDL('TABLE','T','SCOTT')
--------------------------------------------------------------------------------
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
SQL> select dbms_metadata.get_ddl('INDEX','SCOTT_T_IND','SCOTT') FROM dual;
DBMS_METADATA.GET_DDL('INDEX','SCOTT_T_IND','SCOTT')
--------------------------------------------------------------------------------
CREATE INDEX "SCOTT"."SCOTT_T_IND" ON "SCOTT"."T" ("OBJECT_ID", "OBJECT_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
5. import the table to the target db
F:\dumpfile>imp scott/tiger file=scott_stats.dmp log=scott_imp.log
6. create related object with fetched ddl
SQL> CREATE TABLE "SCOTT"."T"
2 ( "OWNER" VARCHAR2(30),
3 "OBJECT_NAME" VARCHAR2(128),
4 "SUBOBJECT_NAME" VARCHAR2(30),
5 "OBJECT_ID" NUMBER,
6 "DATA_OBJECT_ID" NUMBER,
7 "OBJECT_TYPE" VARCHAR2(19),
8 "CREATED" DATE,
9 "LAST_DDL_TIME" DATE,
10 "TIMESTAMP" VARCHAR2(19),
11 "STATUS" VARCHAR2(7),
12 "TEMPORARY" VARCHAR2(1),
13 "GENERATED" VARCHAR2(1),
14 "SECONDARY" VARCHAR2(1)
15 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
16 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
17 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
18 TABLESPACE "USERS";
SQL> CREATE INDEX "SCOTT"."SCOTT_T_IND" ON "SCOTT"."T" ("OBJECT_ID", "OBJECT_NAME");
7. import the stastics from the table
SQL> exec dbms_stats.import_table_stats('SCOTT','T',NULL,'SCOTT_STATS','SCOTT_STATS1', TRUE);
Compare with the target and the source db's execution plan, I found a little bit differences.
sql 1 in product:
SQL> select object_id, object_name from t where object_id < 100;
6272 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3140600170
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5227 | 153K| 32 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| SCOTT_T_IND | 5227 | 153K| 32 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"<100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
441 consistent gets
21 physical reads
0 redo size
109627 bytes sent via SQL*Net to client
5090 bytes received via SQL*Net from client
420 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6272 rows processed
sql1 in my db:
SQL> select object_id, object_name from t where object_id < 100;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3140600170
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5227 | 153K| 0 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| SCOTT_T_IND | 5227 | 153K| 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"<100)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
341 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
I notice that the Statistics session will different with product and the cost in my db is always 0.
I remember that you remind me the export the system statistics
Quote:
I forgot to mention, export and import also system statistics (same package).
And what's kind of system statistics need to be exported and imported?
Will this action will fix the problem I mentioned?
Sorry for a little bit delay respond.
Thanks very much.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
